Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > insert multiple records to table

Reply
Thread Tools

insert multiple records to table

 
 
Roy Adams
Guest
Posts: n/a
 
      05-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[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
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      05-26-2004
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.


 
Reply With Quote
 
 
 
 
Roy
Guest
Posts: n/a
 
      05-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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      05-26-2004
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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Multiple Records Using One Insert Statemen with MySQLdb module anton.ranieri.it@gmail.com Python 1 12-06-2008 12:47 PM
Best way to Insert multiple records into a table? Ian Macey ASP .Net 1 01-25-2005 02:11 PM
Help ASP; get last inserted value from one table, insert multiple rows in another table. PT ASP General 1 10-07-2004 07:27 AM
Insert Multiple Records at a time based on checkbox selection Vijay Pathak via .NET 247 ASP .Net 0 09-22-2004 06:53 AM
Re: insert multiple records using asp.net Michael Ramey ASP .Net 0 01-30-2004 10:25 PM



Advertisments