Sigle quote in Select and Update query 
Author Message
 Sigle quote in Select and Update query

Hey,

Using VB6, ADO 2.1 and SQL 7........

How do I select something from the datrabase where the item is text and it
already contains a sigle quote?  Like Bally's??

And how do you contruct a statement to update that record?

Update customer
Set Address = '123 new ave'
WHERE name = 'Bally's'

I tried double quotes, triple double quotes........help!

Thanks

Stephan



Tue, 14 Oct 2003 00:48:42 GMT  
 Sigle quote in Select and Update query
Not quite sure, but I think that you should use " name='Bally[']s' "



Quote:
> Hey,

> Using VB6, ADO 2.1 and SQL 7........

> How do I select something from the datrabase where the item is text and it
> already contains a sigle quote?  Like Bally's??

> And how do you contruct a statement to update that record?

> Update customer
> Set Address = '123 new ave'
> WHERE name = 'Bally's'

> I tried double quotes, triple double quotes........help!

> Thanks

> Stephan



Tue, 14 Oct 2003 01:59:23 GMT  
 Sigle quote in Select and Update query
SUMMARY
Building concatenated SQL statements based on user-typed text values can
result in invalid SQL statements. This article provides a solution to the
problem of building concatenated SQL.

NOTE: Microsoft Visual Basic 6.0 and later products and Microsoft Office 98
and later products come with a Replace function built-in

MORE INFORMATION
When building concatenated SQL statements, you can run into the following
problems based on incorporating user-typed text into the SQL statement:

User Types the Delimiter Character
If the user types the same character you use to delimit the text field, such
as:

LName contains: O'Brien
   SQL = "SELECT * FROM Employees WHERE LastName='" & LName & "'"
SQL now contains:
   SELECT * FROM Employees WHERE LastName='O'Brien'
this can result in the following error message when you execute the SQL
statement:
  Run-time error 3075 Syntax error in query expression '...'
One solution is to replace the apostrophe delimiter with quotes ("), such
as:
   SQL = "SELECT * FROM Employees WHERE LastName=""" & LName & """"
However, the user could easily type O"Brien by mistake (forgetting to
release the SHIFT key when typing the apostrophe) and the problem reappears.
In addition, SQL Server uses " to delimit table and field names. If the
user-supplied value exceeds the maximum length of an identifier name, SQL
Server will return a syntax error.

The solution is to replace the apostrophe in the variable with two
apostrophes so that SQL contains:

   SELECT * FROM Employees WHERE LastName='O''Brien'

--

Kent Prokopy


Quote:
> Hey,

> Using VB6, ADO 2.1 and SQL 7........

> How do I select something from the datrabase where the item is text and it
> already contains a sigle quote?  Like Bally's??

> And how do you contruct a statement to update that record?

> Update customer
> Set Address = '123 new ave'
> WHERE name = 'Bally's'

> I tried double quotes, triple double quotes........help!

> Thanks

> Stephan



Tue, 14 Oct 2003 07:22:53 GMT  
 Sigle quote in Select and Update query


Fri, 19 Jun 1992 00:00:00 GMT  
 Sigle quote in Select and Update query
The information Kent quoted is from Q178070, if you want to save it for
future reference.


Tue, 14 Oct 2003 08:18:19 GMT  
 Sigle quote in Select and Update query


Fri, 19 Jun 1992 00:00:00 GMT  
 Sigle quote in Select and Update query
One of the database properties of SQL 7 is "Use quoted identifiers."  Find it by
right-clicking the database icon, then go to the "Options" tab.  If quoted
identifiers is on, you must double up your single quotes as discussed in a
previous response.  If that's not convenient, uncheck "Use quoted identifiers"
and then the following SQL would work:

SELECT * FROM [My Table]
WHERE [Last Name] = "O'Brien"

This information comes from SQL Server Books Online, "Delimited Identifiers."

Quote:
-----Original Message-----

Hey,

Using VB6, ADO 2.1 and SQL 7........

How do I select something from the datrabase where the item is text and it
already contains a sigle quote?  Like Bally's??

And how do you contruct a statement to update that record?

Update customer
Set Address = '123 new ave'
WHERE name = 'Bally's'

I tried double quotes, triple double quotes........help!

Thanks

Stephan

.



Tue, 14 Oct 2003 21:29:29 GMT  
 Sigle quote in Select and Update query
Hi
    Search for the Single quote in your text and replace it with two single
quotes.
Eg:
    if the text contains      ------  Bally's
     replace this text with  ------ Bally''s ( two single quotes)

Now ur sql  statement will be

strsql = "Update customer Set Address = '123 new ave' WHERE name =
'Bally''s' "

 Devi S


Quote:
> Hey,

> Using VB6, ADO 2.1 and SQL 7........

> How do I select something from the datrabase where the item is text and it
> already contains a sigle quote?  Like Bally's??

> And how do you contruct a statement to update that record?

> Update customer
> Set Address = '123 new ave'
> WHERE name = 'Bally's'

> I tried double quotes, triple double quotes........help!

> Thanks

> Stephan



Mon, 20 Oct 2003 11:21:33 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Sigle quote in Select and Update query

2. Sigle quote problem in SQL Statement in SQL

3. Sigle quote problem in SQL Statement in SQL

4. SQL Update query and Quote character in VB CGI

5. ADO Execute fails to update prior to subsequent select query

6. Selecting from a Parameterised query in a CreateQuerydef'd query

7. Updating Links in Word 2002, Edit, Links, Select Update Method

8. Getting a resultset from a SELECT query embedded in another query

9. SELECT queries - Use Access query or not?

10. Dialog box displayed during query insert or query update

11. VB6,Access97,ADO - Update query doesn't update all records

12. Question about Quotes in SELECT statements

 

 
Powered by phpBB® Forum Software