sqlite vb.net multi insert in one command from datatable

Question

I have this code, but it only inserts 7 records out of 54. I am sure that the records are fine in the datatable.

Dim dt As DataTable
Dim sc As SQLiteCommand
Dim Script As String = Nothing
Dim Script2 As String = Nothing
Dim Script3 As String = Nothing
Script = "insert into paperdate(dtime,papernum,paperstat,user) select " & "'" & dt.Rows(0)(0) & "'" & " as " & "dtime" & "," & dt.Rows(0)(1) & " as " & "papernum" & "," & "'" & dt.Rows(0)(2) & "'" & " as " & "paperstat" & "," & "'" & dt.Rows(0)(3) & "'" & " as " & "user"
For i As Integer = 1 To dt.Rows.Count - 1
Script2 = " union select " & "'" & dt.Rows(i)(0) & "'" & "," & dt.Rows(i)(1) & "," & "'" & dt.Rows(i)(2) & "'" & "," & "'" & dt.Rows(i)(3) & "'"
Script3 = Script3 & Script2
Next
sc = New SQLiteCommand(Script & Script3, mycon)
sc.ExecuteNonQuery()
sc.Dispose()

I hope someone has the answer, thanks

score 1 · Accepted Answer · 2012-06-02 18:26:58Z

1

It may be that you have duplicate records in the second select. If that is the case, then the solution is to use UNION ALL instead of UNION.

A compound SELECT created using UNION ALL operator returns all the rows from the SELECT to the left of the UNION ALL operator, and all the rows from the SELECT to the right of it. The UNION operator works the same way as UNION ALL, except that duplicate rows are removed from the final result set.

Reference: http://www.sqlite.org/lang_select.html

If that isn't it, then you might want to trace the SQL, and run the SELECT parts directly in the sqlite client.

Update

As per your comment, here is the change you would do to your code:

Script2 = " union select " & "'" & dt.Rows(i)(0) & "'" & "," & dt.Rows(i)(1) & "," & "'" & dt.Rows(i)(2) & "'" & "," & "'" & dt.Rows(i)(3) & "'"

becomes

Script2 = " union all select " & "'" & dt.Rows(i)(0) & "'" & "," & dt.Rows(i)(1) & "," & "'" & dt.Rows(i)(2) & "'" & "," & "'" & dt.Rows(i)(3) & "'"

edited Jun 2, 2012 at 18:26

answered Jun 2, 2012 at 17:14

user610650

Sign up to request clarification or add additional context in comments.

2 Comments

XenKid Over a year ago

Ok, yes, I have so many duplicate records, because it doesn't have a primary key, can you update my code of what are you trying to say, just wanna be sure. thanks

user610650 Over a year ago

@XenKid: sure, I augmented my answer as you asked.

Collectives™ on Stack Overflow

sqlite vb.net multi insert in one command from datatable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related