Sql Select - Vb Script - Syntax Validation 追蹤

0
Avatar
Legacy Poster

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

 

1 意見

0
Avatar
Ian Cummings
版主
評論操作 永久連結

In our example for the OnFillList, the Do...Loop has something like the below:

strNames = ""
rs.MoveFirst()
do while NOT rs.EOF
    strNames = strNames + rs.fields("FieldName") + vbCrLf
    rs.MoveNext()
loop

I suspect that what you have here in your code might be the root of the problem.

登入寫評論。