insert multiple records

Discussion in 'ASP General' started by shank, Feb 21, 2004.

  1. shank

    shank Guest

    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)
    %>
    shank, Feb 21, 2004
    #1
    1. Advertising

  2. shank

    Chris Barber Guest

    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" <> wrote in message
    news:u3ZBs1J%...
    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)
    %>
    Chris Barber, Feb 21, 2004
    #2
    1. Advertising

  3. shank

    Chris Barber Guest

    .... 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" <> wrote in message
    news:%23SAlJLK%...
    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" <> wrote in message
    news:u3ZBs1J%...
    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)
    %>
    Chris Barber, Feb 21, 2004
    #3
  4. shank

    shank Guest

    >>Run multiple INSERT INTO statements.<<
    do you have an example of this?
    thanks


    "Chris Barber" <> wrote in message
    news:%23SAlJLK%...
    > 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" <> wrote in message
    > news:u3ZBs1J%...
    > 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)
    > %>
    >
    >
    >
    shank, Feb 21, 2004
    #4
  5. shank

    Chris Barber Guest

    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" <> wrote in message
    news:evdrZnK%...
    >>Run multiple INSERT INTO statements.<<

    do you have an example of this?
    thanks


    "Chris Barber" <> wrote in message
    news:%23SAlJLK%...
    > 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" <> wrote in message
    > news:u3ZBs1J%...
    > 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)
    > %>
    >
    >
    >
    Chris Barber, Feb 21, 2004
    #5
  6. shank

    shank Guest

    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" <> wrote in message
    news:em%23893K%...
    > 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" <> wrote in message
    > news:evdrZnK%...
    > >>Run multiple INSERT INTO statements.<<

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

    >
    >
    >
    shank, Feb 21, 2004
    #6
  7. shank

    Chris Barber Guest

    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" <> wrote in message
    news:ZaOZb.80465$...
    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" <> wrote in message
    news:em%23893K%...
    > 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" <> wrote in message
    > news:evdrZnK%...
    > >>Run multiple INSERT INTO statements.<<

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

    >
    >
    >
    Chris Barber, Feb 21, 2004
    #7
  8. shank

    shank Guest

    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" <> wrote in message
    news:OSm6cZL%...
    > 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" <> wrote in message
    > news:ZaOZb.80465$...
    > 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" <> wrote in message
    > news:em%23893K%...
    > > 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" <> wrote in message
    > > news:evdrZnK%...
    > > >>Run multiple INSERT INTO statements.<<

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

    > >
    > >
    > >

    >
    >
    >
    shank, Feb 21, 2004
    #8
  9. shank

    Chris Barber Guest

    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" <> wrote in message
    news:07PZb.80575$...
    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" <> wrote in message
    news:OSm6cZL%...
    > 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" <> wrote in message
    > news:ZaOZb.80465$...
    > 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" <> wrote in message
    > news:em%23893K%...
    > > 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" <> wrote in message
    > > news:evdrZnK%...
    > > >>Run multiple INSERT INTO statements.<<

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

    > >
    > >
    > >

    >
    >
    >
    Chris Barber, Feb 21, 2004
    #9
  10. shank

    Bob Lehmann Guest

    Remove your "i".

    Bob Lehmann

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

    > >
    > >
    > >

    >
    >
    Bob Lehmann, Feb 21, 2004
    #10
  11. shank

    Chris Barber Guest

    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" <> wrote in message
    news:%23O4KfqM%...
    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" <> wrote in message
    news:07PZb.80575$...
    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" <> wrote in message
    news:OSm6cZL%...
    > 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" <> wrote in message
    > news:ZaOZb.80465$...
    > 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" <> wrote in message
    > news:em%23893K%...
    > > 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" <> wrote in message
    > > news:evdrZnK%...
    > > >>Run multiple INSERT INTO statements.<<

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

    > >
    > >
    > >

    >
    >
    >
    Chris Barber, Feb 22, 2004
    #11
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Michael Ramey

    Re: insert multiple records using asp.net

    Michael Ramey, Jan 30, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    2,706
    Michael Ramey
    Jan 30, 2004
  2. Vijay Pathak via .NET 247

    Insert Multiple Records at a time based on checkbox selection

    Vijay Pathak via .NET 247, Sep 22, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    569
    Vijay Pathak via .NET 247
    Sep 22, 2004
  3. Ian Macey
    Replies:
    1
    Views:
    893
    =?Utf-8?B?U2NvdHQgU2ltb25z?=
    Jan 25, 2005
  4. Replies:
    1
    Views:
    1,777
    Albert Hopkins
    Dec 6, 2008
  5. Sans Spam
    Replies:
    8
    Views:
    240
    Sans Spam
    May 24, 2004
Loading...

Share This Page