Problem with UPDATE statement...

[williamsba]

Im having problems with this code. Basically I want to update values in a row where the ID for that row equals the ID I pull from the form. Heres what I have:

<%
MyDatabase = &quot;../dorn/dorn.mdb&quot;
MyTable = &quot;drinks&quot;
Set MyDataConnection = Server.CreateObject(&quot;ADODB.Connection&quot;)
Set MyRecordSet = Server.CreateObject(&quot;ADODB.Recordset&quot;)
MyDataConnection.Open &quot;DBQ=&quot; & Server.Mappath(MyDatabase) & &quot;;Driver={Microsoft Access Driver (*.mdb)};&quot;
if request.form(&quot;submit&quot;) <> &quot;&quot; then
	If request.form(&quot;sofu&quot;) <> &quot;&quot; then
		sofu2 = request.form(&quot;sofu&quot;)
		MySQL2 = &quot;SELECT * FROM drinks WHERE ID = &quot; & sofu2
		MyRecordSet.Open MySQL2, MyDataConnection, 1, 3
		MyRecordSet.addnew
		MyRecordSet(&quot;score&quot;) = (request.form(&quot;rating&quot;)) + (MyRecordSet.fields(&quot;score&quot;).value)
		MyRecordSet(&quot;votes&quot;) = MyRecordSet.fields(&quot;votes&quot;).value + 1
		MyRecordSet.update

Basically it adds the values, but it always adds it to a new row, it doesnt check to find the ID that matches up. I used the WHERE ID = sofu2 (which is the ID from the form) Any ideas on why this isnt working?

Brad Williams
Webmaster
2d Force Service Support Group
United States Marine Corps

 

[KyleS]

You're adding a new record to the recordset, so after you look up your info, you're adding a new row to the table and inserting your data there.

If ID is a unique identifier, you should be able to just replace the "MyRecordSet.addnew" line with "MyRecordSet.Edit"

You should probably have something in there just in case your recordset come back blank:
If MyRecordSet.EOF Then
'Do something
End If
That way if it's an empty recordset (when they come back empty through ADO they come back with .EOF and .BOF set to true) something will happen other than an error the user won't understand...
Kyle

 

[williamsba]

Error:

Object doesn't support this property or method: 'MyRecordSet.Edit' Brad Williams
Webmaster
2d Force Service Support Group
United States Marine Corps

 

[KyleS]

OK, brain fart, (reverted to DAO for a second - which is sad, since I rarely use DAO any more) just remove the line all together. Kyle

 

[williamsba]

Awesome! Thank you very much Kyle!!! Brad Williams
Webmaster
2d Force Service Support Group
United States Marine Corps

 

[KyleS]

Glad I could help! (and sorry about the screw-up!) Kyle