ASP and ADO problem

[BluAlex]

Written in VB.
I want to edit a current record via browser.

I got it working, but when I edit a record and puts it into the database, the edited record overwrites the oldest record(the first record), and the record I want to edit remains the same as it was.

Ex.
I got 10 record from 1 to 10
I want to edit record number 10
when i have edited the record and put it back into the database the result is as follows:
record number 10 stays the same before the editing
record number 1 is overwritten by the edited record

how can i fix this problem?
the records got "id" as a primary key

my code
This is the code for putting the edited record into the database:
<%
Set objCon = Server.CreateObject ("ADODB.Connection")
Set objRec = Server.CreateObject ("ADODB.Recordset")
objCon.Open strcon
objRec.Open "news", objCon, adOpenDynamic, adLockPessimistic, adCmdTable

objRec("overskrift") = Request.Form("overskrift")
objRec("nyhetstekst") = Request.Form("nyhetstekst")
objRec("nyhetstekst2") = Request.Form("nyhetstekst2")
objRec("bilde") = Request.Form("bilde")
objRec("bilde2") = Request.Form("bilde2")
objRec("kilde") = Request.Form("kilde")
objRec("url") = Request.Form("url")
objRec("dato") = date()

objRec.Update

objRec.Close
Set objRec = Nothing
objCon.Close
Set objCon = Nothing
%>

what is missing/wrong?

Instead of opening your recordset as a table open it as a query so that you're looking at (and editing) a specific record. What you're doing is opening up your recordset as a table and the pointer to that recordset is on the first record in the table. For example, instead of:

objRec.Open "news", objCon, adOpenDynamic, adLockPessimistic, adCmdTable


Use something like:


objRec.Open "SELECT * FROM news WHERE id=10", objCon


Usually you pass the 'id' from your edit form to the form handler so you dynamically create the select statement like:


objRec.Open "SELECT * FROM news WHERE id=" & Request.Form("id"), objCon


Good luck,
Paul

[BluAlex]

Thanks i did change it.

Now i'm getting this error........

The operation requested by the application is not supported by the provider.

/redigert.asp, line 12

This is line 12: objRec("overskrift") = Request.Form("overskrift")

Not what I'd call a helpful error message!

What does your recordset.Open look like?

Also, and this might be a silly question, but does your connection string work (for other pages)?

Paul

[BluAlex]

The database works just fine for both adding and deleting records via browser.
It's "only" the editing bit that is not working.

I have no idea why this doesn't work. It's got to be something simple but I can't figure it out (in my similar test project it's happening to me too!).

As a work-around you could use an SQL UPDATE statement using the Execute method of your connection object. For example:


Dim strSQL

strSQL = "UPDATE news SET overskrift='" & Request.Form("overskrift") & "', nyhetstekst='" & Request.Form("nyhetstekst") & "', nyhetstekst2='" & Request.Form("nyhetstekst2") & "' WHERE id=10"

objCon.Execute strSQL


This example only incorportates 3 of your fields...you'd have to expand it to include all of your fields. String/Date values need to be surrounded by single quotes, numbers do not.

The nice thing about this approach is that you don't need a recordset object at all. The potentially harsh thing is creating the SQL string if you have a lot of fields to update.

I actually do all my data edits this way - so I haven't run into this recordset.Update problem in VBScript before.

Sorry that I don't know why the recordset.Update is failing...and hope this helps.

Paul