hey, i'm having trouble with an updatable query in mySQL.



what i want to know is can you have an updatable query and if so, what do 

i need to do it. here's some code from what i'm doing right now:



DB_ConnectionString = "driver

{MySQL};server=10.25.113.25;database=test;option=1"



Set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open DB_ConnectionString



SQL_query = "SELECT * FROM table1 WHERE temp = '" & value &"'"

Set RS = Server.CreateObject("ADODB.Recordset")

RS.Open SQL_query, Conn, adOpenKeyset, adLockOptimistic, adCmdText



RS.Fields("test1")="foo"

RS.Fields("test2")="foo"

RS.Fields("test3")="foo"



RS.Update

RS.Close

Set RS = nothing

Conn.close

Set Conn = nothing



it crashes on rs.update with the following error:



Microsoft OLE DB Provider for ODBC Drivers error '80004005' 

Query based update failed. The row to update could not be found.



any and all help would be much appreciated

You have a primary key in this table?



Your query actually returned at least 1 row to update?



Alternatively, you can't do this using just an SQL statement?



<%

strSQL = "UPDATE table1 SET field1 = 'foo', field2 = 'bar'...."

objConn.Execute strSQL,,adCmdText+adExecuteNoRecords

%>



?



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Ben Dean" <ben.dean@t...>

To: "ASP Database Setup" <asp_database_setup@p...>

Sent: Wednesday, June 06, 2001 7:28 PM

Subject: [asp_database_setup] mySQL and updatable query problems. can't do

recordset.update





: hey, i'm having trouble with an updatable query in mySQL.

:

: what i want to know is can you have an updatable query and if so, what do

: i need to do it. here's some code from what i'm doing right now:

:

: DB_ConnectionString = "driver

: {MySQL};server=10.25.113.25;database=test;option=1"

:

: Set Conn = Server.CreateObject("ADODB.Connection")

: Conn.Open DB_ConnectionString

:

: SQL_query = "SELECT * FROM table1 WHERE temp = '" & value &"'"

: Set RS = Server.CreateObject("ADODB.Recordset")

: RS.Open SQL_query, Conn, adOpenKeyset, adLockOptimistic, adCmdText

:

: RS.Fields("test1")="foo"

: RS.Fields("test2")="foo"

: RS.Fields("test3")="foo"

:

: RS.Update

: RS.Close

: Set RS = nothing

: Conn.close

: Set Conn = nothing

:

: it crashes on rs.update with the following error:

:

: Microsoft OLE DB Provider for ODBC Drivers error '80004005'

: Query based update failed. The row to update could not be found.

:

: any and all help would be much appreciated

A work with ASP and MySQL, do you have a text field in your table.



Then this must by the last field in your table.



Yes i know, this sound straigt but.