Execute INSERT Statement In A Loop

Discussion in 'ASP General' started by rn5a@rediffmail.com, May 13, 2007.

  1. Guest

    In a ASP applicatiuon, the FOrm has a textbox & a select list where
    the admin can select multiple options. Basically the admin has to
    enter the name of a new coach in the textbox & select the soccer clubs
    which he will be coaching; thus he can select only one soccer club for
    a new coach or multiple soccer clubs.

    This is how I am trying it: When this Form will be submitted, the new
    coaches name will be inserted in a MS-Access DB table named Coaches
    which has 2 columns - CoachID Primary Key, AutoNumber) & Coach name.

    There'a are 2 more MS-Access DB tables - Clubs & CoachClub. The former
    table too has 2 columns - ClubID (Primary Key AutoNumber) & ClubName.
    The table named CoachClub has 3 columns - CCID (Primary Key
    AutoNumber, CoachID (number) & ClubID (number). Hence when the above
    ASP page is posted, not only should the DB table named Coach be
    populated but at the same time the CoachClub table must also be
    populated.

    When the ASP page is submitted & assuming that the admin has selected
    only 1 club for a new coach, the table CoachClub can be populated
    wtith the following SQL query:

    INSERT INTO CoachClub(CoachID,ClubID) VALUES (" &
    Request.Form("CoachID") & "," & Request.Form("ClubID") & ")"

    CoachID is the name of the textbox & ClibID is the name of the select
    list. The problem I am facing is when the admin selects multiple clubs
    for a new teacher. This is how I tried it (I am first retrieving the
    CoachID of the newly added coach in the DB table Coach so that the
    CoachID of the new coach can be populated in the CoachClub DB table.

    strSQL="SELECT MAX(CoachID) AS MaxCID FROM Coach"
    Set objRS1=Server.CreateObject("ADODB.RECORDSET")
    objRS1.Open strSQL,objConn
    iMaxCID=objRS1("MaxCID")

    'selecting multiple clubs means the next page will get the value as a
    comma-delimited string. Hence I am using the Split function

    strCID=Request.Form("CoachID")
    If(InStr(strCID,", ")>0) Then
    arrCID=Split(strCID,", ")
    strSQL="INSERT INTO CoachClub (CoachID,ClubID) VALUES ("
    For Each iEachCID In arrCID
    strSQL=strSQL & iMaxTID & "," & iEachCID & ")"
    Set objRS1=objConn.Execute(strSQL)
    Next
    End If

    But the above doesn't work. ASP generates the following error:

    Missing semicolon (;) at end of SQL statement.

    pointing to the Set line within the For...Next loop. If I put a colon
    in the SQL query within the For...Next loop immediately after the
    closing bracket, then ASP generates another error:

    Characters found after end of SQL statement

    What am I doing wrong here?
     
    , May 13, 2007
    #1
    1. Advertising

  2. Guest

    On May 13, 12:27 pm, "John Blessing"
    <blessij@**REMOVE**THIS**gmail.com> wrote:
    > <> wrote in message
    >
    > news:...
    >
    >
    >
    >
    >
    > > In a ASP applicatiuon, the FOrm has a textbox & a select list where
    > > the admin can select multiple options. Basically the admin has to
    > > enter the name of a new coach in the textbox & select the soccer clubs
    > > which he will be coaching; thus he can select only one soccer club for
    > > a new coach or multiple soccer clubs.

    >
    > > This is how I am trying it: When this Form will be submitted, the new
    > > coaches name will be inserted in a MS-Access DB table named Coaches
    > > which has 2 columns - CoachID Primary Key, AutoNumber) & Coach name.

    >
    > > There'a are 2 more MS-Access DB tables - Clubs & CoachClub. The former
    > > table too has 2 columns - ClubID (Primary Key AutoNumber) & ClubName.
    > > The table named CoachClub has 3 columns - CCID (Primary Key
    > > AutoNumber, CoachID (number) & ClubID (number). Hence when the above
    > > ASP page is posted, not only should the DB table named Coach be
    > > populated but at the same time the CoachClub table must also be
    > > populated.

    >
    > > When the ASP page is submitted & assuming that the admin has selected
    > > only 1 club for a new coach, the table CoachClub can be populated
    > > wtith the following SQL query:

    >
    > > INSERT INTO CoachClub(CoachID,ClubID) VALUES (" &
    > > Request.Form("CoachID") & "," & Request.Form("ClubID") & ")"

    >
    > > CoachID is the name of the textbox & ClibID is the name of the select
    > > list. The problem I am facing is when the admin selects multiple clubs
    > > for a new teacher. This is how I tried it (I am first retrieving the
    > > CoachID of the newly added coach in the DB table Coach so that the
    > > CoachID of the new coach can be populated in the CoachClub DB table.

    >
    > > strSQL="SELECT MAX(CoachID) AS MaxCID FROM Coach"
    > > Set objRS1=Server.CreateObject("ADODB.RECORDSET")
    > > objRS1.Open strSQL,objConn
    > > iMaxCID=objRS1("MaxCID")

    >
    > > 'selecting multiple clubs means the next page will get the value as a
    > > comma-delimited string. Hence I am using the Split function

    >
    > > strCID=Request.Form("CoachID")
    > > If(InStr(strCID,", ")>0) Then
    > > arrCID=Split(strCID,", ")
    > > strSQL="INSERT INTO CoachClub (CoachID,ClubID) VALUES ("
    > > For Each iEachCID In arrCID
    > > strSQL=strSQL & iMaxTID & "," & iEachCID & ")"
    > > Set objRS1=objConn.Execute(strSQL)
    > > Next
    > > End If

    >
    > > But the above doesn't work. ASP generates the following error:

    >
    > > Missing semicolon (;) at end of SQL statement.

    >
    > > pointing to the Set line within the For...Next loop. If I put a colon
    > > in the SQL query within the For...Next loop immediately after the
    > > closing bracket, then ASP generates another error:

    >
    > > Characters found after end of SQL statement

    >
    > > What am I doing wrong here?

    >
    > Set objRS1=objConn.Execute(strSQL
    >
    > Replace this with a Response.write strSql, then you will see the problem
    >
    > Also, my preference is to explicitly create a connection then do an
    > .execute on that, you are needlessly creating a recordset by your method.
    > --
    > John Blessing
    >
    > http://www.LbeHelpdesk.com- Help Desk software priced to suit all
    > businesseshttp://www.room-booking-software.com- Schedule rooms & equipment bookings
    > for your meeting/class over the web.http://www.lbetoolbox.com- Remove Duplicates from MS Outlook, find/replace,
    > send newsletters- Hide quoted text -
    >
    > - Show quoted text -


    Yes, John, you are correct.....I am unnecessarily creating a recordset
    object without which the INSERT query would do the same. Thanks for
    pointing it out. I have also resolved the issue by bringing the INSERT
    query which is outside the For....Next within the For...Next loop.

    Thanks once again,

    Regards,

    RON
     
    , May 13, 2007
    #2
    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. Matt
    Replies:
    3
    Views:
    436
    Dale King
    Mar 1, 2004
  2. Replies:
    21
    Views:
    1,068
    Giannis Papadopoulos
    Aug 2, 2005
  3. J. Muenchbourg
    Replies:
    3
    Views:
    250
    Aaron Bertrand - MVP
    Sep 30, 2003
  4. Savas Ates
    Replies:
    1
    Views:
    294
    Patrice
    Aug 17, 2004
  5. Isaac Won
    Replies:
    9
    Views:
    398
    Ulrich Eckhardt
    Mar 4, 2013
Loading...

Share This Page