Window-Based Tool: How to get column/Field names from excel table displayed in ListView with Checkboxes to enable me chose columns to be mapped to sql server table?

Question

0.00/5 (No votes)

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("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & OpenFileDialog1.FileName & ";" + " Extended Properties=Excel 8.0")

       'Connection to Excel Datasheet
       excelConnectionString.Open()

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

       'OleDbCommand to Fetch Data from Columns and Rows of selected sheet
       Dim cmd As New System.Data.OleDb.OleDbCommand("SELECT * FROM [Details$]", 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 = ("Number of Column(s) :   " & 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("Text", _
               Me.ListviewBindingSource, "", True))''''Error pointing here: Cannot bind to the property or column on the DataSource.Parameter name: dataMember

Label5.Text = ("Number of Column(s) :   " & 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) :   " & 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 6-Jul-11 15:44pm

Mcmish

Updated 11-Jul-11 22:18pm

v5

Add a Solution

Comments

walterhevedeich 7-Jul-11 0:44am

Whats your GetField method look like? Are you sure its returning a field? and if you have 14 columns, I should be able to see a for each loop that adds the columns to your ListView1. Seems to me, the adding of items was done only 2 times.

Mcmish 12-Jul-11 3:28am

Hi Dave Kreskowiak,

I'm kinda hooked and can't progress for over a week, please would you mind being of help to me. I will be appreciative of your kind gesture in this case. Thanx alot

1 solution

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)

2irfanshaikh · Answer 1 · 2011-07-06T23:40:00

Solution 1

Set 2 properties of ListView
1 CheckBoxes = true
2 View = List.

Posted 6-Jul-11 23:40pm

2irfanshaikh

Comments

Mcmish 7-Jul-11 6:16am

Hi walterhevedeich,

do you mind showing me the sample code of what u mean? I'm meant to receive columns from existing tables that d columns varies, so am i supposed to be adding it manually 14 or 30times? How do i known the exact number of columns that d user of the tool will have in the excel table?
Pls assist out in d coding coz i have exhausted all my ideas here.
Thanx alot

Mcmish 7-Jul-11 6:20am

2irfanshaikh, thanx for ur idea but i had already done that in the design properties, its only diplaying d name of the selected table in the checkbox instead of returning all the column and names in the ListView Checkbox.
can u assist further in this respect, perhaps the coding in ur idea???
I will appreciate that alot. thanx

Mcmish 7-Jul-11 6:26am

GetField Function Below:::

Private Function GetField(ByVal obj As Object, ByVal FieldName As String) As String
If TypeOf obj Is DataColumn Then
Return (DirectCast(obj, DataTable)(FieldName).ToString())
Else
If TypeOf obj Is ValueType AndAlso obj.[GetType]().IsPrimitive Then
Return obj.ToString()
Else
If TypeOf obj Is String Then
Return DirectCast(obj, String)
Else
Try
Dim SourceType As Type = obj.[GetType]()
Dim prop As PropertyInfo = obj.[GetType]().GetProperty(FieldName)
If prop Is Nothing OrElse Not prop.CanRead Then
Dim field As FieldInfo = SourceType.GetField(FieldName)
If field Is Nothing Then
Return "(null)"
Else
Return field.GetValue(obj).ToString()
End If
Else
Return prop.GetValue(obj, Nothing).ToString()
End If
Catch generatedExceptionName As Exception
Return "(null)"
End Try
End If
End If
End If
End Function