ASP page trouble with INSERT query to Access database 1

[cdck]

I have an ASP page that is being used to instigate a new record in the database, then call back the record ID for that new item. The following is the code of the page:
----------------------------------
<%
Dim strconnect, SQL1, SQL2, Con, PR, rstemp, dirPR, tag

tag = CStr(Now())
response.write tag
strconnect="PR"

SQL1 = "INSERT INTO PReq (identifier, complete) VALUES ('" & tag & "', 'no');"
response.write "<P>" & SQL1
SQL2 = "SELECT PR FROM PReq WHERE PReq.identifier = '" & tag & "';"
response.write "<P>" & SQL2

Set Con = Server.CreateObject("ADODB.Connection")
Con.Open strConnect
Con.Execute(SQL1)
Con.Close

Con.Open strConnect
Set rstemp=Con.Execute(SQL2)

PR = rstemp(0)
dirPR = "PRStartStep1.asp?PR=" & PR

rstemp.Close
Set rstemp = Nothing
Con.Close
Set Con = Nothing

response.write dirPR
%>
--------------------------------------
The following is the result that I get:
--------------------------------------
1/16/2007 2:08:04 PM

INSERT INTO PReq (identifier, complete) VALUES ('1/16/2007 2:08:04 PM', 'no');

SELECT PR FROM PReq WHERE PReq.identifier = '1/16/2007 2:08:04 PM';

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

/pr/PRstart1.asp, line 15
--------------------------------------

Line 15 is Con.Execute(SQL1)

I have compared this error to another ASP page written for a different project, which threw no code and worked. The response.write lines were inserted as I tried to debug the code so that I could see if it was writing the queries as I expected it to. Why is this page not working?

Cheryl dc Kern

 

[gmmastros]

Access uses the # symbol to delimit dates, so...

SQL1 = "INSERT INTO PReq (identifier, complete) VALUES ([!]#[/!]" & tag & "[!]#[/!], 'no');"
SQL2 = "SELECT PR FROM PReq WHERE PReq.identifier = [!]#[/!]" & tag & "[!]#[/!];"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cdck]

Although I used the CStr function to convert the date to a regular string, I gave this a try. The field in the database is just a text field with a size of 50. This is the new result:

1/16/2007 2:22:24 PM

INSERT INTO PReq (identifier, complete) VALUES (#1/16/2007 2:22:24 PM#, 'no');

SELECT PR FROM PReq WHERE PReq.identifier = #1/16/2007 2:22:24 PM#;

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

/pr/PRstart1.asp, line 15

Cheryl dc Kern

 

[gmmastros]

If you are going to store dates, then you are better off using the Date data type. If you continue storing them as strings, then you'll want to forget at the # signs and switch back to apostrophes.

I'm not sure, but it could be that identifier is a reserved word in Access. So, you could try...

SQL1 = "INSERT INTO PReq ([!][[/!]identifier[!]][/!], complete) VALUES ('" & tag & "', 'no');"
response.write "<P>" & SQL1
SQL2 = "SELECT PR FROM PReq WHERE PReq.[!][[/!]identifier[!]][/!] = '" & tag & "';"
response.write "<P>" & SQL2

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cdck]

Based on George's suggestion, I went into the database and changed the name of that field, then updated the query to reflect this name change. I could tell that the connection string was working, because I forgot to close the database before I tried the new page, and it told me that the database was already in use and couldn't be accessed. Once I closed the database, I got the same error as before (see below).

Oh, and additional piece of information: I tried converting the Now ouput using CCur, CDate, CDbl, Cint, CLng, and CStr, and always go the same error, which has led me to believe that the format of that data doesn't effect the query.

1/16/2007 2:30:00 PM

INSERT INTO PReq (PRidentifier, complete) VALUES ('1/16/2007 2:30:00 PM', 'no');

SELECT PR FROM PReq WHERE PReq.PRidentifier = '1/16/2007 2:30:00 PM';

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

/pr/PRstart1.asp, line 15

Cheryl dc Kern

 

[gmmastros]

Have you looked at this page?

http://support.microsoft.com/kb/175168

The database cannot be Read-Only, and the quest account must have read-write access to the folder where the database is located.

Are you able to update, insert, or delete other bits of data from your website?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cdck]

George:

Thank you. I realized that this was the first time during this particular project where the intranet page was attempting to update the table, and not just draw information out of it. After a discussion with IT about the permissions on the folder holding the database, the situation is corrected.

I apologize for overlooking something so simple; it's hard to believe that after 2 years of working on this site this is the first time that I've had the site put data into the tables...

cdck

Cheryl dc Kern

 

[gmmastros]

I'm just glad I could help, and that your problem is resolved.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom