Results 1 to 4 of 4

Thread: [RESOLVED] Update Method of sql

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2008
    Posts
    102

    Resolved [RESOLVED] Update Method of sql

    Hello,
    The below code works well for a field that already contains some figure but it gives the following error when the field is blank.
    "Data type mismatch in criteria expression"

    what i want to do is add to the value that is already in the current field.

    Code:
    Dim ProdID
    Dim Qty
    ProdID = InputBox("Enter ID")
    
    'SqlStr = "select * from products where productid = " & Val(ProdID) & ""
    'cn.Execute (SqlStr)
    Qty = InputBox("Enter Qty")
    
    SqlStr = "update products set onhandqty = val(onhandqty) + " & Qty & " where productid = " & ProdID & ""
    Set MyRecSet = cn.Execute(SqlStr)
    Thank you

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Update Method of sql

    1-when you declare your variables, declare them as you want them to be (integer, string, date, etc). The way you did it is declaring them as variants. So, do this:
    Dim ProdID as Integer (or single or double...whatever fits your range of numbers)
    Dim Qty as Integer (same thing)
    This line should probably be changed from:

    Code:
    SqlStr = "update products set onhandqty = val(onhandqty) + " & Qty & " where productid = " & ProdID & "
    to:

    Code:
    SqlStr = "update products set onhandqty = val(onhandqty) + " & Qty & " where productid = " & CStr(ProdID)
    and your select (if you are using it) should be like:

    Code:
    SqlStr = "select * from products where productid = " & CStr(ProdID)

  3. #3
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Update Method of sql

    In the case of a qty field it should not be empty. It should be set to have a default value of 0
    You also should not need to use Val() in the SQL as the qty field should be a numeric type already.

    Update queries do not return a recordset so you should not be using the Set Recordset = when you do an update query just use a cn.Execute instead

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Feb 2008
    Posts
    102

    Re: Update Method of sql

    Thank you sam for your advise, but even adint the Cstr doesnt solve my issue, Datamisser was right, the field should not be empty, i kept 0 in all fields and now it works. Thank you datamiser.

Posting Permissions

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



Click Here to Expand Forum to Full Width