1 Attachment(s)
Adding a reference to Microsoft.Office.Interop.Excel disables New DataTables
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.
Code:
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.
Re: Adding a reference to Microsoft.Office.Interop.Excel disables New DataTables
"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
Re: Adding a reference to Microsoft.Office.Interop.Excel disables New DataTables
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
Re: Adding a reference to Microsoft.Office.Interop.Excel disables New DataTables
Thanks for the information.
I have one more small question. In this code:
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:
Code:
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!
Re: Adding a reference to Microsoft.Office.Interop.Excel disables New DataTables
you can't have a data table row that does not contain every column in the data table. It's just how it works.
Re: Adding a reference to Microsoft.Office.Interop.Excel disables New DataTables
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
Re: Adding a reference to Microsoft.Office.Interop.Excel disables New DataTables
vb.net Code:
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 ...
vb.net Code:
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
Re: Adding a reference to Microsoft.Office.Interop.Excel disables New DataTables
Re: Adding a reference to Microsoft.Office.Interop.Excel disables New DataTables
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.