|
Home > Archive > IIS ASP > May 2004 > insert multiple records to table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
insert multiple records to table
|
|
| Roy Adams 2004-05-30, 11:53 am |
| 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"> </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
| |
| Curt_C [MVP] 2004-05-30, 11:53 am |
| 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" <roy_adams@ntlworld.com> wrote in message
news:131b43be.0405260844.56761468@posting.google.com...
> 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"> </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] 2004-05-30, 11:53 am |
| 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.
| |
|
|
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.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Bob Barrows [MVP] 2004-05-30, 11:53 am |
| 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.
|
|
|
|
|