Adding Record to DB...

[dodgyone]

I have a form on a standard htm page which posts the information to the asp add record page. The code to add the record is as follows:

<%

'open the database using a DSN-less connection
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.ConnectionString=&quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot; & _
&quot;DBQ=C:\Documents and Settings\csbexec\My Documents&quot; & _
&quot;\Intranet\Databases\Worklist.mdb&quot;

Dim objRS
'Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
objRS.Open &quot;Worklist&quot;, objConn, , adLockOptimistic, adCmdTable

objRS.AddNew
objRS(&quot;Name&quot = Request.Form(&quot;Name&quot
objRS(&quot;Event&quot = Request.Form(&quot;Event&quot
objRS(&quot;DateStart&quot = Request.Form(&quot;DateStart&quot
objRS(&quot;DateFinish&quot = Request.Form(&quot;DateFinish&quot
objRS(&quot;TimeStart&quot = Request.Form(&quot;TimeStart&quot
objRS(&quot;TimeFinish&quot = Request.Form(&quot;TimeFinish&quot
objRS(&quot;Description&quot = Request.Form(&quot;Description&quot
objRS.Update
objRS.Close
Set objConn = Nothing

%>

Could somebody please suggest an answe to why I get the error message:

Error Type:
ADODB.Recordset (0x800A0E7D)
Operation is not allowed on an object referencing a closed or invalid connection.

It points to the line:

objRS.Open &quot;Worklist&quot;, objConn, , adLockOptimistic, adCmdTable

Many thanks...

 

[link9]

You have not opened your connection, and coincidentally, looks like your instantiation of your recordset is commented out.

objConn.ConnectionString=&quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot; & _
&quot;DBQ=C:\Documents and Settings\csbexec\My Documents&quot; & _
&quot;\Intranet\Databases\Worklist.mdb&quot;
objConn.open
Dim objRS
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
objRS.Open &quot;Worklist&quot;, objConn, , adLockOptimistic, adCmdTable

 

[dodgyone]

Thanks for getting back to me... I've added the open connection for objConn but I now get the error:

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/intranet test/testing/worklist/AddRecordProcess.asp, line 14

It focuses on line:

objRS.Open &quot;Worklist&quot;, objConn, , adLockOptimistic, adCmdTable

I just cannot see what is wrong with this?

Cheers for your help....

 

[ToddWW]

This is just personal preference. Here's how I update my DB.

<% 
dim objConn
dim strProvider
dim strQuery

strProvider = &quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot; & &quot;DBQ=C:\Documents and Settings\csbexec\My Documents\Intranet\Databases\Worklist.mdb&quot;

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)

strQuery = &quot;INSERT INTO worklist (name,event,datestart,datefinish,timestart,timefinish,description) VALUES ('&quot; & Request.Form(&quot;Name&quot;) & &quot;', '&quot; & Request.Form(&quot;event&quot;) & &quot;', '&quot; & Request.Form(&quot;DateStart&quot;) & &quot;', '&quot; & Request.Form(&quot;DateFinish&quot;) & &quot;', '&quot; & Request.Form(&quot;TimeStart&quot;) & &quot;', '&quot; & Request.Form(&quot;Description&quot;) & &quot;')&quot;

objConn.Open strProvider
objConn.Execute strQuery
objConn.Close
set objConn = Nothing    
%>

I've set all of your values as string values (surrounded by single quotes). In MSAccess, surround dates with #'s. So if those are true date fields, the SQL would look like this.

strQuery = "INSERT INTO worklist (name,event,datestart,datefinish,timestart,timefinish,description) VALUES ('" & Request.Form("Name") & "', '" & Request.Form("event") & "', #" & Request.Form("DateStart") & "#, #" & Request.Form("DateFinish") & "#, '" & Request.Form("TimeStart") & "', '" & Request.Form("Description") & "')"

Basically, what we're doing there is building a long string. Then we execute the SQL string to the databse with the objConn.Execute method.

Now, what I like to do is issue a Response.Write strQuery right before the objConn.Execute method and maybe even comment out the execute method. This way, I can see the SQL string in the browser window and troubleshoot any problems, etc.. When I'm happy, I comment out the Response.Write and enable the objConn.Execute.

ToddWW

 

[dodgyone]

Thank you... it works great...