Unresolved issue....Strings in SQL Statement

Hey, I still can't figure out how to get this to work. I am adding strings into the table in SQL statement. This works fine except there is an error if the string contains a single quote. So this is how it was suggested that I do this, but I'm still getting the error. If anyone can help me out with this it would be greatly appreciated. Thanks.

VB Code:

SQL = "INSERT into " & table & " VALUES ('" & Replace(strFilepath, "'", "''") & "','" & Replace(strFileName, "'", "''") & "','" & Replace(strFileSize, "'", "''") & "','" & Replace(strArtist, "'", "''") & "','" & strTitle & "','" & Replace(strAlbum, "'", "''") & "','" & Replace(strFrequency, "'", "''") & "','" & Replace(strBitrate, "'", "''") & "','" & Replace(strLength, "'", "''") & "','" & Replace(strTrack, "'", "''") & "')"
Cnn.Execute (SQL)

:confused:

Are you sure there is not a single quote in the strTitle string?

Now I'm getting this error: "Error in String Syntax: "Stevie Ray Vaughn'


Any Idea what the problem is now!????

i think having any ' chracters even if they,re doubled up will cause a problem...

try this

VB Code:

SQL = "INSERT into " & Table & " VALUES ('" _
    & Replace(strFilepath, "'", """") & "','" _
    & Replace(strFileName, "'", """") & "','" _
    & Replace(strFileSize, "'", """") & "','" _
    & Replace(strArtist, "'", """") & "','" _
    & strTitle & "','" _
    & Replace(strAlbum, "'", """") & "','" _
    & Replace(strFrequency, "'", """") & "','" _
    & Replace(strBitrate, "'", """") & "','" _
    & Replace(strLength, "'", """") & "','" _
    & Replace(strTrack, "'", """") & "')"
Cnn.Execute (SQL)

:)

I agree with the comment that using variables will solve the situation for basic queries. For example, if I use strText and the variable contains one or more single quote characters then this will pass ok with VB code such as:

lstrSQL = "SELECT * FROM tblWhatever " & _
"WHERE ColumnA = ' " & strText & " ' "

HOWEVER the problem I am now experiencing is that I am getting into stored procedures and passing a variable to a stored procedure no longer disregards the single quote contents of a variable!! So I am back to square one!!

Basically, the following (derived from above) will no longer work IF one or more single quote character is included:

lstrSQL = "EXEC sp_procName & " ' " & strText & " ' "

I know that changing the single quotes here to double quotes via Chr(34), WILL accept my single quote but then I will get a problem if the strText variable contains one or more double quote, so I am no further forward.

So what other alternatives are there for use with stored procedure??

instead of replacing them with double quotes, just remove them, by replacing them with nothing ("") that should do it

The reason you are getting the error is as follows.

The data string you posted = DataText'

(notice the single quote at the end)

and when you run the replace it looks like this

DataText''

and then you add it to the SQL string as a whole and get

'DataText'''

(now 3 single quotes at the end).

Now to the SQL the string it look like you are trying to pass two strings into one parameter like so:

'DataText' ' ' (spaces added to show)

------------

Theres your problem, now I need to fix it myself for some code im working on (you helped find my problem early :)). When I come up with something Ill add it here. But try adding yet another single quote into the equation (e.g 'DataText'''') that may work but will post later what Ive found.

what i do for my sql statements was i made a public function in a module.. it saves space and makes the sql string less crowded

VB Code:

public function apost(TextString as string) as string
    apost = replace(TextString,"'","''")
end function

then in code

Code:

---

sSql = "INSERT INTO tblMyTable [Name],[Company],[PhoneNum] VALUES('" & apost(txtName) & "','" & apost(txtCompany) & "','" & txtPhoneNum & "')"

---

Ive done some tests now, and found that having the extra ' quote at the end is not the reason as I can add data like that to my database.

Check your code for something else :) probly a missing ' somewhere

Replace your execute with a debug.print so that you can see the the created SQL statement. Paste that into T-SQL and see if that runs.