By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,630 Members | 1,126 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,630 IT Pros & Developers. It's quick & easy.

insert multipe records at once how_URGENT

P: 35
i'm developping a web application with an access database

in my application there's a loop that gets values of a form

for i=0 to lastElt
qty=request("qty" & i )
desc=request("desc" & i )
objConn.execute("insert into items values(" & qty & " , ' " & desc & " ' )" )

and i have to insert like 12-15 records
is there a way to insert them at the same time like a batch operation, or something instead of accessing the database multiple times

well im using a stored procedure that does the insert but i f there's a way to batch-insert i'll take it

need code PLEASE!!

another isuue:

i need to insert a row into a table whose PK is an autonumber and then get the PK value. I'm using aq stored procedure to do the insert and then the @@IDENTITY method to get this value(PK)
my question is : if there were 2 or more users executing the page at the same time, can user1's inserted PK be seen by user2
ie. 2 ADO connections here , the @@IDENTITY does it depend on the connection calling it or does it retrieve the last inserted PK value no matter who or what connection did the insert or does a it return the value last inserted by the ADO connection issuing the @@IDENTITY call
Aug 9 '06 #1
Share this Question
Share on Google+
1 Reply

P: 12
Hey r_o, I just joined this forum and saw your post. I'm using the method you mentioned in my web application. Here is how I'm going about it.

Start your loop and assign the unique ID (auto number in Access table) to a variable. In this case I've assigned CustomerID to the variable t_id
'queue up to the top record of the recordset
If Not rs_users.EOF Then rs_users.MoveFirst

'make a temporary variable for user id because the less typing the better :)

'loop through all the records outputting a row for each one
Do Until rs_users.EOF
t_id = rs_users("CustomerID")


If the html portion of the page assign the t_id to a hidden variable. In this case I've assigned it to ua_id.
<input type="hidden" name="ua_id" value="<%=t_id%>">



Here is where I'm taking all the records from the loop in the first page and posting them all to the database. I'm using the Cleaninput function to deal with commas and the like. We then capture the hidden variable ua_id from the previous page and assign it to a variable called arr_ids. Use a for loop along with the SQL statement and you have it.

Function CleanInput(strReqName)
CleanInput = Replace(Request.Form(strReqName),"'","''")
End Function

arr_ids = Split(Request.Form("ua_id"),", ")

'now loop through each id, build the sql, and execute the sql
'Dim id,sql_insert,t_idDate, AreaID, TheMessage
For Each id in arr_ids

sql_update = "UPDATE ExceptionsTemp SET CashItems = '" & CleanInput("txtCash_" & id) & "', " & _
"Suspense = '" & CleanInput("txtSuspense_" & id) & "', " & _
"PackageItems = '" & CleanInput("txtPackages_" & id) & "', " & _
"CDs = '" & CleanInput("txtCDs_" & id) & "'"

'And finally the WHERE portion
sql_update = sql_update & " WHERE VolID = " & id

'Make it so!
Conn.Execute sql_update
Sep 4 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.