Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    156

    Unanswered: Errors When Adding Record with ADO

    Hello All!

    I'm getting errors 3265 and -2147217900 when adding records through ADO. The problem is that it's not happening consistently. When adding records, sometimes it'll error 3265 on the 8th one, then the 3rd one. It'll do it on around the 3rd record entered for a couple more times after it does it on the 8th one and then either crash out completely OR give me an error -2147217900 and then crash out either right after that error or after another few records are entered. Sometimes it'll give this -2147217900 error right off the bat with the first added record. It's seemingly random. But there has to be method to this madness. Also of note is the fact that, once one of these errors occur, I can go ahead and add the record it just gave the error on, assuming it doesn't crash right after the error.

    Error 3265 is: Item cannot be found in the collection corresponding to the requested name or ordinal.

    Error -2147217900 is: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or UPDATE'.

    I'll tell afterwards which error occurs where. There's a lot more to the procedure, but here's the ADO code that's adding the record:
    Code:
    Set rst = New ADODB.Recordset
    rst.Open "LaborCost", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
    With Me
        rst.AddNew Array("Date", "EmployeeNo", "RegularHoursWorked", "OvertimeHoursWorked", _
        "HolidayPay", "Justification", "Comments", "PayRate", "OTPayRate", "MineNo", _
        "Section", "Shift", "TrainType", "TrainHours"), _
        Array(.Date, .cboEmployeeNo, .RegularHoursWorked, .OvertimeHoursWorked, .HolidayPay, _
        .cmbJustification, .Comments, .txtPayRate, .txtOTPayRate, .cmbMineNo, .txtSection, _
        .txtShift, .cmbTrainType, .txtTrainHours)
    End With
    rst.Update
    rst.Close
    Set rst = Nothing
    Error 3265 occurs in the rst.AddNew command. Error -2147217900 occurs in the rst.Open command.

    Sorry for the lengthy message. Thank you much in advance! If you need any info on the data types of any of the fields, let me know. Consistent problems are doable...inconsistent problems are stomach twisting.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    On a guessing tour...

    3265 may refer to using a reserwed word as both control and field name, which should be avoided - Date. I also always use a naming convention on all controls I refer to in code, which makes them different from the field names. On bound forms, using the dot (.) notation in stead of the bang notation (!) may sometimes confuse Access about what you're referring too.

    -2147217900 - try specifying the "command type", i e adCmdTable for table type source or adCmdStoredProc for query

    rst.Open "LaborCost", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic, adCmdTable
    Roy-Vidar

  3. #3
    Join Date
    Jul 2004
    Posts
    156
    I also try to use a naming convention, but this database has been in development for a while and there are some earlier parts of code that don't include the wisdom I've gained over time with working and sweating over programming.

    I will try that with the second error. Do you know why these would only be coming up intermittently?

    Here's some more food for thought that I confirmed today.--> These errors only occur when there are values in cmbTrainType and txtTrainHours, but, of course, still intermittently. Is there a limit to the amount of values you can store in an Array?

    When records are added that do not include Training Hours or Training Type, the errors never show...or, at least, haven't shown in scores of records added so far. I'll try these updates you've suggested and see if that helps. Thanks a bushel!
    DocX

    The teachings of God's Begotten: 2 John 1:9

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I thought about that (Nulls) and conversion, but I don't use .AddNew with FieldList, so I'm not up to speed on what errors you may get. I usually execute a query on the connection in stead.

    When you say it occurs only if there are values in two specific controls, I'm thinking, could it be the datatype is misinterpreted? Perhaps do some testing and conversion? Test for IsNumeric if numeric, use the Val function to return the number, Use the str function if you've stored numbers as text. Are there any validation rules that's violated. On some small tests here, I haven't been able to recreate the errors, so this is just another stab at guessing
    Roy-Vidar

  5. #5
    Join Date
    Jul 2004
    Posts
    156
    Okay...here's some weird stuff. This is an unbound form. So the fact that the controls are named the same as the fields in the table shouldn't matter. Here comes the weird. At the end of the "Add Record" button code, I have this code:
    Code:
        .TotalHoursWorked = Null
        .RegularHoursWorked = Null
        .OvertimeHoursWorked = Null
        .cmbJustification = ""
        .HolidayPay = Null
        .chkHolidayPay = False
        .cmbTrainType = ""
        .txtTrainHours = Null
        .Comments = ""
        .TotalHoursWorked.SetFocus
    I use this code to set all fields back to blank ("" or Null).

    When I took out the cmbTrainType and txtTrainHours lines above, IT WORKS PERFECTLY! Why? I haven't the slightest notion! You can see that the other ones are setup exactly the same. On top of that, these fields are edited after this code happens, obviously, because of data being entered. So these values aren't even left inside their respective controls.

    If I put those lines of code back in, it crashes. I'm at a loss here.
    DocX

    The teachings of God's Begotten: 2 John 1:9

Posting Permissions

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