SQL Insert problems

24 Nov 2007 8:47 AM

barry

Hi

Using the old style of inserting data in a table

str="Insert Into MyTable (Description, Test) Values (
'The Quick, Black Jump's, Over The Lazy Dog', 'Test'
)";

The above would fail since there are commas and single quote in the data.

I thought fill(ing) a Dataset and the using update would overcome the
problem, but it also has the same error, how does one solve such issues.

TIA
Barry

24 Nov 2007 9:12 AM

Jon Skeet [C# MVP]

barry <some***@somewhere.com> wrote:

> Using the old style of inserting data in a table
>
> str="Insert Into MyTable (Description, Test) Values (
> 'The Quick, Black Jump's, Over The Lazy Dog', 'Test'
> )";
>
> The above would fail since there are commas and single quote in the data.

It would fail an English grammar test too, but that's a different
matter ;)

> I thought fill(ing) a Dataset and the using update would overcome the
> problem, but it also has the same error, how does one solve such issues.

Use a parameterised SqlCommand instead of including the values in the
SQL statement itself.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk

24 Nov 2007 4:29 PM

Jack Jackson

Show quote

On Sat, 24 Nov 2007 14:17:08 +0530, "barry" <some***@somewhere.com>
wrote:

>Hi
>
>Using the old style of inserting data in a table
>
>str="Insert Into MyTable (Description, Test) Values (
> 'The Quick, Black Jump's, Over The Lazy Dog', 'Test'
>)";
>
>The above would fail since there are commas and single quote in the data.
>
>I thought fill(ing) a Dataset and the using update would overcome the
>problem, but it also has the same error, how does one solve such issues.
>
>TIA
>Barry

The single quote inside the first value causes a problem, but commas
do not. You have two choices. The easiest solution is to use
parameters for the values. The other way is to use whatever method
the database supports to enclose single quotes within a string. Often
that is to double the quotes, but it might vary by type of backend.