469,323 Members | 1,235 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

ASP NET and UpDATE Boolean to a n MDB

Hi,
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 3298
shweta123
692 Expert 512MB
Hi,

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

e.g

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
Thanks,
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
jhardman
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")
  4.  
  5. rs.open querystring, objConn, adOpenDynamic, adLockOptimistic
  6.  
  7. rs("boolA") = True
  8. rs("boolB") = False
  9. rs("boolC") = True
  10.  
  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.

Jared
Aug 6 '07 #4
shweta123
692 Expert 512MB
Hi,

I think both of these statements work for Access database

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

OR

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
1 post views Thread by CARIGAR | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.