Need to add a character to a string 1

[dvannoy]

I am having problems inserting records into my sql server. the problem is the customers name may have an ' eg..Bob's

thats where I get stuck. I need to replace that character with a " or I would really like to get rid of it all together. this field is being populated from a linked server connected to an AS400. also this field is a locked field that users cannot change. so this would have to be automated.

any help would be appreciated

 

[bjd4jc]

you can use a replace(MyString, "'", "''&quot on your sql statement and it will work fine.

 

[dvannoy]

OK, so on my INSERT statement.

where would I use the replace ?? inside the insert or after?? eg.. "INSERT INTO Cust (field1) VALUES(txttext1.text)"

where would it go?

Thanks

 

[TRYP]

here:

sql="INSERT INTO Cust (field1) VALUES('" & _
replace(txttext1.text,"'","''&quot & "')"

tryp

 

[dvannoy]

Thank you!

 

[recurse]

dvannoy,

I recommend STRONGLY against using the above code as it can introduce a horrible security risk with SQL Server, please imagine what would happen if somone typed in "sp_execute(truncate table Cust)" command into your textbox, or worse! SQL Server will let you pass through commands to the command processor on the server... and they could add themselves as a user.. or delete files or whatever they wanted to do!

The proper way to deal with the problem you are having is to use the command object and create parameters to it. This solution is MUCH faster (especially when executing multiple times) and MUCH more secure.

-recurse

 

[dvannoy]

Thank you for your concern but the text box that the above code affects is locked. users cannot edit that text box at all. this is a simple INSERT statement thats it.