Results 1 to 11 of 11

Thread: vb+mysql

  1. #1
    Join Date
    Apr 2002
    Posts
    5

    Unanswered: vb+mysql

    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

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  3. #3
    Join Date
    Apr 2002
    Posts
    5
    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.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Can you post your connection string ? If you have any confidential information in your string, put * in place of the value.

  5. #5
    Join Date
    Apr 2002
    Posts
    5
    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

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    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) ?

  7. #7
    Join Date
    Apr 2002
    Posts
    5
    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

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    It is used to determine where the recordset will be created. Try the adUseServer(its the default) and let me know.

  9. #9
    Join Date
    Jul 2002
    Posts
    1

    I have the same problem

    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?

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    What is your table structure (field names, data type, constraints (primary key, check constraints and default constraints) ?

  11. #11
    Join Date
    Feb 2003
    Posts
    1
    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-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •