Need help with VBScripting in a DAP

[Greghsti]

Hi,

I am tring to open a customer table and pull out the customer name.
Can someone help me?

Using a SQL server.
Severs name is= apg_service
Database = RMA2000SQLA
Look upTable= Customers
Textbox =CustomerID ' This is the record number need in customer
Testbox =CoName ' customer name from customer table
Button=Command0 ' Press the button and it fill the Textbox CoName

Code:

<SCRIPT language=vbscript event=onclick for=Command0>
<--
Dim Con
Dim Rst

Set Con = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")

Con.Open "Provider=MSDataShape.1;Data Source=apg_service;" & _
Initial Catalog=RMA2000SQLA;Data " & _
"Provider=SQLOLEDB.1"

Rst.Open "SELECT CompanyName FROM Customers WHERE CustomerID = " & _
chr(39) & Document.All.Item("CustomerID").Value & chr(39), Con

Document.All.Item("CoName").Value = Rst("CompanyName").Value

Rst.Close
Con.Close
-->
</SCRIPT>

 

[Stoooo]

Not sure, but you may not have delimited the search criteria properly.

Can't promise this will work, but you could try replacing

Rst.Open "SELECT CompanyName FROM Customers WHERE CustomerID = " & _
chr(39) & Document.All.Item("CustomerID").Value & chr(39), Con

with

Rst.Open "SELECT CompanyName FROM Customers WHERE CustomerID = '" & _
& Document.All.Item("CustomerID").Value & "'", Con

which has ' delimiters either side of the search string.