Hmm..This is odd. Need some help.

[hipopony66]

I'm using the SQL below to add information about files in a filelistbox to a table. On certain groups of files it works great, but on others, it gives an error "Syntax Error in Insert Into Statement". Why would this work on some files and not others? They are all Mp3 I am dealing with. Any help is extremely appreciated. I don't understand this one. Here's the code:

'Use SQL to insert the information in the strings into the table
SQL = "INSERT into " & table _
& "(FilePath, Filename, Filesize, Artist, Title) VALUES ('"_
 & Replace(strFilepath, "'", "''")_
 & "','" & Replace(strFileName, "'", "''") _
& "','" & Replace(strFileSize, "'", "''") _
& "','" & Replace(strArtist, "'", "''") & "','" _
& Replace(strTitle, "'", "''") & "')"
 
Cnn.Execute (SQL) 'THE ERROR OCCURS ON THIS LINE
 
Next

[Lord_Rat]

strArtist and strTitle are probably blank on the ones it is failing.

You need to detect that and insert NULL into the database instead of ""

[hipopony66]

Where would be the best place to detect that? When it reads the tag first and have 2 separate SQL Statements? Or is there a way to insert NULL if the field is blank?

[hipopony66]

All of my files have ID3 v1 tags. So the Artist and Title fields are always >0. Any other ideas?

[Lord_Rat]

Another though.

Do you have the following characters in any of them?

" & | #

[hipopony66]

Some filenames contain "&" (i.e. Huey Lewis & The News)

[MartinLiss]

Your problem is probably that there is a space in one or more of the fields. To get around that problem put "[" and "]" around the field names in the SQL.

[hipopony66]

I think I did what you said, but I'm still getting the error, can you give an example of what you meant?

[MartinLiss]

I think this is correct but I don't have time to test it. Change all variable names like the following.


('[" & Replace(strFilepath, "']", "''")

[Lord_Rat]

The characters I listed are reserved characters for SQL. You should consider escaping them.

In this Thread:
http://www.vbforums.com/showthread.p...hreadid=166453

Kira posted a good example of how to do that.