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

    Unanswered: updating multiple fields together

    Hey all.
    I want to update an access database,updating several fields at the same time.at the moment Im having trouble.
    Im getting error 3001 - Arguments are of the wrong type, are out of acceptble range or are in conflict with one another.
    Im using ado so Im using % instead of *

    lstFamily.Text and lstProduct.text contain data when I run my code.


    am thinking it might be something to do with ado?not sure.When I run the query

    [vb]UPDATE products SET [Run_Rate] = 4848484 where [family] Like 'rik' And [ProductID] Like 'blod '[]/vb
    in access in runs fine,however when I change the values to variables like

    [vb]strSQL = "UPDATE products SET [Run_Rate] = '" & txtRunRate.Text & "' where [family] Like '" & lstfamily.Text & "%' And [ProductID] Like '" & lstProduct.Text & "%'"[/vb]
    it doesnt run.
    Also, I found this


    Runtime error '3001':
    The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.
    CAUSE
    This functionality cannot be accomplished with current versions of ADO. The criteria syntax of the ADO Filter property is defined as follows:

    " FieldName Operator 'Literal_Value' "


    The 'Literal_Value' data type could be string, number, or date. You cannot use the preceding expression to compare two fields. Attempting to do so raises error 3001.
    RESOLUTION
    One workaround is to have several expressions joined by Boolean operators as the Filter criteria. You can only use this if the the values of compared fields are known to the developer. This way the developer can come up with a formula for the criteria string that meets the required results. For example:
    rs.Filter = "Field1 Operator1 'Value1' AND Field2 Operator2 'Value2'"
    This functionality cannot be accomplished with current versions of ADO.
    anyone know what this means ????


    thanks

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Please post the ADO code that you are using.
    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!

  3. #3
    Join Date
    Jul 2004
    Posts
    7
    heres my code
    Code:
    Private Function SaveEdit()
    Dim strSQL As String
    strSQL = "UPDATE products SET [Run_Rate] = " & txtRunRate.Text & "  where [family] = '" & lstfamily.Text & "' And [ProductID] = '" & lstProduct.Text & "'"
    
    Set rsTrolly = New ADODB.Recordset
     With rsTrolly
     .CursorLocation = adUseClient
     Debug.Print strSQL
     .Open dbConn, strSQL
      
     End With
     
    End Function

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Try this instead...
    Code:
    Private Function SaveEdit()
      Dim strSQL As String
      Dim N as integer
    
      On Error Goto SaveEditError
      strSQL = "UPDATE products SET [Run_Rate] = " & txtRunRate.Text & "  where [family] = '" & lstfamily.Text & "' And [ProductID] = '" & lstProduct.Text & "'"
      debug.print strSQL
      dbConn.Execute strSQL
      On Error Goto O  ' Reset the error Handler
      Exit Sub
    
    SaveEditError:
      for N = 0 to dbconn.errors.count 
        if dbconn.errors.count > 0 then
          debug.print dbconn.errors(N).Number
          debug.print dbconn.errors(N).Description
          debug.print dbconn.errors(N).NativeError
        endif
      next n
    End Function
    Run it, then check to see what was actually in strSQL.

    Is the Run_Rate field defined as long, single, or double?

    You may want to TRIM the textbox .Text values before use, to enaure that no extraneous spaces are used.

    also, I added code to print the ADO connection errors collection, including the provider error number, which may prove helpful as well.
    Last edited by loquin; 07-28-04 at 18:22.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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