You have left out a lot of information about your problem: I suggest you try to better describe what your issues are.
- EXECUTIVE SUMMARY
However, your main issue deals with VBScript syntax errors,, SQL insert statement errors, and use of "On Error Resume Next". You also have a minor faux pas usnig RS.Open to run an insert statement.
- PROBLEM ONE: Using On Error Resume Next to suppress errors
You mention using "On Error Resume Next" in this situation. THIS IS A VERY BAD IDEA. Never use On Error Resume Next except when you have a specific reason and know what you are doing and why it is reasonable for your specific case: For example, if it is part of an error handling mechanism that detects when errors have occured by checking the err.number at strategic points . If you actually are using "On Error Resume Next" as you state, then that is the reason you aren't getting any error messages - "On Error Resume Next" suppresses errors. You don't want that. You really do want to see all the errors for now.
- PROBLEM TWO: Using Recordset.Open to invoke an Insert Statement
Okay - First of all, you shouldn't be using the recordset object for inserting records! The recordset object is best used for its intended purpose which is to retrieve a recordset. The post by ricespn gives the idea of a better alternative. Still, the insert statement does get run when you use it as the sql for the recordset.open method, so you should be seeing the records being created IF YOUR SQL WAS PROPERLY FORMATTED, which I suspect is not the case, and if your VBScript code for the RS.Open line was properly written, which it IS NOT.
- PROBLEM THREE: The SLQ Statement is malformed
The SQL issue is in your use of the single quotes to enclose the entire VALUES list. The syntax for a sql insert VALUES is a list of values with each value separated by a comma. Strings (and usually dates) have to be enclosed in single quotes, but NOT THE WHOLE LIST. If you are inserting into a table using only one field, and that the datatpe for that field is a string, then the SQL is correct, but I suspect this isn't your intention. There is probably a problem with the "(fields)" part of your SQL statement if "fields" is not an actual column in your ecpForms table. I am guessing that you really intend the word "fields" to be a vb
string that you created somewhere else (or that it is a method that returns the list of fields) and it contains (or creates) the fields list needed by the sql statement. See the VBScript part below to see the fix for that.
- PROBLEM FOUR: VBScript Syntax Problems
The issue with your VBScript code is that the that you are building a string with syntax that will blow up when compiled (or interpreted in the case of VbScript). Because you have "On Error Resume Next", this error is being suppressed and the code continues on as if everything is fine.
Your code which reads as follows:
RS.open "INSERT INTO ecpForms (fields) VALUES ('" & field values & "')", Connection
Should probably be changed to the folloing:
RS.open "INSERT INTO ecpForms (" & strFieldsSql & ") VALUES (" & strFieldValuesSql & ")", Connection
Notice that I changed the naming convention for the strFieldsSql and strFieldValuesSql variables so their intention is a bit clearer. If these are not variables, but rather method calls (otherwise known as functions) then you might better njame them as follows:
RS.open "INSERT INTO ecpForms (" & GetFieldsSql & ") VALUES (" & GetFieldValuesSql & ")", Connection
You will get the hang of this soon enough - but remember - DON'T shut off the error handling just to get your code to run!!! I can't stress this too much. The error handling should be in place to allow you to discover the problems and fix them. It is important especially in a scripting environment like ASP since you don't have the benefit of a compiler to find your syntax errors.
- THE END
Woody Z http://www.learntoprogramnow.com