Situation

Most seasoned ADO programmers know how to add a record to a database. Using a sql insert statement or recordset Addnew method, you can easily  add records to a database. It gets complicated when we do not know how many records (rows) we will add to the database.

To understand the problem more clearly, think of a restaurant where a waiter is taking your order. You order item or items, the waiter/waitress makes entries to his/her order form which has predefined X number of rows to complete. A customer can order one item, or order multiple items.

In the above insert table, one can insert one row or can insert all three rows. How do we accommodate ADO code for all the insert to work in one pass?

Setup for Solution

This solution works for both Internet Explorer and Netscape

Solution Step 1

We will solve the problem first by naming the fields in order note the html below

[Code Block]

<input type="text" class="tbflat" name="A1" size="3">
<input type="text" class="tbflat" name="B1" size="20">
<input type="text" class="tbflat" name="C1" size="20">
<input type="text" class="tbflat" name="A2" size="3">
<input type="text" class="tbflat" name="B2" size="20">
<input type="text" class="tbflat" name="C2" size="20">

Notice all of the controls are named A1, A2, B1, B2.....

There is a reason why we do this. We run a For loop to dynamically call these controls. Here is how -

[Code Block]

<%
Dim Test_Conn, Test_RS, Test_sql, Counter
'Define necessary variables

Set Test_Conn = Server.CreateObject("ADODB.Connection")
'Set an instance of the Connection object

Test_Conn.Open "Provider=SQLOLEDB; Data Source=yourserver; Initial Catalog=Northwind; User ID=sa; Password=sapassword"
'Open the connection with an OLEDB data provider


'Code to accommodate dynamic insert
for i = 1 to 3
'Total number of rows in our example is 3
a = request("a" & i)
b = request("b" & i)
c = request("c" & i)
'Get form field names, for each loop the form field values change, for example when i is 1 then a gets request.form("a1")...and so on

if len(a) <> 0 then
sql = "insert into table(field1, field2, field3)"
sql = sql & " values ("
sql = sql & "'" & A & "',"
sql = sql & "'" & B & "',"
sql = sql & "'" & C & "',"
sql = sql & ")"
conn.execute(sql)
end if
'Finally execute for this pass the insert statement.
next
'Proceed to next statement

%>

I hope you find places where you can use the above code. I know I did!

I thought this solution was quick and nice! If you like it send me a quick note.  (Yaheya Quazi)

Happy programming!