The unique nature of my problem runs into multiple entries for multiple 

fields...

In other words...I have many students (StudentID) highlighted, and they 

have many outcomes (NCOID) highlited...each of these outcomes for these 

students also has a level of mastery (Level)...

Do I need to write a For Each Student ID...Loop in the following code...and 

if so, how would it look?

Or do I need a FROM ARRAY in my Insert???

Any code is helpful as I'm stabbing in the dark here....

(I thought about Splitting the multiple values...but that didn't seem to 

help)



Current Output:



21, 31, 24

1571, 1572, 1573

Mastered 

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 



[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO 

statement.



/demo/MasteryEnter.asp, line 22 



____________________________________________________________



Actual File...

____________________________________________________________

<%@ Language = VBscript %>

<%

StudentID=Request.Form("StudentID")

NCOID=Request.Form("NCOID")

Level=Request.Form("Level")



Response.Write (StudentID)

Response.write ("<br>")

Response.Write (NCOID)

Response.write ("<br>")

Response.Write (Level)



'INSERT CLIENT INFORMATION

 

SQLINSERT="INSERT INTO Mastery (StudentID, NCOID, Level) "

SQLINSERT=SQLINSERT & "VALUES ("

SQLINSERT=SQLINSERT & "'" & StudentID & "', "

SQLINSERT=SQLINSERT & "'" & NCOID & "', "

SQLINSERT=SQLINSERT & "'" & Level & "'); "





set connupdate = server.createobject("ADODB.Connection")

connupdate.open "demo"

connupdate.execute(SQLINSERT)



connupdate.close



URL="MasteryComplete.asp?StudentID=" & StudentID



Response.redirect (URL)



%>

____________________________________________________________







Thanks,

Rick

Hi there,



As far as I can see, there's nothing wrong with the SQL statement as you 

wrote it.

But is StudentID a char/varchar or a number (e.g. int)? You write the 

StudentID with apostrophes. This is not possible with integer ID's.



To see what the problem really is, I think it's better to post some of the 

code from the page that's supplying the data.

Now it's not clear exactly what you receive in the Request.Form collection. 

If there are multiple values, you can indeed split them, and then use 

Ubound(array) to determine the number of items.

Then loop through the values in the array, and compose your SQLInsert 

string. Add a vbCRLF to every line, to avoid other errors in the SQL statement.



Imar





At 05:42 PM 8/14/2000 +0000, you wrote:

>The unique nature of my problem runs into multiple entries for multiple

>fields...

>In other words...I have many students (StudentID) highlighted, and they

>have many outcomes (NCOID) highlited...each of these outcomes for these

>students also has a level of mastery (Level)...

>Do I need to write a For Each Student ID...Loop in the following code...and

>if so, how would it look?

>Or do I need a FROM ARRAY in my Insert???

>Any code is helpful as I'm stabbing in the dark here....

>(I thought about Splitting the multiple values...but that didn't seem to

>help)

>

>Current Output:

>

>21, 31, 24

>1571, 1572, 1573

>Mastered

>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

>

>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO

>statement.

>

>/demo/MasteryEnter.asp, line 22

>

>____________________________________________________________

>

>Actual File...

>____________________________________________________________

><%@ Language = VBscript %>

><%

>StudentID=Request.Form("StudentID")

>NCOID=Request.Form("NCOID")

>Level=Request.Form("Level")

>

>Response.Write (StudentID)

>Response.write ("<br>")

>Response.Write (NCOID)

>Response.write ("<br>")

>Response.Write (Level)

>

>'INSERT CLIENT INFORMATION

>

>SQLINSERT="INSERT INTO Mastery (StudentID, NCOID, Level) "

>SQLINSERT=SQLINSERT & "VALUES ("

>SQLINSERT=SQLINSERT & "'" & StudentID & "', "

>SQLINSERT=SQLINSERT & "'" & NCOID & "', "

>SQLINSERT=SQLINSERT & "'" & Level & "'); "

>

>

>set connupdate = server.createobject("ADODB.Connection")

>connupdate.open "demo"

>connupdate.execute(SQLINSERT)

>

>connupdate.close

>

>URL="MasteryComplete.asp?StudentID=" & StudentID

>

>Response.redirect (URL)

>

>%>

>____________________________________________________________

>

>

>

>Thanks,

>Rick

>

>---

>You are currently subscribed to asp_databases

Can somebody help me and Rick with the following situation.



Rick is trying to (see previous posts, called "Multiple Inserts" ) insert 

multiple records into an Access database by dynamically building a SQL 

statement which he passes to the execute method of a command object. So far 

so good.



But somehow, the syntax for multiple inserts in Access differs from SQL server.



In SQL I am allowed to do this:



INSERT INTO Mastery (StudentID, NCOID, MasteryLevel) VALUES (80, 2361, 

'Mastered')

INSERT INTO Mastery (StudentID, NCOID, MasteryLevel) VALUES (80, 2365, 

'Mastered')

INSERT INTO Mastery (StudentID, NCOID, MasteryLevel) VALUES (80, 2366, 

'Mastered')



This query has been build inside a loop. After each ) I added a vbCRLF to 

get to the next line.



However, Access doesn't like this. We tried adding a semi colon to every 

line. I also tried skipping the INTO keyword (seems required in Access??)) 

and I tried variations of the above statement (only one semicolon after the 

last insert statement, skipping the vbCRLF etc).



Aaaaaaaaaaaaahhhhhhhhhhh!! What's going on.



Anybody any suggestions? This is almost driving me nuts, since it seems to 

work in SQL server.



Imar

I've always executed each SQL statement separately, so instead of looping

and building all the statement then executing them all, I've looped before

that and executed one statement at a time.



However, I was always under the impression that you could use ; to separate

SQL statements to be executed against Access.



I'm assuming we're talking about Rick here?

I've always found the Access QBE to be very helpful. Cut and paste the

response.write SQL statement and put it into a New Query in SQL view, and

Access will tell you at which character position the error is occuring -

maybe he needs to take out the last ; or maybe take out some spaces



Cheers

Ken



----- Original Message -----

From: "Imar Spaanjaars" 

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, August 16, 2000 11:40 PM

Subject: [asp_databases] Multiple Inserts





> Can somebody help me and Rick with the following situation.

>

> Rick is trying to (see previous posts, called "Multiple Inserts" ) insert

> multiple records into an Access database by dynamically building a SQL

> statement which he passes to the execute method of a command object. So

far

> so good.

>

> But somehow, the syntax for multiple inserts in Access differs from SQL

server.

>

> In SQL I am allowed to do this:

>

> INSERT INTO Mastery (StudentID, NCOID, MasteryLevel) VALUES (80, 2361,

> 'Mastered')

> INSERT INTO Mastery (StudentID, NCOID, MasteryLevel) VALUES (80, 2365,

> 'Mastered')

> INSERT INTO Mastery (StudentID, NCOID, MasteryLevel) VALUES (80, 2366,

> 'Mastered')

>

> This query has been build inside a loop. After each ) I added a vbCRLF to

> get to the next line.

>

> However, Access doesn't like this. We tried adding a semi colon to every

> line. I also tried skipping the INTO keyword (seems required in Access??))

> and I tried variations of the above statement (only one semicolon after

the

> last insert statement, skipping the vbCRLF etc).

>

> Aaaaaaaaaaaaahhhhhhhhhhh!! What's going on.

>

> Anybody any suggestions? This is almost driving me nuts, since it seems to

> work in SQL server.

>

> Imar

>

>

>

>

I was under the same impression that you could use a ; to seperate 

commands. In SQL server only a linefeed is enough, but this doesn't seem to 

work in Access.

Aren't you worried about the decreased performance of executing a SQL 

statement line by line instead of sending them in "batch-mode"?



I tried the exact same thing in the QBE, but the error is the same. 

"Missing semi colon" or "characters at the end of SQL statement", or 

something like that.

Anyway, one by one by one by one we go / Rick goes.



Imar







At 11:44 AM 8/17/2000 +1000, you wrote:

>I've always executed each SQL statement separately, so instead of looping

>and building all the statement then executing them all, I've looped before

>that and executed one statement at a time.

>

>However, I was always under the impression that you could use ; to separate

>SQL statements to be executed against Access.

>

>I'm assuming we're talking about Rick here?

>I've always found the Access QBE to be very helpful. Cut and paste the

>response.write SQL statement and put it into a New Query in SQL view, and

>Access will tell you at which character position the error is occuring -

>maybe he needs to take out the last ; or maybe take out some spaces

>

>Cheers

>Ken

> I was under the same impression that you could use a ; to seperate

> commands. In SQL server only a linefeed is enough, but this doesn't seem

to

> work in Access.

> Aren't you worried about the decreased performance of executing a SQL

> statement line by line instead of sending them in "batch-mode"?



Well not if it can't be done :-)

I've had to do it so rarely that it hasn't really ever come up as an issue.

I only use Access on small projects where the number of users is known to be

small.



> I tried the exact same thing in the QBE, but the error is the same.

> "Missing semi colon" or "characters at the end of SQL statement", or

> something like that.



If you go into SQL View, the cursor should be located on the actual

character that it causing the error...



Cheers

Ken