Access Table and ColumnNames

  • Thread starter Thread starter Peter W Johnson
  • Start date Start date
P

Peter W Johnson

Hi Guys,

I have a VB .NET application that uses Access as the database. I
have one query (qryInvoiceNumbers) which creates a join on the ColumnName
InvoiceNumber in each of three tables using the same columnname
(tblTaxInvoice, tblPayments and tblTransactions). The tblTaxInvoice table
contains invoicenumbers in all rows, the other two tables do not.

As you can see in the following code I can differentiate between the
columnname in the different tables of the query in the select statement but
not in the setting of the variable InvoiceNumber. If is use
dsMasterInvoice.Tables(0).Rows(i)("tblTaxInvoice.InvoiceNumber") I get an
error saying that tblTaxInvoice.InvoiceNumber is not part of the dataset
MasterInvoice.

I do not want to change the table column names as that will require
significant modification to the main code.

Code follows:

mysql = "SELECT * FROM qryInvoiceNumbers WHERE
Left(tblTaxInvoice.InvoiceNumber, 2) = 'AP' ORDER BY
tblTaxInvoice.InvoiceNumber"
daMasterInvoice = New Odbc.OdbcDataAdapter(mysql, myconnection)
dsMasterInvoice = New DataSet
daMasterInvoice.Fill(dsMasterInvoice, "MasterInvoice")
Dim NoOfInvoices As Integer
NoOfInvoices = dsMasterInvoice.Tables(0).Rows.Count
Dim InvoiceNumber As String

For i = 0 To NoOfInvoices - 1

InvoiceNumber =
dsMasterInvoice.Tables(0).Rows(i)("InvoiceNumber")

'blah blah blah

Next

Code finishes:

Any ideas on how I can overcome this? Can I temporarily change the column
name programmatically?

Cheers

Peter
 
Hi Peter,

The datatable you retrieve is just the the result of your resultset, when
you see what you want in the query analyzer (or whatever tool you use to
create the SQL script) then you get that back as datatable.

It even set it in a strongly typed datatable, however then not in the
strongly typed created columns. (For you ask this yourself, you are not
using a strongly typed dataset)

Cor
 
Peter W Johnson said:
Hi Guys,

I have a VB .NET application that uses Access as the
database. I have one query (qryInvoiceNumbers) which creates a join
on the ColumnName InvoiceNumber in each of three tables using the
same columnname
(tblTaxInvoice, tblPayments and tblTransactions). The tblTaxInvoice
table contains invoicenumbers in all rows, the other two tables do
not.

As you can see in the following code I can differentiate between the
columnname in the different tables of the query in the select
statement but not in the setting of the variable InvoiceNumber. If
is use
dsMasterInvoice.Tables(0).Rows(i)("tblTaxInvoice.InvoiceNumber") I
get an error saying that tblTaxInvoice.InvoiceNumber is not part of
the dataset MasterInvoice.

I do not want to change the table column names as that will require
significant modification to the main code.
Click to expand...

In addition to Cor...

If you want to find out the colum names, use

for each col as datacolumn in dsMasterInvoice.Tables(0).columns
debug.writeline (col.columnname)
next

Then you see the names that can be used as the column name when using

dsMasterInvoice.Tables(0).Rows(i)(<the column name>)

Armin
 
Armin,

I tried that and all came up with the same name - "InvoiceNumber".

I have since used the "SELECT tblTaxInvoice.IvoiceNumber as
TaxInvoiceNumber, ......... FROM qryInvoiceNumbers WHERE " and all now works
as I needed it.

Thanks

Peter.
 
Back
Top