I'm having a horrible time simply inserting the date in a MySQL
database through a Visual Basic program. I have a table, called
"ORDERS"; "ID" is the primary key. I'm trying the insert the date,
into the MySQL database, which is a DATETIME datatype. I must keep it
DATETIME so it can be accessed via Microsoft Access.
Here are two versions of the program. The first one fails, with the
"Row cannot be located for updating. Some values may have been changed
since it was last read." The second program works; it just updates the
QTY field. So that proves that I can connect and update the database,
but not the field I want.
Notes:
1) This problem is repeatable.
2) No one else is accessing this database.
3) I can add and change the DATETIME field directly through Microsoft
Access, using ODBC, which proves that the ODBC driver is working.
4) I tried changing the one line to:
rs!ORDER_DATE = "1979-11-25 00:00:00"
rs!ORDER_DATE = "'1979-11-25 00:00:00'"
all with the same results (failure)
'==== THIS VERSION FAILS WITH "Row cannot be located for updating..."
====
'
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim DT as Date
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" &
"SERVER=10.10.10.118;" & "DATABASE=dwyer2003;" & "UID=test;" &
"PWD=test;" & "OPTION=3"
rs.Open "SELECT * FROM ORDERS WHERE ID = '45000', conn, adOpenStatic,
adLockOptimistic
rs.MoveFirst
Dt = #11/25/1979#
rs!ORDER_DATE = Dt
rs.Update
rs.Close
conn.Close
'===================================END=========== ================
'==== THIS WORKS (by updating the QTY field), which shows I'm
connecting ====
'
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" &
"SERVER=10.10.10.118;" & "DATABASE=dwyer2003;" & "UID=test;" &
"PWD=test;" & "OPTION=3"
rs.Open "SELECT * FROM ORDERS WHERE ID = '45000', conn, adOpenStatic,
adLockOptimistic
rs.MoveFirst
rs!QTY = 100
rs.Update
rs.Close
conn.Close
'===================================END=========== ================
Many thanks in advance for any insights, advice, opinions, etc!
