Inserting Multiple Records into a database

[dreamer610]

Hi,
I need to insert multiple records into an access database using an ASP page. I am going to have 14 rows on a page and each row is going to have the same 5 fields. I want each row to be a new record in the database. Usually I use Dreamweaver to create pages to insert records, but I don't see an easy way to do this. Any ideas? Thanks.

 

[girky]

I had the same problem, what i did is name each item in the row ending with the row number. So if you have items a through e then i have a1, b1, c1,....d14, e14. I don't know if this is the most efficient way, but it works for me

dim i, sql
For i = 1 to 14
sql= sql & "INSERT INTO REQUEST_SUB(a, b, c, d, e)"
sql= sql & "VALUES "
sql= sql & "('" & Request.Form("a" & i & "&quot & "',"
sql= sql & "'" & Request.Form("b" & i & "&quot & "',"
sql= sql & "'" & Request.Form("c" & i & "&quot & "',"
sql= sql & "'" & Request.Form("d" & i & "&quot & "',"
sql= sql & "'" & Request.Form("e" & i & "&quot & "');"
next

 

[mwolf00]

girky -

you forgot to execute your sql string inside your loop and you're concatating the first line. I think you mean...

dim i, sql
For i = 1 to 14
sql= "INSERT INTO REQUEST_SUB(a, b, c, d, e)"
sql= sql & "VALUES "
sql= sql & "('" & Request.Form("a" & i & "&quot & "',"
sql= sql & "'" & Request.Form("b" & i & "&quot & "',"
sql= sql & "'" & Request.Form("c" & i & "&quot & "',"
sql= sql & "'" & Request.Form("d" & i & "&quot & "',"
sql= sql & "'" & Request.Form("e" & i & "&quot & "');"
objConn.execute (sql)
next
Get the Best Answers! faq333-2924
Merry Christmas!
mikewolf@tst-us.com
[santa2]

 

[Tarwn]

Yes this is actually very efficient. Much more so than looping through doing AddNew's or something like that. Basically your still only having to create your connections once and send info once but the database is still getting all the inertion commands. Stored procedures might be quicker, but it depends on how they were set up by MS inside the ADO object. It is entirely possible that they could have more overhead than doing a batch insert like this in one line. Might be a good test to run.

I know stored procedures are faster than using the built-in addnew, and most people quote it as downright faster (since it is more efficient for the database) but it would be interesting to see how they compare after ASP adds it's overhead of creating the objects and sending the data.

Maybe I will test it out tonight and let everyone know

-Tarwn Experts are only people who have realized how much they will never know about a language.
________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924

 

[girky]

Thanks mwolf00, I forgot to check that first line.

And I execute mine after the loop, that's why I put the ; in. I guess it doesn't really matter either way.

 

[mwolf00]

Actually girky, I think that you may be right... If access can recognize the individual INSERT statements when they are separated by a semicolon then your first code is valid and would execute faster with one execute statement.

I would add
sql = ""
after your dim statement.... Get the Best Answers! faq333-2924
Merry Christmas!
mikewolf@tst-us.com
[santa2]

 

[dreamer610]

Thanks guys! I'll try these suggestions tonight!