Hello, Im inserting some rows from an excel spreadsheet into a SQL database with the code below. I need to ensure that all fields contain data on the spreadsheet before importing. If there are nulls then do not perform the insert. What would be the simplest way to do this with the code given below?
Try
Dim QuoteNumber As String = lblQuoteNumber.Text.Trim
Dim sSQLTable As String = "BB_ImportQuoteProducts"
Dim Prclevel As String = ""
Dim sExcelFileName As String = ("~/ImportProducts/" & lblImportProductsFileName.
Text)
Dim sWorkbook As String = "[Sheet1$]"
Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OL
EDB.4.0;Da
ta Source=" & Server.MapPath(sExcelFileN
ame) & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
Dim sSqlConnectionString As String = (System.Configuration.Conf
igurationM
anager.Con
nectionStr
ings("quot
eConnectio
nString").
ToString)
Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConn
ectionStri
ng)
Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT '', '" & QuoteNumber & "','" & Prclevel & "', [PartNumber], [Description], [UOFM], [Quantity], [UOMPrice], [ExtPrice] FROM " & sWorkbook), OleDbConn)
OleDbConn.Open()
Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSqlConnection
String)
bulkCopy.DestinationTableN
ame = sSQLTable
bulkCopy.WriteToServer(dr)
OleDbConn.Close()
lblImported.Text = "Yes"
Catch ex As Exception
lblImportProductError.Text
= (ex.Message)
Finally
End Try
Start Free Trial