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?