how to insert multiple records in the code below

[uma9]

this code is used to insert one record....how do i insert multiple records??

Set connect = CreateObject ("ADODB.Connection")
connect.open "DSN=OPTUMETL;Driver= Oracle in OraHome92;Server=urnts1.uhc.com;UID=OPTUMETL;PWD=OPTUMETL"
Reporter.ReportEvent 0, "Database connection", "Successfully connected to URNTS1" 
Set objRecordset = CreateObject("ADODB.Recordset")
' Stmt to execute the code
query="INSERT INTO STG_CARE.ASSESSMENTANSWER(ANSWERID, ANSWERGROUPID, ANSWERTEXT, DISPLAYSEQUENCE, ISEXCLUSIVENO, ISEXCLUSIVEYES, 
CREATEBY, CREATEDATE, UPDATEBY, UPDATEDATE, RECORDSTATUS, RECORDVERSION, RECORDSOURCE, SYNCDATE, RECORDUNIQUEID, 
PARENTRECORDUNIQUEID, AUDIT_CREAT_DT, AUDIT_UPDATE_DT, SRC_SYSTEM_ID)VALUES(95149, 95052, 'Yes (Red Flag)', 10, 0, 0, 1, TO_DATE('11/10/2005 
01:05:29 PM','DD/MM/YYYY HH12:MI:SS PM'), 1, TO_DATE('11/10/2005 01:05:29 PM','DD/MM/YYYY HH12:MI:SS PM'), 0, 1, 0, TO_DATE('01/01/1900 12:00:00 
AM','DD/MM/YYYY HH12:MI:SS PM'), 0, NULL, TO_DATE('18/08/2008 06:06:16 AM','DD/MM/YYYY HH12:MI:SS PM'), TO_DATE('18/08/2008 06:09:13 AM','DD/MM/YYYY 
HH12:MI:SS PM'), 'CARE')"
msgbox query
Set objResults = connect.Execute(query) ' Cmd used to execute the sql
'
Reporter.ReportEvent 0, "Query execution", "Executed query successfully" 
'
'objResults.Close

[QVeen72]

Hi,

Instead of Executing, "Insert" Query, Open a Recordset and add..

REgards
Veena

[uma9]

hi veena....
i have not worked much in VB...can u please tell me how to Open a Recordset and add..can u also tell me if multiple records can be added using the "for" loop....if yes,how is it done....

Hi,

Instead of Executing, "Insert" Query, Open a Recordset and add..

REgards
Veena

[QVeen72]

Hi,

Try This Code:

Dim RST As New ADODB.Recordset
Dim sSQL As String
Dim i As Integer
sSQL = "Select * From MyTableName"
RST.Open sSQl, Conn,adOpenDynamic, adLockOptimistic
For i = 1 To 3
RST.AddNew
RST("SlNo") = i
RST("Col1") ="Whatever.."
RST("Col2") =Text1.Text
RST.Update
Next
RST.Close
Set RST = Nothing

Regards
Veena

[uma9]

thanks for the help veena....

but i am getting a compile error....which states expected:end of statement....please forgive me if this is really a stupid doubt...how do i set it right???