[RESOLVED] Problem adding multiple records
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?
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
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?
Re: Problem adding multiple records
Put a break on this line
Code:
For i = 0 To ctr - 1
and step through the code.
I have a feeling the control array index may not be getting updated.
Re: Problem adding multiple records
Quote:
Originally Posted by Hack
Put a break on this line
Code:
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
Code:
For i = 0 To ctr - 1
'my code
Next
But yes I have stepped through and the values are different!
Re: Problem adding multiple records
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!
Re: Problem adding multiple records
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.
Re: Problem adding multiple records
These are the Debug.Print results of me adding two more records to the DB (reverse order)
Quote:
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
Quote:
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
Re: Problem adding multiple records
Ok. So they are different so you aren't adding the same record twice, right? Isn't that the question?
Re: Problem adding multiple records
Quote:
Originally Posted by Hack
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:
Quote:
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 :confused:
Re: Problem adding multiple records
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.
Re: Problem adding multiple records
Quote:
Originally Posted by 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.
Thanks Hack
I will have to do this tomorrow, but I will definately post back.:wave:
Re: Problem adding multiple records
I'll be looking for it. This just doesn't make any sense. See you tomorrow!
Re: Problem adding multiple records
Why aren't you just doing a cn.Execute on strSQL?
Re: Problem adding multiple records
I agree with you A142! instead of strSQL = .... why not cn.Execute "insert into..."
Re: Problem adding multiple records
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. :D
Re: Problem adding multiple records
Quote:
Originally Posted by 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?
Re: Problem adding multiple records
Code:
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?
Re: Problem adding multiple records
Quote:
Originally Posted by leinad31
Code:
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
Re: Problem adding multiple records
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.
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
Re: Problem adding multiple records
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. :mad: :confused:
Re: Problem adding multiple records
@ techgnome
This works - thanks very much :D
Just so I understand the process now in place ...
- Set up the SQL statement - using question marks for the placeholder of the value to be passed
- Create the parameters first.
- Loop through, changing the values to be passed to the parameters.
Also, do I need to set the adoCommand to nothing and close it?
Re: Problem adding multiple records
set command to nothing, close the connection, set connection to nothing.
-tg
