SQL string

[pgriffin]

I am trying to pass two vars to a VBScript sub to change a numeric field which is a sequence.

something like

Code: Select all

Sub AssignSequence(cProc,cSeq)
    dim imgActDB
    dim SQLUpdate
    dim rsActions
    Set ImgActDB = CreateObject("ADODB.Connection")
    ImgActDB.Open "Provider = MicroSoft.JET.OLEDB.4.0; Data Source=c:\DB\DB.mdb;"
SQLUpdate = "Update Table set SeqNO= ((SeqNO + 10) where SequenceNbr > & cSeq & and ProcID = '" & cProc & "'))"
set rsActions = ImgActDB.Execute(SQLString)
End Sub

This code does NOT work.  I can select the correct records, but cannot change any of the sequence numbers.

[Marcus Tettmar]

You've missed out a couple of quotes, where you are concatinating the cSeq variable with the string:

SQLUpdate = "Update Table set SeqNO= ((SeqNO + 10) where SequenceNbr > " & cSeq & " and ProcID = '" & cProc & "'))"

[pgriffin]

thanks Marcus.

I did have to tweak the code a bit more but you got me over that hurdle. Here is the final version.

SQLUpdate = "Update Table set SequenceNbr = SequenceNbr + 10 where (SequenceNbr > " & cSeq & " and ProcessID = '" & cProc & "')"

staring at code too many hours at a time these days....

thanks again...