Trouble with strings in SQL Statement

[hipopony66]

I'm having a terrible time using strings in my SQL statement. If one of the fields contains a ' then I get an syntax error. I've tried several ways to try an fix this, this is the most recent. Any help with this is EXTREMELY appreciated as I have been stuck on this for 4 days now.

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

[RSINGH]

I think you're replacing with the wrong characters. Try as in the following example.

Replace(strFilepath, "'", "''") etc...

[hipopony66]

and I still get the same error.

[RSINGH]

I just noticed...

There's 2 things wrong with the SQL statement. Firstly, keep the Replace they way it is in my example.

The syntax eror you're getting is due to you not specifying the fields you want to insert to (just noticed that now. )

It should be of the form

INSERT INTO tblTableName(Column1,Column2...ColumnN)
VALUES(Value1,Value2...ValueN)

[hipopony66]

Ok great! Now I'm getting a syntax error in my SQL statement. Also you don't need the column names unless your adding info to items that are not in order. Anyway, I'm getting a syntax error now. What's wrong with it now?

'Use SQL to insert the information in the strings into the table
SQL = "INSERT into " & table & "(FilePath, Filename, Filesize, Artist, Title, Album, Frequency, Bitrate, Length, Track)VALUES ('" _
& Replace(strFilepath, "'", "''") & "','" _
& Replace(strFileName, "'", "''") & "','" _
& Replace(strFileSize, "'", "''") & "','" _
& Replace(strArtist, "'", "''") & "','" _
& Replace(strTitle, "'", "''") & "','" _
& Replace(strAlbum, "'", "''") & "','" _
& Replace(strFrequency, "'", "''") & "','" _
& Replace(strBitrate, "'", "''") & "','" _
& Replace(strLength, "'", "''") & "','" _
& Replace(strTrack, "'", "''") & "')"
Cnn.Execute (SQL)

[ralph]

should be a space in between:
Track)VALUES

btw- I don't know if vb is forgiving on this (using function syntax when nothing is returned). If not, try no parentheses.
Cnn.Execute SQL