insert multiple records to table

Roy Adams · May 26, 2004

Hi forum
I have three text fields set up on a page that when submitted I want
them to be inserted in to a table but in differnt records at once.
the code below only seems to insert the last field

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<%

var colorForm = String(Request.Form("color"));
colorForm_array = colorForm.split(",");

if( colorForm != "" ){
//make the sql connection object and open it here
conn = Server.CreateObject('ADODB.Command');
conn.ActiveConnection = "dsn=Mydsn;";

for( i=0 ; i < colorForm_array.length ; i ++){

conn.CommandText = ("insert into color (color) values ('" +
colorForm_array + "')" );
}
conn.Execute();
conn.ActiveConnection.Close();

}

%>

all there is in the form are 3 text fields with the same name "color"
and a submit button

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
</head>

<body>
<form name="form1" action="" method="post">
<table width="600" border="0" align="center" cellpadding="4"
cellspacing="2">
<tr>
<td width="300"> </td>
<td width="278"> </td>
</tr>
<tr>
<td> </td>
<td><input name="color" type="text" id="color"></td>
</tr>
<tr>
<td> </td>
<td><input name="color" type="text" id="color"></td>
</tr>
<tr>
<td> </td>
<td><input name="color" type="text" id="color"></td>
</tr>
<tr>
<td colspan="2"> <div align="center">
<input type="submit" name="Submit" value="Submit">
</div></td>
</tr>
</table>
</form>
</body>
</html>

I'm quite new to this so any help is appriciated!
Thanks

Bob Barrows [MVP] · May 26, 2004

Roy said:
Hi forum
I have three text fields set up on a page that when submitted I want
them to be inserted in to a table but in differnt records at once.
the code below only seems to insert the last field

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<%

var colorForm = String(Request.Form("color"));
colorForm_array = colorForm.split(",");

if( colorForm != "" ){
//make the sql connection object and open it here
conn = Server.CreateObject('ADODB.Command');
conn.ActiveConnection = "dsn=Mydsn;";

for( i=0 ; i < colorForm_array.length ; i ++){

conn.CommandText = ("insert into color (color) values ('" +
colorForm_array + "')" );
}
conn.Execute();
conn.ActiveConnection.Close();

You need to execute the statement within the loop, not outside:

for( i=0 ; i < colorForm_array.length ; i ++){

conn.CommandText = ("insert into color (color) values ('" +
colorForm_array + "')" );
conn.Execute(,,129);
conn.ActiveConnection.Close();
}

However, I would prefer to make a single call to the database, like this:

var sSQL = "insert into color (color) select'" + colorForm_array[0] + "'"
for( i=1 ; i < colorForm_array.length ; i ++){
sSQL += " union all select '" + colorForm_array + "'"
}
//for debugging:
Response.write(sSQL)
conn.Execute(,,129);
conn.ActiveConnection.Close();

HTH,
Bob Barrows
PS. The "129" is the addition of two settings: 1 and 128. "1" sets the
command type to adCmdText, meaning that you are executing
a string containing a sql statement. The "128" sets the execution
type to adExecuteNoRecords, which is self-explanatory.
"129" combines them, so you have the effect of telling ADO
that you are executing a string containing a sql statement that
does not return any records - the latter is important, without
it, ADO automatically creates a recordset behind-the-scenes,
which is a waste of resources if you're never going to look at
that recordset.

Roy · May 26, 2004

Thanks for the reply bob, although i did work it out just before i got
your reply,
but the way that you tweak my code works well
the problem I'm hving now though is if a text field is empty it still
adds a record, do you know of a way around this?
Just when I thought it was over

Bob Barrows [MVP] · May 26, 2004

Roy said:
Thanks for the reply bob, although i did work it out just before i got
your reply,
but the way that you tweak my code works well
the problem I'm hving now though is if a text field is empty it still
adds a record, do you know of a way around this?
Just when I thought it was over

Use an If statement in the loop to check the content of the textbox.

Bob Barrows

Can someone tell me if this a real tracker? Or is it one designed to show you a different message at certain times, ie. acting like one?	0	Jan 10, 2021
Filter table rows based on multiple checkboxes value	2	Jan 13, 2023
Help needed with thank you message	5	Sep 11, 2021
Javascript DOM	1	Mar 29, 2023
Script to send email not working	1	Apr 10, 2023
How to create a JSON array with values from DOM(HTML TABLE) when I click a button using JQuery/Javascript?	0	May 1, 2023
How to have two html audio players on one page?	0	May 3, 2022
How to wrap <td> content .	16	Sep 28, 2023

insert multiple records to table

Roy Adams

Bob Barrows [MVP]

Roy

Bob Barrows [MVP]

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads