Query is not working properly (RESOLVED)

[Polariss]

I am trying to store the end result of this query into a variable

user = txtUsername.Text
perms = "SELECT permissions FROM users WHERE username = '" & user & "'"

Any ideas

[vb_dba]

Use a recordset:

Dim oCon   As Connection
Dim oRS     As Recordset
Dim user    As String
Dim perms As String
Dim sSql    As String
 
user = txtUsername.Text
sSql = "SELECT permissions FROM users WHERE username = '" & user & "'"
 
'Open a Connection to the database
Set oCon = New Connection
oCon.Open strYourConnectionString
 
'Open the recordset
Set oRS = New Recordset
oRS.Open sSql, oCon, adOpenStatic, adLockReadOnly
 
'Assign the result to the variable
perms = oRS.Fields("permissions").Value
 
oRS.Close
Set oRS = Nothing
oCon.Close
Set oCon = Nothing

[Polariss]

Here is the whole code for the form

Dim mySQL As ADODB.Connection
 
Private Sub cmdLogin_Click()
Dim mrs As Recordset
Dim perms As String
Dim permQuery As String
Dim writeQuery As String
writeQuery = "INSERT INTO verify(username, permissions) VALUES ('" & user & "', '" & perms & "')"
permQuery = "SELECT permissions FROM users WHERE username = '" & user & "'"
user = txtUsername.Text
Set mrs = New Recordset
mrs.Open permQuery, mySQL, adOpenStatic, adLockReadOnly 'ERROR OCCURS HERE
perms = mrs.Fields("permissions").Value
mrs.Close
Set mrs = Nothing
If UserCheck(txtUsername.Text, txtPassword.Text) = True Then
        mySQL.Execute writeQuery
        mySQL.Close
        Set mySQL = Nothing
        frmMenu.Show
        Unload Me
    Else
        MsgBox "You have no account in our database", vbCritical
    End If
End Sub
 
Public Function UserCheck(strUserName As String, strPassword As String) As Boolean
 
    Dim rs As New ADODB.Recordset
    
    rs.Open "SELECT username, password FROM users WHERE username = '" & strUserName & "'", mySQL, adOpenKeyset, adLockBatchOptimistic
 
    If Not rs.EOF Then
        If rs("password") = strPassword Then
            rs.Close
            Set rs = Nothing
            UserCheck = True
            Exit Function
        Else
            rs.Close
            Set rs = Nothing
            UserCheck = False
            Exit Function
 
        End If
    Else
        rs.Close
        Set rs = Nothing
        UserCheck = False
        Exit Function
    End If
 
End Function
 
Private Sub cmdQuit_Click()
End
End Sub
 
Private Sub Form_Load()
Set mySQL = New ADODB.Connection
    mySQL.ConnectionString = ("Provider=MSDASQL;Driver={MySQL ODBC 3.51 Driver};Database=workorder;Server=server;UID=root;PWD=pass;OPTION=147458")
    mySQL.Open
End Sub

[Polariss]

Private Sub cmdLogin_Click()
Dim mrs As Recordset
Dim perms As String
Dim permQuery As String
Dim writeQuery As String
writeQuery = "INSERT INTO verify(username, permissions) VALUES ('" & user & "', '" & perms & "')"
permQuery = "SELECT permissions FROM users WHERE username = '" & user & "'"
user = txtUsername.Text
Set mrs = New Recordset
mrs.Open permQuery, mySQL, adOpenStatic, adLockReadOnly
        If Not mrs.EOF Then
            perms = mrs.Fields("permissions").Value
            mrs.Close
            Set mrs = Nothing
        End If
If UserCheck(txtUsername.Text, txtPassword.Text) = True Then
        mySQL.Execute writeQuery
        mySQL.Close
        Set mySQL = Nothing
        frmMenu.Show
        Unload Me
    Else
        MsgBox "You have no account in our database", vbCritical
    End If
End Sub

But now I have another problem with my writeQuery. Its returning 0 values in the database for the variables user and permissions.

[vb_dba]

You don't assign a value to the "user" variable until after you've already declared your permissions query:

Dim mySQL As ADODB.Connection
 
Private Sub cmdLogin_Click()
Dim mrs As Recordset
Dim perms As String
Dim permQuery As String
Dim writeQuery As String
 
'Move this declaration to here
user = txtUsername.Text
 
writeQuery = "INSERT INTO verify(username, permissions) VALUES ('" & user & "', '" & perms & "')"
permQuery = "SELECT permissions FROM users WHERE username = '" & user & "'"
 
Set mrs = New Recordset
mrs.Open permQuery, mySQL, adOpenStatic, adLockReadOnly 'ERROR OCCURS HERE
perms = mrs.Fields("permissions").Value
mrs.Close
Set mrs = Nothing
If UserCheck(txtUsername.Text, txtPassword.Text) = True Then
        mySQL.Execute writeQuery
        mySQL.Close
        Set mySQL = Nothing
        frmMenu.Show
        Unload Me
    Else
        MsgBox "You have no account in our database", vbCritical
    End If
End Sub

[Polariss]

Now it is returning a value for the variable user into the verify table however perms is still returning an empty value.

Private Sub cmdLogin_Click()
Dim mrs As Recordset
Dim perms As String
Dim permQuery As String
Dim writeQuery As String
user = txtUsername.Text
 
writeQuery = "INSERT INTO verify(username, permissions) VALUES ('" & user & "', '" & perms & "')"
permQuery = "SELECT permissions FROM users WHERE username = '" & user & "'"
 
Set mrs = New Recordset
mrs.Open permQuery, mySQL, adOpenStatic, adLockReadOnly
        If Not mrs.EOF Then
            perms = mrs.Fields("permissions").Value
            mrs.Close
            Set mrs = Nothing
        End If
If UserCheck(txtUsername.Text, txtPassword.Text) = True Then
        mySQL.Execute writeQuery
        mySQL.Close
        Set mySQL = Nothing
        frmMenu.Show
        Unload Me
    Else
        MsgBox "You have no account in our database", vbCritical
    End If
End Sub

Am I not doing something right with the recordset? It seems to keep returning a null value.

[Polariss]

Just moved the other line of writeQuery and now its returning a good value for permissions. Thanks for all your help Chris