I am using access and asp.

I am Selecing some records from one from x table in the db. Then I am inserting those records in another table.

The insert works ok. But, The code won't insert the last record of the data selected.

I want to know why it isnot inserting the last record?

this is what i did
I Created a recordset to select my data. Used a while..Wend to loop.
Created a command object to insert data in another table

See below...

Dim vChild_id
vChild_id = cid

' get enrollment_history records for child
Dim rsGetEnrollmentHist

Set rsGetEnrollmentHist = Server.CreateObject("ADODB.Recordset")
rsGetEnrollmentHist.ActiveConnection = MM_DBConn_STRING

rsGetEnrollmentHist.Source = "SELECT * FROM lws_enrollment_history WHERE child_id = " + Replace(vChild_id, "'", "''") + ""

rsGetEnrollmentHist.CursorType = 0
rsGetEnrollmentHist.CursorLocation = 2
rsGetEnrollmentHist.LockType = 1
'rsGetEnrollmentHist.Open()


' create object
set cmdLogEnrollmentHist= Server.CreateObject("ADODB.Command")
cmdLogEnrollmentHist.ActiveConnection = MM_DBConn_STRING

Dim cmdSQLQuery, tempEnrollment_id, tempUserFirstName, tempProvider_id, tempEnrollmentStatusId

rsGetEnrollmentHist.Open()

'inserty records from rsGetEnrollmentHist recordset
while (NOT rsGetEnrollmentHist.EOF)
        tempEnrollment_id = rsGetEnrollmentHist.Fields.Item("enrollment_id").V alue
        tempUserFirstName = rsUser.Fields.Item("lastname").Value
        tempProvider_id = rsGetEnrollmentHist.Fields.Item("provider_id").Val ue
        tempEnrollmentStatusId = rsGetEnrollmentHist.Fields.Item("enrollment_status _id").Value

cmdSQLQuery = "INSERT INTO lws_enrollment_history_activity (enrollment_id, updated_on, updated_by, enrollment_status_id, provider_id) VALUES (" & tempEnrollment_id & ", #" & Date() & "#, '" & Replace(tempUserFirstName, "'", "''") & "', " & tempEnrollmentStatusId & ", " & tempProvider_id & " ) "

cmdLogEnrollmentHist.CommandText = cmdSQLQuery
cmdLogEnrollmentHist.CommandType = 1
cmdLogEnrollmentHist.CommandTimeout = 0
cmdLogEnrollmentHist.Prepared = true
cmdLogEnrollmentHist.Execute()

rsGetEnrollmentHist.moveNext()
Wend

'closed recordset
rsGetEnrollmentHist.Close()
Set rsGetEnrollmentHist = Nothing


Thanks

Hey,

First are you sure that the query is returning the last record? Have you tested it?

Brian

Yes, I did

Yes, I tested the query. It is working fine

thanks

Forget the way you're doing it at the moment, its very long-winded. You can do it all in one SQL query using the general "INSERT INTO TableA SELECT blah FROM TableB" syntax. Looking at your code, you need a query something like this:
hth
Phil