Access, SQL and Update statements

[gsam]

In Access 2007 I’m using an ActiveX grid to add, delete or edit data saved to
a SQL Database. I need to update a field that will contain the description of
a product. My problem is that I must prepare for the description to contain
both quotations and apostrophes. For example, a sales person might enter the
following into the description field.

And I quote "the dragon's burnt toothâ€, is what he said.

The SQL Update statement that I’m using to save the data is:

Dim sql as string
sql = "UPDATE tblOrderDetail SET [Description] = " _
& Chr(34) & iGrid3.CellValue(lRow, 6) & Chr(34) _
& " WHERE tblOrderDetail.DetailID = " _
& iGrid3.CellValue(lRow, "DetailID")
CurrentProject.Connection.Execute sql

By entering the above description the sql string would look like this and
throw an error:
UPDATE tblOrderDetail SET [Description] = "And I quote "the dragon's burnt
tooth", is what he said." WHERE tblOrderDetail.DetailID = 605

Does anyone have a suggestion on how I correctly enter data that might
contain any number of quotations and apostrophes?

 

[Sylvain Lafontaine]

You must double any embedded quote that you are also using as the delimiter:

sql = "UPDATE tblOrderDetail SET [Description] = " _
& Chr(34) & Replace (iGrid3.CellValue(lRow, 6), chr(34), chr(34) &
chr(34)) & Chr(34) _
& " WHERE tblOrderDetail.DetailID = " _
& iGrid3.CellValue(lRow, "DetailID")

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[JimBurke via AccessMonster.com]

Should you also check to see if the first or last character is a double quote,
and if so concatenate another double quote to the beginning or end
appropriately?

You must double any embedded quote that you are also using as the delimiter:

sql = "UPDATE tblOrderDetail SET [Description] = " _
& Chr(34) & Replace (iGrid3.CellValue(lRow, 6), chr(34), chr(34) &
chr(34)) & Chr(34) _
& " WHERE tblOrderDetail.DetailID = " _
& iGrid3.CellValue(lRow, "DetailID")

In Access 2007 I'm using an ActiveX grid to add, delete or edit data saved
to

[quoted text clipped - 24 lines]

Does anyone have a suggestion on how I correctly enter data that might
contain any number of quotations and apostrophes?

 

[Sylvain Lafontaine]

No, there is no particular check to make for the beginning or the end of the
delimited string and you can test for this easily. However, I'm not sure if
you are not making a confusion between the beginning and the end of the
delimited string and the delimiter itself.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Should you also check to see if the first or last character is a double
quote,
and if so concatenate another double quote to the beginning or end
appropriately?

You must double any embedded quote that you are also using as the
delimiter:

sql = "UPDATE tblOrderDetail SET [Description] = " _
& Chr(34) & Replace (iGrid3.CellValue(lRow, 6), chr(34), chr(34) &
chr(34)) & Chr(34) _
& " WHERE tblOrderDetail.DetailID = " _
& iGrid3.CellValue(lRow, "DetailID")

In Access 2007 I'm using an ActiveX grid to add, delete or edit data
saved
to

[quoted text clipped - 24 lines]

Does anyone have a suggestion on how I correctly enter data that might
contain any number of quotations and apostrophes?

 

[gsam]

This does get rid of the error but the results are that the description is
saved with double quotes, ie:

UPDATE tblOrderDetail SET [Description] = "And I quote ""the dragon's burnt

tooth"", is what he said." WHERE tblOrderDetail.DetailID = 605

So I added a sub to remove the double quotes when the data is filled back
into the form, ie;

If Not IsNull(iGrid3.CellValue(vRow, 6)) Then
iGrid3.CellValue(vRow, 6) = Replace(iGrid3.CellValue(vRow, 6),
Chr(34) & Chr(34), Chr(34))
End If

This is a real pain in the butt but it appears to be working.
Thanks for the help

You must double any embedded quote that you are also using as the delimiter:

sql = "UPDATE tblOrderDetail SET [Description] = " _
& Chr(34) & Replace (iGrid3.CellValue(lRow, 6), chr(34), chr(34) &
chr(34)) & Chr(34) _
& " WHERE tblOrderDetail.DetailID = " _
& iGrid3.CellValue(lRow, "DetailID")

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

In Access 2007 I'm using an ActiveX grid to add, delete or edit data saved
to
a SQL Database. I need to update a field that will contain the description
of
a product. My problem is that I must prepare for the description to
contain
both quotations and apostrophes. For example, a sales person might enter
the
following into the description field.

And I quote "the dragon's burnt tooth", is what he said.

The SQL Update statement that I'm using to save the data is:

Dim sql as string
sql = "UPDATE tblOrderDetail SET [Description] = " _
& Chr(34) & iGrid3.CellValue(lRow, 6) & Chr(34) _
& " WHERE tblOrderDetail.DetailID = " _
& iGrid3.CellValue(lRow, "DetailID")
CurrentProject.Connection.Execute sql

By entering the above description the sql string would look like this and
throw an error:
UPDATE tblOrderDetail SET [Description] = "And I quote "the dragon's burnt
tooth", is what he said." WHERE tblOrderDetail.DetailID = 605

Does anyone have a suggestion on how I correctly enter data that might
contain any number of quotations and apostrophes?

.

 

[Sylvain Lafontaine]

I'm very confuse that doubling the double quotes solve the error message but
also get save as double - double quotes. It should be one or the other but
not both. It's probably because we don't see the whole picture here on what
you are doing exactly.

Is it possible that you are double-doubling them or that you are using a
single quote as the delimiter somewhere or that you are storing part of the
sql string in a variable or somewhere else to be used/concatenated again
later?

Using the single or the double quotes and when to double them or not can be
very confusing sometimes and like always, the Devil likes to hide himself in
the details.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

This does get rid of the error but the results are that the description is
saved with double quotes, ie:

UPDATE tblOrderDetail SET [Description] = "And I quote ""the dragon's
burnt

tooth"", is what he said." WHERE tblOrderDetail.DetailID = 605

So I added a sub to remove the double quotes when the data is filled back
into the form, ie;

If Not IsNull(iGrid3.CellValue(vRow, 6)) Then
iGrid3.CellValue(vRow, 6) = Replace(iGrid3.CellValue(vRow, 6),
Chr(34) & Chr(34), Chr(34))
End If

This is a real pain in the butt but it appears to be working.
Thanks for the help

You must double any embedded quote that you are also using as the
delimiter:

sql = "UPDATE tblOrderDetail SET [Description] = " _
& Chr(34) & Replace (iGrid3.CellValue(lRow, 6), chr(34), chr(34) &
chr(34)) & Chr(34) _
& " WHERE tblOrderDetail.DetailID = " _
& iGrid3.CellValue(lRow, "DetailID")

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

In Access 2007 I'm using an ActiveX grid to add, delete or edit data
saved
to
a SQL Database. I need to update a field that will contain the
description
of
a product. My problem is that I must prepare for the description to
contain
both quotations and apostrophes. For example, a sales person might
enter
the
following into the description field.

And I quote "the dragon's burnt tooth", is what he said.

The SQL Update statement that I'm using to save the data is:

Dim sql as string
sql = "UPDATE tblOrderDetail SET [Description] = " _
& Chr(34) & iGrid3.CellValue(lRow, 6) & Chr(34) _
& " WHERE tblOrderDetail.DetailID = " _
& iGrid3.CellValue(lRow, "DetailID")
CurrentProject.Connection.Execute sql

By entering the above description the sql string would look like this
and
throw an error:
UPDATE tblOrderDetail SET [Description] = "And I quote "the dragon's
burnt
tooth", is what he said." WHERE tblOrderDetail.DetailID = 605

Does anyone have a suggestion on how I correctly enter data that might
contain any number of quotations and apostrophes?

.