Unresolved issue....Strings in SQL Statement

[hipopony66]

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.

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)

[Joacim Andersson]

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

[hipopony66]

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


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

[darre1]

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

try this

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)

[sbarkeruk]

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??

[darre1]

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

[Grimfort]

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.

[kleinma]

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

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

then in code

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

[Grimfort]

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

[RSINGH]

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.