Single Quote Problem

using ASP.NET with C#...
if I have something like this: 'Bachelor's Degree'
how can I make it so that it doesn't think the apostrophe of Bachelor's is not considered as the closing quote?
thanks!replace the quote by " before inserting it into the database?
If this is when you are saving to the DB and you are constructing the SQL like:

string degree = "'bachelor's degree'"
string sql = "INSERT INTO blah blah"

//build up sql

sql += degree

//build rest of sql

If you do this the the ' becomes a problem. However what you can do is something like this. Construct the SQL like:

INSERT MyTable VALUES(@.Degree)

Then using a ado command object and the parameters you can do:

cmd.Paramerters.Add("@.Degree", degree)

This is also a method to stop injection attacks to sql too.

Does the above make sense, or do you want an full example of using command objects?

Woka
Lets say you had a text box on a form/web page, that a user types into to do a search. Lets say this is a username field, to search the db for something on users.

Normall ppl construct the sql statement like:

string sql = "SELECT ID, Description"

sql += "FROM MyTable "
sql += "WHERE Username = '" & txtUsername.Text & "' "

This means that if a username contains a ' then this will cause problems like you mentioned.

However, injection attacks like I mentioned earlier, can be used if the developer does the above.

Instead of typeing wokawidget as the username a user can type:

Wokawidget'; delete from Users where usersname<>'

typing the above in would create an SQL statement:

SELECT ID, Description FROM MyTable where Username = 'Wokawidget'; delete from users where username<>''

when clicking the search button the user table gets deleted. DOH!!!

Using command objects can stop this...

Hope this helps.

WOka
Although parameterization is the preferred way to fly, the following does work:

INSERT INTO myTable(myColumn) VALUES ('Bachelor''s degree')

Noticing that the ' became '' (two apostrophes)
INSERT INTO myTable(DegreeType) VALUES (@.Degree)

Then use command params to fill @.Degree then it does work.

Woka
Although parameterization is the preferred way to fly, the following does work:

INSERT INTO myTable(myColumn) VALUES ('Bachelor''s degree')

Noticing that the ' became '' (two apostrophes)

Lord rat, can explain more? Becuase I am also having the same problem. When user is entering description in the textfield, if '' exits, the whole SQL command will return an error.

Regards,
Roger
Use parametrization, it's way better. All this problems disapear and it's just an extra line.

And the funny thing is, it's easier than to concatenate the string.

Do yourself a favor and use parametrization.

HTH
HoraShadow