Hi, I am trying to insert a text value into an SQL table. The name of the field is comments. And its contents can be a word, a sentence or a paragraph.

Example:

The house’s door was broken. Willie’s dog was who did it.

As you can see this sample has two apostrophizes. When I try to insert this text into SQL. SQL return with this value.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 's'.
I am calling this procedure from an ASP Page.
Can someone help with this issue? I know that there is a solution.



arnniema

__________________
arnniema

Hi, is me again. I found the solution. This is how to do it using vbscript:
1. first remove any leading spaces.
StatusComment = trim(txtStatusComment)
2. find the length of the variable in question
L=len(trim(StatusComment))
3. declare a dommy variable
Dim TXT1
4. write a loop
for z = 1 to L
5. write a decision testing to look for an apostrophy
6. while running the loop test for the presents of the apostrophy
7. if it is found add the apostrophy code as it is below
8. else add the characters that aren’t apostrophy until the loop end.
   if mid(StatusComment, z, 1) = "'" then
        TXT1 = TXT1 & "'" & chr(39) & ""
   else
        TXT1 = TXT1 & mid(StatusComment, z, 1)
   end if
next


The entire sample code
***********************
StatusComment = trim(txtStatusComment)
L=len(trim(StatusComment))
Dim TXT1
for z = 1 to L
    if mid(StatusComment, z, 1) = "'" then
        TXT1 = TXT1 & "'" & chr(39) & ""
    else
    TXT1 = TXT1 & mid(StatusComment, z, 1)
    end if
next


arnniema

Hii arnniema!!

user replace method
i.e.
vLastName = Replace(vLastName, "'", "''")

now use this vLastName in ur sql statement.
plz check the link
http://searchvb.techtarget.com/tip/1...my=%2Fpr%2F4f0

Hope this will help you




Cheers :)

vinod