Sql statment error with apostrophe

Gizmosays

Member
Joined
May 22, 2005
Messages
6
Programming Experience
Beginner
Well I was testing out my program for bugs an I found one...
I have a text box that is used for a description of a movie, when I execute the update sql statement it accepts all characters with no errors except the apostrophe which when used it errors out. I suppose it interferes with the string used to update the database. I tried the keydown event work fine but, it also applies for the quotation char since they are in the same key. Any ideas on what to do to prevent or work around the apostrophe. Thanks in advance.

Here is the sql statement I use..
strSql = "Update movies set title = '" & txtTitle.Text & "' , " & "category = '" & cmbCategory.Text & "' , " & "Ratings = '" & cmbRatings.Text & "' , " & "Length = '" & nudLength.Text & "', " & "Movielocation = '" & txtMovielocation.Text & "', " & "userReview = '" & TbuserReview.Value & "' ," & "Description = '" & txtdescription.Text & "'" & "where title = '" & item & "'"

 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,321
Location
Sydney, Australia
Programming Experience
10+
I have had the same problem. You escape a single quote in an SQL string value with another single quote, so instead of txtTitle.Text use txTitle.Text.Replace("'", "''"). Also, I would highly recommend using String.Format instead of all those concatenation operators (&). It makes things much more readable and therefore less error-prone. I would rewrite your line of code like this:
VB.NET:
strSQL = String.Format("UPDATE Movies SET Title = '{0}', Category = '{1}', Ratings = '{2}', Length = '{3}', MovieLocation = '{4}', UserReview = '{5}', Description = '{6}' WHERE Title = '{7}'", _
		  txtTitle.Text.Replace("'", "''"), _
		  cmbCategory.Text.Replace("'", "''"), _
		  cmbRatings.Text.Replace("'", "''"), _
		  nudLength.Text, _
		  txtMovieLocation.Text.Replace("'", "''"), _
		  tbUserReview.Text.Replace("'", "''"), _
		  txtDescription.Text.Replace("'", "''"), _
		  item)
I personally find this much more readable. Also note that you have single quotes around the value for every field. This may not apply to you but single quotes are only used for strings. If the field is a not a text type then do not put single quotes around the values.
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
Another good way to avoid the problem with single quotes and other such problems (SQL injection for example) is to use parameters in the SQL command.
 

Gizmosays

Member
Joined
May 22, 2005
Messages
6
Programming Experience
Beginner
Thank you jmcilhinney that did take care of the apostrophe error. I could not get the format you use to create the sql statement to work but, I am sure it is just me missing somthing small. I will keep trying to get it to work since you are right that method is much easier to read and less error-prone.
Thank you for your help.
 

webappguru

Member
Joined
Jan 16, 2005
Messages
7
Programming Experience
3-5
I would have to agree! SQL Parameters are a better way to go. What it basically does is it set the items such as textboxes to parameters so it will allow ' or , or anything else. You can also prevent SQL injection attacks. You may want to google "SQL PARAMETERS"
 
Top Bottom