Multiple Row INSERT Error

  • Thread starter Thread starter dookywater
  • Start date Start date
D

dookywater

I know this topic has been hit several times in multiple groups but
nobody has complained about an error (that I could find). I am trying
to INSERT multiple rows in an Access DB from a survey (using VB with
ASP.NET). The DB is setup such that there is 3 Columns (ID, Question,
Answer). Typically when one person takes the survey (which is 20
questions) the INSERT statement will add 20 rows to the DB with ID
1-20. Here is some code with my query and error (using fictional data
and only 2 rows to be INSERTED as opposed to 20).

dim dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
.............)

sql=("INSERT INTO Survey (ID, Question, Answer) SELECT ('5',
'question5', 'answer5') UNION ALL SELECT ('6', 'question6', 'answer6')
")

dbcomm=New OleDbCommand(sql,dbconn)
dbconn.Open()
dbcomm.ExecuteNonQuery()
dbconn.Close()

Syntax error (missing operator) in query expression '('5', 'question5',
'answer5') UNION ALL SELECT ('6', 'question6', 'answer6')'.

The interesting thing is that the line with the error on it actually
shows up at this line:
dbcomm.ExecuteNonQuery()
Thus I have tried using the ExecuteReader() method instead to no avail.
Same error.

I've tried using and not using parenthesis in multiple places within
the query and have come up with another error that simply reads Syntax
Error in INSERT INTO statement so I think the way I have them now is
correct.

In the end the fictional data I am trying to INSERT would be more like
' " & a1.SelectedValue & " ', but for testing purposed I'm just keeping
it simple.

Any suggestions on why I might be getting this error would be greatly
appreciated.
 
Hi,


The right syntax is without parentheses, but won't work with Jet (will work
with MS SQL Server):


INSERT INTO tableName( listOfields) SELECT listOfValues UNION ALL
secondListOfValues



With Jet, try multiple statements inserting ONE line at a time, with VALUES:

INSERT INTO tableName( listOfField) VALUES( listOfValues)



Hoping it may help,
Vanderghast, Access MVP
 
Yikes, my greatest fear... that it won't work with JET.
I have no problem inserting one row at a time but 20 INSERT statements
vs. 1 INSERT statement using UNION is a no-brainer.
Since for now all I have access to is MS Access I will suffer through
the 20 inserts.
Thanks for the useful bit of information, at least now I can stop
driving myself crazy over it not working.
Cheers.
 
For what it is worth, there are alternative methods for achieving this kind
of multi-row insert. One common method is to use a Data Adapter and a
Dataset. You can add each record to be inserted into the dataset and then
call the Update method on the Data Adapter to insert all the records to the
database. You will need to set the InsertCommand for the Data Adapter. I
don't know all the specifics of your setup, however, it may be something to
consider. The following KB article shows this approach.

http://support.microsoft.com/default.aspx?scid=kb;en-us;301248

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I know this topic has been hit several times in multiple groups but
nobody has complained about an error (that I could find). I am trying
to INSERT multiple rows in an Access DB from a survey (using VB with
ASP.NET). The DB is setup such that there is 3 Columns (ID, Question,
Answer). Typically when one person takes the survey (which is 20
questions) the INSERT statement will add 20 rows to the DB with ID
1-20. Here is some code with my query and error (using fictional data
and only 2 rows to be INSERTED as opposed to 20).

dim dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
.............)

sql=("INSERT INTO Survey (ID, Question, Answer) SELECT ('5',
'question5', 'answer5') UNION ALL SELECT ('6', 'question6', 'answer6')
")

dbcomm=New OleDbCommand(sql,dbconn)
dbconn.Open()
dbcomm.ExecuteNonQuery()
dbconn.Close()

Syntax error (missing operator) in query expression '('5', 'question5',
'answer5') UNION ALL SELECT ('6', 'question6', 'answer6')'.

The interesting thing is that the line with the error on it actually
shows up at this line:
dbcomm.ExecuteNonQuery()
Thus I have tried using the ExecuteReader() method instead to no avail.
Same error.

I've tried using and not using parenthesis in multiple places within
the query and have come up with another error that simply reads Syntax
Error in INSERT INTO statement so I think the way I have them now is
correct.

In the end the fictional data I am trying to INSERT would be more like
' " & a1.SelectedValue & " ', but for testing purposed I'm just keeping
it simple.

Any suggestions on why I might be getting this error would be greatly
appreciated.
 
Hi,


Can also use a DAO/ADO recordset, adding the records one at a time in the
recordset.

Or you mean that we can append the data from an array (one row per record to
be added), into a table, in just one statement? My only reference that has
something more or less related is "Professional C# 2005", but while they
show how to get an array as data source, they are silent about appending
data from an array to a databased-table (directly or indirectly), in just
one (not looping) statement. Not that I consider the mentioned book an
excellent reference about working with databases, so, if you have more info
(than in the mentioned book) that you can share, you have all my attention.



Vanderghast, Access MVP
 
David, thanks for that article link. That seems to be a feasible
approach for my situation. Hopefully I can implement it without any
trouble.
 
Back
Top