Hi All,

I have created two ASP files(Insert.asp and Update.asp). Let me first explain my setup. I have an Access database with an "AutoNumber" field called "ReferenceNumber." So each time I INSERT a record, the ReferenceNumber will increment by one.

Now, with my Insert.asp page, I insert a record into the Access database. Once I have done that, i want to use the Update.asp page to update that record I have just inserted.

Here is the problem. Every time I do an update to a particular record, a new record is added to the database instead of updating that particular record. Hence, a new ReferenceNumber(AutoNumber) is generated. Below are my codes of the update.asp file. PLEASE HELP!!! I have checked my codes over and over, and they seems right. I just don't know why it cause to generate a new record. It is driving me crazy! Your helps are GREATLY appreciated!!! THANK YOU!!!


incumbentname=Request.form("incumbentname")
analystname=Request.form("analystname")
rnumber=request.form("referencenumber")

Set conn = Server.CreateObject("ADODB.Connection")
cnpath="DBQ=" & server.mappath("classifications_studies.mdb")
conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & cnpath

SQLstmt = "UPDATE classificationmain SET "
SQLstmt = SQLstmt & "positionnumber='" & positionnumber & "',"
SQLstmt = SQLstmt & "incumbentname='" & incumbent & "',"
SQLStmt = SQLStmt & " where referencenumber= " & rnumber & ""

Can you post your complete code..? SQL Statement looks fine

The problem seems to be with your update statements. It might help with you posting the whole code.

Enter a response.write (referencenumber) into your update asp page. This will display the value of referencenumber that is being passed to the update page. I think that referencenumber from the request form is passing a null value. This is because incrementing is done at the table level, not on the insert page. Accordingly, the update page is posting to the first record where referencenumber =" " If you post both pages of code I can verify my suspicion

John

Hi There,
to do that proberly you must at first Consider the fields that will be chang are(positionnumber,incumbentname), and the field that must be fixed is (referencenumber)in order to determine which the recordset need to update.

then write the code below :

conn.execute = "UPDATE classificationmain SET " & _
& SQLstmt = SQLstmt & "positionnumber='" & positionnumber & "'," & _
& SQLstmt = SQLstmt & "incumbentname='" & incumbent & "'" & _
& SQLStmt = SQLStmt & " where referencenumber= " & rnumber & ""

Note the second code line (incumbentname) it must be with out (,).

reg,

ASP MAN


ali

At least response.write the ACTUAL SQL that will be excuted. Also consider moving to an OleDb connection string, they are much more reliable than the ODBC driver.

--

Joe