Access DB probs in ASP/VB at 'set RS=' line

[Solitude1056]

I've got a survey with a working conn, but once the proc page gets to the recordset point, it throws the following error:

Microsoft OLE DB Provider for ODBC Drivers error "80004005"
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

The webpage processes the form's results from the prior page, and is supposed to then enter the info into the database. When the SQL string is tested in Access, it's fine, but I get an error once I try it through the live webpage. I've debugged enough and have discovered the error is from the third line of the following relevant part of the code:

SQL = "insert into Table1 (ID,Member,Degree,Title) "
SQL = SQL & "Values(" & intUID & "," & intMember & "," & intDegree & "," & intTitle & &quot "

set RS = Server.CreateObject("ADODB.Recordset&quot

RS.Open SQL, Conn
Conn.Execute SQL

I've found webpages that suggest Access does best if you Addnew and *then* get the ID/Auto-number for the new record (I have the first page on the survey getting max(ID) instead). When I tried Addnew, it also threw an error. If anyone has any ideas, I'd really love to hear them.

 

[KyleS]

My ASP knowledge is (unfortuneately) not where (I think) it should be, but I do know ADO & SQL and I don't think your problem the "Set.." statement is your issue.

Your SQL statement is an action (Append Query in Access) and will not return records, so trying to use it to open a recordset should give you an error.

From the looks of what you have you could just remove these 2 lines and it should work from there.
Set RS = Server.CreateObject("ADODB.Recordset&quot

RS.Open SQL, Conn

Let me know how this works for you Kyle

 

[jimmythegeek]

Kyle is correct. If you are going to use the .Execute method, you don't HAVE to open a recordset. You simply create your SQL Statement, then execute it. So long as your connection has been established somewhere prior to the .Execute (normally in a header or include file), you should be fine. This works especially well on action queries (insert, delete, update).

==========
SQL = "insert into Table1 (ID,Member,Degree,Title) "
SQL = SQL & "Values(" & intUID & "," & intMember & "," & intDegree & "," & intTitle & &quot "
Conn.Execute SQL
==========

You only need to open a recordset if you are goint to use the information in it (looping, editing, populating tables, etc.). You can also combine the two:

Dim SQL, rs

SQL = "SELECT * FROM MyTable ORDER BY MyField"
Set rs = Conn.Execute SQL

Do Until rs.EOF
.....

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[Solitude1056]

Unfortunately, I still get an 500 error. I commented out those lines like suggested, and it still doesn't work. I'm not sure if it's making matters worse that I'm using a DSNless connection, or if that's irrevelant. Also, when I've tried to use suggested code that signifies things like cursors and whatnot, it still errors out.

Any other ideas?