Updating a record

[Swizylstik]

This should be easy right? Using this tutorial as a guide [HTML]http://www.w3schools.com/ado/ado_update.asp[/HTML] and I still can't get it to work.

Here is a sample of what I have and what the outcome is: [HTML]http://www.simplymad.org/RTape/test_shows_display.asp[/HTML]

I've saved the two asp files as txt files so that you may see the code behind this mess I've made:

[HTML]http://www.simplymad.org/RTape/test_shows_display.txt[/HTML]
and
[HTML]http://www.simplymad.org/RTape/x_demoupdate.txt[/HTML]

Can anyone help me understand what I'm doing wrong here? And is there any online resource that will be more helpful than the page I mention above? This is newbie stuff and I'm still lost!

Thanks!

[jhardman]

The first page looks good. In the second page you forgot to open the recordset:

set rs=Server.CreateObject("ADODB.Recordset")
     sSQL = "SELECT * FROM shows WHERE EventID='" & cid & "'"
rs.open sSQL, db

It might also be a good idea to make the eventId different, perhaps readonly or hidden so the user can't change this on accident, then on the update section you can simplify to this:

sql = "SELECT * FROM shows WHERE EventID='" & cid & "'"
rs.open sql, db
for each x in rs.fields
   if x.name="EventId" then
      'no change needed
   else
      rs(x) = request(x)
   end if
next
rs.update 

Let me know if this helps.

Jared

[ilearneditonline]

sql = "SELECT * FROM shows WHERE EventID='" & cid & "'"
 

Also, depending on the database, this query would not work if the eventid is a numeric value. In that case it would be

sql="SELECT * FROM shows WHERE EventID=" & cid

Also, when I am having problems like this, I would print out the SQL rather than execute it. Then I would run the output directly against the database with whatever interface is available. MS SQL i would use query analyzer. This way you can see if you are getting the expected results.

[markrawlingson]

If the "EventID" is a unique identifier in your table - Just set it up as an autonumber/primary key in the database. It will automatically be read-only and it counts/updates by itself, so you don't have to.

[syamas]

The first page looks good. In the second page you forgot to open the recordset:

set rs=Server.CreateObject("ADODB.Recordset")
     sSQL = "SELECT * FROM shows WHERE EventID='" & cid & "'"
rs.open sSQL, db

It might also be a good idea to make the eventId different, perhaps readonly or hidden so the user can't change this on accident, then on the update section you can simplify to this:

sql = "SELECT * FROM shows WHERE EventID='" & cid & "'"
rs.open sql, db
for each x in rs.fields
   if x.name="EventId" then
      'no change needed
   else
      rs(x) = request(x)
   end if
next
rs.update 

Let me know if this helps.

Jared

I think the below changes
set cn = Server.CreateObject("ADODB.Connection")
cn.open "provider=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & Server.MapPath("\dbname.mdb")
rs.open sql, db, adopendynamic,adlockoptimistic

[removed signature. Sig is not allowed in technical posts]

[Swizylstik]

Thanks so much for your help with this!