insert multiple records

S

shank

I'm trying to use online samples for submitting multiple records from ASP
into a stored procedure. Failing! Through the below form, a user could be
submitting many records at a time. I'm not getting any records inserted. For
troubleshooting, I cut the form down to 1 textbox and when submitted it
populated 5 rows of the same data. So I know I'm connected, but not getting
the LOOP and NEXT correct? How do I set this up so many records can be
inserted? What am I not seeing?
thanks!

====== <FORM> ======
<form action="test.asp" method="post" name="frmSortKey">
<table>
<tr>
<td class="colhead">New#</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsSongs.EOF))
%>
<tr>
<td class="resultsright">
<input name="SK" type="text"
value="<%=(rsSongs.Fields.Item("SortKey").Value)%>" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden"
value="<%=(rsSongs.Fields.Item("ProjectID").Value)%>">
<input name="IDD" type="hidden"
value="<%=(rsSongs.Fields.Item("ID").Value)%>">
</td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsSongs.MoveNext()
Wend
%>
</table>
<input name="SubmitNow" type="hidden" value="1">
<input name="Submit" type="submit" value="Change track numbers">
</form>

====== TEST.ASP code ======
<%
Dim rsTracks__PID
rsTracks__PID = "0"
if(Request("PID") <> "") then rsTracks__PID = Request("PID")

Dim rsTracks__SK
rsTracks__SK = "0"
if(Request("SK") <> "") then rsTracks__SK = Request("SK")

Dim rsTracks__IDD
rsTracks__IDD = "0"
if(Request("IDD") <> "") then rsTracks__IDD = Request("IDD")

%>
<%
FOR i = 1 to 5
set rsTracks = Server.CreateObject("ADODB.Command")
rsTracks.ActiveConnection = MM_STRING
rsTracks.CommandText = "admin.stp_DOD_TrackNumbers"
rsTracks.CommandType = 4
rsTracks.CommandTimeout = 0
rsTracks.Prepared = true
rsTracks.Parameters.Append rsTracks.CreateParameter("@RETURN_VALUE", 3, 4)
rsTracks.Parameters.Append rsTracks.CreateParameter("@PID", 3,
1,9,rsTracks__PID)
rsTracks.Parameters.Append rsTracks.CreateParameter("@SK", 3,
1,9,rsTracks__SK)
rsTracks.Parameters.Append rsTracks.CreateParameter("@IDD", 3,
1,9,rsTracks__IDD)
rsTracks.Execute()

NEXT
MM_STRING.close
set MM_STRING=nothing
%>

====== STORED PROCEDURE ======
CREATE PROCEDURE stp_DOD_TrackNumbers
@PID int, @SK int, @IDD int
AS

INSERT INTO tmpDODSongs (ProjectID, SortKey, OldIDD)
VALUES (@PID, @SK, @IDD)
GO

====== TEST.ASP output ======
This...
<%=Request("PID")%><br>
<%=Request("SK")%><br>
<%=Request("IDD")%><br><br>
Produces this...
24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
121, 122, 123, 124, 125, 126, 127, 130, 131, 132, 133, 134, 135, 136, 137,
138, 139
 
A

AlanM

Couldent spot anything

By try using

**
do untill rsSongs.EOF

rsSongs.MoveNext
loop
**

Why?

I know that works with a recordset (But so should while wend)
 
D

Danny@Kendal

shank said:
I'm trying to use online samples for submitting multiple records from ASP
into a stored procedure. Failing! Through the below form, a user could be
submitting many records at a time. I'm not getting any records inserted.
For troubleshooting, I cut the form down to 1 textbox and when submitted
it populated 5 rows of the same data. So I know I'm connected, but not
getting the LOOP and NEXT correct? How do I set this up so many records
can be inserted? What am I not seeing?

Dunno. I'm no expert but the for/next loop seems to enclose lines which I'd
have thought should be outside the loop. Does the following suggestiong
help?
<%
FOR i = 1 to 5

I'd move the above line...
set rsTracks = Server.CreateObject("ADODB.Command")
rsTracks.ActiveConnection = MM_STRING
rsTracks.CommandText = "admin.stp_DOD_TrackNumbers"
rsTracks.CommandType = 4
rsTracks.CommandTimeout = 0
rsTracks.Prepared = true

....to here.
 
P

Patrice

As you have multiple fields wiht the same name you read back a collection.
Ie. Resquest.Form("MyField") returns the value for all 5 fields. I would use
Request.Form("MyField")(i) (if I remember) to extract the value for a
particular line.

--
 
S

shank

Not sure what you mean. I edited this line and it did nothing
if(Request("PID") <> "") then rsTracks__PID = Request("PID")(i)
What needs editing in your approach?
thanks!
 
P

Patrice

For now it looks like to me that you have multiple fields with the same
name. Request.Form("PID") will return the values for *ALL* PID fields.
Request.Form("PID")(i) allows to return the value for the ith field.

Try for example :
<html>
<body>
<form method="post">
<%
If Not IsEmpty(Request.Form("MyField")) Then'
Response.Write Request.Form("MyField") & "<br>"
Response.Write Request.Form("MyField")(1) & "<br>"
Response.Write Request.Form("MyField")(2) & "<br>"
End If
For i=1 to 2
Response.Write "<input type=text name=MyField>"
Next
%>
<input type=submit>
</form>
</body>
</form>

If you enter a and b Request.Form("MyField") shows a,b while
Request.Form("MyField")(1) shows a and Request.Form("MyField")(2) shows b...

Here you take all those values once and you pass them all as parameters. You
should take the appropriate values for fields and execute the command. For
the code above it would be:

For i=1 to 2
cmd.Parameters.Value=Request.form("MyField")(i)
cmd.Execute
Next

So that you execute the command twice with the values for the ith input
field named MyField...
 
S

shank

I really appreciate your efforts, but I'm still getting the same basic
results when I try to implement your code. Backing up to what I had last
night... I'm starting from scratch and taking each step, one at a time. In
the end, I want to insert multiple rows into a table. But first I'm sure I
have to groom the data properly before submitting to the table. Below is my
starting form with no data, the form with data, landing page code, results
and expected results. Hopefully this enough to explan what I'm trying to do.

I don't know if my landing page code is at fault or the submitting form with
data. How does a form submit? Is it always one big stream of data? Can a
form be forced to loop through the rows? Code samples would be appreciated!

thanks!

= = = = = = = = STATIC FORM NO DATA = = = = = = = =
<form action="test.asp" method="post" name="frmSortKey">
<table class="tbldisplay">
<%
While ((Repeat1__numRows <> 0) AND (NOT rsSongs.EOF))
%>
<tr>
<td class="resultsright">
<input name="SK" type="text"
value="<%=(rsSongs.Fields.Item("SortKey").Value)%>" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden"
value="<%=(rsSongs.Fields.Item("ProjectID").Value)%>">
<input name="IDD" type="hidden"
value="<%=(rsSongs.Fields.Item("ID").Value)%>">
</td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsSongs.MoveNext()
Wend
%>
</table>
<input name="SubmitNow" type="hidden" value="1">
<input name="Submit" type="submit" value="Change track numbers">
</form>

= = = = = = = = FORM WITH DATA = = = = = = = =
<form action="test.asp" method="post" name="frmSortKey">
<table class="tbldisplay">
<tr>
<td class="resultsright">
<input name="SK" type="text" value="1" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="121">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="2" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="122">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="3" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="123">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="4" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="124">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="5" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="125">
</td>
</tr>

<tr>
<td class="resultsright">
<input name="SK" type="text" value="6" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="126">
</td>
</tr>

<tr>
<td class="resultsright">
<input name="SK" type="text" value="7" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="127">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="8" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="130">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="9" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="131">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="10" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="132">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="11" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="133">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="12" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="134">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="13" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="135">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="14" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="136">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="15" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="137">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="16" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="138">
</td>
</tr>
<tr>
<td class="resultsright">
<input name="SK" type="text" value="17" size="3" maxlength="2"
style="text-align:right">
<input name="PID" type="hidden" value="24">
<input name="IDD" type="hidden" value="139">
</td>
</tr>
</table>
<input name="SubmitNow" type="hidden" value="1">
<input name="Submit" type="submit" value="Change">
</form>

= = = = = = = = LANDING PAGE CODE TEST.ASP = = = = = = = =
<% For i = 1 to 5 %>
<%=Request("PID")%>&nbsp;<%=Request("SK")%>&nbsp;<%=Request("IDD")%><br>
<% Next %>

= = = = = = = = ACTUAL RESULTS = = = = = = = =
24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24 1, 2, 3,
4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 121, 122, 123, 124, 125,
126, 127, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139
24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24 1, 2, 3,
4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 121, 122, 123, 124, 125,
126, 127, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139
24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24 1, 2, 3,
4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 121, 122, 123, 124, 125,
126, 127, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139
24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24 1, 2, 3,
4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 121, 122, 123, 124, 125,
126, 127, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139
24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24 1, 2, 3,
4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 121, 122, 123, 124, 125,
126, 127, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139

= = = = = = = = EXPECTED RESULTS = = = = = = = =
(In the end I will be inserting these records into a database. For the time
being, I'd at least like to get the data groomed correctly)
24 1 121
24 2 122
24 3 123
24 4 124
24 5 125
24 6 126
24 7 127
24 8 130
24 9 131
24 10 132
24 11 133
24 12 134
24 13 135
24 14 136
24 15 137
24 16 138
24 17 139
 
P

Patrice

I changed just Request to Request.Form and added (i) after to extract the
proper value.

= = = = = = = = LANDING PAGE CODE TEST.ASP = = = = = = = =
<% For i = 1 to 5 %>
<%=Request.Form("PID")(i)%>&nbsp;<%=Request.Form("SK")(i)%>&nbsp;<%=Request.
Form("IDD")(i)%><br>
<% Next %>

You should see now 5 lines with the values for the 5 first records...
 
P

Patrice

But this is outside of your loop and in your loop you' ll use always the
same value. It should be inside your loop so that you can use the
approprivate value for each iteration.

Anyway doesn't really matter. Your other post seems to show you are on the
trigt track...

--
 
S

shank

I'm really getting closer now! By chance, there's 17 records. In reality,
there could be any number of records.
<% For i = 1 to 5 %> <-- as you mentioned this gave me the first 5 records
<% For i = 1 to 100 %> <-- this gave me an out of range error
<% For i = 1 to IsEmpty(Request.Form("PID")) %> <-- this gave me no results

What do I need to get this to result in whatever records are being
submitted?

<% For i = 1 to IsEmpty(Request.Form("PID")) %>
<%=Request.Form("PID")(i)%>&nbsp;<%=Request.Form("SK")(i)%>&nbsp;<%=Request.Form("IDD")(i)%><br>
<% Next %>

Thanks!
 
P

Patrice

For example Request.Form("MyField").Count.

The key point is that if you have several names, Request.Form("MyField") is
actually a connection...
 
S

shank

Now we're cookin' with real butter!
Thanks! :))

Patrice said:
For example Request.Form("MyField").Count.

The key point is that if you have several names, Request.Form("MyField")
is
actually a connection...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top