Data with Apostrophes

  • Thread starter Thread starter Carl Thomas
  • Start date Start date
C

Carl Thomas

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
 
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
Click to expand...

Carl, use SqlParameter to pass the data. Saves you all these kinds of troubles.
 
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
 
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
 
Carl,

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

Kerry Moorman
 
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 said:
Carl, use SqlParameter to pass the data. Saves you all these kinds of
troubles.
Click to expand...

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.
Click to expand...
 
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
 
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 said:
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
Click to expand...
 
I assume by that you mean two apostrophes rather than a singlr quote. OK -
gotta remember that. But it means I have to go back into the old database
and change all the apostrophe-bearing words. I think I'll hold onto the
DataAdapter approach I'm using now which seems to work. I do want to try
the SqlParameter approach with an INSERT since I suspect it may be a little
faster. I'll kepp this in mind though. Thanx

Carl
 
Ah, no. You don't have to exclude all Irish surnames from your database.
This was tried at various points in time in the past with disastrous
consequences. The Replace approach forces you to simply repeat any single
quote (apostrophe) to two single quotes just before it's concatenated into a
SQL string. However, as we've discussed, this approach is full of other
issues. The "parameter" approach deals with what I call the O'Malley issue
as well as date formatting and other issues you might not have encountered
yet.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Be'gorra. Since I'm starting ta be playin' with LINQ, can I assume that the
same considerations will be applyin' there?

Carl
 
I'm noot sur laddie. I'm expecting eet will.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Back
Top