Dynamic SQL - update with a double quote in my field

[k8277]

Hello - I am building some dynamic sql and I have a problem with a field I am reading from a database that contains a double quote that I am then trying to include in some dynamic sql to update another table.

Basically I have this problem:

create a recordset by selecting * from table A
loop through the recordset
assign the variable partnum to a field in the recordset

so at a point in this loop, my variable partnum will get this value: partnum = 12345 1/2"bulk

I am then trying to include this partnum variable in some dynamic sql:
strsql = "update tblparts set inventory = 3 where partnum = '" & partnum & "' and year = 2009"

so the net effect of my strsql variable is:
"update tblparts set inventory = 3 where partnum = '12345 1/2"bulk' and year = 2009"

The double quote in my part number is causing the dynamic sql to fail because it isn't expecting the double quote in the middle of the string.

 

[markros]

What front-end you're using?

In any case, you better not use dynamic SQL at all.

See

http://forums.asp.net/t/1254125.aspx

 

[markros]

If you would really really want to use dynamic SQL then you need to replace each single quote with double single quote

replace(myString,"'","''")

 

[bborissov]

Use parameter. Do not build the exact clause:
I am not sure how your FrontEnd handle parameters but basically it is like this:

strsql = "update tblparts set inventory = 3 where partnum = ? and year = 2009"

The question mark is a place holder of your parameter.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

ANd why on earth are you doing this in a loop? Why aren't you simply writing one no-dynamic, set-based Sql staement?

update t
set inventory = 3 
from tblparts t
join tablea a on t.partnum = a.partnum 
where  year = 2009

This is shorter, easier to maintain and most important, much much faster than the process you are using.

"NOTHING is more important in a database than integrity." ESquared

 

[markros]

Yes, very good question - why we both missed it?????

 

[k8277]

The reason for the loop is because the actual code is not as simple as I made it seem.