ado problem - connection.execute

star84 · #1 (**permalink**) 07-14-04, 06:29

hey all,
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

Code:

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.

Code:

If lstProduct.Text = "" Then
    MsgBox "you must select a product"
Else
    'Message Box function
    intresponse = MsgBox(strMessage, vbYesNo + vbInformation, "Delete Verification")
        If intresponse = vbYes Then
           With dbConn
          
           .Execute strSql2 'problem is here
           .Execute strSql
           End With
           
             MsgBox "Successful Deletion of " & lstProduct.Text
                resetscreen
        Else
            MsgBox "Record was not deleted.Either you cancelled the operation or you did not select a product"
                resetscreen
        End If
    
End If

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" .

Madhivanan · #2 (**permalink**) 07-14-04, 07:39

Hi

Look at the query

Code:

 strSql2 = "DELETE * From usages where family Like ' " & lstfamily.Text & "%' And ProductID Like '" & lstProduct.Text & "%'"

Make sure both family and ProductID spelled correctly

Madhivanan

star84 · #3 (**permalink**) 07-14-04, 08:03

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.

Madhivanan · #4 (**permalink**) 07-14-04, 08:42

Hi,
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.

Madhivanan

star84 · #5 (**permalink**) 07-14-04, 09:41

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.

SCIROCCO · #6 (**permalink**) 07-14-04, 12:05

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech