inserting multiple rows in a single table

[craftit]

Hi All,
I am having a huge problem trying to insert multiple rows in a table
all in one trip.can anyone please help me out.
i am using access 2000 as my database and i am developing a winform

application.
I need your help all.

Thaks in advance

PS. A sample code would be very much appreciated.

 

[vis781]

Yes, some sample code would be appreciated I mean to say that can you post the code you are having problems with. If you have added rows to your datatable then it is just a matter of building the correct SQL and calling the update method of the dataadapter.

 

[Anti-Rich]

one possible way to do multiple rows at once, is to put all your statements in one variable, and separate each statement with a semi colon.

another possible way would be to create an array of sql statements, and execute them with an open connection.
(assuming you have an oledbconnection set up as "cnn")
ie.:

dim cmd as new oledbcommand
with cmd

.connection = cnn
.commandtype=commandtype.text

.connection.open

for i as integer=0 to sqlarray.length-1

.commandtext = sqlarray(i)
.executenonquery()

​

next
.connection.close

​

end with

note that for this to work you need to setup the array correctly and populate it dynamically (if you dont know how many rows you are inserting). anything further, let me know and ill try to help

hope that made things easier

regards
adam

 

[jmcilhinney]

The way to insert multiple rows into a database is to put them all in a DataTable and then call the Update method of a DataAdapter that has the appropriate SQL code in its InsertCommand. As vis says, show us what you're doing now so we can see what the issue is. Seriously, why should we create an entire example when it may be as simple as one small change to the code you're currently using?

 

[cjard]

Hi All,
I am having a huge problem trying to insert multiple rows in a table
all in one trip.can anyone please help me out.
i am using access 2000 as my database and i am developing a winform

application.
I need your help all.

Thaks in advance

PS. A sample code would be very much appreciated.

what you ask is technically impossible. SQL has no data modifying statement that inserts values in bulk.. it is necessary to repeatedly call INSERT INTO x(...) VALUES(...) to insert multiple records

an alternative may be to do this:
INSERT INTO x (...)
SELECT (...)
UNION ALL
SELECT (...)
...

but it's a major nuisance. I fail to see the advantage of inserting multiples at once.. you might as well just ensure your tableadapter has a properly configured insert statement, and add mutliple new rows to a datatable. Upon calling Update() on the table adapter, the adapter itself will repeatedly call insert to add the multiple rows.

 

[craftit]

inserting multiple rows in a table

Here is what i'm trying to do.
i need to let people fill their time sheet for projects done.
initially i make available a row,if u need to fill
for two projects you click and row, that makes another row visible.
Which means that i have not one row to insert at a time,i have more than one.
Here is the sample code but in another way

Private Sub insertStaffArray3()
Dim adapter As OleDbDataAdapter
Dim arrayCommand As OleDbCommand
Dim job As String = Me.prjName.Text.ToString
Dim JobId As String = Me.projId.Text.ToString
Dim strstaff1 As String = Me.staff1.Text.ToString
Dim strstaff2 As String = Me.staff2.Text.ToString
Dim strhrs1 As String = Me.hrs1.Text.ToString
Dim strhrs2 As String = Me.hrs2.Text.ToString
Dim arrayJob() As String = {job, job}
Dim arrayJobId() As String = {JobId, JobId}
Dim arrayStaff() As String = {strstaff1, strstaff2}
Dim arrayHrs() As String = {strhrs1, strhrs2}
adapter = New OleDbDataAdapter("SELECT jobDescription,jobId,employeeName,scheduledTime FROM ScheduledTime", OleDbConnection1)
arrayCommand = New OleDbCommand("Insert into ScheduledTime(jobDescription,jobId,employeeName,scheduledTime) values(?,?,?,?)", OleDbConnection1)
'arrayCommand.Parameters
arrayCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("jobDescription", System.Data.OleDb.OleDbType.VarWChar, 50, "jobDescription"))
arrayCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("jobId", System.Data.OleDb.OleDbType.VarWChar, 50, "jobId"))
arrayCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("employeeName", System.Data.OleDb.OleDbType.VarWChar, 50, "employeeName"))
arrayCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("scheduledTime", System.Data.OleDb.OleDbType.Double, 0, "scheduledTime"))
arrayCommand.Parameters("jobDescription").Value = arrayJob
arrayCommand.Parameters("jobId").Value = arrayJobId
arrayCommand.Parameters("employeeName").Value = arrayStaff
arrayCommand.Parameters("scheduledTime").Value = arrayHrs
 
 
Try
Me.OleDbConnection1.Open()
arrayCommand.ExecuteNonQuery()
Catch ex As OleDbException
MessageBox.Show(ex.Message)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
Me.OleDbConnection1.Close()
End Try
End Sub

 

[craftit]

one possible way to do multiple rows at once, is to put all your statements in one variable, and separate each statement with a semi colon.

another possible way would be to create an array of sql statements, and execute them with an open connection.
(assuming you have an oledbconnection set up as "cnn")
ie.:

dim cmd as new oledbcommand
 
 
 
 

with cmd[INDENT].connection = cnn[/INDENT][INDENT].commandtype=commandtype.text[/INDENT][INDENT].connection.open[/INDENT][INDENT]for i as integer=0 to sqlarray.length-1[INDENT].commandtext = sqlarray(i)[/INDENT][INDENT].executenonquery()[/INDENT]next 
.connection.close
 
 
 
 
 

[/INDENT]end with 
 
 
note that for this to work you need to setup the array correctly and populate it dynamically (if you dont know how many rows you are inserting). anything further, let me know and ill try to help
 
hope that made things easier
 
regards
adam

Here is what i'm trying to do.
i need to let people fill their time sheet for projects done.
initially i make available a row,if u need to fill
for two projects you click and row, that makes another row visible.
Which means that i have not one row to insert at a time,i have more than one.
Here is the sample code but in another way

Private Sub insertStaffArray3()
Dim adapter As OleDbDataAdapter
Dim arrayCommand As OleDbCommand
Dim job As String = Me.prjName.Text.ToString
Dim JobId As String = Me.projId.Text.ToString
Dim strstaff1 As String = Me.staff1.Text.ToString
Dim strstaff2 As String = Me.staff2.Text.ToString
Dim strhrs1 As String = Me.hrs1.Text.ToString
Dim strhrs2 As String = Me.hrs2.Text.ToString
Dim arrayJob() As String = {job, job}
Dim arrayJobId() As String = {JobId, JobId}
Dim arrayStaff() As String = {strstaff1, strstaff2}
Dim arrayHrs() As String = {strhrs1, strhrs2}
adapter = New OleDbDataAdapter("SELECT jobDescription,jobId,employeeName,scheduledTime FROM ScheduledTime", OleDbConnection1)
arrayCommand = New OleDbCommand("Insert into ScheduledTime(jobDescription,jobId,employeeName,sc heduledTime) values(?,?,?,?)", OleDbConnection1)
'arrayCommand.Parameters
arrayCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("jobDescription", System.Data.OleDb.OleDbType.VarWChar, 50, "jobDescription"))
arrayCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("jobId", System.Data.OleDb.OleDbType.VarWChar, 50, "jobId"))
arrayCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("employeeName", System.Data.OleDb.OleDbType.VarWChar, 50, "employeeName"))
arrayCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("scheduledTime", System.Data.OleDb.OleDbType.Double, 0, "scheduledTime"))
arrayCommand.Parameters("jobDescription").Value = arrayJob
arrayCommand.Parameters("jobId").Value = arrayJobId
arrayCommand.Parameters("employeeName").Value = arrayStaff
arrayCommand.Parameters("scheduledTime").Value = arrayHrs
 
 
Try
Me.OleDbConnection1.Open()
arrayCommand.ExecuteNonQuery()
Catch ex As OleDbException
MessageBox.Show(ex.Message)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
Me.OleDbConnection1.Close()
End Try

 

[cjard]

I dont understand; your entire thread has been talking about inserting rows, but the code you posted is for selecting rows.. The two are opposite things.