[RESOLVED] Problem adding multiple records

[aikidokid]

EDIT: Should have posted in Database forum, so if admin can change please

VB6 and Access 2002

Can anybody see why the following code only adds the first entry.

Say, for example I add 4 records to the DB, all four records are added, but all with the same values as the first record.

I haven't used this method of adding data before, so am I missing something obvious?

Set cn = New ADODB.Connection
cn.ConnectionString = strConnection & App.Path & "\Membership.mdb"
cn.Open

Dim adoCommand As ADODB.Command
Set adoCommand = New ADODB.Command

For i = 0 To ctr - 1
    iMonth = CInt(cmbMM(i).Text)
    dtDate = CDate(cmbDD(i).Text & "/" & cmbMM(i).Text & "/" & cmbYY(i).Text)
    strDay = cmbDay(i).Text
    strType = cmbType(i).Text
    strVenue = cmbVenue(i).Text
    strTimes = cmbTime1(i).Text & " to " & cmbTime2(i).Text & " - " & cmbTime3(i).Text & " to " & cmbTime4(i).Text
    strYear = cmbYY(i).Text
    
strSQL = "INSERT INTO tbl_CourseList (Course_Month, Course_Date, " & _
                                               "Course_Day, Course_Type, " & _
                                               "Course_Venue, Course_Times, Course_Year)" & _
                                " Values (iMonth, dtDate, strDay, strType, strVenue, strtimes, strYear)"
                                
    With adoCommand
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = strSQL
        .Prepared = True
        .Parameters.Append .CreateParameter(, adInteger, adParamInput, 50, iMonth)
        .Parameters.Append .CreateParameter(, adDate, adParamInput, 50, dtDate)
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, strDay)
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, strType)
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, strVenue)
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, strTimes)
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, strYear)
        
        .Execute , , adCmdText + adExecuteNoRecords
        'note: the last two arguments used for Execute here makes the execution of the command faster
    End With
Next

cn.Close
Set cn = Nothing

Also, what do I set the adoCommand to at the end to tidy it up?

[Hack]

Put a break on this line

For i = 0 To ctr - 1

and step through the code.

I have a feeling the control array index may not be getting updated.

[aikidokid]

Put a break on this line

For i = 0 To ctr - 1

and step through the code.

I have a feeling the control array index may not be getting updated.

In my code I do have

For i = 0 To ctr - 1
     'my code
Next

But yes I have stepped through and the values are different!

[aikidokid]

Just stepped through again, adding two more records.
They were both added with the two different sets of values, but are saved in the DB with the same values as the first record!

[Hack]

Do a Debug.Print for each control in the array. If in fact they are getting changed, you should see 4 different sets of data.

If this is so, do a debug.print on your SQL statement and see what is actually being sent back to the database.

[aikidokid]

These are the Debug.Print results of me adding two more records to the DB (reverse order)

INSERT INTO tbl_CourseList (Course_Month, Course_Date, Course_Day, Course_Type, Course_Venue, Course_Times, Course_Year) Values (iMonth, dtDate, strDay, strType, strVenue, strtimes, strYear)
2007
10.00 to 10.00 - 10.00 to 10.00
HQ
3rd Dan Grading
Sunday
06/11/2007
11

INSERT INTO tbl_CourseList (Course_Month, Course_Date, Course_Day, Course_Type, Course_Venue, Course_Times, Course_Year) Values (iMonth, dtDate, strDay, strType, strVenue, strtimes, strYear)
2007
09.00 to 09.00 - 09.00 to 09.00
Clifton
2nd Dan Grading
Saturday
05/11/2007
11

[Hack]

Ok. So they are different so you aren't adding the same record twice, right? Isn't that the question?

[aikidokid]

Ok. So they are different so you aren't adding the same record twice, right? Isn't that the question?

Sort of, but if you look below, see what has been saved into the database:

Course_ID Course_Month Course_Date Course_Day Course_Type Course_Venue Course_Times Course_Year
38 11 05/11/2007 Saturday 2nd Dan Grading Clifton 09.00 to 09.00 - 09.00 to 09.00 2007
39 11 05/11/2007 Saturday 2nd Dan Grading Clifton 09.00 to 09.00 - 09.00 to 09.00 2007

Exactly the same values as only the first record added

[Hack]

That doesn't make any sense. If your INSERT has the right values, then those values should be what is getting back to the database.

Put a break right after the .Execute and send it through a multi-record insert loop.

After each insert look at the new record in the table.

Keep the debug.print in place for the insert.

Tell me what you see in the table after each individual insert.

[aikidokid]

That doesn't make any sense. If your INSERT has the right values, then those values should be what is getting back to the database.

Put a break right after the .Execute and send it through a multi-record insert loop.

After each insert look at the new record in the table.

Keep the debug.print in place for the insert.

Tell me what you see in the table after each individual insert.

Thanks Hack

I will have to do this tomorrow, but I will definately post back.

[Hack]

I'll be looking for it. This just doesn't make any sense. See you tomorrow!

[Al42]

Why aren't you just doing a cn.Execute on strSQL?

[jireh]

I agree with you A142! instead of strSQL = .... why not cn.Execute "insert into..."

[aikidokid]

I was looking in the FAQ's and this was the method advised to me by one of the mods.

Even so, I am still intrigued as to why I am having this problem - it shouldn't happen!
If I can find out why then it helps for the future if it happens again.

[Hack]

That doesn't make any sense. If your INSERT has the right values, then those values should be what is getting back to the database.

Put a break right after the .Execute and send it through a multi-record insert loop.

After each insert look at the new record in the table.

Keep the debug.print in place for the insert.

Tell me what you see in the table after each individual insert.

Did you try this yet?

[leinad31]

strSQL = "INSERT INTO tbl_CourseList (Course_Month, Course_Date, " & _
                                               "Course_Day, Course_Type, " & _
                                               "Course_Venue, Course_Times, Course_Year)" & _
                                " Values (?, ?, ?, ?, ?, ?, ?)"

Your not clearing the parameters collection.

I'm surprised you didn't get errors... are you using ON ERROR RESUME NEXT?

[aikidokid]

strSQL = "INSERT INTO tbl_CourseList (Course_Month, Course_Date, " & _
                                               "Course_Day, Course_Type, " & _
                                               "Course_Venue, Course_Times, Course_Year)" & _
                                " Values (?, ?, ?, ?, ?, ?, ?)"

Your not clearing the parameters collection.

I'm surprised you didn't get errors... are you using ON ERROR RESUME NEXT?

@Hack
No, hopefully in the next hour or so I will have time.

@leinad31

As I said earlier, I haven't used this method before.
You are saying replace the variables with question marks and just use the variables with the parameter values?
BTW I am not using On Error Resume Next

[techgnome]

I'm surprised you aren't getting errors either.... each time you append a parameter, you're adding a new one... the OLD ones are still there....

which is why the same values keep getting put in there.... solution is simple... re-arrange the code.

Set cn = New ADODB.Connection
cn.ConnectionString = strConnection & App.Path & "\Membership.mdb"
cn.Open

Dim adoCommand As ADODB.Command
Set adoCommand = New ADODB.Command


strSQL = "INSERT INTO tbl_CourseList (Course_Month, Course_Date, " & _
                                               "Course_Day, Course_Type, " & _
                                               "Course_Venue, Course_Times, Course_Year)" & _
                                " Values (?, ?, ?, ?, ?, ?, ?)"
                                
    With adoCommand
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = strSQL
        .Prepared = True
        .Parameters.Append .CreateParameter(, adInteger, adParamInput, 50)
        .Parameters.Append .CreateParameter(, adDate, adParamInput, 50)
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50)
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50)
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50)
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50)
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50)
    End With


For i = 0 To ctr - 1
    iMonth = CInt(cmbMM(i).Text)
    dtDate = CDate(cmbDD(i).Text & "/" & cmbMM(i).Text & "/" & cmbYY(i).Text)
    strDay = cmbDay(i).Text
    strType = cmbType(i).Text
    strVenue = cmbVenue(i).Text
    strTimes = cmbTime1(i).Text & " to " & cmbTime2(i).Text & " - " & cmbTime3(i).Text & " to " & cmbTime4(i).Text
    strYear = cmbYY(i).Text

    With adoCommand
        .Parameters(0).Value = iMonth
        .Parameters(1).Value = dtDate
        .Parameters(2).Value = strDay
        .Parameters(3).Value = strType
        .Parameters(4).Value = strVenue
        .Parameters(5).Value = strTimes
        .Parameters(6).Value = strYear
       
        .Execute , , adCmdText + adExecuteNoRecords
        'note: the last two arguments used for Execute here makes the execution of the command faster
    End With
Next

cn.Close
Set cn = Nothing

-tg

[aikidokid]

leinad31, I have just tried this with the question marks in place of the variables, as you showed and I still get the same problem of the first record being added for both entries I added.

I stepped through the whole process and looked at each value as it was passed and they were different.

[aikidokid]

@ techgnome

This works - thanks very much

Just so I understand the process now in place ...

1. Set up the SQL statement - using question marks for the placeholder of the value to be passed
2. Create the parameters first.
3. Loop through, changing the values to be passed to the parameters.

Also, do I need to set the adoCommand to nothing and close it?

[techgnome]

set command to nothing, close the connection, set connection to nothing.

-tg