Comments by Mcmish - CodeProject

Mcmish 9-Aug-11 21:32pm View

Hi Losmac,

thnx. I guess i have achieve that feat of having to compare the names, but could you in any way show me sample of the data type comparison method, so I can pick it up from there? The data types includes nvarchar, int, decimal.

Thanx in advance.

Mcmish 8-Aug-11 4:59am View

Thanx Sergey,

I really appreciates. I had tried similar methods already but its truncating the data cos of foreign keys constraint. And one of the methods will not be ideal as the column names differs at times and that will lead to either mismatch of data or truncating without inserting at all.

Any further assistance on how to solved the foreign keys issues to allow importing the selected table one after another would be highly appreciated.

Mcmish 7-Aug-11 21:27pm View

Hi Walt,

thanks for your kind interest in helping out. i'm trying to import the data rows that belong to each column in a column by column format since the column names in excel sometimes differs with the column names of sql server table.

So, when i checked d excel table column i will also checked d sql server table corresponding column in order to avoid mismatch of data, so, i want to insert column by column. Any idea???

Thanx

Mcmish 22-Jul-11 13:41pm View

Thanx Dave, i apprecaites ur replies, i guess i have tried implementing that but still bringing same error.

Sorry to bother you more,I have a listview1 that contained list of column headers with checkedboxes, now i want to return the real data that belong to Listview1.checkedItems to DataGridview1 in the form of rows. kindly assist on the right syntax, pls. thanx

Below is the code that retrieved the column names to the listview:

Me.ListviewBindingSource.DataSource = _dt.Columns
For Each Column In ListviewBindingSource
ListView1.Items.Add(Column.ToString()
Next

What syntax can i use to get data from listview1.checkeditems and return it as DataRow???

Mcmish 12-Jul-11 7:26am View

Prerak,

Thanx alot for ur understanding, it still bringing error:

Value cannot be null.Parameter name: dataSource

Thanx anyway and sorry for the disturbance

Mcmish 12-Jul-11 6:51am View

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

Mcmish 12-Jul-11 6:34am View

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:23am View

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

Mcmish 12-Jul-11 3:28am View

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

Mcmish 7-Jul-11 12:44pm View

Hi Dave, any solution for me as am in dire need of solution to the seeming puzzle. Thanx

Mcmish 7-Jul-11 6:26am View

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

Mcmish 7-Jul-11 6:20am View

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:16am View

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 4:51am View

Deleted

Hi Christian,

I'm a newbie here and i sorely need your help here, pls?

I'm developing IMPORT TABLE FROM EXCEL FOR SQL SERVER where am doing my practicals.

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?

The following Code call and display all excel sheets in a combobox and its working perfectly:
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, its urgent as am developing ETL Tool as my first project of my Industrial Training... My code below:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
ListView1.Items.Add(GetField(obj:="", FieldName:=""))
ListView1.Items.Add(ComboBox1.DisplayMember())
Dim selectedIndex As Integer
selectedIndex = ComboBox1.SelectedIndex
Dim selectedItem As String
selectedItem = ComboBox1.SelectedItem()
Label5.Text = ("Number of Column(s) : " & ListView1.Items.Count)

End Sub

Its only display 1 checkbox in the ListView1 without column name but i have like 14 columns in the table selected. How can i do it? Can you correct the codings for me? I've tried with function 'getfield' but not working
Any immediate answer will serve my interest and more.
Thanx alot

Mcmish 7-Jul-11 4:46am View

Deleted

Hi Christian,

I'm a newbie here and i sorely need your help here, pls?

I'm developing IMPORT TABLE FROM EXCEL FOR SQL SERVER where am doing my practicals.

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?

The following Code call and display all excel sheets in a combobox and its working perfectly:
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:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
ListView1.Items.Add(GetField(obj:="", FieldName:=""))
ListView1.Items.Add(ComboBox1.DisplayMember())
Dim selectedIndex As Integer
selectedIndex = ComboBox1.SelectedIndex
Dim selectedItem As String
selectedItem = ComboBox1.SelectedItem()
Label5.Text = ("Number of Column(s) : " & ListView1.Items.Count)

End Sub

Its only display 1 checkbox in the ListView1 without column name but i have 14 columns in the table selected. How can i do it? Can any1 correct the codings for me?
Any immediate answer will serve my interest and more.

Mcmish 6-Jul-11 23:26pm View

Emrah, I quite appreciate, thanx alot but i must clarify a point that the Excel template is define differently by names WebTMA upto 4 different templates, so i dynamically chose the particular template and am able to display all the sheets in a combobox but i must specify one of the sheets's name before it works, so am required not to specify any specific sheet name so that any1 i chose from d combobox will be use. see my code below:

Private Sub Button2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
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:="")
'ComboBox1.DataSource = GetDataFromExcel(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()

End Sub

Here is where i have problem, the SELECT * FROM [ ], i don't wanna specify the sheet name "Details" so that i can also work on other sheet as well:

Dim cmd As New System.Data.OleDb.OleDbCommand("SELECT * FROM [Details$]", excelConnectionString)

Any solution will be appreciated.
Thanx once again

Comments by Mcmish (Top 16 by date)