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

insert multiple records to table

P: n/a
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[i] + "')" );
}
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">&nbsp;</td>
<td width="278">&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="color" type="text" id="color"></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="color" type="text" id="color"></td>
</tr>
<tr>
<td>&nbsp;</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
Jul 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
you will have to run it as 3 inserts. Roll it into a transaction oif you
need to ensure they all go through.
It's a SQL thing, not really an ASP thing

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Roy Adams" <ro*******@ntlworld.com> wrote in message
news:13**************************@posting.google.c om...
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[i] + "')" );
}
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">&nbsp;</td>
<td width="278">&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="color" type="text" id="color"></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="color" type="text" id="color"></td>
</tr>
<tr>
<td>&nbsp;</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

Jul 19 '05 #2

P: n/a
Roy Adams wrote:
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[i] + "')" );
}
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[i] + "')" );
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[i] + "'"
}
//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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #3

P: n/a
Roy

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:(

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 19 '05 #4

P: n/a
Roy wrote:
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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.