|
P: n/a
|
I have a recordset that contains multiple records of product a user is
purchasing. For clarity, I converted the recordset fields to variables. I
need to take that entire recordset and insert it into another table on a
remote server. The below code only inserts 1 record. How do I change the
code to get all records inserted?
thanks!
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
DataConn2.Execute(SQL)
%>
| |
Share this Question
|
P: n/a
|
Run multiple INSERT INTO statements.
or .... use a disconnected recordset, manipulate it by adding records and
setting the field values and then pass it back using BatchUpdate to get the
updates back to the database [which is how I would do it].
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:u3****************@TK2MSFTNGP10.phx.gbl...
I have a recordset that contains multiple records of product a user is
purchasing. For clarity, I converted the recordset fields to variables. I
need to take that entire recordset and insert it into another table on a
remote server. The below code only inserts 1 record. How do I change the
code to get all records inserted?
thanks!
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
DataConn2.Execute(SQL)
%>
| |
|
P: n/a
|
.... then again, I already have a custom DLL that I wrote some time ago that
gets me disconnected recordset for Access and SQL Server allowing me to
concentrate on the program as opposed to the ADO.
If you want it may be useful - not sure) then your welcome to it (VB
project and optional MSI installer): http://ftp.belper.blue-canoe.net/RSAccess/
I have updated it for my own development to handle DBFs and also to allow
specification of the cursor location so that you can elect to get an active
server-side cursor recordset. If you need either of these then email me and
I'll update the ZIP and MSI.
Hope this helps.
Chris.
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
Run multiple INSERT INTO statements.
or .... use a disconnected recordset, manipulate it by adding records and
setting the field values and then pass it back using BatchUpdate to get the
updates back to the database [which is how I would do it].
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:u3****************@TK2MSFTNGP10.phx.gbl...
I have a recordset that contains multiple records of product a user is
purchasing. For clarity, I converted the recordset fields to variables. I
need to take that entire recordset and insert it into another table on a
remote server. The below code only inserts 1 record. How do I change the
code to get all records inserted?
thanks!
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
DataConn2.Execute(SQL)
%> | |
|
P: n/a
|
>>Run multiple INSERT INTO statements.<<
do you have an example of this?
thanks
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... Run multiple INSERT INTO statements.
or .... use a disconnected recordset, manipulate it by adding records and
setting the field values and then pass it back using BatchUpdate to get
the updates back to the database [which is how I would do it].
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:u3****************@TK2MSFTNGP10.phx.gbl...
I have a recordset that contains multiple records of product a user is
purchasing. For clarity, I converted the recordset fields to variables. I
need to take that entire recordset and insert it into another table on a
remote server. The below code only inserts 1 record. How do I change the
code to get all records inserted?
thanks!
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
DataConn2.Execute(SQL)
%>
| |
|
P: n/a
|
You already have an example - just loop through the array or recordset of
values that you have to insert and run your current code for each one.
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:ev****************@TK2MSFTNGP10.phx.gbl... Run multiple INSERT INTO statements.<<
do you have an example of this?
thanks
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... Run multiple INSERT INTO statements.
or .... use a disconnected recordset, manipulate it by adding records and
setting the field values and then pass it back using BatchUpdate to get
the updates back to the database [which is how I would do it].
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:u3****************@TK2MSFTNGP10.phx.gbl...
I have a recordset that contains multiple records of product a user is
purchasing. For clarity, I converted the recordset fields to variables. I
need to take that entire recordset and insert it into another table on a
remote server. The below code only inserts 1 record. How do I change the
code to get all records inserted?
thanks!
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
DataConn2.Execute(SQL)
%>
| |
|
P: n/a
|
I'm not that familiar with looping through recordsets. On the following
code, I get this error...
Expected end of statement
Dim i As Integer
------^
What do I need to change?
thanks
<%
Dim DataConn2
Dim i As Integer
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To wsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:em******************@tk2msftngp13.phx.gbl... You already have an example - just loop through the array or recordset of
values that you have to insert and run your current code for each one.
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:ev****************@TK2MSFTNGP10.phx.gbl...Run multiple INSERT INTO statements.<< do you have an example of this?
thanks
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... Run multiple INSERT INTO statements.
or .... use a disconnected recordset, manipulate it by adding records
and setting the field values and then pass it back using BatchUpdate to get
the updates back to the database [which is how I would do it].
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:u3****************@TK2MSFTNGP10.phx.gbl...
I have a recordset that contains multiple records of product a user is
purchasing. For clarity, I converted the recordset fields to variables.
I need to take that entire recordset and insert it into another table on a
remote server. The below code only inserts 1 record. How do I change the
code to get all records inserted?
thanks!
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
DataConn2.Execute(SQL)
%>
| |
|
P: n/a
|
Hmm.
OK.
1. You can't Dim *as* in ASP - everything is a variant.
So you now have:
<%
Dim DataConn2
Dim i
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To wsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
'Where are the values for iod_OrderID etc. coming from?
'You need to set them here to be values relevant to the current index of
wsOrderDetails.
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
What is wsOrderDetails (I know that its obviously a table in your database)?
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:Za********************@twister.tampabay.rr.co m...
I'm not that familiar with looping through recordsets. On the following
code, I get this error...
Expected end of statement
Dim i As Integer
------^
What do I need to change?
thanks
<%
Dim DataConn2
Dim i As Integer
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To wsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:em******************@tk2msftngp13.phx.gbl... You already have an example - just loop through the array or recordset of
values that you have to insert and run your current code for each one.
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:ev****************@TK2MSFTNGP10.phx.gbl...Run multiple INSERT INTO statements.<< do you have an example of this?
thanks
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... Run multiple INSERT INTO statements.
or .... use a disconnected recordset, manipulate it by adding records
and setting the field values and then pass it back using BatchUpdate to get
the updates back to the database [which is how I would do it].
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:u3****************@TK2MSFTNGP10.phx.gbl...
I have a recordset that contains multiple records of product a user is
purchasing. For clarity, I converted the recordset fields to variables.
I need to take that entire recordset and insert it into another table on a
remote server. The below code only inserts 1 record. How do I change the
code to get all records inserted?
thanks!
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
DataConn2.Execute(SQL)
%>
| |
|
P: n/a
|
1) I changed wsOrderDetails to rsOrderDetails which is the recordset I am
getting records from. That was a typo on my part.
2) I am now getting the following error...
Expected end of statement
Next i
-----^
How do I fix that?
thanks!
<%
Dim DataConn2
Dim i
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To rsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:OS***************@tk2msftngp13.phx.gbl... Hmm.
OK.
1. You can't Dim *as* in ASP - everything is a variant.
So you now have:
<%
Dim DataConn2
Dim i
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To wsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
'Where are the values for iod_OrderID etc. coming from?
'You need to set them here to be values relevant to the current index of
wsOrderDetails.
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
What is wsOrderDetails (I know that its obviously a table in your
database)?
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:Za********************@twister.tampabay.rr.co m...
I'm not that familiar with looping through recordsets. On the following
code, I get this error...
Expected end of statement
Dim i As Integer
------^
What do I need to change?
thanks
<%
Dim DataConn2
Dim i As Integer
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To wsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:em******************@tk2msftngp13.phx.gbl... You already have an example - just loop through the array or recordset
of values that you have to insert and run your current code for each one.
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:ev****************@TK2MSFTNGP10.phx.gbl...>Run multiple INSERT INTO statements.<< do you have an example of this?
thanks
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... Run multiple INSERT INTO statements.
or .... use a disconnected recordset, manipulate it by adding records and setting the field values and then pass it back using BatchUpdate to
get the updates back to the database [which is how I would do it].
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:u3****************@TK2MSFTNGP10.phx.gbl...
I have a recordset that contains multiple records of product a user is
purchasing. For clarity, I converted the recordset fields to
variables. I need to take that entire recordset and insert it into another table on
a remote server. The below code only inserts 1 record. How do I change
the code to get all records inserted?
thanks!
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '"
& iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
DataConn2.Execute(SQL)
%>
| |
|
P: n/a
|
Try this:
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
'Presumes that rsOrderDetails is a recordset with 1 or more records that
contain order data.
With rsOrderDetails
If Not(.EOF) Then .MoveFirst
'Loop through the recordset and apply the data using an INSERT INTO sql
statement.
Do Until .EOF
'Get the relevant recordset field values.
iod_OrderID = .Fields("FieldName).Value
iod_OrderNo = .Fields("FieldName).Value
iod_Description = .Fields("FieldName).Value
iod_Qty = .Fields("FieldName).Value
iod_PriceEach = .Fields("FieldName).Value
iod_PriceLine = .Fields("FieldName).Value
'Construct the SQL
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description,
Qty, PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "',
'" & iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", &
iod_Priceline & ")"
'Execute the SQL
DataConn2.Execute(SQL)
.MoveNext
Loop
End With
%>
Obviously you have to change the fieldnames to be whatever they should be.
NB: Learn the value of indentation and comments when doing ASP - your
scripts will be spaghetti and unfathomable without it. Also, never ever
release a script or ASP page that doesn't use Option Explicit at the top -
you *will* regret it if you don't.
Hope this helps.
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:07********************@twister.tampabay.rr.co m...
1) I changed wsOrderDetails to rsOrderDetails which is the recordset I am
getting records from. That was a typo on my part.
2) I am now getting the following error...
Expected end of statement
Next i
-----^
How do I fix that?
thanks!
<%
Dim DataConn2
Dim i
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To rsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:OS***************@tk2msftngp13.phx.gbl... Hmm.
OK.
1. You can't Dim *as* in ASP - everything is a variant.
So you now have:
<%
Dim DataConn2
Dim i
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To wsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
'Where are the values for iod_OrderID etc. coming from?
'You need to set them here to be values relevant to the current index of
wsOrderDetails.
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
What is wsOrderDetails (I know that its obviously a table in your
database)?
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:Za********************@twister.tampabay.rr.co m...
I'm not that familiar with looping through recordsets. On the following
code, I get this error...
Expected end of statement
Dim i As Integer
------^
What do I need to change?
thanks
<%
Dim DataConn2
Dim i As Integer
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To wsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:em******************@tk2msftngp13.phx.gbl... You already have an example - just loop through the array or recordset
of values that you have to insert and run your current code for each one.
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:ev****************@TK2MSFTNGP10.phx.gbl...>Run multiple INSERT INTO statements.<< do you have an example of this?
thanks
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... Run multiple INSERT INTO statements.
or .... use a disconnected recordset, manipulate it by adding records and setting the field values and then pass it back using BatchUpdate to
get the updates back to the database [which is how I would do it].
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:u3****************@TK2MSFTNGP10.phx.gbl...
I have a recordset that contains multiple records of product a user is
purchasing. For clarity, I converted the recordset fields to
variables. I need to take that entire recordset and insert it into another table on
a remote server. The below code only inserts 1 record. How do I change
the code to get all records inserted?
thanks!
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '"
& iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
DataConn2.Execute(SQL)
%>
| |
|
P: n/a
|
Remove your "i".
Bob Lehmann
"shank" <sh***@tampabay.rr.com> wrote in message
news:07********************@twister.tampabay.rr.co m... 1) I changed wsOrderDetails to rsOrderDetails which is the recordset I am
getting records from. That was a typo on my part.
2) I am now getting the following error...
Expected end of statement
Next i
-----^
How do I fix that?
thanks!
<%
Dim DataConn2
Dim i
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To rsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:OS***************@tk2msftngp13.phx.gbl... Hmm.
OK.
1. You can't Dim *as* in ASP - everything is a variant.
So you now have:
<%
Dim DataConn2
Dim i
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To wsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
'Where are the values for iod_OrderID etc. coming from?
'You need to set them here to be values relevant to the current index of
wsOrderDetails.
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
What is wsOrderDetails (I know that its obviously a table in your database)?
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:Za********************@twister.tampabay.rr.co m...
I'm not that familiar with looping through recordsets. On the following
code, I get this error...
Expected end of statement
Dim i As Integer
------^
What do I need to change?
thanks
<%
Dim DataConn2
Dim i As Integer
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To wsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:em******************@tk2msftngp13.phx.gbl... You already have an example - just loop through the array or recordset of values that you have to insert and run your current code for each
one.
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:ev****************@TK2MSFTNGP10.phx.gbl...
>>Run multiple INSERT INTO statements.<<
do you have an example of this?
thanks
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
> Run multiple INSERT INTO statements.
>
> or .... use a disconnected recordset, manipulate it by adding
records and > setting the field values and then pass it back using BatchUpdate to get the
> updates back to the database [which is how I would do it].
>
> Chris.
>
> "shank" <sh***@tampabay.rr.com> wrote in message
> news:u3****************@TK2MSFTNGP10.phx.gbl...
> I have a recordset that contains multiple records of product a user
is > purchasing. For clarity, I converted the recordset fields to
variables. I > need to take that entire recordset and insert it into another table
on a > remote server. The below code only inserts 1 record. How do I change the > code to get all records inserted?
> thanks!
>
> <%
> Dim DataConn2
> Set DataConn2 = Server.CreateObject("ADODB.Connection")
> DataConn2.Open MM_kasKSS_STRING
> SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description,
Qty, > PriceEach, Priceline) "
> SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "',
'" & > iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
> iod_Priceline & ")"
> DataConn2.Execute(SQL)
> %>
>
>
>
| |
|
P: n/a
|
An watch out for my glaring type (copied and pasted):
... = .Fields("FieldName).Value
should be:
... = .Fields("FieldName").Value
with FieldName replaced by the real field name from rsOrderDetails.
Chris.
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:%2*****************@TK2MSFTNGP09.phx.gbl...
Try this:
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
'Presumes that rsOrderDetails is a recordset with 1 or more records that
contain order data.
With rsOrderDetails
If Not(.EOF) Then .MoveFirst
'Loop through the recordset and apply the data using an INSERT INTO sql
statement.
Do Until .EOF
'Get the relevant recordset field values.
iod_OrderID = .Fields("FieldName).Value
iod_OrderNo = .Fields("FieldName).Value
iod_Description = .Fields("FieldName).Value
iod_Qty = .Fields("FieldName).Value
iod_PriceEach = .Fields("FieldName).Value
iod_PriceLine = .Fields("FieldName).Value
'Construct the SQL
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description,
Qty, PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "',
'" & iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", &
iod_Priceline & ")"
'Execute the SQL
DataConn2.Execute(SQL)
.MoveNext
Loop
End With
%>
Obviously you have to change the fieldnames to be whatever they should be.
NB: Learn the value of indentation and comments when doing ASP - your
scripts will be spaghetti and unfathomable without it. Also, never ever
release a script or ASP page that doesn't use Option Explicit at the top -
you *will* regret it if you don't.
Hope this helps.
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:07********************@twister.tampabay.rr.co m...
1) I changed wsOrderDetails to rsOrderDetails which is the recordset I am
getting records from. That was a typo on my part.
2) I am now getting the following error...
Expected end of statement
Next i
-----^
How do I fix that?
thanks!
<%
Dim DataConn2
Dim i
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To rsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:OS***************@tk2msftngp13.phx.gbl... Hmm.
OK.
1. You can't Dim *as* in ASP - everything is a variant.
So you now have:
<%
Dim DataConn2
Dim i
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To wsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
'Where are the values for iod_OrderID etc. coming from?
'You need to set them here to be values relevant to the current index of
wsOrderDetails.
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
What is wsOrderDetails (I know that its obviously a table in your
database)?
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:Za********************@twister.tampabay.rr.co m...
I'm not that familiar with looping through recordsets. On the following
code, I get this error...
Expected end of statement
Dim i As Integer
------^
What do I need to change?
thanks
<%
Dim DataConn2
Dim i As Integer
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
For i = 0 To wsOrderDetails.ListCount -1 Step 1
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '" &
iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
Next i
DataConn2.Execute(SQL)
%>
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:em******************@tk2msftngp13.phx.gbl... You already have an example - just loop through the array or recordset
of values that you have to insert and run your current code for each one.
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:ev****************@TK2MSFTNGP10.phx.gbl...>Run multiple INSERT INTO statements.<< do you have an example of this?
thanks
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... Run multiple INSERT INTO statements.
or .... use a disconnected recordset, manipulate it by adding records and setting the field values and then pass it back using BatchUpdate to
get the updates back to the database [which is how I would do it].
Chris.
"shank" <sh***@tampabay.rr.com> wrote in message
news:u3****************@TK2MSFTNGP10.phx.gbl...
I have a recordset that contains multiple records of product a user is
purchasing. For clarity, I converted the recordset fields to
variables. I need to take that entire recordset and insert it into another table on
a remote server. The below code only inserts 1 record. How do I change
the code to get all records inserted?
thanks!
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open MM_kasKSS_STRING
SQL = "INSERT INTO wsOrderDetails (OrderID, OrderNo, Description, Qty,
PriceEach, Priceline) "
SQL = SQL & "Values ('" & iod_OrderID & "', '" & iod_OrderNo & "', '"
& iod_Description & "', " & iod_Qty & ", " & iod_PriceEach & ", " &
iod_Priceline & ")"
DataConn2.Execute(SQL)
%>
| | This discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 2678
- replies: 10
- date asked: Jul 19 '05
|
