Sql statment error with apostrophe

[Gizmosays]

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]

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:

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]

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]

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]

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"