SQL Server 7 with ADO Query Help

16 Sep 2005 5:50 PM

Evan Dobkin

When running the following query against MS SQL Server 7.0:

SELECT

documentitems.manufacturerpartnumber,documentitems.customnumber01,documenthe
aders.customnumber01,documentitems.description,documentheaders.projectno
FROM DocumentHeaders INNER JOIN DocumentItems ON DocumentHeaders.ID =
DocumentItems.DocID WHERE ( DocumentHeaders.DocNo='ORLQ1017' AND
DocumentItems.CustomNumber01=2) It returns 2 records (Which is correct).
The problem that I am having is that I include in the query that I want the
DocumentHeaders.CustomNumber01 field AND the DocumentItems.CustomerNumber01
field. The field names in the two different tables are the same. The issue
that I have is that in the query results that SQL Server returns, it returns
two columns with the same name "CustomerNumber01" and "CustomerNumber01".
SQL Server is not prefixing the field names with the table names like I did
when submitting the query.

This makes it impossible for me the retrieve the separate field values. Is
there a way to get SQL Server to return the table qualified names of these
same named fields? I am using ADO with Visual Basic 6 to do the query, but I
would think in theory the query should be able to be run in MS Query
Analyzer or VB.

Thanks,

Evan

16 Sep 2005 5:58 PM

Perayu

You can specify the return column name as you wanted.
Try this:
SELECT
documentitems.manufacturerpartnumber,
documentitems.customnumber01 As DocItemCustNo,
documentheaders.customnumber01 As DocHeaderCustNo,
documentitems.description,
documentheaders.projectno
FROM DocumentHeaders INNER JOIN DocumentItems ON DocumentHeaders.ID =
DocumentItems.DocID WHERE ( DocumentHeaders.DocNo='ORLQ1017' AND
DocumentItems.CustomNumber01=2)

Perayu

Show quote

"Evan Dobkin" <evan@NOSPAMquotewerks.com> wrote in message
news:11im1kbqvicvif6@corp.supernews.com...
> When running the following query against MS SQL Server 7.0:
>
> SELECT
>
> documentitems.manufacturerpartnumber,documentitems.customnumber01,documenthe
> aders.customnumber01,documentitems.description,documentheaders.projectno
> FROM DocumentHeaders INNER JOIN DocumentItems ON DocumentHeaders.ID =
> DocumentItems.DocID WHERE ( DocumentHeaders.DocNo='ORLQ1017' AND
> DocumentItems.CustomNumber01=2) It returns 2 records (Which is correct).
> The problem that I am having is that I include in the query that I want
> the
> DocumentHeaders.CustomNumber01 field AND the
> DocumentItems.CustomerNumber01
> field. The field names in the two different tables are the same. The
> issue
> that I have is that in the query results that SQL Server returns, it
> returns
> two columns with the same name "CustomerNumber01" and "CustomerNumber01".
> SQL Server is not prefixing the field names with the table names like I
> did
> when submitting the query.
>
> This makes it impossible for me the retrieve the separate field values.
> Is
> there a way to get SQL Server to return the table qualified names of these
> same named fields? I am using ADO with Visual Basic 6 to do the query, but
> I
> would think in theory the query should be able to be run in MS Query
> Analyzer or VB.
>
> Thanks,
>
> Evan
>
>

16 Sep 2005 6:19 PM

Evan Dobkin

Perayu,

Thanks for the quick response. In the context of the original post how can I
get the query to return the prefix to the field name? So I can get it to
return the documentitems. prefix to the field name.

TIA,

Evan

16 Sep 2005 6:34 PM

Bob Barrows [MVP]

Evan Dobkin wrote:

> Perayu,
>
> Thanks for the quick response. In the context of the original post
> how can I get the query to return the prefix to the field name? So I
> can get it to return the documentitems. prefix to the field name.
>
> TIA,
>
> Evan

You can't.
You must rewrite your query to supply column aliases as perayu suggested in
his reply.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

16 Sep 2005 6:43 PM

Evan Dobkin

Bob,

Thanks for confirming.

Regards,

Evan