PDA

View Full Version : vb+mysql

vearn21
04-29-02, 04:41
hi..
just wanna ask about vb+mysql db.
i tried to update field that have datatype date but it not work.

dim mydate
dim mydate12 as date

mydate12=format(now,"yyyy-mm-dd")
'mydate12=2002-04-29

mydate=format(now,"yyyy-mm-dd")
'mydate = 2002-04-29

sql = "UPDATE customer SET date_keyin=" & mydate & "," & "user_name=" & "'ihan'"
sql = sql & " WHERE (id_no =" & txtID & " AND ref_no=" & "'" & txtNoRef & "')"
conn.Execute sql

i wanna update date_keyin field.
if i use mydate variable, it insert null value but when i use mydate12 it work but it insert value 2000-00-01.
why? how can i update the true date. using 'now' command. any suggestion?

thanx
rnealejr
04-30-02, 11:19
I would use string instead of date to test because the date data type stores time information as well (especially when using "now"), which could be causing a problem.

You will also have to single quote your value to mysql (in your sql statement) unless you use a straight number representation of your date, e.g. 20020429 (no dashes).

Your commented out code is incorrect:
'mydate12=2002-04-29 -- needs to have double quotes around the value. VB subtracts the values so you end up with mydate12 = 1969.
vearn21
05-01-02, 22:41
hi..
thanx..yes..correct..i must use single quote for the date.
thanx again.

this is another problem i have when dealing with myodbc

okeys..this error come when i insert new data into mysql db using myodbc connection.coz of this error i don't use myodbc.

run time error -2147217887
multiple step operation generate error.check each status value

this is my programme

Set rs = New Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM customer", conn, adOpenStatic, adLockOptimistic

rs.AddNew
rs!id_no = txtID
rs!ref_no = Trim(UCase(txtNoRef))
rs!cname = Trim(UCase(txtName))
rs!ic_new = txtICN
rs!ic_old = Trim(UCase(txtICO))
rs!bank = Trim(UCase(co_bank.Text))
rs!address1 = Trim(UCase(txtAdd1))
rs!address2 = Trim(UCase(txtAdd2))
rs!poscode = txtPos
rs!city = Trim(UCase(txtCt))
rs!State = Trim(UCase(co_negeri.Text))
rs!pincharge = Trim(UCase(co_incharge.Text))
rs!Type = Trim(UCase(txtType))
rs!Year = txtYear
rs!active = UCase(co_active.Text)
rs!remark = Trim(UCase(txtRemark))
rs!date_keyin = Now
rs!user_name = "me"
rs.update
rs.Close

each field has a value but the error mess told to check the value..maybe it because the datatype.

first it happen in rs!name field..and i change the fieldname to rs!cname.after that it happen at rs!pincharge field. i dont know why.
before this i insert new data..it's dont have any problem.it's happen only sometimes..maybe i make some mistake but i dont know what. any ideas?

thanx.
rnealejr
05-02-02, 19:01
Can you post your connection string ? If you have any confidential information in your string, put * in place of the value.
vearn21
05-06-02, 05:39
hi..

this is my conn string

Set conn = New Connection

conn.ConnectionString = "Provider=MSDASQL;Driver=MySQL ODBC 3.51 Driver;" & _
"Server=129.2.2.22;UID=mysql;PWD=;database=sisdata; " & _
"Option=16386"

conn.Open

anything wrong with my conn string?

thanx
rnealejr
05-07-02, 16:02
Have you tried to change the cursorlocation to adUseServer ? What is your table structure (field names, data type, constraints (primary key, check constraints and default constraints) ?
vearn21
05-08-02, 03:30
hi again..

i haven't test with adUseServer..err..what this cursor for?

this is my fields datatype.

mysql> desc customer;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| id_no | int(10) unsigned| | PRI | 0 | |
| ref_no | varchar(50) | YES | | NULL | |
| ic_new | varchar(12) | YES | | NULL | |
| ic_old | varchar(8) | YES | | NULL | |
| cname | varchar(70) | YES | | NULL | |
| address1 | varchar(50) | YES | | NULL | |
| address2 | varchar(20) | YES | | NULL | |
| poscode | smallint(5) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | varchar(30) | YES | | NULL | |
| pincharge | varchar(10) | YES | | NULL | |
| year | smallint(4) | YES | | NULL | |
| bank | varchar(10) | YES | | NULL | |
| type | varchar(20) | YES | | NULL | |
| active | char(1) | YES | | NULL | |
| remark | varchar(255) | YES | | NULL | |
| date_keyin | date | YES | | NULL | |
| flagname | char(1) | YES | | NULL | |
| user_name | varchar(8) | YES | | NULL | |
+------------+------------------+------+-----+---------+-------+
19 rows in set (0.00 sec)

that's all..i don't make any relation.

thanx
rnealejr
05-08-02, 12:26
It is used to determine where the recordset will be created. Try the adUseServer(its the default) and let me know.
anuska
07-23-02, 08:39
I have the same problem: I canīt add new records to my MySQL data base. I used asUseServer but I receive the next error message : "Multiple-step OLE DB operation generated errors. Chekc each OLE DB status values, if available. No work was done!"
Can you help me, please?
rnealejr
07-23-02, 20:01
What is your table structure (field names, data type, constraints (primary key, check constraints and default constraints) ?
ExeCute
02-10-03, 16:13
Hi, id like to follow on this discussion as ive been searching through the net for answers.

I get exactly the same errors as the guy previous, im using virtually the same identical code.

----

rsUsers.AddNew

rsUsers.Fields(0) = username.Text
rsUsers.Fields(1) = password.Text

rsUsers.Update

----

My Connection String is :-

----

sSQLInv = "select * from user"

Dim lsDSN As String

lsDSN = "driver={MySQL};server=207.***.***.***;" & _
"database=exec_****_****;uid=****;pwd=******

cnnUsers.Open lsDSN
With cmdUsers
Set .ActiveConnection = cnnUsers
.CommandType = adCmdText
.CommandText = sSQLInv
End With
With rsUsers
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open cmdUsers
End With

----

It connects perfectly, i can browse records, but when i run the add record table, it gives me the same errors.

Ive cursorLocation as adUseClient and Server and i get the same 2 error messages as both.

My database declarations are correct, i can add the data correctly on myphp admin but not via vb.

Please help.

Error message :-

Run-time error '-2147217887 (80040e21)
Multiple-step operation generated errors. Check each status value.

And debugs on the following : rsUsers.Fields(0) = username.Text

Thanks for your time,

-ExeC-