RE: Using queries in a table adaptor?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Let me explain father:
The first I had add a table adaptor to an existing dataset call
productDataSet.xsd (using the graphical editor). This adaptor looks at a
table in an Access database connected to the dataset. When added a query was
added that filled the complete table. The dataset, table-adaptor, and
associated binding-source are placed on a form (a MDI form, this should not
make any difference); the table is filled when the form is loaded.
I then added the two queries shown in the original message. The first query
returns a 5 column table with anywhere from between 0 and 5 rows; each column
has an aliases to provide column headers. The second query returns 3 derived
columns with an aliases and returns one row. These both only return one
table and are simple queries on a simple table.
The question is:
How do i use the columns and aliases in the queries and only those columns?
What seems to be returned is the complete underlying table columns and the
rows requested.
I assumed that the dataset is a typed data set, so I should be able to see
the columns in the IntelliSense list in the code editor, which I do not. In
addition, they are not to be found in the Autos or Local windows when a set a
brake point after executing the code shown in the original message.
On the first query, I have worked it out so I can use it with the flex grid
form ComponentOne. I am use the flex grid because of some unique features I
wanted.
The real problem is the second query:
I want the max and min to use in code. It looks like the only way I can use
them is assuming the number of columns in the original table and taking the
next three in a generic ItemArray. This is very poor programming techniques
and tightly couples the database to the program, which I do not want.
Does this clarify what I want?
Thank you for your help.

--
Mike Reed


"Michael D. Reed" wrote:

I have a data set called “ProductDataSet.xsd” that include several tables one
called [Drive Packages] with 14 columns and several rows of data. The table
adaptor attached to this is a table adapter called “Drive Package
TableAdapter”. To this table adapter I have added several queries:

One called list Data
SELECT [Drive Package ID], [Drive Nomenclature] AS Drive,
[HP Max] AS HP, [RPM Max] AS [Max RPM],
[RPM Min] AS [Min RPM]
FROM [Drive Package]
WHERE ([Product Line ID] = ProductLineID) AND ([Unit Size] = UnitSize)
ORDER BY [Drive Nomenclature]
The two procedures are called FillListData and GetByListdata
When I excite the following statement
DataTable ListData =
this.drive_PackageTableAdapter.GetListData(ProductLineID, UnitSize);
The columns I have aliased above are now where to be seen. For this quarry
I did not need them explicitly because I bound them to a C1 flex grid.
Although, I did have to make five columns in the right order and names. Note
I the correct amount of rows for the where clause.

The second one Max Mins
SELECT MAX([HP Max]) AS MaxHPMax, MIN([RPM Min]) AS MinRPMMin,
MAX([RPM Max]) AS MaxRPMMax
FROM [Drive Package]
WHERE (([Product Line ID] = ProductLineID) AND ([Unit Size] =
UnitSize));
The two procedures are called FillMaxMins and GetMaxMins.
Again exciting
DataTable dataMaxMins = this.drive_PackageTableAdapter.GetMaxMins(2, "086");
The aliases are no where to be seen. This one returns only one record and
it did. So I tried:
object[] MaxMins = new object[3];
MaxMins = this.drive_PackageTableAdapter.GetMaxMins(2,
"086").Rows[0].ItemArray;
And I can see the data I want but it is tacken on the end of an array of 14
items. One for each column in the orignal table and the there ones above.

The question is how do I get the coliums in the quary and only the colums in
the quary? These are not complacte quaries and if I can not use the dataset
for these what good is it.

--
Mike Reed
.

Relevant Pages

  • Re: Using queries in a table adaptor?
    ... When added a query ... I then added the two queries shown in the original message. ... columns with an aliases and returns one row. ... I did not need them explicitly because I bound them to a C1 flex grid. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Column Alias in MS Query
    ... Using a Field Alias in Query Does Not Work with Some Third-Party ... recently posted some northwind queries which use aliases ... re-write as stored procedures on the server (use many ...
    (microsoft.public.excel.programming)
  • Re: Slow Query
    ... aliases are not a problem. ... I'm not familiar with Pass-through queries. ... I have the ability to query ...
    (microsoft.public.access.queries)
  • Re: SQL too long?
    ... I haven't done a thorough analysis here, but I suspect that the number of characters needed to express the SQL may not be as important as the number of aggregate functions like Sumand Countthat you invoke along the way. ... Although what you now have may be correct, assuming no simian behavior on the part of Access, splitting up the Query may also make it easier to debug if you later encounter any questions concerning its correctness, or if you need to revise it in some way. ... For example, in my copy of Access 2000, it says that the "Number of characters in an SQL statement" has a maximum value of "approximately 64,000", so you probably have some wiggle room there, even without using abbreviated aliases for some of the names. ... tblARTrn01.invno) INNER JOIN tblARMst01 ON tblDUPSFreight.invno = tblARMst01. ...
    (microsoft.public.access.queries)
  • Re: SQL problem...
    ... > The following query works fine on SQL Server... ... Table aliases work normally in Access. ... AccountTypes is aliased as ADebt, and at the same time there ... Columns IDAccounts and IDAccountCred (and IDAccounts ...
    (microsoft.public.access.queries)