Data with Apostrophes

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
 
G

Guest

Carl,

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

Kerry Moorman
 
M

Morten Wennevik

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.
 
C

Carl Thomas

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
 
C

Carl Thomas

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
 
G

Guest

Carl,

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

Kerry Moorman
 
M

Morten Wennevik [C# MVP]

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...
 
C

Carl Thomas

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
 
C

Carl Thomas

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...
 
G

Guest

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

Carl Thomas

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
 
W

William Vaughn

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)
-----------------------------------------------------------------------------------------------------------------------
 
C

Carl Thomas

Be'gorra. Since I'm starting ta be playin' with LINQ, can I assume that the
same considerations will be applyin' there?

Carl
 
W

William Vaughn

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with apostrophes 2
why and when to use sqlCommandBuilder 4
SQL Update/Insert - problem with Apostrophes 1
ADO.NET and single quotes not being handled 3
Extracting data from Access 1
inserting ' into a record, then searching on it 2
SqlException:Cannot create a row of size X which is greater than m 3
Problem with binding Oracle-BLOB to SQL-Parameters 2

Top