code runs correctly on second try

New to XOJO, coming from vfp. Have a modal form with a textbox. The user inputs a new name to add to a table. The textbox lostfocus event runs code to check if that name exists in a table. If it does, a message is displayed, the text in the textbox is cleared and the user must enter a different name. The code does not run correctly the first time and allows duplicate names to be added, but if I type in the name a second time it works. I have the same problem with a delete button. This leads me to believe that it is a problem with how the window is set up, and not the code, but I’m just not sure.

Posting the relevant code would help us help you.

Code:

Lost focus code for newfield textfield:

[code]Me.Text = Uppercase(Me.Text)
nftext = Uppercase(Me.Text)

'Buyer#
If schoice = “B” Then
DoubleCheck
End If[/code]

Method Doublecheck:

[code]Dim drs As RecordSet
Dim ddrs As RecordSet
Dim dsql As String
Dim ddsql As String

‘Buyer#
If schoice = “B” Then
dsql = "SELECT mem_id, buynum FROM buy_num WHERE buynum =’"+nftext+"’"
drs = clas.SQLSelect(dsql)
If drs.Field(“buynum”).StringValue = (nftext) Then
dcheck = False
If dcheck = False Then
ddsql = “SELECT lname,fname FROM member WHERE id =’”+ drs.Field(“mem_id”).StringValue+"’"
ddrs = clas.SQLSelect(ddsql)
MsgBox("Buyer # in use by: “+ddrs.Field(“lname”).StringValue+”, "+ddrs.Field(“fname”).StringValue)
NewField.Text = " "
NewField.SetFocus
End If
else
dcheck = True
SaveButton.Visible = True
End If
End If

CancelButton code:
AddButton.Visible = True
DeleteButton.Visible = True
ExitButton.Visible = True

SaveButton.Visible = False
CancelButton.Visible = False
NewField.Visible= False
NewLabel.Visible = False
ChoiceBox.Visible= False
ChoiceLabel.Visible = False

NewField.Text = " "[/code]

Some of this code is redundant, as I have been playing with it for a while trying different things. It works great the second time. The first time I run it, I can enter a name that I know has been used and the program will allow me to save it. If I click the cancel button and try to add the same name again the program functions correctly giving me the message and will not allow me to save it. All of the variables, and the sql statements look the same for the first and second pass. When I check on the first pass dsr recordset is nil. It should not be, and this is where things are going wrong. On the second pass dsr has the correct table values. I also have a delete button to remove records. Ths button will not work until I do something else first (press add button) after doing this the code works fine. I will focus on the code above because whatever I’m doing wrong, is probably the same thing. So the problem is my sqlselect statement is not functioning on the first pass.

Edit (Paul): Added code tags as described in Post Formatting Tips

Try changing

If drs.Field("buynum").StringValue = (nftext) Then

To

If drs.EOF = False Then

Also using the code tag will make reading your post much easier.

You are doing the DOUBLECHECK when the textfield LOSES FOCUS?

Don’t…

Add code to KEYDOWN and call it when the user presses TAB or ENTER or RETURN

and use PREPARED STATEMENT or risk SQL Injection

+1

Charles Pugsley Code:

Lost focus code for newfield textfield:

[code]Me.Text = Uppercase(Me.Text)
nftext = Uppercase(Me.Text)

'Buyer#
If schoice = “B” Then
DoubleCheck
End If[/code]

[code]Method Doublecheck:

Dim drs As RecordSet
Dim ddrs As RecordSet
Dim dsql As String
Dim ddsql As String

‘Buyer#
If schoice = “B” Then
dsql = "SELECT mem_id, buynum FROM buy_num WHERE buynum =’"+nftext+"’"
drs = clas.SQLSelect(dsql)
If drs.Field(“buynum”).StringValue = (nftext) Then
dcheck = False
If dcheck = False Then
ddsql = “SELECT lname,fname FROM member WHERE id =’”+ drs.Field(“mem_id”).StringValue+"’"
ddrs = clas.SQLSelect(ddsql)
MsgBox("Buyer # in use by: “+ddrs.Field(“lname”).StringValue+”, "+ddrs.Field(“fname”).StringValue)
NewField.Text = " "
NewField.SetFocus
End If
else
dcheck = True
SaveButton.Visible = True
End If
End If[/code]

CancelButton code:

[code]AddButton.Visible = True
DeleteButton.Visible = True
ExitButton.Visible = True

SaveButton.Visible = False
CancelButton.Visible = False
NewField.Visible= False
NewLabel.Visible = False
ChoiceBox.Visible= False
ChoiceLabel.Visible = False

NewField.Text = " "[/code]

Also, please learn about SQL injection, your code is an open door for hackers.

+20

Thanks for you responses.
Changed the code as suggested to: If drs.EOF = False Then

[code]Dim drs As RecordSet
Dim ddrs As RecordSet
Dim dsql As String
Dim ddsql As String

‘Buyer#
If schoice = “B” Then
dsql = "SELECT mem_id, buynum FROM buy_num WHERE buynum =’"+nftext+"’"
drs = clas.SQLSelect(dsql)
‘If drs.Field(“buynum”).StringValue = (nftext) Then
If drs.EOF = False Then
dcheck = False
If dcheck = False Then
ddsql = "SELECT lname,fname FROM member WHERE id =’"+ drs.Field(“mem_id”).StringValue+"’"
ddrs = clas.SQLSelect(ddsql)
MsgBox("Buyer # in use by: “+ddrs.Field(“lname”).StringValue+”, "+ddrs.Field(“fname”).StringValue)
NewField.Text = " "
NewField.SetFocus
End If
else
dcheck = True
SaveButton.Visible = True
End If
End If
[/code]
No difference.
My very first post, sorry about code tags.

Added the code to KEYDOWN, in this case I used the TAB key.

If Keyboard.AsyncKeyDown(&h30) Then
  //Tab
  'Me.Text = Uppercase(Me.Text)
  'nftext = Uppercase(Me.Text)
  
  'Buyer#
  If schoice = "B" Then
    DoubleCheck
  End If
End If

No difference.

I understand about SQL Injection. At this point I’m just playing with code to see if XOJO is right for me.
and am not worried about it.

The problem: The first time the keydown event fires and runs the DoubleCheck method, drs is NIL. I am using a name that I know has been used before, so drs should not be nil. If I cause the keydown event to fire a second time and I use the same name, drs is not nil and contains information from the correct record. There is something causing drs = clas.SQLSelect(dsql) to not run correctly on the first try.

Why not check for a database error after the SQL?
Check what nftext contains when it runs for the first time.
Use the debugger and check the status of variables, and error conditions after the SQL statement is executed.

My best guess is that first time round, nftext contains rubbish
Or that the database in clas is not initialised at the point, but is afterwards.

We have no sign here of how clas is set up, when it is initialised etc…
But the debugger will tell you for certain, while we are just guessing.

My application consists of 4 windows. A main window with buttons to select what you want to do. A search window where the user finds the correct customer and selects them, or adds a new customer. A customer window which gives detailed information about the customer. On the customer window there are several buttons which allow them to edit specific details. When one of these buttons is pushed the edit window opens and depending on which button was pushed formats for that specific function.
So I start the program, the main window opens. I click the customer button and am taken to the search screen. I select the correct customer and am taken to the customer screen. So far so good.
Once on the customer screen is open I select the accounts button and the edit window opens. In this case the window has a list box which is filled from the clas db buy_num table, and shows any existing account numbers the customer has. So far so good.
I now have the choice to exit, delete, or add a new account number. exit and delete are great. When I click add new button the newField text box opens and I type in the new account. When I tab out of the field the method DoubleCheck fires and creates a new recordset drs from clas db, buy_num table. If the RS drs is nil, the save button appears and I save the new account. If RS drs isn’t nil a message box tells me the account name is in use, clears the newField text box, and sets focus to it so I can try again.

I have checked nftext it is the same first time and second time. I have used ADAMS which I know is in use.
I have checked the dsql and it is the same both times
I have checked other variables that may have a bearing on DoubleCheck, and they are fine.
I have checked for errors as best I can with nothing reported.
As I have already accessed clas.buy_num to get the RS to fill the listbox, I don’t see a problem there?
As stated code works great the second time I try to run it after I cancel the first attempt.
I use a method - Populate to open the edit window code below.
Method Populate

Dim sql As String

'Buyer#
If schoice = "B" Then
  TitleLabel.Text = "Edit Member's Buyer #'s"
  TitleLabel.TextSize = 16
  TitleLabel.Bold = True
  SavedLabel.Text = "Saved Buyer #'s"
  SavedLabel.Bold = True
  
  sql = "SELECT mem_id, buynum FROM Buy_Num WHERE mem_id ='"+ Str(currentId)+ "'ORDER BY buynum "
  ers = clas.SQLSelect(sql)
  If ers <> Nil Then
    SavedBox.DeleteAllRows
    While Not ers.EOF
      Savedbox.AddRow(ers.Field("mem_id").StringValue)
      SavedBox.Cell(SavedBox.LastIndex, 1) = ers.Field("buynum").StringValue 
      ers.MoveNext
    Wend
  End If
  SavedBox.SetFocus
End If

Is this somehow not allowing the DoubleCheck method from running correctly the first time? I’m missing something.

There needs to be a space between ’ & ORDER BY". You can check for SQL errors by

sql = "SELECT mem_id, buynum FROM Buy_Num WHERE mem_id ='"+ Str(currentId)+ "' ORDER BY buynum " ers = clas.SQLSelect(sql) If clas.Error Then MsgBox("SQL error " + clas.ErrorMessage) End If If ers <> Nil Then

You search for the new account number. Then…

Ignoring your code samples, that is incorrect. drs is nil when there is an error, not when there are no matches.
When there are no matches, rs is valid, and rs.eof is true

If   not drs.error   and not  drs.eof   then

Because if your code is an error, rs will be nil, even if the account already exists.
In your current logic, then next thing you do is add the new account.
By which time it exists twice.

The code is now working correctly. I changed the DoubleCheck method to fire when the SaveButton is pushed instead of keydown event from newField text box. The final code I used is this:
DoubleCheck Method

[code]Dim dsql As String
Dim ddsql As String

If schoice = “B” Then
dsql = “SELECT mem_id, buynum FROM buy_num WHERE buynum =’”+TRIM(nftext)+"’"
drs = clas.SQLSelect(dsql)
If drs.EOF = False Then
ddsql = “SELECT lname,fname FROM member WHERE id =’”+ drs.Field(“mem_id”).StringValue+"’"
ddrs = clas.SQLSelect(ddsql)
MsgBox("Buyer # in use by: “+ddrs.Field(“lname”).StringValue+”, "+ddrs.Field(“fname”).StringValue)
NewField.Text = " "
NewField.SetFocus
ELSE
UpdateField
End If
End If
[/code]
This code only worked some times when firing from newField keydown event. I had placed code to check for sql errors, but never received a single error. It doesn’t make sense why this code didn’t work correctly, but with the help of the form members who responded I was able to learn some stuff, and think things through to find another way, hopefully a better way for my program to work. Thanks!

? This answers my question
Like