By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,639 Members | 1,988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,639 IT Pros & Developers. It's quick & easy.

Execute INSERT Statement In A Loop

P: n/a
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 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<rn**@rediffmail.comwrote in message
news:11**********************@n59g2000hsh.googlegr oups.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(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
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
May 13 '07 #2

P: n/a
On May 13, 12:27 pm, "John Blessing"
<blessij@**REMOVE**THIS**gmail.comwrote:
<r...@rediffmail.comwrote in message

news:11**********************@n59g2000hsh.googlegr oups.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(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 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.