recordset object with ASP and MySQL - updating and deleting

Is it possible to use the recordset object to update and delete your database records when you are using ASP with MySQL? I know you can use it to add new records, but when I've tried using it to update and delete, it always fails. I've had to use the execute method with the connection object instead and write out the SQL statement (UPDATE tableName SET ..... etc.)

Does anybody have any information on this issue?

Thanks,
IS

I don't see any problem in updating or deleting mysql records with asp.
I tried both using
connection.Execute(string) and
ADODB.Recordset object

Regards
Praveen

Let's see your code for the update and delete - maybe that's where the problem lies?

Here is a snippet of code I've used that doesn't work with MySQL, but has worked just fine using SQL Server.

dim intCategoryID
intCategoryID = request ("categoryID")

dim rsCategories, qryCategories
set rsCategories = server.createObject ("ADODB.recordSet")
qryCategories = "SELECT * FROM tblCategories WHERE categoryID = " & intCategoryID
rsCategories.open qryCategories, connect, adOpenStatic, adLockOptimistic

rsCategories ("categoryName") = request ("categoryName")
rsCategories.update

rsCategories.close
set rsCategories = nothing

Seems pretty straightforward, no? Well, I get an error that says something to the effect of "multi step OLE DB operation generated errors. Check each status value." And yes, a value for request ("categoryName") (and all the other variables for that matter) is most definitely present.

In order to do the update, I have to do it implicitly with an SQL update statement using connect.execute. I guess that's ok, it's just odd that I can't use the recordset object. I figured it's because MySQL doesn't natively support ADO.

looks fine to me. Here's some troubleshooting suggestions about the error message you got. Maybe they might help?

http://www.aspfaq.com/show.asp?id=2288

Sometimes I have problems with a Select * statement and a request for an id (is it a request.Querystring or request.form?). When you do a response.write after the select statement, you actually get any records?

If it's any consolation, using update and delete statements instead of a recordset are better from a performance standpoint.

The process is request.form. I never tried writing out request.form; I usually just write request for short.

Since the error could be a datatype issue, I started wondering if perhaps it might be because of the data type I've defined in MySQL for this field. I was using TINYTEXT for the field in question. Perhaps if it was a varchar type ADO would like that better? I think that may be the only thing I haven't tried. It's just puzzling that I can use the addNew method but I can't update a recordset.

Thanks for the info you sent me.

ur welcome.

I remember reading somewhere that it's better to use the full request.querystring or request.form (although I can't remember off hand what the reason was!). There is something on this at http://www.aspfaq.com/show.asp?id=2111. So since then, I've been writing it out in full.

Yes, you would think that if you can use AddNew you should be able to use ADO to update. Very frustrating. Let me see if I can try the code here. Your catagories table just has CatagoryID and CatagoryName, right?

Yes, that is correct. Those are the only two fields in the table. The ID is an autonumber primary key.

I have a feeling your problem is in your recordset.open statement. You are using

rsCategories.open qryCategories, connect, adOpenStatic, adLockOptimistic

I think u need the dynamic cursor. Give this a shot

rsCategories.open qryCategories, connect, 2, 3

Hope that does the trick. Failing that - Whack the error msg into google, that's usually the best way to solve your problems.

stumpy.

Good thought on the different cursor type. I've tried using adOpenDynamic and adLockPessismistic in various combinations with no luck.

I had thought also that maybe the data type I had defined in the MySQL database was causing problems, so I changed it from tinytext to varchar. Still no luck.

Maybe it's a limitation of the MyODBC version installed (and therefore a limitation of the ISP) or that I need to be using a different option in the connection string when I connect to the database? When I refer to connection options, I'm referring to the numerical list here: http://www.mysql.com/doc/en/MyODBC_...parameters.html

I really know nothing about those connection options, so I've not ever used them or experimented with them before. Perhaps some of you know more about what they mean (the descriptions with them on the mysql.com page listed above I've found to not be very extensive).

It's an odd problem, but since I can use the update, delete, and insert statements, it hasn't forced me to cease my programming entirely!

I couldn't get it to work either and the error seemed to be related to the adOpenStatic, adLockOptimistic.

You might want to take a look at:

http://www.mysql.com/products/myodb...#MyODBC_With_VB

which "contains simple samples on the usage of MySQL ODBC 3.51 Driver with ADO, DAO and RDO. "

Smithim,

I am having exactly the same problem you describe, but with a different error -

Syntax error or access violation: You have an error in your SQL syntax near 'code='pasapsk' WHERE ID= 2 And Price= 49 And Available= -1 ' at line 1

Will try your method, but would be interested to know if you ever got the update method to work.

Skinner

No, never did get it to work. What I always had to end up doing was using UPDATE and DELETE queries to modify or delete records with ASP. Oddly enough, however, I am usually able to add records using ADO. Even then, sometimes I found there was more consistency in successfully inserting records when I used an INSERT query.

Same thing here, although the error message I was getting was due to a field in the database containing a space, and not enclosing it within "`"s.

Never mind. I'll carry on using the queries. Thanks for your prompt reply.

Skinner