SQL update problem

  • Thread starter Thread starter Patrick D.
  • Start date Start date
P

Patrick D.

C#, webapplication

Hi

usually this sql update command works fine:
(html is a memo-field)

UPDATE [Table1] SET
HTML: 
='content of a memo-field' WHERE [ContentID]='1'

As soon as I have a content like

'winter' is colder than "summer"

to assign to the html-field the sql-statement produces an error since the
little signs around winter and summer are a problem for the sql-statement.

Server.HtmlEncode(String) didn't help.

What do I need to do, to be able to assign any text-contents to the
memo-field?

Thanks for your help.

Patrick
 
Patrick said:
C#, webapplication

Hi

usually this sql update command works fine:
(html is a memo-field)

UPDATE [Table1] SET
HTML: 
='content of a memo-field' WHERE [ContentID]='1'

As soon as I have a content like

'winter' is colder than "summer"

to assign to the html-field the sql-statement produces an error since the
little signs around winter and summer are a problem for the sql-statement.

Server.HtmlEncode(String) didn't help.

What do I need to do, to be able to assign any text-contents to the
memo-field?

Thanks for your help.

Patrick
[/QUOTE]

The preferrable way to handle this is to use a parameterised query. Then
you don't have to worry about encoding the string.

UPDATE [Table1] SET [html]=? WHERE [ContentID]='1'

Add a parameter to the command object. Example:

cmd.Parameters.Add("", OleDbType.VarChar, 0).Value = "'winter' is colder
than \"summer\"";

If you can't use parameters, you have to encode the string when you put
it in the query. This is done differently depending on what database you
are using. Here are some of the most common:

SQL Server: replace ' with ''
MS Access: replace ' with ''
MySQL: replace \ with \\, then replace ' with \'
Click to expand...
 
Hi Göran

Thanks for your input.

I now resolved the problem with string.Replace(OldChar, NewChar) while I
take a character, which is used very seldom to replace the " ' " quote.

Patrick

Göran Andersson said:
Patrick said:
C#, webapplication

Hi

usually this sql update command works fine:
(html is a memo-field)

UPDATE [Table1] SET
HTML: 
='content of a memo-field' WHERE [ContentID]='1'

As soon as I have a content like

'winter' is colder than "summer"

to assign to the html-field the sql-statement produces an error since the
little signs around winter and summer are a problem for the sql-statement.

Server.HtmlEncode(String) didn't help.

What do I need to do, to be able to assign any text-contents to the
memo-field?

Thanks for your help.

Patrick
[/QUOTE]

The preferrable way to handle this is to use a parameterised query. Then
you don't have to worry about encoding the string.

UPDATE [Table1] SET [html]=? WHERE [ContentID]='1'

Add a parameter to the command object. Example:

cmd.Parameters.Add("", OleDbType.VarChar, 0).Value = "'winter' is colder
than \"summer\"";

If you can't use parameters, you have to encode the string when you put
it in the query. This is done differently depending on what database you
are using. Here are some of the most common:

SQL Server: replace ' with ''
MS Access: replace ' with ''
MySQL: replace \ with \\, then replace ' with \'
[/QUOTE]
Click to expand...
Click to expand...
 
Patrick said:
Hi Göran

Thanks for your input.

I now resolved the problem with string.Replace(OldChar, NewChar) while I
take a character, which is used very seldom to replace the " ' " quote.

Patrick
Click to expand...

That's a kludge. You should encode the string properly so that it's
stored correctly in the database.
 
Back
Top