I have a table called “test” which has the 3 fields id (int), name(string) and age(int)
At the moment I can store the excel sheet values to this table. But I want to check wheather id is an integer, name is a string ….and so on. And then store these invalid rows in a separate database table called “temptest”. Could you please help me in this regard. Here is my code
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
If (FileUpload1.HasFile And Path.GetExtension(FileUpload1.FileName) = ".xls") Then
Dim strName As String = Path.GetFullPath(FileUpload1.PostedFile.FileName)
Dim sSQLTable As String = "test"
Dim sWorkbook As String = "[s1$]"
'Create our connection strings
Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strName + ";Extended Properties=""Excel 8.0;HDR=YES;"""
Dim sSqlConnectionString As String = "Data Source=home\SQLEXPRESS;Initial Catalog=db1;User ID=sa; pwd=sa;"
'Execute a query to erase any previous data from our destination table
Dim sClearSQL = "DELETE FROM " & sSQLTable
Dim SqlConn As SqlConnection = New SqlConnection(sSqlConnectionString)
Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
SqlConn.Open()
SqlCmd.ExecuteNonQuery()
SqlConn.Close()
Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn)
Dim oledbdadp As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM " & sWorkbook, OleDbConn)
Dim dt As New DataTable
oledbdadp.Fill(dt)
OleDbConn.Open()
If (dt.Rows.Count = 0) Then
Label1.Text = "No data in the excel sheet"
Else
Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSqlConnectionString)
'While (dr.Read())
' Dim a As Boolean = IsNumeric(dr(0))
' Label2.Text = a.ToString()
'End While
bulkCopy.DestinationTableName = sSQLTable
bulkCopy.WriteToServer(dr)
OleDbConn.Close()
Label1.Text = "Successfully sent to database"
End If
Else
Label1.Text = "Please select a valid file"
End If
End Sub
|