By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,402 Members | 1,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,402 IT Pros & Developers. It's quick & easy.

Deleting rows from a table

P: n/a
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)

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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)
Nov 23 '05 #2

P: n/a
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.

Nov 23 '05 #3

P: n/a

""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
Nov 23 '05 #4

P: n/a
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.

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.