Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Execute INSERT Statement In A Loop

Reply
Thread Tools

Execute INSERT Statement In A Loop

 
 
rn5a@rediffmail.com
Guest
Posts: n/a
 
      05-13-2007
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?

 
Reply With Quote
 
 
 
 
rn5a@rediffmail.com
Guest
Posts: n/a
 
      05-13-2007
On May 13, 12:27 pm, "John Blessing"
<blessij@**REMOVE**THIS**gmail.com> wrote:
> <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed) 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

 
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
Triple nested loop python (While loop insde of for loop inside ofwhile loop) Isaac Won Python 9 03-04-2013 10:08 AM
Which of switch statement and if-else statement takes less time to execute? swaroophr@gmail.com C Programming 21 08-02-2005 09:24 AM
Can i execute aspx file in my asp pag with server.execute method(sorry) Savas Ates ASP General 1 08-17-2004 04:52 PM
execute the statements inside if statement without testing the if expression? Matt Java 3 03-01-2004 08:57 PM
INSERT statement contains fewer items than the insert list J. Muenchbourg ASP General 3 09-30-2003 09:24 PM



Advertisments