Unable to save the String data into a table from Excel sheet in SQL Server 2005 db

[tskmjk]

Hi all,

I am developing an application which reads an excel file which has the following records and inserts into a table in SQL SERVER 2005 database [Enterprise Edition].

Data:

Product Version CustType
------------- ------------- --------------
Norton SystemWorks Basic Edition 2009 CS
Norton 360 2 CS
Norton 360 2 CS
Norton SystemWorks Standard Edition 2009 CS
Norton AntiVirus 2009 CS
Norton Internet Security 2006 CS
Norton Internet Security 2008 CS
Norton SystemWorks 2006 CS
Norton 360 1 CS

This is code:

   Dim cn As New OleDbConnection
        Dim dea As New OleDbDataAdapter
        Dim dsee As New DataSet
        Dim cea As String
        Dim con1 As New OleDbConnection
        Dim da1 As New OleDbDataAdapter
        Dim da2 As New OleDbDataAdapter
        Dim ds1 As New DataSet
        Dim ds2 As New DataSet
        Dim dt1 As New DataTable
        Dim strOdbcCmdd As New OleDbCommand()
        Dim iCnt As Integer = 0
        Dim iCnt1 As Integer = 0
        Dim straData(10) As String
        Dim straData1(4) As String
        Dim strSqlQry As String = String.Empty
        Dim strOdbcCmd As New OleDbCommand()
        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & TextBox1.Text & ";" & "Extended Properties=Excel 8.0")
        cn.Open()
        cea = "select * from [Sheet1$]"
        dea = New OleDbDataAdapter(cea, cn)
        dea.Fill(dsee, "xls")
        con1 = New OleDbConnection("Provider=SQLNCLI;Server=APP-GMF14-2K-D;Database=tempdb;Uid=sa;Pwd=Iseva123")
        con1.Open()
        Dim objstr(14) As String
        Dim cnt As Integer = 0
        For i As Integer = 0 To dsee.Tables("xls").Rows.Count - 1
            strSqlQry = "Insert into test(product,version,custsubtype) values('" & dsee.Tables("xls").Rows(i).Item(0) & "','" & dsee.Tables("xls").Rows(i).Item(1) & "','" & dsee.Tables("xls").Rows(i).Item(2) & "')"
            MsgBox(strSqlQry)
            strOdbcCmd.CommandText = strSqlQry
            strOdbcCmd.Connection = con1
            strOdbcCmd.ExecuteNonQuery()
            strSqlQry = ""
        Next
        MsgBox("Inserted " & dsee.Tables("xls").Rows.Count & " records !!")
        cn.Close()
        con1.Close()

But I am unable to capture the second column data if it is a string like "Basic Edition 2009" which shows Null or blank like:

Table Test:

ID Product Version CustType
--------------------------------------------------------------------
1 Norton SystemWorks <NULL> CS
2 Norton 360 2 CS
3 Norton 360 2 CS
4 Norton SystemWorks <NULL> CS
5 Norton AntiVirus 2009 CS
6 Norton Internet Security 2006 CS
7 Norton Internet Security 2008 CS
8 Norton SystemWorks 2006 CS
9 Norton 360 1 CS


How to make the string data read?