SqlBulkCopy Strange Issue 1

[LittlBUGer]

Hello all. I'm having a very strange issue when using ASP.NET 2.0 (and ADO.NET 2.0) along with the SqlBulkCopy command. Basically all I'm doing is having a user upload a pre-formatted Excel file with data in which I'm using the SqlBulkCopy command to copy the data from the file into a temporary SQL Server 2000 table. This seems to work fine except for a few columns and I can't figure out why. The columns with issues thus far are dates, which is strange as there's other columns with dates that work just fine in the exact same file. Has anyone else seen such behavior like this? I'm using basic code and I just can't figure it out. Here's some parts of my code:

Protected Sub ExcelImport(ByVal thefile As String)
	Dim sSQLTable As String = "TempExcelImport"
	Dim sExcelFileName As String = thefile
	Dim sWorkbook As String = "[VB_Stock_Data$]"

	Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("~/Temp/" & sExcelFileName) & ";Extended Properties=""Excel 8.0;HDR=YES;"""
	Dim sSqlConnectionString As String = dataconnection.ConnectionString

	Dim sClearSQL As String = "DELETE FROM " & sSQLTable
	Dim SqlConn As SqlConnection = New SqlConnection(sSqlConnectionString)
	Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
	Try
	    SqlConn.Open()
	    SqlCmd.ExecuteNonQuery()
	Catch ex As Exception
	    lblMessage.Visible = True
	    lblMessage.ForeColor = System.Drawing.Color.Red
	    lblMessage.Text = "ERROR: Excel Import Temp Table Clear Failed! - " & ex.Message
	Finally
	    SqlConn.Close()
	End Try

	Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
	Dim OleDbStr As String = "SELECT * FROM " & sWorkbook & " WHERE [Line Num] > 5"
	Dim OleDbCmd As OleDbCommand = New OleDbCommand((OleDbStr), OleDbConn)

	Try
	    OleDbConn.Open()
	    OleDbCmd.ExecuteNonQuery()

	    Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
	    Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSqlConnectionString)

	    bulkCopy.DestinationTableName = sSQLTable
	    bulkCopy.WriteToServer(dr)
	Catch ex2 As Exception
	    lblMessage.Visible = True
	    lblMessage.ForeColor = System.Drawing.Color.Red
	    lblMessage.Text = "ERROR: Excel Import to Temp Failed! - " & ex2.Message
	Finally
	    OleDbConn.Close()
	End Try
End Sub

And here's some pieces of info from my temp table, the columns within anyway:

[Line Num]
Gender
[Birth Date]
[Birth Weight]
[Brand Date]
[Wean Date]
...

It's driving me mad as I just can't find any fault or why other date columns work but some don't (such as there's a 'Brand Date' and 'Wean Date' column that works just fine but the 'Birth Date' column does not). I've tried formatting the Excel file in different ways too but no luck. Any suggestions anyone? I'd be happy to post more information if needed. Thanks.

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

 

[jbenson001]

What do you mean it doesn't work? Do you get an error or just not the results you expect. I would assume that you have some data in that column that is not a valid date.

 

[LittlBUGer]

That is the strange part. The Excel document is formated to have all of the date columns be the same. So as I said above, the Brand Date works but the Birth Date doesn't, which doesn't make sense. And what I mean by doesn't work is that I get no errors or warnings or anything, the data just doesn't get imported into the table for that column. The column will just have NULLs like there was no data in the Excel sheet at all. It really just doesn't make any sense to me.

Is there another way, other than those Try blocks, to get more information out of the SqlBulkCopy command? Thanks for your help.

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

 

[ca8msm]

Try setting the format of the column to Text in the Excel file. I've come across this problem before (although it was a few years ago) and I think that solved it. If not, I'll try to dig out exactly what I did.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[LittlBUGer]

Thank you for your help, that seemed to fix the issue with that column, but now some other columns aren't working. More specifically, previously the Excel sheet only had about half of the columns filled with information and of those half, that one date column didn't work. Now that the date column works, I began checking the rest by filling all columns with data. Now nothing at all gets imported into the database and there's still no errors shown. Could it be possibly because of how the table is setup, the data types maybe? Thanks again for your help.

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

 

[LittlBUGer]

I finally got it to show me some errors. I've changed the date columns formats in the Excel file to be General, Text, and Date and the same error still occurs:

System.InvalidOperationException: The given value of type String from the data source cannot be converted to type smalldatetime of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a DateTime. ---> System.FormatException: String was not recognized as a valid DateTime. at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles) at System.DateTime.Parse(String s, IFormatProvider provider) at System.Convert.ToDateTime(String value, IFormatProvider provider) at System.String.System.IConvertible.ToDateTime(IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at AutoDataEntry.ExcelImport(String thefile) in C:\LDN\AutoDataEntry.aspx.vb:line 397

Any ideas? Thanks.

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

 

[LittlBUGer]

Nevermind this thread anymore. I just decided to make nearly all columns in the database table to be varchar and that seems to have solved the issues. I'll just have to check formatting elsewhere. Thanks.

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

 

[ca8msm]

Yes, you are probably better to use a "staging" table for this anyway. Get all the data into SQL Server, and then use this staging table to populate the real table, performing any necessary conversions along the way. It may be slightly more intensive this way, but it should help eliviate any potential problems with the data.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]