Bret Griswold
|
| Posted: 03/02/2005, 7:31 AM |
|
I have an AfterInsert event that does the following
strSQL = "SELECT TicketId FROM TICKETS WHERE TITLE = '" & fldTitle & "' AND DESCRIPTION = '" & fldDescription & "'"
This allows me to get the TicketId that was just created in my SQL database. Works fine EXCEPT when there is an apostrophe in the Title or description. I get an invalid syntax on the SELECT command. SELECT * FROM TICKETS WHERE TITLE = 'Test' AND DESCRIPTION = 'Can't'
Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near 't'.
I have tried using other text quoters ( " " and [ ] ) but that returns others errors.
Can anyone suggest something - please!! Thanks!
|
|
|
 |
Brandon
|
| Posted: 03/02/2005, 9:32 AM |
|
This is common question in SQL.
Use a double apostrophe instead of a single apostrophe such as:
SELECT * FROM TICKETS WHERE TITLE = 'Test' AND DESCRIPTION = 'Can''t'
|
|
|
|
Bret
Posts: 13
|
| Posted: 03/02/2005, 12:27 PM |
|
Thanks for the response - but I can't use the double apsotrophe as you indicated - because Can't is entered onto the form by the user into the description field.strSQL = "SELECT TicketId FROM TICKETS WHERE TITLE = '" & fldTitle & "' AND DESCRIPTION = '" & fldDescription & "'"
Or are you suggesting to use them as follows ?strSQL = "SELECT TicketId FROM TICKETS WHERE TITLE = '" & fldTitle & "' AND DESCRIPTION = ''" & fldDescription & "''"
|
 |
|
Benjamin Krajmalnik
|
| Posted: 03/02/2005, 4:01 PM |
|
Brandon,
There is a "ToSQL" method in the connection which will take care of that for
you.
|
|
|
|
Nicole
Posts: 586
|
| Posted: 03/03/2005, 2:49 AM |
|
Bret,
I see you’re using CodeCharge. Connection object doesn’t provide toSQL() method there. Use separate ToSQL function:
strSQL = "SELECT TicketId FROM TICKETS WHERE TITLE = " & ToSQL(fldTitle, "Text") & " AND DESCRIPTION = " & ToSQL(fldDescription, "Text")
_________________
Regards,
Nicole |
|
|
|
