vb.net import csv to sql, how to handle empty cell

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

vb.net import csv to sql, how to handle empty cell

Author

Topic

ladyleaf
Starting Member

1 Post

Posted - 2014-07-17 : 01:34:29

hi all,

I am using vb.net to import csv data to sql server table, all works fine if all cell in the csv has proper value, but error occurs as "Failed to convert parameter value from a String to a Double." once there has empty cell in the csv. anybody has experienced this? can advise me? my code is:

Dim table As New DataTable()
Dim parser As New FileIO.TextFieldParser("D:\test_1.csv")

table.Columns.Add("OBJECTID")
table.Columns.Add("FIELD1")
table.Columns.Add("FIELD2")
table.Columns.Add("FIELD3")
table.Columns.Add("FIELD4")
table.Columns.Add("FIELD5")
table.Columns.Add("FIELD6")
table.Columns.Add("FIELD7")
table.Columns.Add("FIELD8")
table.Columns.Add("FIELD9")

parser.Delimiters = New String() {","}
parser.HasFieldsEnclosedInQuotes = True
parser.TrimWhiteSpace = True
parser.ReadLine()

Do Until parser.EndOfData = True
table.Rows.Add(parser.ReadFields())
Loop

Dim strSql As String = "INSERT INTO BQInfoTest (OBJECTID, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7, FIELD8, FIELD9) VALUES (@OBJECTID, @FIELD1, @FIELD2, @FIELD3, @FIELD4, @FIELD5, @FIELD6,@FIELD7,@FIELD8, @FIELD9)"

Using SqlconnectionString As New SqlConnection("Server=PC-36\TESTDB;Database=test_db;User Id=testuser;Password=testuser;")
Dim cmd As New SqlClient.SqlCommand(strSql, SqlconnectionString)
With cmd.Parameters
.Add("@OBJECTID", SqlDbType.Int, 8, "OBJECTID")
.Add("@FIELD1", SqlDbType.VarChar, 15, "FIELD1")
.Add("@FIELD2", SqlDbType.VarChar, 200, "FIELD2")
.Add("@FIELD3", SqlDbType.VarChar, 20, "FIELD3")
.Add("@FIELD4", SqlDbType.Int, 8, "FIELD4")
.Add("@FIELD5", SqlDbType.Date, 20, "FIELD5")
.Add("@FIELD6", SqlDbType.Date, 20, "FIELD6")
.Add("@FIELD7", SqlDbType.Date, 20, "FIELD7")
.Add("@FIELD8", SqlDbType.Float, 8, "FIELD8")
.Add("@FIELD9", SqlDbType.Float, 8, "FIELD9")

End With

Dim adapter As New SqlClient.SqlDataAdapter()
adapter.InsertCommand = cmd

Dim iRowsInserted As Int32 = adapter.Update(table)
End Using

I also use the following code to handle the empty cell, still the same error:

If String.IsNullOrEmpty("@FIELD9") = True Then
cmd.Parameters("@FIELD9").Value = DBNull.Value
Else
.Add("@FIELD9", SqlDbType.Float, 8, "FIELD9")
End If

Any help is appreciated, thanks.

tm
Posting Yak Master

160 Posts

Posted - 2014-07-22 : 08:41:28

I think this is a .Net issue but below is an example that may help.

The example below is to trap for blank and change it to "0.0" before sending it to Stored Procedure.

' =======================================
Dim table As New DataTable()
Dim parser As New FileIO.TextFieldParser("c:\temp\test_1.csv")
table.Columns.Add("OBJECTID")
table.Columns.Add("FIELD1")
table.Columns.Add("FIELD2")
table.Columns.Add("FIELD3")
table.Columns.Add("FIELD4")
table.Columns.Add("FIELD5")
table.Columns.Add("FIELD6")
table.Columns.Add("FIELD7")
table.Columns.Add("FIELD8")
table.Columns.Add("FIELD9")

parser.Delimiters = New String() {","}
parser.HasFieldsEnclosedInQuotes = True
parser.TrimWhiteSpace = True

Dim currentrow As String()
currentrow = parser.ReadFields()
' Set to zero for SQL float parameter
If currentrow(8) = "" Then
currentrow(8) = "0.0"
End If

table.Rows.Add(currentrow)

Do Until parser.EndOfData = True
currentrow = parser.ReadFields()

' Set to zero for SQL float parameter
If currentrow(8) = "" Then
currentrow(8) = "0.0"
End If

table.Rows.Add(currentrow)
Loop

' ======================================

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources