How to update SQL table using variable

[Vack58]

Have the following code which prompts the user for a PO number and a ship to code. I want to take the values they enter and update the ship to code in the POORDHDR_SQL table. When I run the script below it says ShipTo is not a valid field.

How to I update the SQL table with the value of my variable ShipTo?


Option Explicit
Dim ConWSH, strConnection, strSQL1, rs1, adOpenForwardOnly

Set ConWSH=WScript.CreateObject("ADODB.Connection")


strConnection = "Driver={SQL Server};Server=D6RZ4VC1;User ID=sa;Password=sspqiv;Database=DATA;"
with ConWSH
.ConnectionString=strConnection
.ConnectionTimeout=25
.CommandTimeout=25
.Open
end with

Dim Message, PO, Message2, Title2
Dim Title, Text1, ShipTo

' Define dialog box variables.
Message = "Please enter a PO Number followed by release"
Title = "Change PO from Drop Ship to Normal"
Text1 = "Process Cancelled"
Message2 = "Please enter new Ship To Code"

PO = InputBox(Message, Title, "00", 100, 100)
ShipTo = InputBox(Message2, Title2, "",100, 100)

' Evaluate the user input.

If PO = "" Then ' Canceled by the user
WScript.Echo Text1
Else

PO = (right(PO + 100000000,8))
WScript.Echo PO
End If

if ShipTo = "" Then
Wscript.Echo Text1
Else
Wscript.Echo ShipTo
end if



strSQL1 = "Update POORDHDR_SQL Set Ship_to_cd = ShipTo where ord_no = PO"

set rs1=createobject("ADODB.Recordset")
with rs1
.activeconnection=ConWSH
.CursorType=adOpenForwardOnly
.CursorLocation=3
.open strSQL1
End with

 

[PHV]

I'd try something like this:

strSQL1 = "Update POORDHDR_SQL Set Ship_to_cd='" & ShipTo & "' where ord_no='" & PO & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Vack58]

that did the trick.

Thanks a lot