gogetsome
asked on
VBA populate label from database
Hello, I'm trying to populate a label from the data that is being returned from a SQL query, but keep getting an error: must declare the varible @stringm163
Can someone please look at my code below and show me where I'm going wrong?
Code:
Sub getUser()
Dim conn As ADODB.Connection
Dim connString As String
connString = "Provider=sqloledb;Data Source=localhost;Initial Catalog=test;User Id=scott;Password=rocks"
Set conn = New ADODB.Connection
conn.ConnectionString = connString
conn.Open connString
Dim rs As ADODB.Recordset
Dim sSQL As String
Set rs = New ADODB.Recordset
sSQL = "select username from cstb_sopWindowUsers where username = @StringM163"
rs.Open sSQL, conn
StringM164.Value = rs.Fields.Item("username") .Value
rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub
Can someone please look at my code below and show me where I'm going wrong?
Code:
Sub getUser()
Dim conn As ADODB.Connection
Dim connString As String
connString = "Provider=sqloledb;Data Source=localhost;Initial Catalog=test;User Id=scott;Password=rocks"
Set conn = New ADODB.Connection
conn.ConnectionString = connString
conn.Open connString
Dim rs As ADODB.Recordset
Dim sSQL As String
Set rs = New ADODB.Recordset
sSQL = "select username from cstb_sopWindowUsers where username = @StringM163"
rs.Open sSQL, conn
StringM164.Value = rs.Fields.Item("username")
rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub
(slight correction, string needs to be surrounded by single tick marks)
sSQL = "select username from cstb_sopWindowUsers where username='" & YourFormName.[ @StringM163] & "'"
sSQL = "select username from cstb_sopWindowUsers where username='" & YourFormName.[ @StringM163] & "'"
ASKER
Thank you Jim for responding. I'm trying to obtain the value from a field. I'm using:
sSQL = "select username from cstb_sopWindowUsers where username='" & SalesTransactionEntry.[ @StringM163] & "'"
but am now getting this error method or data member not found
sSQL = "select username from cstb_sopWindowUsers where username='" & SalesTransactionEntry.[ @StringM163] & "'"
but am now getting this error method or data member not found
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, but I had the @ character as if I were passing a variable to a SPROC. I took out the @ and now it works great! Thank you very much for your assistance Jim.
Field name...
sSQL = "select username from cstb_sopWindowUsers where username=" & YourFormName.[ @StringM163]
SQL Server parameter...
You'll need an ADODB.Command object, create/set an ADODB.Parameter of @StringM163, then execute your command.