Insert Command

[David A. Osborn]

I am having problems with the following insert command:

Me.OleDbInsertCommand1.CommandText = "INSERT INTO lutLookup(Type_Name,
Work_Code, Work_Code_ID) VALUES ('Payment_ID',' " & _
lstrCode & " ',' "& txtID.Text & " ')"

The problem occurs when the string variable lstrCode contains a string with
an ' in it like Dave's House. I thought I could replace the ' with ''' to
solve the problem but this didn't solve it. Any ideas?

 

[Chris]

I am having problems with the following insert command:

Me.OleDbInsertCommand1.CommandText = "INSERT INTO lutLookup(Type_Name,
Work_Code, Work_Code_ID) VALUES ('Payment_ID',' " & _
lstrCode & " ',' "& txtID.Text & " ')"

The problem occurs when the string variable lstrCode contains a string with
an ' in it like Dave's House. I thought I could replace the ' with ''' to
solve the problem but this didn't solve it. Any ideas?

To fix the problem you have to refer to your database of choice. For
example mysql you insert the ' by inserting a /' instead. Not sure what
it is for other databases.

Chris

 

[Nick Malik [Microsoft]]

Use SqlParameters

Change your SQL Insert stmt to:

Me.OleDbInsertCommand1.CommandText = "INSERT INTO lutLookup(Type_Name,
Work_Code, Work_Code_ID) VALUES (?,?,?)"

then add three parameters to your command, one for each of the actual
values. The OleDb adapter will figure out the correct way to send the
quoted string, and you won't have to worry about SQL Injection attacks from
your friendly neighborhood hacker.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--

 

[David A. Osborn]

Its an access db.

To fix the problem you have to refer to your database of choice. For
example mysql you insert the ' by inserting a /' instead. Not sure what
it is for other databases.

Chris

 

[David A. Osborn]

This doesn't seem to work if I do

Me.OleDbInsertCommand1.CommandText = "INSERT INTO lutLookup(Type_Name,
Work_Code, Work_Code_ID) VALUES (?,?,?)"
Me.OleDbInsertCommand1.Parameters("Type_Name").Value = "WorkCode_ID"
Me.OleDbInsertCommand1.Parameters("Work_Code").Value = txtCode.text
Me.OleDbInsertCommand1.Parameters("Work_Code_ID").Value = txtID.text

Instead of the Type_Name being inserted into the DB as the string
WorkCode_ID it is always null. The other two values go in fine.