Error on ASP INSERT into MsAccess DB

Brainfishes · #1 (**permalink**) 03-26-08, 10:24

Hi,

I'm getting a peristant error while I'm trying to add records to my database... I'm a novice at ASP and the error messages aint exactly helpful...

So the error is:

ADODB.Recordset error '800a0e7d'

The connection cannot be used to perform this operation. It is either closed or invalid in this context.

/b/r/a/brainfishes/AOD/home/add_worker.asp, line 11

My code is:

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<%
Dim DBCON
dim RS
dim SQL

DBCON = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="  & 
"C:\sites\content\b\r\a\brainfishes\db\data.mdb" & ";"

set RS=Server.CreateObject("ADODB.recordset")
RS.Open DBCON

SQL= "INSERT INTO links (link_name,link_url) VALUES (""" & 
request.form("link_name") & """',"
SQL = SQL & "" & request.form("link_url") & """)"

response.write(SQL)

RS.execute SQL

RS.close

response.Redirect("http://brainfishes.brinkster.net/AOD/home/links.asp")

%>

I've double checked all the field names and the location of the DB, it's all correct... can someone please tell me where I'm going wrong?

Thanks,

Brainfishes.

gvee · #2 (**permalink**) 03-26-08, 10:59

Recordsets are for select commands.

You do not need one for an insert; you execute the command directly on the connection.

So you need to create a connection object instead of a recordset.

http://w3schools.com/ado/default.asp for more inforamtion

Brainfishes · #3 (**permalink**) 03-26-08, 15:18

Thanks for the info, got it fixed and running now, managed to add a delete record function too!

Brainfishes

gvee · #4 (**permalink**) 03-26-08, 18:30

Great news! Fancy sharing your working code with the rest of the community?

Brainfishes · #5 (**permalink**) 03-26-08, 19:01

Sure,

My Add code is now:

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<%
dim conn

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\sites\content\b\r\a\brainfishes\db\data.mdb"

SQL= "INSERT INTO links (link_name,link_url) VALUES ('" & request.form("link_name") & "',"
SQL = SQL & "'" & request.form("link_url") & "')"

conn.execute SQL

conn.close

response.Redirect("http://brainfishes.brinkster.net/AOD/home/links.asp")

%>

I had to make a few changes to the syntax of my SQL statement too, there were a few too many quotation marks it seems.

My Delete code is very similar:

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<%
dim conn
dim rname

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\sites\content\b\r\a\brainfishes\db\data.mdb"

rname = request.form("link_name")

SQL= "DELETE FROM links WHERE link_name='" & rname & "'"

conn.execute SQL

conn.close

response.Redirect("http://brainfishes.brinkster.net/AOD/home/links.asp")

%>

Once again, thanks for your help... as I said before I'm new at ASP and the error messages it produces are, shall we say, less than helpfull!

Cheers,

Brainfishes.

gvee · #6 (**permalink**) 03-26-08, 20:03

Hehe, the error messages have a tendancy to be a little cryptic!

Thanks for posting back; I am a firm believer of adding the solution to a thread so that future readers may benefit

If you have any more problems, you know where to come!!

Internet Services

Web Development

Digital Marketing

Consumer Tech