5 Replies - 1005 Views - Last Post: 24 March 2012 - 04:58 AM Rate Topic: -----

#1 donnellkar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 28-February 12

Deleting single record from database

Posted 23 March 2012 - 07:20 PM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Deleting single record from database

#2 maj3091  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,776
  • Joined: 26-March 09

Re: Deleting single record from database

Posted 24 March 2012 - 12:51 AM

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

Was This Post Helpful? 1
  • +
  • -

#3 donnellkar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 28-February 12

Re: Deleting single record from database

Posted 24 March 2012 - 02:43 AM

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?
Was This Post Helpful? 0
  • +
  • -

#4 maj3091  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,776
  • Joined: 26-March 09

Re: Deleting single record from database

Posted 24 March 2012 - 04:28 AM

Just wrap the textbox.text with the Cint function. You shouldn't need the single quotes.
Was This Post Helpful? 0
  • +
  • -

#5 donnellkar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 28-February 12

Re: Deleting single record from database

Posted 24 March 2012 - 04:51 AM

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
Was This Post Helpful? 0
  • +
  • -

#6 maj3091  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,776
  • Joined: 26-March 09

Re: Deleting single record from database

Posted 24 March 2012 - 04:58 AM

Nice one, glad you're sorted.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1