Perform Select and Insert on same table in database in a click event problem.

[BrailleSchool]

Error:
Run-time error '-2147217908 (80040e0c)':
Command text was not set for the command object.

Highlighted line is the line debug points to.

What I am trying to do is first check the database table to see if the username value exists, if it does, throw up a form. If it doesnt exist, then to add the record to the database. I am sure thats the part that is at fault here.

Thanks in advance for any help.

Private Sub cmdAddOperator_Click()
    Dim strSQL As String
    
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    conn.CursorLocation = adUseClient
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source =Database.mdb;" & _
               "Persist Security Info=False"
    
    [b][color=red]rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic[/color][/b][color=red][/color]
    
    strSQL = "SELECT Username "
    strSQL = strSQL & "FROM Users "
    strSQL = strSQL & "WHERE Username = '" & txtOperatorUsername.Text & "')"
 
    If txtOperatorUsername.Text = rs.Fields("Username").Value Then
        frmUsernameExists.Show
   ElseIf Val(txtOperatorPassword2.Text) <> Val(txtOperatorPassword.Text) Then
        frmOperatorPasswordNotSame.Show
    Else
        strSQL = "INSERT INTO Users "
        strSQL = strSQL & "(id,Username,Password,Operator,FullAccess) "
        strSQL = strSQL & "VALUES ('', "
        strSQL = strSQL & "'" & txtOperatorUsername.Text & "', "
        strSQL = strSQL & "'" & txtOperatorPassword.Text & "', "
        strSQL = strSQL & "'" & txtOperatorName.Text & "', "
        strSQL = strSQL & "'" & cboCompleteAccessYesNo.Text & "')"
    End If
 
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

[szlamany]

You have not build the STRSQL string yet - you cannot open the recordset.

Right?

[BrailleSchool]

You have not build the STRSQL string yet - you cannot open the recordset.

Right?

'CHECK DB FOR USERNAME ALREADY EXIST
strSQL = "SELECT Username "
    strSQL = strSQL & "FROM Users "
    strSQL = strSQL & "WHERE Username = '" & txtOperatorUsername.Text & "')"
'IF USERNAME EXIST, INFORM END ADMIN
    If txtOperatorUsername.Text = rs.Fields("Username").Value Then
        frmUsernameExists.Show
'IF PASSWORDS DONT MATCH, INFORM END ADMIN
   ElseIf Val(txtOperatorPassword2.Text) <> Val(txtOperatorPassword.Text) Then
        frmOperatorPasswordNotSame.Show
    Else
'IF EVERYTHING IS FINE, INSERT THE RECORD
        strSQL = "INSERT INTO Users "
        strSQL = strSQL & "(id,Username,Password,Operator,FullAccess) "
        strSQL = strSQL & "VALUES ('', "
        strSQL = strSQL & "'" & txtOperatorUsername.Text & "', "
        strSQL = strSQL & "'" & txtOperatorPassword.Text & "', "
        strSQL = strSQL & "'" & txtOperatorName.Text & "', "
        strSQL = strSQL & "'" & cboCompleteAccessYesNo.Text & "')"
    End If

[dglienna]

Is that part of the other code? Looks like strSQL is not in scope. Is it declared privately somewhere else? Check it's value with a msgbox.

[BrailleSchool]

Is that part of the other code? Looks like strSQL is not in scope. Is it declared privately somewhere else? Check it's value with a msgbox.

its not declared as private anywhere
i also tried:

Private Sub cmdAddOperator_Click()
    Dim strSQL As String
    Dim strSQL2 As String
    
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    conn.CursorLocation = adUseClient
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source =Database.mdb;" & _
               "Persist Security Info=False"
    
    rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
    
    strSQL = "SELECT Username "
    strSQL = strSQL & "FROM Users "
    strSQL = strSQL & "WHERE Username = '" & txtOperatorUsername.Text & "')"
    conn.Execute strSQL
    
    If txtOperatorUsername.Text = rs.Fields("Username").Value Then
        frmUsernameExists.Show
    ElseIf Val(txtOperatorPassword2.Text) <> Val(txtOperatorPassword.Text) Then
        frmOperatorPasswordNotSame.Show
    Else
        strSQL2 = "INSERT INTO Users "
        strSQL2 = strSQL2 & "(id,Username,Password,Operator,FullAccess) "
        strSQL2 = strSQL2 & "VALUES ('', "
        strSQL2 = strSQL2 & "'" & txtOperatorUsername.Text & "', "
        strSQL2 = strSQL2 & "'" & txtOperatorPassword.Text & "', "
        strSQL2 = strSQL2 & "'" & txtOperatorName.Text & "', "
        strSQL2 = strSQL2 & "'" & cboCompleteAccessYesNo.Text & "')"
        conn.Execute strSQL2
    End If
    
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

and got the same error.
will try the msg box and see what happens
i tried the msgbox and it wouldnt let me get to it, as soon as the error comes up, i click close and the app closes, tried in VB and same thing happened.

[szlamany]

Ok - let's go slow here..

The code you posted is simply got a small problem.

The line it blows up on is because the prior line is in the wrong place - this is the correction.

Private Sub cmdAddOperator_Click()
    Dim strSQL As String
    
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    conn.CursorLocation = adUseClient
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source =Database.mdb;" & _
               "Persist Security Info=False"
    
    strSQL = "SELECT Username "
    strSQL = strSQL & "FROM Users "
    strSQL = strSQL & "WHERE Username = '" & txtOperatorUsername.Text & "')"

    rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
    '  Move the OPEN, that uses strSQL, to after the string is built
    '  Before the string is built makes it execute an empty command...


    If txtOperatorUsername.Text = rs.Fields("Username").Value Then
        frmUsernameExists.Show
   ElseIf Val(txtOperatorPassword2.Text) <> Val(txtOperatorPassword.Text) Then
        frmOperatorPasswordNotSame.Show
    Else
        strSQL = "INSERT INTO Users "
        strSQL = strSQL & "(id,Username,Password,Operator,FullAccess) "
        strSQL = strSQL & "VALUES ('', "
        strSQL = strSQL & "'" & txtOperatorUsername.Text & "', "
        strSQL = strSQL & "'" & txtOperatorPassword.Text & "', "
        strSQL = strSQL & "'" & txtOperatorName.Text & "', "
        strSQL = strSQL & "'" & cboCompleteAccessYesNo.Text & "')"
    End If

    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

[dglienna]

My bad. I should have said to put the msgbox before the open statement.

[BrailleSchool]

Ok - let's go slow here..

The code you posted is simply got a small problem.

The line it blows up on is because the prior line is in the wrong place - this is the correction.

Private Sub cmdAddOperator_Click()
    Dim strSQL As String
    
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    conn.CursorLocation = adUseClient
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source =Database.mdb;" & _
               "Persist Security Info=False"
    
    strSQL = "SELECT Username "
    strSQL = strSQL & "FROM Users "
    strSQL = strSQL & "WHERE Username = '" & txtOperatorUsername.Text & "')"

    rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
    '  Move the OPEN, that uses strSQL, to after the string is built
    '  Before the string is built makes it execute an empty command...


    If txtOperatorUsername.Text = rs.Fields("Username").Value Then
        frmUsernameExists.Show
   ElseIf Val(txtOperatorPassword2.Text) <> Val(txtOperatorPassword.Text) Then
        frmOperatorPasswordNotSame.Show
    Else
        strSQL = "INSERT INTO Users "
        strSQL = strSQL & "(id,Username,Password,Operator,FullAccess) "
        strSQL = strSQL & "VALUES ('', "
        strSQL = strSQL & "'" & txtOperatorUsername.Text & "', "
        strSQL = strSQL & "'" & txtOperatorPassword.Text & "', "
        strSQL = strSQL & "'" & txtOperatorName.Text & "', "
        strSQL = strSQL & "'" & cboCompleteAccessYesNo.Text & "')"
    End If

    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

If I type in a username that already exists, it shows the prompt like it is supposed to. When you click on the ok button, it clears the username field, then if you enter in a username that doesnt exist, then it is supposed to add the record (as long as the password fields match), but instead of adding it, I get the following error message (which i believe means username doesnt exist)

Also, if i complete the form fields with a username that doesnt exist, (with the passwords not matching or the passwords matching) then i get the same error message. ugh

What is supposed to happen is when you have completed the form and clicked on the add button, its supposed to check the db to see if the username exists, if it does, throw up a form to say it exists. If the username doesnt exist then its supposed to check to see if the passwords fields match. if the passwords fields dont match, then to inform user. If everything is fine (username doesnt exist in db and password fields match) then to add the record to the db.

run-time error '3021':
either bof or eof is true, or the current record has been deleted. requested operation requires a current record.

Update:
I changed part of the code to look like the following and still get the b/eof error:

If txtOperatorUsername.Text = rs.Fields("Username").Value Then
        frmUsernameExists.Show
    ElseIf Val(txtOperatorPassword2.Text) <> Val(txtOperatorPassword.Text) Then
        frmOperatorPasswordNotSame.Show
    ElseIf (rs.BOF And rs.EOF) = False Then
        strSQL = "INSERT INTO Users "
        strSQL = strSQL & "(id,Username,Password,Operator,FullAccess) "
        strSQL = strSQL & "VALUES ('', "
        strSQL = strSQL & "'" & txtOperatorUsername.Text & "', "
        strSQL = strSQL & "'" & txtOperatorPassword.Text & "', "
        strSQL = strSQL & "'" & txtOperatorName.Text & "', "
        strSQL = strSQL & "'" & cboCompleteAccessYesNo.Text & "')"
    End If

Complete code to date:

Private Sub cmdAddOperator_Click()
    Dim strSQL As String
    
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    conn.CursorLocation = adUseClient
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source =Database.mdb;" & _
               "Persist Security Info=False"
    
    strSQL = "SELECT Username "
    strSQL = strSQL & "FROM Users "
    strSQL = strSQL & "WHERE Username = '" & txtOperatorUsername.Text & "'"
    
    rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
    
    If txtOperatorUsername.Text = rs.Fields("Username").Value Then
        frmUsernameExists.Show
    ElseIf Val(txtOperatorPassword2.Text) <> Val(txtOperatorPassword.Text) Then
        frmOperatorPasswordNotSame.Show
    ElseIf (rs.BOF And rs.EOF) = False Then
        strSQL = "INSERT INTO Users "
        strSQL = strSQL & "(id,Username,Password,Operator,FullAccess) "
        strSQL = strSQL & "VALUES ('', "
        strSQL = strSQL & "'" & txtOperatorUsername.Text & "', "
        strSQL = strSQL & "'" & txtOperatorPassword.Text & "', "
        strSQL = strSQL & "'" & txtOperatorName.Text & "', "
        strSQL = strSQL & "'" & cboCompleteAccessYesNo.Text & "')"
    End If
    
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

I also did a msgbox on the select statement, it shows the select to be correct but when i click ok, i then get the b/eof error. so it is either the password field check or the insert. still working on it. select capture enclosed.

[dglienna]

You still are using VAL() on the passwords. And ths:

(rs.BOF And rs.EOF) = False

should be

Not rs.BOF And Not rs.EOF

except that if the record isn't found, you want to add it, not if a record was returned, right?

[dglienna]

Plus, don't you want an ADMIN to add people? Seems like anyone that tries to log in would be able to create a new account.

[BrailleSchool]

Plus, don't you want an ADMIN to add people? Seems like anyone that tries to log in would be able to create a new account.

the add/delete/edit operator options are only available to admins, when a reg user logs in, those options are not available.

[BrailleSchool]

You still are using VAL() on the passwords. And ths:


should be

Not rs.BOF And Not rs.EOF

except that if the record isn't found, you want to add it, not if a record was returned, right?

Yes, only add a record if username doesnt exist and passwords match. otherwise prompt user when username is found or passwords dont match. ill make the alterations now

Update:
I made the modification. If the username existed, I was informed. If i changed the username to a username that didnt exist, i got the bof/eof error msg. didnt make a difference if passwords matched or not and didnt get the prompt if the passwords didnt match.

[dglienna]

If you are using VAL() on a string, it won't work, rather they'd be 0

[BrailleSchool]

If you are using VAL() on a string, it won't work, rather they'd be 0

removed val()
still not able to get this to work so it seems ill have to do a two step process. check for username and if it doesnt exist, then to bring up the addition form.