468,290 Members | 2,078 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,290 developers. It's quick & easy.

Can't update DATETIME field using Visual Basic

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
0 5063

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by GH | last post: by
4 posts views Thread by gj | last post: by
1 post views Thread by glenn | last post: by
reply views Thread by NPC403 | last post: by
2 posts views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.