SQLCommandBuilder inserting double rows ??

[SteveNJ]

Hi,
i'm building an application to take a CSV file and tranfer it to a SQL database. Everything works well the way I have it and the data gets posted into the database.
The problem is the data gets inserted TWICE. I have 4 rows in my test CSV file and 8 rows end up in my SQL table. I've looked at the insert statement using the commandText and it looks fine. Below is the code

I'm at a loss here and really need help.

Thanks!!
Dim FilePath As String = Server.MapPath("/qxqrwadmin/upload")
Dim Conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited""")
Dim Comm As New System.Data.OleDb.OleDbDataAdapter("select * from lth.csv", Conn)
Comm.AcceptChangesDuringFill = false
Dim DS As new System.Data.DataSet
ds.HasChanges()
Comm.Fill(ds)
ds.HasChanges()

DataGrid1.DataSource = DS.Tables(0).DefaultView 'Test to see the data being properly selected
DataGrid1.DataBind()

Dim strConnect as string
Dim strSQL as string
strSQL = "Select * from hostingbysc.lthIVRApplications where 1=2"
strConnect = ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_lthASPNET")
Dim objConn As New SQLConnection(strConnect)
Dim objCmd As New System.Data.SQLClient.SQLDataAdapter(strSQL, objConn)


Dim cb As SQLCommandBuilder = New SQLCommandBuilder(ObjCmd)
objCmd.Update(ds)

 

[William Ryan eMVP]

A primary key on the table coupled with setting the ContinueUpdateOnError to
true on the adapter should work, but this is a band aid and I'm not
suggesting it (although ever table probably should have a key....)

The CommandBuilder builds its update logic from Inference based on the
Select statment so check the GetInsertCommand and verify what it's sending.

Just to be double sure, right before the call to objCmd.Update(ds)
add in Debug.Assert(ds.Tables[0].Rows.Count = 4, "There aren't exactly four
rows")

Also, just as an aside, those two calls to hasChanges aren't doing anything.
They aren't controlling logic flow or anything so all they are essentially
doing is causing a for loop to run through the datatable and check each
row's RowState for changes. Probably not very helpful.

First, I'd verify the rows again regradless of how many should be there in
the CSV.
Then check the actual commands being sent to the db, if this doesn't solve
it we can run a trace and see what's being sent back to the db.

Let me know.

Bill
--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

Hi,
i'm building an application to take a CSV file and tranfer it to a SQL

database. Everything works well the way I have it and the data gets posted
into the database.

The problem is the data gets inserted TWICE. I have 4 rows in my test CSV

file and 8 rows end up in my SQL table. I've looked at the insert statement
using the commandText and it looks fine. Below is the code

I'm at a loss here and really need help.

Thanks!!

Dim FilePath As String = Server.MapPath("/qxqrwadmin/upload")
Dim Conn As New

System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & filepath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited""")

Dim Comm As New System.Data.OleDb.OleDbDataAdapter("select * from lth.csv", Conn)
Comm.AcceptChangesDuringFill = false
Dim DS As new System.Data.DataSet
ds.HasChanges()
Comm.Fill(ds)
ds.HasChanges()

DataGrid1.DataSource = DS.Tables(0).DefaultView 'Test to see the data being properly selected
DataGrid1.DataBind()

Dim strConnect as string
Dim strSQL as string
strSQL = "Select * from hostingbysc.lthIVRApplications where 1=2"
strConnect = ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_lthASPNET")
Dim objConn As New SQLConnection(strConnect)
Dim objCmd As New System.Data.SQLClient.SQLDataAdapter(strSQL, objConn)


Dim cb As SQLCommandBuilder = New SQLCommandBuilder(ObjCmd)
objCmd.Update(ds)

engine supports Post Alerts, Ratings, and Searching.