Execute INSERT Statement In A Loop

[rn5a]

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(Coach ID,ClubID) VALUES (" &
Request.Form("C oachID") & "," & Request.Form("C lubID") & ")"

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.C reateObject("AD ODB.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(st rCID,", ")
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?

[John Blessing]

<rn**@rediffmai l.comwrote in message
news:11******** **************@ n59g2000hsh.goo glegroups.com.. .

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(Coach ID,ClubID) VALUES (" &
Request.Form("C oachID") & "," & Request.Form("C lubID") & ")"

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.C reateObject("AD ODB.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(st rCID,", ")
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
businesses
http://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

[rn5a]

On May 13, 12:27 pm, "John Blessing"
<blessij@**REMO VE**THIS**gmail .comwrote:

<r...@rediffmai l.comwrote in message

news:11******** **************@ n59g2000hsh.goo glegroups.com.. .

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(Coach ID,ClubID) VALUES (" &
Request.Form("C oachID") & "," & Request.Form("C lubID") & ")"

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.C reateObject("AD ODB.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(st rCID,", ")
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