I have names in a data collection the include apostrophes like O'Hare. When
I use a simple VC#/ADO.NET approach with an SQL INSERT statement and
cmd.ExecuteNonQuery, it always hangs up on the apostrophe saying that there
is an unterminated string. When I try using a DataAdapter and
SqlCommandBuilder, it says I need and INSERT statement, although it will do
data without the apostrophes. Any idea how to handle these cases? I've
also tried parameters, but the results are the same.

TIA

Carl

Carl,

Can you post your code where you use cmd.ExecuteNonQuery with parameters and
you still have problems?

Kerry Moorman


"Carl Thomas" wrote:

> I have names in a data collection the include apostrophes like O'Hare. When
> I use a simple VC#/ADO.NET approach with an SQL INSERT statement and
> cmd.ExecuteNonQuery, it always hangs up on the apostrophe saying that there
> is an unterminated string. When I try using a DataAdapter and
> SqlCommandBuilder, it says I need and INSERT statement, although it will do
> data without the apostrophes. Any idea how to handle these cases? I've
> also tried parameters, but the results are the same.
>
> TIA
>
> Carl
>
>
>

On Sat, 11 Aug 2007 18:02:02 +0200, Carl Thomas <(E-Mail Removed)> wrote:

> I have names in a data collection the include apostrophes like O'Hare. When
> I use a simple VC#/ADO.NET approach with an SQL INSERT statement and
> cmd.ExecuteNonQuery, it always hangs up on the apostrophe saying that there
> is an unterminated string. When I try using a DataAdapter and
> SqlCommandBuilder, it says I need and INSERT statement, although it will do
> data without the apostrophes. Any idea how to handle these cases? I've
> also tried parameters, but the results are the same.
>
> TIA
>
> Carl
>
>
>

Carl, use SqlParameter to pass the data. Saves you all these kinds of troubles.

--
Happy coding!
Morten Wennevik [C# MVP]

The problem occurs at runtime from the following code snippet:

using (SqlDataReader drdr =
cmdIn.ExecuteReader())
{
drdr.Read();
string latitude =
GetCoordinateString( drdr.GetString( 1 ) );
string longitude =
GetCoordinateString( drdr.GetString( 2 ) );
string strInsertString
= "INSERT INTO
AirportData (ICAO, Latitude, Longitude, Altitude,"

+ " AirportName, CityName,"

+ " StateProvinceName, CountryName)"
+ "VALUES ('" +
drdr.GetString( 0 ) + "', '"
+
latitude + " ', '"
+
longitude + " ', '"
+
drdr.GetString( 3 ) + "', '"
+
drdr.GetString( 4 ) + "', '"
+
drdr.GetString( 5 ) + "', '"
+
drdr.GetString( 6 ) + "', '"
+
drdr.GetString( 7 ) + "')";
using (SqlCommand cmdInsert
= new SqlCommand( strInsertString, cnOut ))
{
cmdInsert.ExecuteNonQuery();
}
}
When it hits O'Hare as once instance, it does an SqlException at
ExecuteNonQuery claiming that there is an unterminated string. I have since
found a way around it that blythly ignores the problem. I've broken the
problem up to retrieve the data from one database using an SqlDataReader and
out it into a class list and then separately reading it from a class list
into an SqlDataAdapter for the insert. It is a little less elegent, but
works without a problem.

Carl

"Kerry Moorman" <(E-Mail Removed)> wrote in message
news:BB72B829-BA04-4BA8-8E3D-(E-Mail Removed)...
> Carl,
>
> Can you post your code where you use cmd.ExecuteNonQuery with parameters
> and
> you still have problems?
>
> Kerry Moorman
>
>
> "Carl Thomas" wrote:
>
>> I have names in a data collection the include apostrophes like O'Hare.
>> When
>> I use a simple VC#/ADO.NET approach with an SQL INSERT statement and
>> cmd.ExecuteNonQuery, it always hangs up on the apostrophe saying that
>> there
>> is an unterminated string. When I try using a DataAdapter and
>> SqlCommandBuilder, it says I need and INSERT statement, although it will
>> do
>> data without the apostrophes. Any idea how to handle these cases? I've
>> also tried parameters, but the results are the same.
>>
>> TIA
>>
>> Carl
>>
>>
>>

I haven't tried SqlParameters since I found a work around. I may give it a
try since my solution was less elegent than I'd like (see my reply to Kerry
Moorman's post). Somehow, I've never gotten around to SqlParameters, but it
might be a nice application to try.

Thanx.

Carl

"Morten Wennevik" <(E-Mail Removed)> wrote in message
newsp.twwpsdsdklbvpo@ubuan...
> On Sat, 11 Aug 2007 18:02:02 +0200, Carl Thomas
> <(E-Mail Removed)> wrote:
>
>> I have names in a data collection the include apostrophes like O'Hare.
>> When
>> I use a simple VC#/ADO.NET approach with an SQL INSERT statement and
>> cmd.ExecuteNonQuery, it always hangs up on the apostrophe saying that
>> there
>> is an unterminated string. When I try using a DataAdapter and
>> SqlCommandBuilder, it says I need and INSERT statement, although it will
>> do
>> data without the apostrophes. Any idea how to handle these cases? I've
>> also tried parameters, but the results are the same.
>>
>> TIA
>>
>> Carl
>>
>>
>>
>
> Carl, use SqlParameter to pass the data. Saves you all these kinds of
> troubles.
>
> --
> Happy coding!
> Morten Wennevik [C# MVP]

Carl,

I don't see any use of parameters, as you mentioned in your first post.

Kerry Moorman


"Carl Thomas" wrote:

> The problem occurs at runtime from the following code snippet:
>
> using (SqlDataReader drdr =
> cmdIn.ExecuteReader())
> {
> drdr.Read();
> string latitude =
> GetCoordinateString( drdr.GetString( 1 ) );
> string longitude =
> GetCoordinateString( drdr.GetString( 2 ) );
> string strInsertString
> = "INSERT INTO
> AirportData (ICAO, Latitude, Longitude, Altitude,"
>
> + " AirportName, CityName,"
>
> + " StateProvinceName, CountryName)"
> + "VALUES ('" +
> drdr.GetString( 0 ) + "', '"
> +
> latitude + " ', '"
> +
> longitude + " ', '"
> +
> drdr.GetString( 3 ) + "', '"
> +
> drdr.GetString( 4 ) + "', '"
> +
> drdr.GetString( 5 ) + "', '"
> +
> drdr.GetString( 6 ) + "', '"
> +
> drdr.GetString( 7 ) + "')";
> using (SqlCommand cmdInsert
> = new SqlCommand( strInsertString, cnOut ))
> {
> cmdInsert.ExecuteNonQuery();
> }
> }
> When it hits O'Hare as once instance, it does an SqlException at
> ExecuteNonQuery claiming that there is an unterminated string. I have since
> found a way around it that blythly ignores the problem. I've broken the
> problem up to retrieve the data from one database using an SqlDataReader and
> out it into a class list and then separately reading it from a class list
> into an SqlDataAdapter for the insert. It is a little less elegent, but
> works without a problem.
>
> Carl
>
> "Kerry Moorman" <(E-Mail Removed)> wrote in message
> news:BB72B829-BA04-4BA8-8E3D-(E-Mail Removed)...
> > Carl,
> >
> > Can you post your code where you use cmd.ExecuteNonQuery with parameters
> > and
> > you still have problems?
> >
> > Kerry Moorman
> >
> >
> > "Carl Thomas" wrote:
> >
> >> I have names in a data collection the include apostrophes like O'Hare.
> >> When
> >> I use a simple VC#/ADO.NET approach with an SQL INSERT statement and
> >> cmd.ExecuteNonQuery, it always hangs up on the apostrophe saying that
> >> there
> >> is an unterminated string. When I try using a DataAdapter and
> >> SqlCommandBuilder, it says I need and INSERT statement, although it will
> >> do
> >> data without the apostrophes. Any idea how to handle these cases? I've
> >> also tried parameters, but the results are the same.
> >>
> >> TIA
> >>
> >> Carl
> >>
> >>
> >>
>
>
>

On Sun, 12 Aug 2007 23:13:11 +0200, Carl Thomas <(E-Mail Removed)> wrote:

> I haven't tried SqlParameters since I found a work around. I may give it a
> try since my solution was less elegent than I'd like (see my reply to Kerry
> Moorman's post). Somehow, I've never gotten around to SqlParameters, but it
> might be a nice application to try.
>
> Thanx.
>
> Carl
>
> "Morten Wennevik" <(E-Mail Removed)> wrote in message
> newsp.twwpsdsdklbvpo@ubuan...
>> On Sat, 11 Aug 2007 18:02:02 +0200, Carl Thomas
>> <(E-Mail Removed)> wrote:
>>
>>> I have names in a data collection the include apostrophes like O'Hare.
>>> When
>>> I use a simple VC#/ADO.NET approach with an SQL INSERT statement and
>>> cmd.ExecuteNonQuery, it always hangs up on the apostrophe saying that
>>> there
>>> is an unterminated string. When I try using a DataAdapter and
>>> SqlCommandBuilder, it says I need and INSERT statement, although it will
>>> do
>>> data without the apostrophes. Any idea how to handle these cases? I've
>>> also tried parameters, but the results are the same.
>>>
>>> TIA
>>>
>>> Carl
>>>
>>>
>>>
>>
>> Carl, use SqlParameter to pass the data. Saves you all these kinds of
>> troubles.
>>
>> --
>> Happy coding!
>> Morten Wennevik [C# MVP]
>
>
>

You really should give SqlParameter/OleDBParameter a try, apart from handling strings with ' they also to behind the scenes data conversion that potentially saves a whole lot of grief if you get trouble writing or reading database data.

--
Happy coding!
Morten Wennevik [C# MVP]

Right - they didn't help anything so I kept the simpler approach. I realize
it would be easier to take from one database and copy individual items into
another, but using an intermediary array has worked in all cases so far and
is transparent, if a little more complex. Maybe I was using the parameters
wrong; I'll check it out again.

Carl

"Kerry Moorman" <(E-Mail Removed)> wrote in message
news:CFE871FB-11B3-4A8D-8A0E-(E-Mail Removed)...
> Carl,
>
> I don't see any use of parameters, as you mentioned in your first post.
>
> Kerry Moorman
>
>
> "Carl Thomas" wrote:
>
>> The problem occurs at runtime from the following code snippet:
>>
>> using (SqlDataReader drdr =
>> cmdIn.ExecuteReader())
>> {
>> drdr.Read();
>> string latitude =
>> GetCoordinateString( drdr.GetString( 1 ) );
>> string longitude =
>> GetCoordinateString( drdr.GetString( 2 ) );
>> string strInsertString
>> = "INSERT INTO
>> AirportData (ICAO, Latitude, Longitude, Altitude,"
>>
>> + " AirportName, CityName,"
>>
>> + " StateProvinceName, CountryName)"
>> + "VALUES ('" +
>> drdr.GetString( 0 ) + "', '"
>> +
>> latitude + " ', '"
>> +
>> longitude + " ', '"
>> +
>> drdr.GetString( 3 ) + "', '"
>> +
>> drdr.GetString( 4 ) + "', '"
>> +
>> drdr.GetString( 5 ) + "', '"
>> +
>> drdr.GetString( 6 ) + "', '"
>> +
>> drdr.GetString( 7 ) + "')";
>> using (SqlCommand
>> cmdInsert
>> = new SqlCommand( strInsertString, cnOut ))
>> {
>>
>> cmdInsert.ExecuteNonQuery();
>> }
>> }
>> When it hits O'Hare as once instance, it does an SqlException at
>> ExecuteNonQuery claiming that there is an unterminated string. I have
>> since
>> found a way around it that blythly ignores the problem. I've broken the
>> problem up to retrieve the data from one database using an SqlDataReader
>> and
>> out it into a class list and then separately reading it from a class list
>> into an SqlDataAdapter for the insert. It is a little less elegent, but
>> works without a problem.
>>
>> Carl
>>
>> "Kerry Moorman" <(E-Mail Removed)> wrote in message
>> news:BB72B829-BA04-4BA8-8E3D-(E-Mail Removed)...
>> > Carl,
>> >
>> > Can you post your code where you use cmd.ExecuteNonQuery with
>> > parameters
>> > and
>> > you still have problems?
>> >
>> > Kerry Moorman
>> >
>> >
>> > "Carl Thomas" wrote:
>> >
>> >> I have names in a data collection the include apostrophes like O'Hare.
>> >> When
>> >> I use a simple VC#/ADO.NET approach with an SQL INSERT statement and
>> >> cmd.ExecuteNonQuery, it always hangs up on the apostrophe saying that
>> >> there
>> >> is an unterminated string. When I try using a DataAdapter and
>> >> SqlCommandBuilder, it says I need and INSERT statement, although it
>> >> will
>> >> do
>> >> data without the apostrophes. Any idea how to handle these cases?
>> >> I've
>> >> also tried parameters, but the results are the same.
>> >>
>> >> TIA
>> >>
>> >> Carl
>> >>
>> >>
>> >>
>>
>>
>>

Actually, I misspoke - I did try the parameter approach, but it didn't help.
Maybe I was using them wrong, but separating the read operations from one
database and the write operations into a second one through an intermediate
array worked. If it was slightly less elegent and complex, it worked. I'll
give the parametric approach another go to help trim the code a bit.

Carl

"Carl Thomas" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I haven't tried SqlParameters since I found a work around. I may give it a
>try since my solution was less elegent than I'd like (see my reply to Kerry
>Moorman's post). Somehow, I've never gotten around to SqlParameters, but
>it might be a nice application to try.
>
> Thanx.
>
> Carl
>
> "Morten Wennevik" <(E-Mail Removed)> wrote in message
> newsp.twwpsdsdklbvpo@ubuan...
>> On Sat, 11 Aug 2007 18:02:02 +0200, Carl Thomas
>> <(E-Mail Removed)> wrote:
>>
>>> I have names in a data collection the include apostrophes like O'Hare.
>>> When
>>> I use a simple VC#/ADO.NET approach with an SQL INSERT statement and
>>> cmd.ExecuteNonQuery, it always hangs up on the apostrophe saying that
>>> there
>>> is an unterminated string. When I try using a DataAdapter and
>>> SqlCommandBuilder, it says I need and INSERT statement, although it will
>>> do
>>> data without the apostrophes. Any idea how to handle these cases?
>>> I've
>>> also tried parameters, but the results are the same.
>>>
>>> TIA
>>>
>>> Carl
>>>
>>>
>>>
>>
>> Carl, use SqlParameter to pass the data. Saves you all these kinds of
>> troubles.
>>
>> --
>> Happy coding!
>> Morten Wennevik [C# MVP]
>
>

All that you have to do is double up on them. Sinding "O''Hare" will actually
send "O'Hare".
--
Tom Garth


"Carl Thomas" wrote:

> I have names in a data collection the include apostrophes like O'Hare. When
> I use a simple VC#/ADO.NET approach with an SQL INSERT statement and
> cmd.ExecuteNonQuery, it always hangs up on the apostrophe saying that there
> is an unterminated string. When I try using a DataAdapter and
> SqlCommandBuilder, it says I need and INSERT statement, although it will do
> data without the apostrophes. Any idea how to handle these cases? I've
> also tried parameters, but the results are the same.
>
> TIA
>
> Carl
>
>
>