Im working with vb and access using ado connection object.
What Im trying to do is delete from 2 different tables under the same button.I have 2 sql statements
strSql = "DELETE * From products where family Like '" & lstfamily.Text & "%' And ProductID Like '" & lstProduct.Text & "%'"
strSql2 = "DELETE * From usages where family Like ' " & lstfamily.Text & "%' And ProductID Like '" & lstProduct.Text & "%'"
I then try to execute these statement like so.
If lstProduct.Text = "" Then
MsgBox "you must select a product"
'Message Box function
intresponse = MsgBox(strMessage, vbYesNo + vbInformation, "Delete Verification")
If intresponse = vbYes Then
.Execute strSql2 'problem is here
MsgBox "Successful Deletion of " & lstProduct.Text
MsgBox "Record was not deleted.Either you cancelled the operation or you did not select a product"
However srtSql2 causes an error.the first sql statement works but the second statement doesnt.I pressume at this stage that the SQL is not the problem as the first statement,which is identical, works.I may be wrong there of course.The programm stops at .Execute strSql2 and the message "no value given for one or more requested parameters" .
they are spelled right.
In the cases Im trying I know that the records do exist but for example
If a record you wanted to delete didnt exist(say was already deleted),would that cause the program to crash.If so is there a way to check if a record exists before hand.Would error handling prevent it from crashing.
Deleting a record which does not exist in the table would not cause any error. If you get the error "no value given for one or more requested parameters", then definitely one of the fields may not exist in the table. Make sure again that you have given the correct filed names in the query.
feel silly now,ye were right.I ha the corresponding fields spelled slightly differently in my 2 tables.ProductID in Products table and Product_Id in usages.doh!sorry for wasting your time and thanks for pointing me where to go.
FYI: Since your SQL statement will not return any records use the adExecuteNoRecords constant. i.e.
cn.Execute strSql, adExecuteNoRecords
adExecuteNoRecords indicates that the command text is a command or stored procedure that does not return rows (for example, a command that only inserts data or delates data). If any rows are retrieved, they are discarded and not returned.
Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!
Or try our Ask An Expert service to answer any of your questions!