-
Feb 19th, 2015, 08:44 AM
#1
Thread Starter
Lively Member
[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
-
Feb 19th, 2015, 09:03 AM
#2
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)
-
Feb 19th, 2015, 11:07 AM
#3
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
-
Feb 20th, 2015, 01:15 AM
#4
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|