471,857 Members | 1,939 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,857 software developers and data experts.

ASP NET and UpDATE Boolean to a n MDB

I need to update a table in an mdb that has yes/no boxes. I can hard code a TRUE to the function but when I try and pass it I get an error which is saying type mismatch in criteria expression

This is my query string

Dim queryString As String = "UPDATE [Item] SET [Item_PO_number]=@Item_PO_number, [Controled_Item]=@Controled_I" & _
"tem, [Stock_Item]=@Stock_Item, [Consort]=@Consort, [Hours]=@Hours, [Partial_GR_I" & _
"tem]=@Partial_GR_Item, [Total_GR_Item]=@Total_GR_Item, [Items_Quantity]=@Items_Q" & _
"uantity WHERE (([Item].[Shopping_cart_Name] = '" & text1 & "') AND ([It" & _
"em].[Line_Number] = '" & text2 & "'))"

Please Help
Aug 5 '07 #1
4 3346
692 Expert 512MB

May be some columns from your table are numeric and you are passing string data to it.


WHERE (([Item].[Shopping_cart_Name] = '" & text1 & "') AND ([It" & _
"em].[Line_Number] = '" & text2 & "'))"

Here, try giving " & text2 & " , if Line_Number is numeric data type.
Aug 6 '07 #2
But it’s not the variable it’s the Boolean values I’ve tride it without them and it works fine.
I’m passing them over like this

Dim A As Boolean
Dim B As Boolean
Dim C As Boolean
Dim D As Boolean
Dim F As Boolean
Dim G As Boolean

‘I then ste them to true or false

x = MyUpdateMethoditem2(txtShopingCartName.Text, txtLineNumber.Text, txtPONumber.Text, A, B, C, D, F, G, txtQuantity.Text)
Aug 6 '07 #3
3,406 Expert 2GB
Here are a couple thoughts. First off, I prefer to open the db not with an "Update" statement like you do, the method I use involves opening a recordset with a "SELECT" statement, then modifying the values in the recordSet, then updating the db from the recordset. this goes something like this:
Expand|Select|Wrap|Line Numbers
  1. querystring = "SELECT * FROM myDBTable WHERE "
  2. querystring = querystring & "  '...whatever your complex where clause is
  3. set rs = server.createObject("adodb.recordset")
  5. rs.open querystring, objConn, adOpenDynamic, adLockOptimistic
  7. rs("boolA") = True
  8. rs("boolB") = False
  9. rs("boolC") = True
  11. rs.update 
You see? This pulls the records in question into a local recordset object, you update the local object with the values you want, then just say rs.update to update the db from the recordset.

Second, I have noticed over time that every db wants booleans formatted differently. You say, "booleans can have formats?" and I say, "yeah, can you believe it?" for example, in SQL Server you need to send the booleans as integer 0, or anything else is saved as 1. It's been a while since I did an access db, but I thought access recognized vb booleans. If not, have you tried sending a string that says "true" or "false":
Expand|Select|Wrap|Line Numbers
  1.  rs("boolA") = "True"
? Let me know if this helps.

Aug 6 '07 #4
692 Expert 512MB

I think both of these statements work for Access database

sql = "Update Table1 set boolA = 0 where ....."
sql = "Update Table1 set boolA = 1 where ....."


sql = "Update Table1 set boolA = false where ....."
sql = "Update Table1 set boolA = true where ....."

You can debug it by updating a single value query.
Aug 7 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by amitbadgi | last post: by
4 posts views Thread by Jonathan Upright | last post: by
reply views Thread by sdash | last post: by
11 posts views Thread by John | last post: by
reply views Thread by troyblakely | last post: by
2 posts views Thread by Looch | last post: by
reply views Thread by NeoPa | last post: by
reply views Thread by YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.