473,388 Members | 1,942 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

Execute INSERT Statement In A Loop

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
2 9400
<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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Jill Graham | last post by:
Hi, I'm using an access database and found following problem : I have a table X with 2 records : record 1 & record 2. I execute following procedure with 2 steps : 1. The procedure deletes...
8
by: Sans Spam | last post by:
Greetings! I have a table that contains all of the function permissions within a given application. These functions are different sections of a site and each has its own permissions (READ, WRITE,...
4
by: finlma | last post by:
I'm trying to run an EXECUTE IMMEDIATE within a PL/SQL if loop but it doesn't work for me. I'm trying to create a column conditionally but it doesn't work. It fails because there are apostrophes...
2
by: Nabil | last post by:
I am new to DB2 and here is my situation. I have 2 temp tables where I am trying to insert data (from 2 select statements) DECLARE v_t1 VARCHAR(50); DECLARE v_t1 VARCHAR(50); DECLARE stmt1...
5
by: Andrew | last post by:
Hi: I run into the following problem where I would use CreateQuerydef to create a new Table and then use "Execute" to execute the statement. However, I keep getting an error message saying that...
3
by: ben.werdmuller | last post by:
Hi, Is there an easy way in ASP/VBscript to grab an autonumber (primary key) field just after an SQL insert? This is probably easy, but I'm stuck .. Cheers.
5
by: alingsjtu | last post by:
Hello, every body. When execute dynamic generated multiple OPENQUERY statements (which linkes to DB2) in SQLServer, I always got SQL1040N The maximum number of applications is already connected...
6
by: rn5a | last post by:
In a shopping cart app, when a user finalizes his order, records from a temporary table named 'TempCart' get inserted into another table name 'Orders' after which the records from 'TempCart' are...
4
by: gmazza | last post by:
Hi there, I am trying to insert 4 fields into a table grabbing the 4 fields from a form, here is my code: Private Sub LastPaymentAmount_AfterUpdate() Dim CurrentProject As DAO.Database Set...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.