Adding a reference to Microsoft.Office.Interop.Excel disables New DataTables

[sanderson]

Hi:

I had some code working to my satisfaction. As a final step, I added a reference to 'Microsoft.Office.Interop.Exce', and now my Data Tables produce errors. Apparently, I am no longer allowed to instantiate a DataTable.

Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel


Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ds1 As String = "\\stylmark.com\fs-styl\Public\HISTORY\icedet 12mos122912.xlsx"
        Dim ss1 As String = "SELECT [Item], [SONbr], [OrdDate], [Eng], [Cfg#Rev] FROM [MASTER$] " & _
                            "ORDER BY [Item] DESC "
        Dim dt1 As New DataTable
        dt1.Columns.Add("Item", GetType([String]))

---

I have n attachment that illustrates the problem.

Thanks in advance for the help.

[kebo]

"DataTable" is used in the Microsoft.Office.Interop.Excel namespace. If you are trying to use the data table that we are all familiar with while importing the Microsoft.Office.Interop.Excel namespace then declare your table using its fully qualified name i.e. System.Data.DataTable

[dunfiddlin]

Imports Microsoft.Office.Interop.Excel creates ambiguities between classes with the same names at this level. Do you mean DataTable or Excel.DataTable? And how is the compiler supposed to know? Step back to Microsoft.Office.Interop and define Excel types explicitly eg. Excel.Application

[sanderson]

Thanks for the information.

I have one more small question. In this code:

If dr1.Item("Eng").ToString = "Y" Then

                        Try
                            dt3.Rows.Add.Item(0) = dr1.Item("Item")

                        Catch ex As Exception

                        End Try

                        Try
                            ' dt3.Rows.Add.Item(1) = dr1.Item("SONbr")
                            dt3.Rows.Item(1) = "test"

The last line (directly above) is wrong. If I repeat the syntax:

dt3.Rows.Add.Item(0) = dr1.Item("SONbr")

then I get a new row for every column. I have about seven columns per row. How can I control this?

For an overview, I an manually populating a data table. Everything is working except for the data format.

Thanks again!

[kebo]

you can't have a data table row that does not contain every column in the data table. It's just how it works.

[sanderson]

That's great, because this is not what I'm looking for. In the data above, if dt3.Rows.Add.Item(x) is used, I am getting the staggered rows/columns:
ROW0 ROW1 ROW2 ROW3 ROW4
1 DATA 1
2 ----- DATA 2
3 ----- ----- DATA 3
4 ----- ----- ----- DATA 4
5 ----- ----- ----- ----- DATA 5

I would like all of this to end up on one row.

Thanks

[dunfiddlin]

dt3.Rows.Add.Item(0) = dr1.Item("Item") ' means add a row with a value in column 0

One method of building a datatable from scratch ...

Dim dsTable As DataTable = New DataTable
 
 
    
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
 
        For i = 0 To 3
            dsTable.Columns.Add() ' you already did this bit to get your 7 columns
        Next
 
        
            Dim row As DataRow = dsTable.NewRow ' create a new row
 
            With row                   ' insert values into each column
                .SetField(0, "This")
                .SetField(1, "That")
                .SetField(2, "The Other")
                .SetField(3, "Whatever")
            End With
 
            dsTable.Rows.Add(row) ' add the row to the table 
       
 
    End Sub

[sanderson]

Thank you...

[Shaggy Hiker]

I'm curious about that try...catch you have in there. Is that real? What are you trying to prevent? Has dunfiddlin's answer gotten around that problem?

I really dislike empty Catch blocks. The only time they ever made sense to me was a certain problem with some UDP communication work I did a long time ago where meaningless errors were occasionally thrown.