SQL problem due to Chr(39) ' 
Author Message
 SQL problem due to Chr(39) '

Hi,

I use the SQL below in VB using DAO.
If strDocName or strDocTitle contains a ' character,
it generates error 3075, which is logical, due to the
Chr(39) in these variables.
How can I get around this problem and still put
a value containing Chr(39) in the dB?

    strSQL = "INSERT INTO Doc " & _
             "(ID, Doc, Title) " & _
             "VALUES" & "('" & vntID & "', '" & strDocName & _
                          "', '" & strDocTitle & "')"

--
Thanks in advance!
Ali Eghtebas



Fri, 30 Nov 2001 03:00:00 GMT  
 SQL problem due to Chr(39) '
Ali,

Use the Replace (VB6) function on your variables, ie:

strSQL = "INSERT INTO Doc (ID, Doc, Title) VALUES" & "('" & vntID & "', '" &
Replace(strDocName, "'", "''") & "', '" & Replace(strDocTitle, "'", "''") &
"')"

--
Regards

VB-Joker
Carsten Thomsen/MCSE

PLEASE post ALL replies to the newsgroup(s) so we can all benefit from the
discussion!

Quote:
> Hi,

> I use the SQL below in VB using DAO.
> If strDocName or strDocTitle contains a ' character,
> it generates error 3075, which is logical, due to the
> Chr(39) in these variables.
> How can I get around this problem and still put
> a value containing Chr(39) in the dB?

>     strSQL = "INSERT INTO Doc " & _
>              "(ID, Doc, Title) " & _
>              "VALUES" & "('" & vntID & "', '" & strDocName & _
>                           "', '" & strDocTitle & "')"

> --
> Thanks in advance!
> Ali Eghtebas



Fri, 30 Nov 2001 03:00:00 GMT  
 SQL problem due to Chr(39) '
I use VB5. Is there any other way?



Quote:
> Ali,

> Use the Replace (VB6) function on your variables, ie:

> strSQL = "INSERT INTO Doc (ID, Doc, Title) VALUES" & "('" & vntID & "',
'" &
> Replace(strDocName, "'", "''") & "', '" & Replace(strDocTitle, "'", "''")
&
> "')"



Fri, 30 Nov 2001 03:00:00 GMT  
 SQL problem due to Chr(39) '
Yes there is! In case anyone needs to know:
Just replace ' with '', this means Chr(39) & Chr(39).

/Ali



Quote:
> I use VB5. Is there any other way?



Fri, 30 Nov 2001 03:00:00 GMT  
 SQL problem due to Chr(39) '
Yep, sure is.  Roll the function yourself.  Check out this KB article on how
to implement several of VB6's new commands in VB5:

http://support.microsoft.com/support/kb/articles/Q188/0/07.ASP

--

Later,
Tony Fountain, MCP
===================================================
Please do not email me directly, my wife already clutters up my inbox
===================================================

Quote:
> I use VB5. Is there any other way?



> > Ali,

> > Use the Replace (VB6) function on your variables, ie:

> > strSQL = "INSERT INTO Doc (ID, Doc, Title) VALUES" & "('" & vntID & "',
> '" &
> > Replace(strDocName, "'", "''") & "', '" & Replace(strDocTitle, "'",
"''")
> &
> > "')"



Fri, 30 Nov 2001 03:00:00 GMT  
 SQL problem due to Chr(39) '
Thanx again Tony :-)
I found the URL very useful!

Regards
Ali Eghtebas

Tony Fountain <tonyfo#primenet.com> skrev i inl?gg

Quote:
> Yep, sure is.  Roll the function yourself.  Check out this KB article on
how
> to implement several of VB6's new commands in VB5:

> http://support.microsoft.com/support/kb/articles/Q188/0/07.ASP

> --

> Later,
> Tony Fountain, MCP



Sat, 01 Dec 2001 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Chr(10) and Chr(13) doesn't work

2. SQL and ' (ASCII 39) character problem

3. Problem with SQL 'Like' Clause

4. Problems with SQL 'Like' clause

5. SQL 'Like' statement problem

6. SQL '*' problem

7. SQL '*' problem

8. Access won't start due to DAO

9. Updating a flag date due to a user's event

10. Task's due date and reminder date

11. Changing a task's due date

12. HRESULT errors due to crystal dll's

 

 
Powered by phpBB® Forum Software