Can't delet recordset

[Rolf Rosenquist]

When in ASP and an Access database, I can delete all records in a table with
the code:

sql = "SELECT * FROM Table "
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open sql, objConn, adLockPessimistic, adCmdTable
If Not RS.EOF Then
Do Until RS.EOF
RS.Delete
RS.Update
RS.MoveFirst
Loop
End If

But when I use the same program to a MySQL databse it doesn't work. It will
only delete the last record and then jump out as if it was ready.

I am tearing my last hair on this one! - Can anybody tell what is wrong?
/ Rolf

[Rolf Rosenquist]

Tried your suggestion (that I did not know about) but got this Error
message:

Microsoft OLE DB Provider for ODBC Drivers fel '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.0.14-nt]You have an error in your SQL
syntax. Check the manual that corresponds to your MySQL server version for
the right syntax to use near '* FROM Table' at line 1

The reference manual at [url]http://www.mysql.com/doc/en/DELETE.html[/url] did not
reveal anything to me.

/ Rolf


"Bob Barrows" <reb_01501@yahoo.com> skrev i meddelandet
news:u8zHxMnXDHA.1680@tk2msftngp13.phx.gbl...

> Rolf Rosenquist wrote:

> > When in ASP and an Access database, I can delete all records in a
> > table with the code:
> >
> > sql = "SELECT * FROM Table "
> > Set RS = Server.CreateObject("ADODB.Recordset")
> > RS.Open sql, objConn, adLockPessimistic, adCmdTable
> > If Not RS.EOF Then
> > Do Until RS.EOF
> > RS.Delete
> > RS.Update
> > RS.MoveFirst
> > Loop
> > End If
> >
> > But when I use the same program to a MySQL databse it doesn't work.
> > It will only delete the last record and then jump out as if it was
> > ready.
> >
> > I am tearing my last hair on this one! - Can anybody tell what is
> > wrong? / Rolf

>
> Don't take this the wrong way (you've obviously been lead down the wrong
> path), but, this is horrid. Using a slow recordset loop to do the work of

a

> single sql statement?!?
>
> sql = "DELETE * FROM Table"
> objConn.Execute sql,,adCmdText
>
> In ASP, recordsets should be used to retrieve read-only data sets for the
> purpose of displaying them only. All data modifications should be done via
> the appropriate sql data modification language (DML) elements: INSERT,
> UPDATE and DELETE.
>
> I've never worked with MySQL, so I can't explain the strange behavior of
> this recordset (you do not need the RS.Update line, BTW), it's probably

due

> to some quirk in the MySQL data provider that you are using.
>
> HTH,
> Bob Barrows
>
>

[Bob Barrows]

As I said, I don't know anything about MySQL. perhaps it wants:
Delete From Table
instead of
Delete * From Table.

That must be it: the "Delete * From... " syntax is JetSQL-specific.
ANSI-standard SQL does not allow the *.

I just tried to follow your link ... I get a server or DNS error. I find it
difficult to believe the manual doesn't contain the proper syntax.

Bob Barrows

..Rolf Rosenquist wrote:

> Tried your suggestion (that I did not know about) but got this Error
> message:
>
> Microsoft OLE DB Provider for ODBC Drivers fel '80040e14'
>
> [MySQL][ODBC 3.51 Driver][mysqld-4.0.14-nt]You have an error in your
> SQL syntax. Check the manual that corresponds to your MySQL server
> version for the right syntax to use near '* FROM Table' at line 1
>
> The reference manual at [url]http://www.mysql.com/doc/en/DELETE.html[/url] did
> not reveal anything to me.
>
> / Rolf
>
>
> "Bob Barrows" <reb_01501@yahoo.com> skrev i meddelandet
> news:u8zHxMnXDHA.1680@tk2msftngp13.phx.gbl...

>> Rolf Rosenquist wrote:

>>> When in ASP and an Access database, I can delete all records in a
>>> table with the code:
>>>
>>> sql = "SELECT * FROM Table "
>>> Set RS = Server.CreateObject("ADODB.Recordset")
>>> RS.Open sql, objConn, adLockPessimistic, adCmdTable
>>> If Not RS.EOF Then
>>> Do Until RS.EOF
>>> RS.Delete
>>> RS.Update
>>> RS.MoveFirst
>>> Loop
>>> End If
>>>
>>> But when I use the same program to a MySQL databse it doesn't work.
>>> It will only delete the last record and then jump out as if it was
>>> ready.
>>>
>>> I am tearing my last hair on this one! - Can anybody tell what is
>>> wrong? / Rolf

>>
>> Don't take this the wrong way (you've obviously been lead down the
>> wrong path), but, this is horrid. Using a slow recordset loop to do
>> the work of a single sql statement?!?
>>
>> sql = "DELETE * FROM Table"
>> objConn.Execute sql,,adCmdText
>>
>> In ASP, recordsets should be used to retrieve read-only data sets
>> for the purpose of displaying them only. All data modifications
>> should be done via the appropriate sql data modification language
>> (DML) elements: INSERT, UPDATE and DELETE.
>>
>> I've never worked with MySQL, so I can't explain the strange
>> behavior of this recordset (you do not need the RS.Update line,
>> BTW), it's probably due to some quirk in the MySQL data provider
>> that you are using.
>>
>> HTH,
>> Bob Barrows