How to get table column/Field names from excel table displayed in ListView with Checkboxes

Question

1.00/5 (1 vote)

See more:

The following Code call and display all excel sheets in a combobox and its working perfectly:

VB

OpenFileDialog1.ShowDialog()
      XlsPath.Text = OpenFileDialog1.FileName

      Dim excelConnectionString As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(&quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source= &quot; &amp; OpenFileDialog1.FileName &amp; &quot;;&quot; + &quot; Extended Properties=Excel 8.0&quot;)

      'Connection to Excel Datasheet
      excelConnectionString.Open()

      'Fetching all sheets from the Excel Template (Function Call)
      ComboBox1.DataSource = GetExcelSheetNames(SVTest:=&quot;&quot;)

      'OleDbCommand to Fetch Data from Columns and Rows of selected sheet
      Dim cmd As New System.Data.OleDb.OleDbCommand(&quot;SELECT * FROM [Details$]&quot;, excelConnectionString)
      Dim daCSV As New OleDbDataAdapter()
      daCSV.SelectCommand = cmd
      Dim dtCSV = New DataTable()
      daCSV.Fill(dtCSV)
      ListView1.Visible = True
      ListBox2.Visible = True
      ListView1.DataBindings.Add()

      Dim index As Integer
      index = ComboBox1.FindStringExact(0)
      Label5.Text = (&quot;Number of Column(s) :   &quot; &amp; dtCSV.Columns.Count)
      excelConnectionString.Close()

However, when i select a table from the list in the combobox,

I want it to display all the table columns in LstView1 with checkboxes........

How can i achieve that? pls guys, its urgent as am developing ETL Tool...

My code below:

VB

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
       Dim selectedItem As String
       selectedItem = ComboBox1.SelectedItem
       Me.ListviewBindingSource = New BindingSource
       AddHandler Me.Load, AddressOf ComboBox1_SelectedIndexChanged
       Me.ListView1.Dock = DockStyle.Bottom
       Me.Controls.Add(Me.ListView1)
       Me.ListviewBindingSource.DataSource = _dt.Columns(SelectedItem)''Error pointing here:Object reference not set to an instance of an object

       Me.ListView1.DataBindings.Add(New Binding(&quot;Text&quot;, _
               Me.ListviewBindingSource, &quot;&quot;, True))''Error pointing here: Cannot bind to the property or column on the DataSource.Parameter name: dataMember

Label5.Text = ("Number of Column(s) :   " &amp; ListView1.Items.Count)
   End Sub

VB

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
           For i = 1 To _dt.Columns.Count
               ListView1.Items.Add(New ListViewItem(i))
               ListView1.Items.Add(New ListViewItem(selectedItem))'' Error pointing here:Object reference not set to an instance of an object

           Next i
Label5.Text = ("Number of Column(s) :   " &amp; ListView1.Items.Count)
End Sub

Error: Object reference not set to an instance of an object.

How can i do it? Can any1 correct the codings for me? I want it display all the field/column names/text in Listview1 checkboxes

I have tried with d 2 different approaches, but still couldn't get it to display any field names
Any answer will serve my interest and more.
Thanx guys

Posted 11-Jul-11 22:30pm

Mcmish

Add a Solution

2 solutions

Solution 1

What is AddHandler Me.Load, AddressOf ComboBox1_SelectedIndexChanged in first code section? Just remove it. In second code it is obvious that you have not set anything to selectedItem

To solve your problem, use this in first code block.

selectedItem = ComboBox1.SelectedItem.ToString()

or

selectedItem = ComboBox1.SelectedText

Me.ListviewBindingSource.DataSource = _dt
Me.ListView1.DataBindings.Add(New Binding("Text", _dt, SelectedItem, True))

Posted 11-Jul-11 23:02pm

Prerak Patel

Updated 12-Jul-11 1:01am

v3

Comments

Mcmish 12-Jul-11 6:23am

Thanx, i forget to remove that line while posting, it doesn't apply.
however, i have tried your suggestion before, and the msg is usually:

String cannot be converted to table. OR
it will display just the selected text as String and not the columns/fields that belong to that table.

Pls assist me further on how to set the selectedItem as a table cos when i declare it as table, it says: String cannot be converted to table.

Thanx alot

Prerak Patel 12-Jul-11 6:47am

Change the binding

Mcmish 12-Jul-11 6:34am

Prerak Patel,

In the above context of ur suggestions it either brings out null value or of the selectedtext which represent TableName and not d table fields.

Hope to hear frm u again. Thanx

Mcmish 12-Jul-11 6:51am

Hi Prerak,

sorry to disturb u further. This is d new error after implementing d new code u gave me:

Cannot bind to the property or column Buildings on the DataSource.
Parameter name: dataMember

Prerak Patel 12-Jul-11 7:01am

Try something like this. Do some more debugging and googling if you face any problem.

Mcmish 12-Jul-11 7:26am

Prerak,

Thanx alot for ur understanding, it still bringing error:

Value cannot be null.Parameter name: dataSource

Thanx anyway and sorry for the disturbance

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mcmish · Accepted Answer · 2011-08-17T19:26:00

The following is the final workable solution to the question above:

SQL

dt = Me.ComboBox1.SelectedItem
      
       For Each Column As DataColumn In dt.Columns
           
           ListView1.Items.Add(Column.ColumnName)
       Next
       
       
             Label1.Text = ("The table " & dt.TableName.ToUpper & " has ( " & (dt.Columns.Count) & " ) columns")

       CheckAll.CheckState = CheckState.Checked
       For Each items As ListViewItem In Me.ListView1.Items
           If items.Checked = False Then
               items.Checked = True
           End If

       Next