Receiving error '3001' when updating recordset

[awelch]

THis is my first vb project so bear with me please. I am creating a simple database with a vb 6 front end. I am trying to track and assign id tags to equipment. I can get the primary recordset to save the main record, however this bit of code which should update the id tags is not working. I am receiving the following error: Run-time error '3001' Arguments are of the wrong type, ,are out of range, or are in conflict with one another. Any ideas would be wonderful. The table that needs to be updated has the following fields:

Number <autonumber>
Category <text>
Description <text>
CategoryTagPrefix <text>
LastTagNumber <Number>
NextTagNumber <Number>

The debugger is highlighting the code:
adoAssignID.Update sql
________________________________________

Private Sub cboCategory_Click()
  Dim intUserResponse As Integer
  'Dim adoAssignID As Recordset
  Dim sql, aLast, aNext, Name As String
  Dim db As Connection
  Set db = New Connection

  db.CursorLocation = adUseClient
  db.Open "PROVIDER=MSDASQL;dsn=Inventory;uid=sa;pwd=;"

  sql = "Select * from zAssetCategories Order by Category"
  
  Set adoAssignID = New Recordset
  Name = cboCategory

  'search for record
   adoAssignID.Open sql, db, adOpenStatic, adLockOptimistic
   adoAssignID.MoveFirst
   adoAssignID.Find "Category = '" & Name & "'", , adSearchForward, adBookmarkFirst
 
  txtFields(0) = Trim(adoAssignID![CategoryTagPrefix] & Str(adoAssignID![NextTagNumber]))

  aLast = (adoAssignID!NextTagNumber)
  aNext = (adoAssignID!NextTagNumber + 1)

  sql = "Update zAssetCategories LastTagNumber = '" & (Val(aLast)) & "'"
  sql = sql & ",NextTagNumber = '" & (Val(aNext)) & "'"
  sql = sql & "where Category = '" & (Trim(Name)) & "'"
  adoAssignID.Update sql
  
  Me.cboCategory.Enabled = False
  Me.txtFields(0).Enabled = False

End Sub

Thanks in advance for your help.

Andrea

 

[CajunCenturion]

I always use the SET verb in an update statements, and you might try adding some spaces (specifically in front of the where) clause.

sql = "Update zAssetCategories SET LastTagNumber = '" & (Val(aLast)) & "'"
sql = sql & ", NextTagNumber = '" & (Val(aNext)) & "'"
sql = sql & " where Category = '" & (Trim(Name)) & "'"

Also, you should verify that the DB Fields LastTagNumber, NextTagNumber, and Category are Text fields. You would not use the single quote when dealing with numeric fields. If all three are numeric, the sql statement would like the following:

sql = "Update zAssetCategories SET LastTagNumber = " & (Val(aLast))
sql = sql & ", NextTagNumber = " & (Val(aNext))
sql = sql & " where Category = " & (Trim(Name))

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[RJFost]

For future reference, any VB error in the 3000-3999 range is an Access/database error. As soon as you see one in that range, start checking your SQL- I suggest a breakpoint after you've substituted your values into the query, then a &quot;? sql&quot; in the Debug window. You'll often find errors that way that aren't intuitively obvious at design time

 

[awelch]

The fields in the table are defined as: LastTagNumber and NextTagNumber are numeric fields, however the Category field is Text. I made the recommended changes to the following update statement and am still receiving the error.

  sql = "UPDATE zAssetCategories SET LastTagNumber = " & (Val(aLast)) 
  sql = sql & ", NextTagNumber = " & (Val(aNext)) 
  sql = sql & " WHERE Category = '" & (Trim(Name)) & "'"
  adoAssignID.Update sql

Any other suggestions. I am completely lost when it comes to sql. Looking forward to taking a class next month, but doesn't help with this.

Thanks again for your time.

Andrea

 

[rb74]

Andrea,

I am assuming you are using ADO to connect to your database. It seems when you posted your code in your first post, your dimension statement for the recordset was commented out. I may be wrong and you may be using a global recordset in a module or in your general declarations area, but if not, try to dimension the recordset like this:

Dim adoAssignID As New ADODB.Recordset

Hope this helps


Rob [Vader2]

 

[awelch]

I just wanted to Thank you again for your tips. I finally got the solution to the problem. I had to change the update statement to an execute command. Here is the fix:

Private Sub cboCategory_Click()
  Dim intUserResponse As Integer
  Dim adoAssignID As New ADODB.Recordset
  Dim sql, aLast, aNext, Name As String

  Dim db As New ADODB.Connection
  Set db = New ADODB.Connection
    
  db.CursorLocation = adUseClient
  db.Open "PROVIDER=MSDASQL;dsn=Inventory;uid=sa;pwd=;"

  sql = "Select * from zAssetCategories Order by Category"
  
  Set adoAssignID = New Recordset
  Name = cboCategory

  If (Len(Name) > 0) Then
  
    'search for record
    adoAssignID.Open sql, db, adOpenStatic, adLockBatchOptimistic
    adoAssignID.MoveFirst
    adoAssignID.Find "Category = '" & Name & "'", , adSearchForward, adBookmarkFirst

   End If

  txtFields(0) = Trim(adoAssignID![CategoryTagPrefix] & Str(adoAssignID![NextTagNumber]))

  aLast = (Val(adoAssignID!NextTagNumber))
  aNext = (Val(adoAssignID!NextTagNumber + 1))
  
  sql = "Update zAssetCategories Set LastTagNumber = " & (Val(aLast))
  sql = sql & ", NextTagNumber = " & (Val(aNext))
  sql = sql & " WHERE Category = '" & (Trim(Name)) & "'"
  Set adoAssignID = db.Execute(sql)

  Me.cboCategory.Enabled = False
  Me.txtFields(0).Enabled = False

End Sub

Thanks again for all the tips....