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=Micros oft.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
"Peter" <pc*****@online.nospam> wrote in message news:%2****************@TK2MSFTNGP10.phx.gbl...
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=Micros oft.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=Micros oft.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]

""Peter Huang" [MSFT]" <v-******@online.microsoft.com> wrote in message
news:18**************@TK2MSFTNGXA02.phx.gbl...

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=Micros oft.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.