Deleting rows from a table

[Peter]

I have the following code which retreives data from Excel spreadsheet.
How do I exclude or delete records from the table that have blank first column, I do not need to write the records back.

Thanks


Peter



Dim excelConnection As System.Data.OleDb.OleDbConnection
Dim dbg As DataGrid
Dim DtSet As New System.Data.DataSet

Try
'
' Fetch Data from Excel
'
Dim cmd As System.Data.OleDb.OleDbDataAdapter

excelConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='" & pathExcelFile & " '; " & "Extended Properties=Excel 8.0;")
'
' Select the data from sheet of the workbook.
'
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [" & workSheetName & "$]", excelConnection)
cmd.TableMappings.Add("Table", "Data")
cmd.Fill(DtSet)

Catch ex As Exception
excelConnection.Close()
Finally
excelConnection.Close()
End Try

Return DtSet.Tables(0)

 

[Peter]

One more thing
the first column in the spreadsheet is a Date and Time
I have the following code which retreives data from Excel spreadsheet.
How do I exclude or delete records from the table that have blank first column, I do not need to write the records back.

Thanks


Peter



Dim excelConnection As System.Data.OleDb.OleDbConnection
Dim dbg As DataGrid
Dim DtSet As New System.Data.DataSet

Try
'
' Fetch Data from Excel
'
Dim cmd As System.Data.OleDb.OleDbDataAdapter

excelConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='" & pathExcelFile & " '; " & "Extended Properties=Excel 8.0;")
'
' Select the data from sheet of the workbook.
'
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [" & workSheetName & "$]", excelConnection)
cmd.TableMappings.Add("Table", "Data")
cmd.Fill(DtSet)

Catch ex As Exception
excelConnection.Close()
Finally
excelConnection.Close()
End Try

Return DtSet.Tables(0)

 

[Peter Huang [MSFT]]

Hi

I think you can use the RowFilter, first select all the records, and then
use the RowFilter to filter the ones which is not NULL in a DataView, and
the DataView is the result.
You may have a try.

Dim excelConnection As System.Data.OleDb.OleDbConnection
Dim dbg As DataGrid
Dim DtSet As New System.Data.DataSet
Dim pathExcelFile As String = "c:\temp\TestBook.xls"
Dim workSheetName As String = "Sheet1"
Try
'
' Fetch Data from Excel
'
Dim cmd As System.Data.OleDb.OleDbDataAdapter
excelConnection = New
System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='" & pathExcelFile & " '; " &
"Extended Properties=Excel 8.0;")
'
' Select the data from sheet of the workbook.
'
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from ["
& workSheetName & "$]", excelConnection)
cmd.TableMappings.Add("Table", "Data")
cmd.Fill(DtSet)
Catch ex As Exception
excelConnection.Close()
Finally
excelConnection.Close()
End Try
Dim dv As DataView
dv = New DataView
With dv
.Table = DtSet.Tables(0)
.RowFilter = DtSet.Tables(0).Columns(0).ColumnName & " Is NOT
Null"
End With
Me.DataGrid1.DataSource = dv

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter]

Hi

I think you can use the RowFilter, first select all the records, and then
use the RowFilter to filter the ones which is not NULL in a DataView, and
the DataView is the result.
You may have a try.

Dim excelConnection As System.Data.OleDb.OleDbConnection
Dim dbg As DataGrid
Dim DtSet As New System.Data.DataSet
Dim pathExcelFile As String = "c:\temp\TestBook.xls"
Dim workSheetName As String = "Sheet1"
Try
'
' Fetch Data from Excel
'
Dim cmd As System.Data.OleDb.OleDbDataAdapter
excelConnection = New
System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='" & pathExcelFile & " '; " &
"Extended Properties=Excel 8.0;")
'
' Select the data from sheet of the workbook.
'
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from ["
& workSheetName & "$]", excelConnection)
cmd.TableMappings.Add("Table", "Data")
cmd.Fill(DtSet)
Catch ex As Exception
excelConnection.Close()
Finally
excelConnection.Close()
End Try
Dim dv As DataView
dv = New DataView
With dv
.Table = DtSet.Tables(0)
.RowFilter = DtSet.Tables(0).Columns(0).ColumnName & " Is NOT
Null"
End With
Me.DataGrid1.DataSource = dv

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no
rights.

Thank you this works

 

[Peter Huang [MSFT]]

Hi

You are welcomed!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.