Insert Command

[David A. Osborn]

I am having problems with the following insert command:

Me.OleDbInsertC ommand1.Command Text = "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]

Re: Insert Command

David A. Osborn wrote:[color=blue]
> I am having problems with the following insert command:
>
> Me.OleDbInsertC ommand1.Command Text = "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?
>
>
>
>[/color]

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]]

Re: Insert Command

Use SqlParameters

Change your SQL Insert stmt to:[color=blue]
> Me.OleDbInsertC ommand1.Command Text = "INSERT INTO lutLookup(Type_ Name,
> Work_Code, Work_Code_ID) VALUES (?,?,?)"[/color]

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" <dosborn278@hot mail.com> wrote in message
news:VCdKe.2380 48$_o.7044@attb i_s71...[color=blue]
>I am having problems with the following insert command:
>
> Me.OleDbInsertC ommand1.Command Text = "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?
>
>
>
>[/color]

[David A. Osborn]

Re: Insert Command

Its an access db.

"Chris" <no@spam.com> wrote in message
news:urhwATVnFH A.2916@TK2MSFTN GP14.phx.gbl...[color=blue]
> David A. Osborn wrote:[color=green]
>> I am having problems with the following insert command:
>>
>> Me.OleDbInsertC ommand1.Command Text = "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?
>>
>>
>>
>>[/color]
>
> 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
>[/color]

[David A. Osborn]

Re: Insert Command

This doesn't seem to work if I do

Me.OleDbInsertC ommand1.Command Text = "INSERT INTO lutLookup(Type_ Name,
Work_Code, Work_Code_ID) VALUES (?,?,?)"
Me.OleDbInsertC ommand1.Paramet ers("Type_Name" ).Value = "WorkCode_I D"
Me.OleDbInsertC ommand1.Paramet ers("Work_Code" ).Value = txtCode.text
Me.OleDbInsertC ommand1.Paramet ers("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.

"Nick Malik [Microsoft]" <nickmalik@hotm ail.nospam.com> wrote in message
news:C7ednSM1ie hrAmTfRVn-uA@comcast.com. ..[color=blue]
> Use SqlParameters
>
> Change your SQL Insert stmt to:[color=green]
>> Me.OleDbInsertC ommand1.Command Text = "INSERT INTO lutLookup(Type_ Name,
>> Work_Code, Work_Code_ID) VALUES (?,?,?)"[/color]
>
> 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" <dosborn278@hot mail.com> wrote in message
> news:VCdKe.2380 48$_o.7044@attb i_s71...[color=green]
>>I am having problems with the following insert command:
>>
>> Me.OleDbInsertC ommand1.Command Text = "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?
>>
>>
>>
>>[/color]
>
>
>[/color]