Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    7

    Unanswered: ado problem - connection.execute

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

  2. #2
    Join Date
    Oct 2003
    Posts
    357

    Thumbs up

    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
    Last edited by Madhivanan; 07-14-04 at 08:41.

  3. #3
    Join Date
    Jul 2004
    Posts
    7
    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.

  4. #4
    Join Date
    Oct 2003
    Posts
    357

    Thumbs up

    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

  5. #5
    Join Date
    Jul 2004
    Posts
    7
    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.

  6. #6
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    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.
    http://www.scirocco.ca/images/banner...occobanner.gif

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •