[donnellkar]

Hi there,
I have having some trouble deleting a record from a database. I have several items connected with one order number.
I can delete the entire order, but I want to be able to delete one item at a time if needed.
I'm getting a runtime error " -2147217913 data type mismatch in criteria expression "
Any help would be much appreciated.
Here is my code, I have a few lines commented out as it helps me to rememver what doesn't work!

Private Sub cmdDeleteItem_Click()

    txtOrder_ID = adoOrders.Recordset.Fields.Item(0)
    txtProd_ID = adoOrders.Recordset.Fields.Item(1)
    txtQuantity = adoOrders.Recordset.Fields.Item(2)
    
'Dim deletesql As String

'deletesql = " DELETE FROM OrderDetails (  Order_ID, Prod_ID, Quantity) Values ('" + txtOrder_ID.Text + "' , '" + txtProd_ID.Text + "', '" + txtQuantity.Text + "' )"
   ' deletesql = " DELETE Order_ID, Prod_ID, Quantity FROM OrderDetails  WHERE (Order_ID AND Prod_ID = '" + txtOrder_ID.Text + "' AND '" + txtProd_ID.Text + "' )"
    Set conn = New ADODB.Connection
        conn.ConnectionString = constring
        conn.Open
       ' conn.Execute deletesql
   ' conn.Execute "DELETE * FROM OrderDetails WHERE Order_ID = ('" + txtOrder_ID.Text + "') AND Prod_ID =  ( '" + txtProd_ID.Text + " ')"
    conn.Execute "DELETE * FROM OrderDetails WHERE Order_ID = ('" + txtOrder_ID.Text + "') AND Prod_ID = " + txtProd_ID.Text
   ' conn.Execute "DELETE * FROM OrderDetails WHERE Order_ID = ( '+ txtOrder_ID.Text+' ) AND Prod_ID = " + txtProd_ID.Text
        conn.Close
        
End Sub

In the conn.Execute line that isn't commented out, when I debug the program if I hover over txtOrder_ID.Text and txtProd_ID.Text, the values have been captured so I'm not sure why it isn;t working.

[maj3091]

Couple of things.

There is no need to specify * in the delete query, to my knowledge it's format is DELETE FROM table WHERE criteria

Are OrderID and ProdID strings in the database? If so, both should be wrapped with single quotes.

If either of them are number types in the Database, then try casting them accordingly to match the datatype in the database. For example: cInt(txtProd_ID.Text)

This post has been edited by maj3091: 24 March 2012 - 12:55 AM

[donnellkar]

Thanks for getting back to me Maj,
Both are integers, I've never used cInt(txtProd_ID.Text) before, do I declare that as a variable or insert it into the sql statement?

[maj3091]

Just wrap the textbox.text with the Cint function. You shouldn't need the single quotes.

[donnellkar]

Thanks Maj,
I have fixed the problem, following your advice I removed the single quotes from around txtOrder_ID.Text and it is working fine.
Thanks again,
Rab

[maj3091]

Nice one, glad you're sorted.