I'm about to quit!

[hipopony66]

I can take VB, but I HATE VB w/ SQL! I've been messing with this forever, and I have to have gotten EVERY possible error. What's wrong with it now? There's supposedly another error in the SQL statement. Someone help me before I give up on my program!

SQL = "INSERT into " & table & "(FilePath, Filename, Filesize, Artist, Title, Frequency, Bitrate, Length) VALUES ('" _
& Replace(strFilepath, "'", "''") & "','" _
& Replace(strFileName, "'", "''") & "','" _
& Replace(strFileSize, "'", "''") & "','" _
& Replace(strArtist, "'", "''") & "','" _
& Replace(strTitle, "'", "''") & "','" _
& Replace(strFrequency, "'", "''") & "','" _
& Replace(strBitrate, "'", "''") & "','" _
& Replace(strLength, "'", "''") & "')"
Cnn.Execute (SQL)

[jim mcnamara]

You need to learn a debugging trick or two.

When you have a blob of code like you have print out the string.
In this case, find a way to print SQL.

Open"somefile.dbg" for output as #1
print #1, SQL
close #1

If you can read SQL (the language) and also see what you are actually trying to put in the insert syntax, you can fix it.

[Jay Rogozinsky]

OK,OK.

Show me the rest, I can help.

[Hack]

Dont give up. I've had these kinds of problems with SQL statements 10 times longer than yours...

Here is what I'd like to see.

Run that SQL statement through a Debug.Print SQL. Cut the results out of the Immediate Window, and paste them here.

I want to see exactly what is happening.

[jim mcnamara]

As we speak, I'm working on code (in Pro-C - Oracle pre-compiler)
with a SQL statement that covers over 5 pages of text. A 9 table query.

We can probably read what you have. And fix it. But.
What happens when Hack isn't around? The big thing in programming is learning ways to fix and debug code, not waiting on somebody to answer you on a forum.

[hipopony66]

But I'm fairly new to SQL so I don't know much about the language.

[Hack]

And you can count on help in getting more experienced...so, what did the result of your Debug.Print SQL statement reveal?

[hipopony66]

I'm not sure if I did it right, but I put: "Debug.Print" in front of "SQL=" in my SQL statement. Then I executed the code. I got an error "Command text was not set for command object", and "FALSE" in the immediate window. Did I do this right?

[Hack]

Do this

SQL = "INSERT into " & table & "(FilePath, Filename, Filesize, Artist, Title, Frequency, Bitrate, Length) VALUES ('" _
& Replace(strFilepath, "'", "''") & "','" _
& Replace(strFileName, "'", "''") & "','" _
& Replace(strFileSize, "'", "''") & "','" _
& Replace(strArtist, "'", "''") & "','" _
& Replace(strTitle, "'", "''") & "','" _
& Replace(strFrequency, "'", "''") & "','" _
& Replace(strBitrate, "'", "''") & "','" _
& Replace(strLength, "'", "''") & "')"
'Cnn.Execute (SQL)
Debug.Print SQL

[Jay Rogozinsky]

OK, I could get long-winded here, but I won't. For me, SQL is not the best way to do all database access.

If you are simply trying to add a record, look up the AddNew method of the RecordSet object (it's quite easy). This would be good cause it would show you what the non-SQL functionality is.

Additionally, these objects are compatable with DAO/RDO/etc.

[Hack]

Originally posted by Jay Rogozinsky
If you are simply trying to add a record, look up the AddNew method of the RecordSet object (it's quite easy). This would be good cause it would show you what the non-SQL functionality is.

There is another consideration.

I'm a contract programmer. I'm required to use the data access method of my current client, i.e., DAO/RDO/ADO

I'm also required to write my code by there current coding standards. I've had clients that required I use VB's .AddNew/.Edit/.Delete/.Update etc.

I've had clients that require I use SQL for all database operations.

I've had clients that didn't care.

I believe it behooves programmers to know how to use them all. Even if you are not a contract programmer, who is to say you will remain with the company at which you are currently employed for the remainder of your career?

The more different ways you know of doing the same thing, the better off you are in a wide variety of categories. (Incidentally, in the remote case someone might be interested, I'm required to use DAO and SQL at my current client site).

[hipopony66]

I'm not sure if SQL is the best way to do what I'm wanting to do. I don't want to give up on it, but I think there might be an easier way to do it for my purposes. I have a Access 97 database that I will distribute with my program. All I want to do is add tables to the database, based on what the user inputs, and then add records to the to tables. I will get info on files in a filelistbox by looping through the filelistbox and storing the info as strings. Then I want to add the strings to the tables. Is SQL the best way for me to do this, or is there an easier way I might use?