By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,301 Members | 3,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,301 IT Pros & Developers. It's quick & easy.

Can't update DATETIME field using Visual Basic

P: n/a
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!
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.