Connecting to database using include file

misskaos · October 20th, 2006, 03:47 PM

I am trying to connect to my database with an include file.

The name of my include file is
dbConn.asp

Code:

<%
Dim Connection, RS
Dim recount
Connection = "Provider=SQLOLEDB.1;UID=sa;PWD=;Initial Catalog=ECPTool2;Data Source=localhost"
Set RS = server.CreateObject("ADODB.RecordSet")
%>

and I am using it here, in my postnew.asp page

Code:


RS.open "INSERT INTO ecpForms (fields) VALUES ('" & field values & "')", Connection

I am not receiving an error message, I really wish I was! At least then I'd have a better idea of what was wrong.. but anyways, I'm not receiving an error. Right now I have the On Error Resume Next in there jus so it would do half of what I wanted, but if I remove it, all it does is reset itself. Does not insert my record or anything.

Any ideas???

T.B.
[email protected]

ricespn · October 21st, 2006, 01:55 AM

Try This..

This part goes in your include file "dbConn.asp"
<%
Option Explicit
DIM strConnect ' Variable to hold connection string
strConnect = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=ECPTool2;Data Source=(local)" ' Assign Connection String to variable
%>

This part goes in your "postnew.asp"

<%
DIM objComm 'Variable to Hold Command Object
Const adCmdText = &H0001 'Constatnt to Hold
SET objComm =Server.CreateObject ("ADODB.Command") ' Create Command Object
objComm.ActiveConnection = strConnect ' Set the connection active usng the string value from the include file
objComm.CommandText = "INSERT INTO ecpForms (fields) VALUES ('" & field values & "')" 'Insert Values Into Table ecpForms
objComm.CommandType = adCmdText 'Set command type to value &H0001 This value was taken from the ADO constant Library file located at : C:\Program Files\Common Files\System\Ado\adovbs.inc
objComm.Execute 'Execute transaction
SET objComm = NOTHING 'Clear object from memory
%>

=======================
Strange and crazy, but everything is possible

ricespn · October 21st, 2006, 02:15 AM

If you don't want to type a connection string or to make sure that the string connection works do this:

1. open notepad and save the file on your desktop using any name with extension ".UDL" this is going to create a Data Link file.

2. Double click the file and is going to open the Microsoft Data Link, from there you can configure your connection string and test it.

3. Save the connection string a pop up window will ask you that the connection string will be saved in plain text, just click yes or OK.

4. Now open that file with notepad again and you will see the connection string reated for you and ready to copy and paste.

This option gives you the advantage to create a connection string using any database adaptor and test it first.

Have fun... :)

=======================
Strange and crazy, but everything is possible

woodyz · October 24th, 2006, 10:08 AM

Hello misskaos,

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

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

Have fun

Woody Z http://www.learntoprogramnow.com

misskaos · October 26th, 2006, 10:50 AM

thank u, this works great.
Const adCmdText = &H0001
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = Connection
objCommand.Commandtext = "INSERT INTO ecpForms (ecpNumber, ecpTitle, ecpFilename, ecpOriginator, ecpDateDistributed, ecpDateDue, ecpSMEcomplete, ecpPMcomplete) VALUES ('" & pEcpNumber & "', '" & pEcpTitle & "', '" & pEcpFilename & "', '" & pEcpOriginator & "', '" & pEcpDateDistributed & "', '" & pEcpDateDue & "', '" & pEcpSMEcomplete & "', '" & pEcpPMcomplete & "')"
objCommand.CommandType = adCmdText
objCommand.Execute
Set objCommand = nothing

    emailMessage = "Do not reply. This is an automatic email." & Chr(13)
    emailMessage = emailMessage & "ECP Number: " & pEcpNumber & Chr(13)
    emailMessage = emailMessage & "ECP Title: " & pEcpTitle & Chr(13) & Chr(13)
    emailMessage = emailMessage & "New ECP form just posted on forum." & Chr(13) & Chr(13)

T.B.
[email protected]