Excellent!! that works. I thank you very much for the help.
would you also suggest a reference on concatenating sql stmts with transformations like this. This is just the start of an extensive goup I have to do. What I am looking for is the general guidelines for what to enclose and with what.
Thanks again
RK
Normally, I would use placeholders in my sql statement. This avoids all the BS about quoting or not quoting variables.
Cmd.CommandText = "INSERT INTO WBS.dbo.pludec38 (PLU,Dscrpt1, Price) VALUES ( ?, ?, CONVERT(smallmoney, ?))"
With Cmd do
.Parameters.Refresh
.Parameters(0) = Var1
.Parameters(1) = Var2
.Parameters(3) = Var3
.Execute
End With
The advantages of this are:
1. You don't have to worry about quoting or not quoting, if the variable is a string, decimal, date or whatever. Simply let the ADO object take care of it as needed. Not only that, it will automatically escape quotes for you, if Var1, Var2 or Var3 have quotes within them.
2. If you have to execute the statement multiple times with different values, you don't need to set the CommandText property each time. Simply set the .Parameters property values and Execute again.
Note that I haven't actually tested out the code above, but I guess it should work. :)
the placeholder approach works nicely with an insert statement and I've tried it with an update statement but keep getting an error. This is the current code:
The error occurs at or near the refresh stmt. What am I doing that is causing this? And what is the difference between this and the insert. The insert uses the parameters block without choking.
Thanks
RK
Your SQL statement is incorrect. Change it to
Cmd.CommandText = "UPDATE WBS.dbo.pludec38 SET PLU = ?, Dscrpt1 = ?, Price = ?"
BTW
Var3 = strArray(3)
not using multiple 'set' stmts makes a difference. The stmt doesn't choke and runs through to completion. However it uses the last value in the array for the updates. I thought this would update each individual record as it looped through. In one way I guess it is, an update for each loop - each record and then stops on the last value.
So I need to find an index value for a where clause.
Thanks for the help on this.
RK
Yep, without a WHERE clause, the SQL statement will update every record in the table.