I am trying to store the end result of this query into a variable
VB Code:
user = txtUsername.Text perms = "SELECT permissions FROM users WHERE username = '" & user & "'"
Any ideas
Printable View
I am trying to store the end result of this query into a variable
VB Code:
user = txtUsername.Text perms = "SELECT permissions FROM users WHERE username = '" & user & "'"
Any ideas
Use a recordset:
VB Code:
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
Here is the whole code for the form
VB Code:
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
VB Code:
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.
You don't assign a value to the "user" variable until after you've already declared your permissions query:
VB Code:
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
Now it is returning a value for the variable user into the verify table however perms is still returning an empty value.
VB Code:
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.
Just moved the other line of writeQuery and now its returning a good value for permissions. Thanks for all your help Chris