Velocity Reviews - Computer Hardware Reviews

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

Reply
Thread Tools

insert multiple records

 
 
shank
Guest
Posts: n/a
 
      02-21-2004
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)
%>


 
Reply With Quote
 
 
 
 
Chris Barber
Guest
Posts: n/a
 
      02-21-2004
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" <(E-Mail Removed)> wrote in message
news:u3ZBs1J%(E-Mail Removed)...
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)
%>



 
Reply With Quote
 
 
 
 
Chris Barber
Guest
Posts: n/a
 
      02-21-2004
.... 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" <(E-Mail Removed)> wrote in message
news:%23SAlJLK%(E-Mail Removed)...
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" <(E-Mail Removed)> wrote in message
news:u3ZBs1J%(E-Mail Removed)...
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)
%>




 
Reply With Quote
 
shank
Guest
Posts: n/a
 
      02-21-2004
>>Run multiple INSERT INTO statements.<<
do you have an example of this?
thanks


"Chris Barber" <(E-Mail Removed)> wrote in message
news:%23SAlJLK%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:u3ZBs1J%(E-Mail Removed)...
> 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)
> %>
>
>
>



 
Reply With Quote
 
Chris Barber
Guest
Posts: n/a
 
      02-21-2004
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" <(E-Mail Removed)> wrote in message
news:evdrZnK%(E-Mail Removed)...
>>Run multiple INSERT INTO statements.<<

do you have an example of this?
thanks


"Chris Barber" <(E-Mail Removed)> wrote in message
news:%23SAlJLK%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:u3ZBs1J%(E-Mail Removed)...
> 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)
> %>
>
>
>




 
Reply With Quote
 
shank
Guest
Posts: n/a
 
      02-21-2004
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" <(E-Mail Removed)> wrote in message
news:em%23893K%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:evdrZnK%(E-Mail Removed)...
> >>Run multiple INSERT INTO statements.<<

> do you have an example of this?
> thanks
>
>
> "Chris Barber" <(E-Mail Removed)> wrote in message
> news:%23SAlJLK%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:u3ZBs1J%(E-Mail Removed)...
> > 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)
> > %>
> >
> >
> >

>
>
>



 
Reply With Quote
 
Chris Barber
Guest
Posts: n/a
 
      02-21-2004
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" <(E-Mail Removed)> wrote in message
news:ZaOZb.80465$(E-Mail Removed) 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" <(E-Mail Removed)> wrote in message
news:em%23893K%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:evdrZnK%(E-Mail Removed)...
> >>Run multiple INSERT INTO statements.<<

> do you have an example of this?
> thanks
>
>
> "Chris Barber" <(E-Mail Removed)> wrote in message
> news:%23SAlJLK%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:u3ZBs1J%(E-Mail Removed)...
> > 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)
> > %>
> >
> >
> >

>
>
>




 
Reply With Quote
 
shank
Guest
Posts: n/a
 
      02-21-2004
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" <(E-Mail Removed)> wrote in message
news:OSm6cZL%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:ZaOZb.80465$(E-Mail Removed) 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" <(E-Mail Removed)> wrote in message
> news:em%23893K%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:evdrZnK%(E-Mail Removed)...
> > >>Run multiple INSERT INTO statements.<<

> > do you have an example of this?
> > thanks
> >
> >
> > "Chris Barber" <(E-Mail Removed)> wrote in message
> > news:%23SAlJLK%(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:u3ZBs1J%(E-Mail Removed)...
> > > 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)
> > > %>
> > >
> > >
> > >

> >
> >
> >

>
>
>



 
Reply With Quote
 
Chris Barber
Guest
Posts: n/a
 
      02-21-2004
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" <(E-Mail Removed)> wrote in message
news:07PZb.80575$(E-Mail Removed) 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" <(E-Mail Removed)> wrote in message
news:OSm6cZL%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:ZaOZb.80465$(E-Mail Removed) 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" <(E-Mail Removed)> wrote in message
> news:em%23893K%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:evdrZnK%(E-Mail Removed)...
> > >>Run multiple INSERT INTO statements.<<

> > do you have an example of this?
> > thanks
> >
> >
> > "Chris Barber" <(E-Mail Removed)> wrote in message
> > news:%23SAlJLK%(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:u3ZBs1J%(E-Mail Removed)...
> > > 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)
> > > %>
> > >
> > >
> > >

> >
> >
> >

>
>
>




 
Reply With Quote
 
Bob Lehmann
Guest
Posts: n/a
 
      02-21-2004
Remove your "i".

Bob Lehmann

"shank" <(E-Mail Removed)> wrote in message
news:07PZb.80575$(E-Mail Removed) 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" <(E-Mail Removed)> wrote in message
> news:OSm6cZL%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:ZaOZb.80465$(E-Mail Removed) 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" <(E-Mail Removed)> wrote in message
> > news:em%23893K%(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:evdrZnK%(E-Mail Removed)...
> > > >>Run multiple INSERT INTO statements.<<
> > > do you have an example of this?
> > > thanks
> > >
> > >
> > > "Chris Barber" <(E-Mail Removed)> wrote in message
> > > news:%23SAlJLK%(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > news:u3ZBs1J%(E-Mail Removed)...
> > > > 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)
> > > > %>
> > > >
> > > >
> > > >
> > >
> > >
> > >

> >
> >
> >

>
>



 
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
Insert Multiple Records at a time based on checkbox selection Vijay Pathak via .NET 247 ASP .Net 0 09-22-2004 06:53 AM
Submit Form Elements to Insert Multiple Records into a Database Sans Spam ASP General 8 05-24-2004 09:16 PM
Re: insert multiple records using asp.net Michael Ramey ASP .Net 0 01-30-2004 10:25 PM



Advertisments