Hi,

I am trying to populate a value on my label with a value from a database. I have the below script, but when I run It, I don't get any error, and I don't return any data.

Ive tried hard coding the variables Item and CUstomer incase it was a data issue, but it looks fine. 

Can anyone see what the issue may be:

dim objCon

dim strCon

dim rs

dataSource = "msldb01" 'sql server"

initialCatalog = "live_trmde_app" 'Database name

tableName = "itemcust" 'Table(view) of dropdown source

Customer = Field("AUDI Shipment.Field 23") 'Value for Query

Item = Field("AUDI Shipment.Field 7") 'Value for Query

userId = "sa" 'SQL Authentication is used

password = "xxxxxx" 'SQL Authentication is used
 

'Create connection

Set objCon = CreateObject("ADODB.Connection")

'SQL authentication

objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=" & dataSource & ";Initial Catalog=" & initialCatalog & ";Persist Security Info=False;User ID=" & userId & ";Password=" & password & ";"

objCon.Open

'generate SQL
 

strCon = "SELECT cust_item FROM itemcust WHERE cust_num = '" & Customer & "' AND item = '" & Item & "'"
 

'run query

Set rs = CreateObject("ADODB.Recordset")

Set rs = objCon.Execute(strCon)
 

'fill options

options=""

Do Until rs.EOF

If Len(options)>0 Then options = options + vbCR

options = options + rs("cust_item").value

rs.MoveNext

Loop
 

Value = options

Thanks

scott