ASP insert into multiple rows with one sql statement

Are these 5 Different Records?
Give me an example of what is listed on all 5 boxes.

I am putting together an example right now, and it should not take long to make it up.
But, in order for me to know what it is that you are wanting exactly.
I am going to need to know the following information:

Combo1
Information = ID#
Combo2
Information = ID#
Combo3
Information = ID#
Combo4
Information = ID#
Combo5
Information = ID#

How ever it is, I need to know in order to make this up correctly.
Carrzkiss

Let me know if this is what you are wanting.
http://www.pcitdad.com/test/EE/Q_24079450/Choose.asp?id=1

To work it.
Choose any item from the dropdown list(s)
Then click the Submit button.
The items will be updated into the Database for the Users.ID=1

Hope this will help.
Carrzkiss

Hi there,

carrzkiss, your almost there, the value of the drop boxes contain numbers from 1 to 10.

the page should contain records from the database with a drop down box against each record. So basically, the drop down boxes are dynamic as the amount of drop down boxes only appear on the page depending on the amount of records in the database.

What i want is an insert into statement where i select e.g. 5 drop down boxes and the information i select from the drop down box is inserted into the database into multiple rows.

drop down box contains numbers from 1 to 10.

Thank You

Kind Regards,
Fezi

I am no good with Dynamic content.
What have you been able to do on your own?
If you can provide some code, then maybe/hopefully I will be able to assist.
Without some help on this, it is going to be a rough road, as it is so hard to find
Good solid information on Dynamic Content.

Wait to hear back...

Hi there, all i have been able to do is display records from the database along with drop down box against them.. in a loop.

linkpick.asp contains the following code


<form action="linkpickverify.asp?pid=<%=request.querystring("pid")%>&add=true" name="form1"  method="post">
<table cellpadding="3" width="100%" style="font-size:11px; font-family:verdana;">

<tr>
<td colspan="4" style="color:red">
<%if request.querystring("result") = "done" then%>
Links sucessfully added!
<br><br>
<%end if%>
</td>
</tr>

<tr>
<td>Page Name</td>
<td>URL</td>
<td>Active</td>
<td align="center">Order</td>
</tr>

<%
set objRS = Server.CreateObject("ADODB.Recordset")
SQL= "SELECT * FROM link"
Set objRS = db.execute(sql)

objRS.MoveFirst
Do While Not objRS.EOF

%>

<tr style="font-size:11px; font-family:verdana;">
<td><%=objRS("name")%></td>
<td><%=objRS("url")%></td>
<%
if objRS("active") = 0 then
response.write "<td align='center'><img src='images/deactive.gif' border='0'></td>"
else
response.write "<td align='center'><img src='images/active.gif' border='0'></td>"
end if
%>
<td align="center">
<select name="order">
      <option value="" SELECTED></option>
      <option value="1">1</option>
      <option value="2">2</option>
      <option value="3">3</option>
      <option value="4">4</option>
      <option value="5">5</option>
      <option value="6">6</option>
      <option value="7">7</option>
      <option value="8">8</option>
      <option value="9">9</option>
      <option value="10">10</option>
</select>
</td>

</tr>
<%
objRS.MoveNext
Loop
%>
<tr>
<td align="right" colspan="5"><br><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</form>


linkpickverify.asp contains the following code

<%

if request.querystring("add") = "true" then

set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open

a=request.querystring("pid")
b=Request.Form("order")

sSQLString = "INSERT INTO linksref (pageid, order) VALUES ('" + a + "', '" + b + "')"
db.Execute(sSQLString)

Response.redirect "linkpick.asp?pid="&request.querystring("pid")&"&add=true&result=done"

end if
%>

I hope this is useful.. I have tried numerous ways, and unsuccessful...  let me know if you need any more information, i wil be sitting on my computer trying to work it out..  for the next few hours.

Cheers carrzkiss...

I think that I see what you are trying to do. (The same thing that I was trying to do back last year)
Using the Dropdown to Order your links.

OK.
I was going to do the same thing for [Images] that was on a site, but could never figure it out.

I will see what I can do, and hopefully get somewhere with it.
I will inform you here in a little while on my progress.

Cheers to you to.
Carrzkiss

Hi carrzkiss, thank you very much, glad to know i am not along on this problem.. I've been trying to figure this out for the last 2 weeks.. getting a little annoyed with it. I do appreciate what ever help i can get on this.

there are some solutions on EE for multiple inserts, i'm having a good read through, none that are related to what i want.. If i do find any, wil post you the link..

Thanks Carrzkiss... please do let me know if you find a solution. Cheers.

Kind Regards,
Faraz

Do you know how many records are going to be shown per page?
If so, then I "Might" have a way of doing this.
But, still not really sure, it is just something that I will have to work on here.

Hi carrzkiss,
I don't know the amount of records i will have, because that depends on how many links exist, but overall you can only select a maximum of 10 links. I will have all the records displayed in one page.

Do you know what i mean?

OK.
I am still playing around with it, and it is being a pain.
I have managed to give the List their own Names.

Do the following (This is if you have a field in your Database Table named: ID
But can be changed to what you need it to be, as long as it is a unique # for the different boxes
The name of to be different.
I think that you get the point.)

Add this to your Combobox
<select name="order<%=objRS("ID")%>">

Now, on every one that is made, it will take on the name of the
URL that is inserted.
So:

ID   URL         Name
1     site.com       EE
2    mysite.com   My

When the form is loaded, it will list the first box as: Order1
And the second box will be:   Order2
And so forth down the line for every link that is inserted into the form.

So,
Now that we know how to have different names, we just need to get it to INSERT into the Table
All the different ones that exist on the page.
That is going to be the tricky part.

I did find this code, which may work, if I can figure it out.
(or) yourself.

Link
http://www.codetoad.com/forum/16_22396.asp
Code below

Re: Dynamically process the Request.Form Collection  Troy Wolf at 09:43 on Friday, August 29, 2003 
 
Here is the VBScript code to dynamically process the Request.Form collection. I use the InStr() function to see which element name I`m working with, then build the appropriate SQL statement.
--------------------------------
<%
'for each element in Request.Form
if InStr(1,element,"work_on") > 0 then
sql = "insert into workon (work_on) values (`" & _
Replace(Request.Form(element),"`","``") & "`)"
cnn.Execute(sql)
elseif InStr(1,element,"work_db") > 0 then
sql = "insert into workdb (work_db) values (`" & _
Replace(Request.Form(element),"`","``") & "`)"
cnn.Execute(sql)
end if
next
%>
 
Maybe, changing it around to suit your needs, will have to look deeper into it.
 
Let me know if you come up with anything as well.

Select allOpen in new window

Hi Carrzkiss, I cant seem to get the code working, will have to look into it later, I got something though, see below code.

What i am trying to do is insert the id's of the link, page id and value of the drop down box (order).

If you look at the code below for linksverify.asp you will see when you submit the form the next page displays the sql statement which you simply insert into the db, the problem i have now is to execute that statement, it just does not work..

Look at my preview, you will understand better
http://www.farazshafiq.com/cms/in/linkpick.asp?pid=1&add=true

do you understand what i mean?

linkpick.asp
 
<form action="linkpickverify.asp?pid=<%=request.querystring("pid")%>&add=true" name="form1"  method="post">
<table cellpadding="3" width="100%" style="font-size:11px; font-family:verdana;">
 
<tr>
<td colspan="4" style="color:red">
<%if request.querystring("result") = "done" then%>
Links sucessfully added!
<br><br>
<%end if%>
</td>
</tr>
 
<tr>
<td>ID</td>
<td>Name</td>
<td>URL</td>
<td>Active</td>
<td align="center">Order</td>
</tr>
 
<%
set objRS = Server.CreateObject("ADODB.Recordset")
SQL= "SELECT * FROM link"
Set objRS = db.execute(sql)
 
objRS.MoveFirst
Do While Not objRS.EOF
 
%>
 
<tr style="font-size:11px; font-family:verdana;">
<td><%=objRS("id")%></td>
<td><%=objRS("name")%></td>
<td><%=objRS("url")%></td>
<%
if objRS("active") = 0 then
response.write "<td align='center'><img src='images/deactive.gif' border='0'></td>"
else
response.write "<td align='center'><img src='images/active.gif' border='0'></td>"
end if
%>
<td align="center">
<select name="order<%=objRS("id")%>">
	<option value="" SELECTED></option>
	<option value="1">1</option>
	<option value="2">2</option>
	<option value="3">3</option>
	<option value="4">4</option>
	<option value="5">5</option>
	<option value="6">6</option>
	<option value="7">7</option>
	<option value="8">8</option>
	<option value="9">9</option>
	<option value="10">10</option>
</select>
</td>
 
</tr>
<%
objRS.MoveNext
Loop
%>
<tr>
<td align="right" colspan="5"><br><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</form>
 
 
linkpickverify.asp
 
<%
 
if request.querystring("add") = "true" then
 
set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open
 
a=request.querystring("pid")
b=Request.Form("order")
 
for each item in request.form
if item <> "Submit" then 
	if request.form(item) <> "" then
 
	response.write "insert into linksref (link_id, pageid, order) values ("&replace(item,"order","")&","&request.querystring("pid")&","&replace(request.form(item),"'","")&")<BR>"
 
'sSQLString = "INSERT INTO linksref (pageid, order) VALUES ('" + a + "', '" + b + "')"
'db.Execute(sSQLString)
 
	end if
end if
 
next
 
'Response.redirect "linkpick.asp?pid="&request.querystring("pid")&"&add=true&result=done"
 
end if
%>

Select allOpen in new window

Hi there,

Is there any luck, i cant seem to figure it out, i'm having a really terrible time dealing with this problem, and i really need this multiple insert to work..

would really appreciate if you can get it to work asap.. plz

Cheers

Kind Regards,
Fezi

I am trying.
I also have other issues as well.
But I have not forgot about you, as this is something that I am interesting in as well.

I will get back with you once I can come up with something

I think that I might have it!!!
I am hoping by this evening it will be completed.

I have got it to insert the "URL Names"
I just have to get it to grab the "Order" And "Url ID"

I will let you know something hopefully by 8:00pm EST

Hi there, thats great news... I will not be inserting the link names, the reason to this is, say i wish to change the name and description of the link, which is stored in another table, it will get a little complicated when the names are dynamically changing.

So, I have decided i will insert the ID's (link id, page id and order). I think that is probably the best idea, because i may have a few pages containing the same link. And at the front end i will do a join between the two tables to display the link.

Cheers

Kind Regards,
Fezi

Now, I have to re-design the code.

I was getting the "Names" entered, and now everything is entering as triples (I have 3 URL's in a table that I am testing)

So, I will get back with you when I am able to get to work.
This is really a nerve wrecking ordeal
But it is something that I am needing as well, which is one of the reason's why I am continuing to work on it.

Just give me some more time.
Hopefully, thinks will work out.
I might have to do something diffent, but, if I can get it all to work right, I will let you know.

One quick question
link id = The is the ID Number of the Link
page id = What is this? Is this the page that you are own? Basically, the same # inserts into every record
order = And this is from the Dropdown box, which is being a pain right now.

The: page id..... is what I am wondering about here
What is it?

Hi Carrzkiss,

page id = this is the page the links are being changed on. basically it will be a request.querystring("pid")

everything else is correct...

Thanx Carrzkiss

King Regrds,
Fezi

OK
That is what I was thinking.

OK.
I will see what I can do, it is really being a pain in the butt.

Hi carrzkiss,

I can imagine what you're going through, it is tough. Will it be complicated if i add an update to it aswell. Because i would like to change the order of links aswell as insert. Is that something complicated.

Kind Regards,
Faraz

Right now, we just need to get the Insert done.
Once we can get that done,
Then I think another Question would have to be opened to accomidate the UPDATE
As it is a different matter all together.

So lets work through this one first.

Hi Carrzkiss, Yes i understand, as you can tell am new to experts-exchange. Let me know when you have accomplished the mission, so i can open another question for the update.

Cheerz.

I will have you a demo code up and running TODAY  YEAAAAAAAA!!!!!!!
here in a few hours.

Hi, Thats great news... cheers... I will be waiting, thanx


Kind Regards,
Fezi

Hi Carrzkiss,

Let me be the first to say THANK YOU. It is good. I can see what you have done, i would have never thought of that.

The only thing i dont like which is very small is the checkboxes, because it is a long process for selection, first you have to select which links you want then you have to order your links. I was thinking of doing it another way, which is. All the drop down fields contain a null value as selected and only those insert into the database which you order. I hope you know what i mean, but it is good. I will do the ammending myself.

I will post another question for the update bit for this.

Kind Regards,
Fezi

Carzkiss - Provided excellent service, helped very well and the solution was easy to understand. and... It was a FULL solution. Excellent Job