SQLBulkCopy doesn't fail, but doesn't Insert

Question

So I have the following code to take a Excel file and write it to a table in my DB.

        string target = Server.MapPath("~/Upload");
        if (fupStation.HasFile)
        {
            fupStation.SaveAs(System.IO.Path.Combine(target, fupStation.FileName));

            string connectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", target + "\\" + fupStation.FileName); string query = String.Format("select * from [{0}$]", "Client Station Assignments");
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);

            DataSet dsStation = new DataSet();
            dataAdapter.Fill(dsStation);

            DataTable dtStation = new DataTable();
            dtStation = dsStation.Tables[0];

            gvOne.DataSource = dtStation;
            gvOne.DataBind();

            using (SqlBulkCopy s = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["cnSQL"].ConnectionString))
            {
                s.DestinationTableName = "StationImport";
                s.NotifyAfter = 100;
                s.WriteToServer(dtStation);
                s.Close();
            }

        }

So Here is the issue. It reads the file and populate the Datatable just fine (proven by populating the Grid with it) And I know the SQLBulkCopy code is being called, but in the end it gives no errors, but nothing shows up in my table!

Anyone been thru this before? This is my first time using BulkCopy (and reading files too!) so I wouldn't be suprized if I am doing soemthing wrong.

Thanks

Do all the columns map correctly? "the copy will not succeed if there are any mismatched columns between the two." (source sqlteam.com/article/…) — Kevin Main
– Kevin Main, Commented May 4, 2012 at 15:10
I have an Identity column on the table that is, of course, not on the spressheet. It onnce I removed that column, that insert when in fine. Is there any way to do an insert into a table with an ID column? — Limey
– Limey, Commented May 4, 2012 at 15:25
@Kevin: Ok, the mapping was the solution! it seems that ince I have the Identity column, mapping is then required for the insert to occur (although it is odd that it didn't give me an error) Make sure to give and answer so I can accept. — Limey
– Limey, Commented May 4, 2012 at 15:31
Also found out that numbers and dcimal points are not allowed in the column names of the spreadsheet. — Limey
– Limey, Commented May 4, 2012 at 15:44

Kevin Main · Accepted Answer · 2012-05-08 07:32:13Z

3

Do all the columns map correctly? "the copy will not succeed if there are any mismatched columns between the two." (source http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server)

answered May 8, 2012 at 7:32

Kevin Main

2,34418 silver badges29 bronze badges

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

Comments

Internet Engineer · Accepted Answer · 2012-05-04 15:10:37Z

My code works in vb.net

Remember that the datatable and the sql table need to match in field datatype and column order.

    Using connection As SqlConnection = New SqlConnection(conn)
        connection.Open()
        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, Nothing)
            bulkCopy.DestinationTableName = "TableName"
            bulkCopy.BulkCopyTimeout = 60000
            bulkCopy.BatchSize = 0
            bulkCopy.WriteToServer(MyDataTable)
            bulkCopy.Close()
        End Using
        connection.Close()
    End Using

Collectives™ on Stack Overflow

SQLBulkCopy doesn't fail, but doesn't Insert

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related