Looping one recordset with another

[Gary Williams]

Hi All,

I need to insert many new records into a remote access database table. I found this script (globalUpdate.asp) on an asp code site, that works perfectly to insert a single record, comprising the data in the form, into a table.

What I need to do is expand this solution so that instead of inserting one record, insert a batch of records. That is, replace the form with a csv file of new records exported fron an excel file, and loop around the script (globalUpdate.asp) until all records have been inserted.

I have also found the final script 'open csv as recordset' which will create the necessary recordset for the new records to be inserted. How do you get globalUpdate.asp to loop around the csv record set, or is it the other way around?

Regards

Gary


************ code for Form ********************

<FORM ACTION="/scripts/globalUpdate.asp" METHOD=POST>
Name: <INPUT TYPE=TEXT NAME=Name SIZE=40>
Phone Number: <INPUT TYPE=TEXT Name=PhoneNumber SIZE=15>
Email Address: <INPUT TYPE=TEXT NAME=Email SIZE=25>
<INPUT TYPE=HIDDEN NAME=TableName VALUE="Contact">
<INPUT TYPE=SUBMIT>
</FORM>

**************code for globalUpdate.asp ******************

<%
Option Explicit

'Our ADO constants we'll need
Const adOpenForwardOnly = 0
Const adLockOptimistic = 3
Const adCmdTable = &H0002

'Create our connection object and open a connection to our database
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")

objConn.ConnectionString = "DSN=TestDB"
objConn.Open

'Create a recordset object
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")

'Open a table view for the table name specified by Request("TableName")
Dim strTableName
strTableName = Request("TableName")

objRS.Open strTableName, objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable

'Add a new record...
objRS.AddNew

'Iterate through the fields of the table...
Dim fld
For Each fld in objRS.Fields

'If a value for the column name was passed in,
'set the column name equal to the value passed through the form...
if Len(Request(fld.Name)) > 0 then
fld.Value = Request(fld.Name)
end if

Next

'We're done, so update the record
objRS.Update

'Clean Up...
objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing

'Send the user to some confirmation page
Response.Redirect "RecordAdded.htm"

***************code for open csv as a recordset*********

Dim connCSV As New ADODB.Connection
Dim rsTest As New ADODB.Recordset
Dim adcomm As New ADODB.Command
Dim path As String

path = "C:\Testdir\" 'Here Test dir is the Directory where
' the text file is located. don't write the file name here.

'This is connection for a text file without Header

'connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& path & ";Extended Properties='text;HDR=NO;FMT=Delimited'"


'This is connection for a text file with Header (i.e., columns

connCSV.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
& path & ";Extensions=asc,csv,tab,txt;HDR=NO;Persist Security Info=False"


rsTest.Open "Select * From test.txt", _
connCSV, adOpenStatic, adLockReadOnly, adCmdText
Do While Not rsTest.EOF
MsgBox rsTest(0) 'You can select the required data
rsTest.movenext
Loop

**************************

[Spudhead]

If I understand you right, the "open a CSV" bit is on the right lines, but you don't need all that stuff in globalUpdate.asp

Is this any use:


<%
dim sPath, cnCSV, rsCSV, cnDB

Set cnDB = Server.CreateObject("ADODB.Connection")
cnDB.ConnectionString = "DSN=TestDB"
cnDB.Open

sPath = "C:\Testdir\"
set cnCSV = Server.CreateObject("ADODB.Connection")
cnCSV.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & sPath & ";Extensions=asc,csv,tab,txt;HDR=NO;Persist Security Info=False"

set rsCSV = cnCSV.execute("SELECT * FROM test.txt")
do while not rsCSV.EOF

str_First_CSV_Field = rsCSV.Fields(0)
str_Second_CSV_Field = rsCSV.Fields(1)

cnDB.execute("INSERT INTO [myTable] (First_Database_Field, Second_Database_Field) VALUES ('"&str_First_CSV_Field&"', '"&str_Second_CSV_Field&"')")

rsCSV.movenext
loop

rsCSV.close()
set rsCSV = nothing
cnCSV.close()
set cnCSV = nothing

cnDB.close()
set cnDB= nothing
%>

[Gary Williams]

Hi Spudhead,

Brilliant! Once I got the paths, etc, sorted out, it worked a treat. Now I can batch upload records without downloading/uploading the whole database.

My database didn't have a DSN so I tweeked the code as below.

Many thanks for this.

Regards

Gary

*****************************
<%
dim sPath, cnCSV, rsCSV, cnDB, strdbpath

strdbpath = "d:\physical path to database\testdata.mdb"

' open the connection
Set cnDB=server.createobject("ADODB.CONNECTION")
cnDB.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strdbpath

sPath = "D:\physical path to directory of test.csv file\"
set cnCSV = Server.CreateObject("ADODB.Connection")
cnCSV.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & sPath & ";Extensions=asc,csv,tab,txt;HDR=NO;Persist Security Info=False"

set rsCSV = cnCSV.execute("SELECT * FROM test.csv")
do while not rsCSV.EOF

str_First_CSV_Field = rsCSV.Fields(0)
str_Second_CSV_Field = rsCSV.Fields(1)
str_Third_CSV_Field = rsCSV.Fields(2)
str_Fourth_CSV_Field = rsCSV.Fields(3)
str_Fifth_CSV_Field = rsCSV.Fields(4)
str_Sixth_CSV_Field = rsCSV.Fields(5)
str_Seventh_CSV_Field = rsCSV.Fields(6)
str_Eighth_CSV_Field = rsCSV.Fields(7)

cnDB.execute("INSERT INTO tablename (FieldName1,FieldName2,FieldName3,FieldName4,FieldName5,FieldName6,FieldName7,FieldName8) VALUES ('"&str_First_CSV_Field&"', '"&str_Second_CSV_Field&"' , '"&str_Third_CSV_Field&"' , '"&str_Fourth_CSV_Field&"' , '"&str_Fifth_CSV_Field&"' , '"&str_Sixth_CSV_Field&"' , '"&str_Seventh_CSV_Field&"' , '"&str_Eighth_CSV_Field&"' )")

rsCSV.movenext
loop

rsCSV.close()
set rsCSV = nothing
cnCSV.close()
set cnCSV = nothing

cnDB.close()
set cnDB = nothing
%>
***************************