|
 |
asp_databases thread: Multiple Insert Working but where to put that Semicolon???
Message #1 by "Rick" <rick@f...> on Tue, 15 Aug 2000 15:16:8
|
|
Here's the code (Thanks to Imar)
<%
Response.Buffer = TRUE
Dim aStudentID ' array holding the StudentID's
Dim aNCOID ' array to hold NCOID
Dim sMasteryLevel ' string to hold the MasteryLevel.
' Localize submitted data first
aStudentID = split(Request.Form("StudentID"), ",") ' Split the StudentID
from Request.Form
aNCOID = split(Request.Form("NCOID"), ",") ' Split the NCOID from
Request.Form
sMasteryLevel = Request.Form("MasteryLevel") ' Leave MasteryLevel Alone
'Determine number of items in form collection
Dim iStudentLoop ' as int.Is used to loop through all items in the
StudentID array
Dim iNCOIDLoop ' as int. Is used to loop through all items in the NCOID
array
Dim iNumOfStudents ' as int. This will hold the number of selected
StudentIDs
Dim iNumOfNCOIDs ' as int. This will hold the number of selected NCOIDs
Dim sSQLInsert ' as String
iNumOfStudents = UBound(aStudentID) ' upperbound of array = (number of
items -1) because arrays are zero-based
iNumOfNCOIDs = UBound(aNCOID)
'INSERT CLIENT INFORMATION
for iStudentLoop = 0 to iNumOfStudents
for iNCOIDLoop = 0 to iNumOfNCOIDs
sSQLInsert = sSQLInsert & "INSERT INTO Mastery (StudentID, NCOID,
MasteryLevel) VALUES ("
sSQLInsert = sSQLInsert & trim(aStudentID(iStudentLoop)) & ", " ' add the
StudentID. Remains the same in innerloop
sSQLInsert = sSQLInsert & trim(aNCOID(iNCOIDLoop)) & ", '" ' add the
NCOID, loop as long there are items in array
sSQLInsert = sSQLInsert & sMasteryLevel & "')" & vbCRLF ' add the
MasteryLevel and a line feed
next
next
Dim cmdInsert
sConn="demo"
Set cmdInsert= Server.CreateObject("ADODB.Command")
cmdInsert.ActiveConnection = sConn
cmdInsert.CommandText = sSQLInsert
cmdInsert.Execute,, adCmdText OR adExecuteNorecords
Response.Clear
URL="MasteryComplete1.asp"
Response.redirect (URL)
%>
Here's the Message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Missing semicolon (;) at end of
SQL statement.
/demo/MasteryEnter1.asp, line 41
How do I squeeze that semi-colon in at the end???
Thanks,
Rick
Message #2 by "Ken Schaefer" <ken@a...> on Wed, 16 Aug 2000 11:46:36 +1000
|
|
Rick,
You need a semicolon in between each INSERT statement.
Can you response.write your SQL string and post it to the list. (I'm asking
this because the usually the only time I see this error is when there is a
malformed SQL statement eg:
"INSERT INTO Table1(field1) VALUES('value1') WHERE field1 = 6"
which can't be done, when you think about it. Instead Access asks for a
semicoloon before the WHERE clause)
Cheers
Ken
----- Original Message -----
From: "Rick"
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, August 15, 2000 3:00 PM
Subject: [asp_databases] Multiple Insert Working but where to put that
Semicolon???
> Here's the code (Thanks to Imar)
>
> <%
> Response.Buffer = TRUE
>
> Dim aStudentID ' array holding the StudentID's
> Dim aNCOID ' array to hold NCOID
> Dim sMasteryLevel ' string to hold the MasteryLevel.
>
> ' Localize submitted data first
> aStudentID = split(Request.Form("StudentID"), ",") ' Split the StudentID
> from Request.Form
> aNCOID = split(Request.Form("NCOID"), ",") ' Split the NCOID from
> Request.Form
> sMasteryLevel = Request.Form("MasteryLevel") ' Leave MasteryLevel Alone
>
> 'Determine number of items in form collection
> Dim iStudentLoop ' as int.Is used to loop through all items in the
> StudentID array
> Dim iNCOIDLoop ' as int. Is used to loop through all items in the NCOID
> array
> Dim iNumOfStudents ' as int. This will hold the number of selected
> StudentIDs
> Dim iNumOfNCOIDs ' as int. This will hold the number of selected NCOIDs
> Dim sSQLInsert ' as String
> iNumOfStudents = UBound(aStudentID) ' upperbound of array = (number of
> items -1) because arrays are zero-based
> iNumOfNCOIDs = UBound(aNCOID)
>
> 'INSERT CLIENT INFORMATION
>
> for iStudentLoop = 0 to iNumOfStudents
> for iNCOIDLoop = 0 to iNumOfNCOIDs
> sSQLInsert = sSQLInsert & "INSERT INTO Mastery (StudentID, NCOID,
> MasteryLevel) VALUES ("
> sSQLInsert = sSQLInsert & trim(aStudentID(iStudentLoop)) & ", " ' add the
> StudentID. Remains the same in innerloop
> sSQLInsert = sSQLInsert & trim(aNCOID(iNCOIDLoop)) & ", '" ' add the
> NCOID, loop as long there are items in array
> sSQLInsert = sSQLInsert & sMasteryLevel & "')" & vbCRLF ' add the
> MasteryLevel and a line feed
> next
> next
>
> Dim cmdInsert
>
> sConn="demo"
> Set cmdInsert= Server.CreateObject("ADODB.Command")
> cmdInsert.ActiveConnection = sConn
> cmdInsert.CommandText = sSQLInsert
> cmdInsert.Execute,, adCmdText OR adExecuteNorecords
>
> Response.Clear
> URL="MasteryComplete1.asp"
> Response.redirect (URL)
>
> %>
>
> Here's the Message:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Missing semicolon (;) at end of
> SQL statement.
>
> /demo/MasteryEnter1.asp, line 41
>
> How do I squeeze that semi-colon in at the end???
> Thanks,
> Rick
>
>
Message #3 by "Mislan, Rick" <rick@f...> on Wed, 16 Aug 2000 08:10:25 -0400
|
|
Sure, I also rectified my SQL statement to add a semi-colon...
___________________________________________________________________________
for iStudentLoop = 0 to iNumOfStudents
for iNCOIDLoop = 0 to iNumOfNCOIDs
sSQLInsert = sSQLInsert & "INSERT INTO Mastery (StudentID,
NCOID, MasteryLevel) VALUES ("
sSQLInsert = sSQLInsert & trim(aStudentID(iStudentLoop)) &
", " ' add the StudentID. Remains the same in innerloop
sSQLInsert = sSQLInsert & trim(aNCOID(iNCOIDLoop)) & ", '"
' add the NCOID, loop as long there are items in array
sSQLInsert = sSQLInsert & sMasteryLevel & "')" & vbCRLF '
add the MasteryLevel and a line feed
sSQLInsert = sSQLInsert & "; "
next
next
___________________________________________________________________________
Here's the output...
___________________________________________________________________________
INSERT INTO Mastery (StudentID, NCOID, MasteryLevel) VALUES (21, 1571,
'Mastered') ; INSERT INTO Mastery (StudentID, NCOID, MasteryLevel) VALUES
(21, 1572, 'Mastered') ; INSERT INTO Mastery (StudentID, NCOID,
MasteryLevel) VALUES (31, 1571, 'Mastered') ; INSERT INTO Mastery
(StudentID, NCOID, MasteryLevel) VALUES (31, 1572, 'Mastered') ; INSERT INTO
Mastery (StudentID, NCOID, MasteryLevel) VALUES (24, 1571, 'Mastered') ;
INSERT INTO Mastery (StudentID, NCOID, MasteryLevel) VALUES (24, 1572,
'Mastered') ;
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL
statement.
/demo/MasteryEnter1.asp, line 44
Thanks,
Rick
|
|
|
View Cart
