We help IT Professionals succeed at work.

VBA populate label from database

gogetsome
gogetsome asked
on
Medium Priority
255 Views
Last Modified: 2010-04-07
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
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Is @StringM163 a field name, or a SQL Server parameter?

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.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
(slight correction, string needs to be surrounded by single tick marks)
sSQL = "select username from cstb_sopWindowUsers where username='" &  YourFormName.[ @StringM163] & "'"  

Author

Commented:
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
SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
SQL may not allow you to name fields with a punctuation mark as the first character.  

Even if it does, this is highly confusing, as the @ character is usually the prefix for stored procedure variables, and it will force you to use square brackets [ ] to refer to it.

Try renaming your field something meaningful, such as txtUser, txtCustomer, anything instead of @StringM163, and try again.

sSQL = "select username from cstb_sopWindowUsers where username='" &  SalesTransactionEntry.txtUser & "'"  

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.