updating multiple fields together

star84 · #1 (**permalink**) 07-28-04, 08:10

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

Quote:

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

Quote:

This functionality cannot be accomplished with current versions of ADO.

anyone know what this means ????

thanks

SCIROCCO · #2 (**permalink**) 07-28-04, 09:21

Please post the ADO code that you are using.

star84 · #3 (**permalink**) 07-28-04, 09:53

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

loquin · #4 (**permalink**) 07-28-04, 17:11

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech