Problem working with parameters (still)

[Serge Myrand]

Hi,

I realy need help on that one, I loose a lot of time every time I use
parameters in ASP/JET/ADO/Access 2000.
I like to work having the SQL text in the my code (for now). The
following SQL query works only when I do not change the columns name.
But I do need to change them because the application is multi-language.
The query require 2 parameters, one of them is need by the sub-query
named QBasket.

The parameters are created like that one:

set prm = cmd.CreateParameter("@prmCLIENT_NO", adChar, adParamInput,
13)
cmd.Parameters.Append prm
cmd.Parameters("@prmCLIENT_NO").Value = sClientNo


sProd = "Product" ' example of a column name

' Works find (no column name have changed)
sSQL = "SELECT i.PART_NO, i.REV_NO, i.DESCRIP " & _
"FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
b.PART_NO " & _
"WHERE i.PART_NO =?"

'Join expression not supported.
sSQL = "SELECT i.PART_NO AS " & sProd & ", i.REV_NO, i.DESCRIP " & _
"FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON " & sProd & " =
b.PART_NO " & _
"WHERE " & sProd & "=?"

' No value given for one or more required parameters.
sSQL = "SELECT i.PART_NO AS " & sProd & ", i.REV_NO, i.DESCRIP " & _
"FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
b.PART_NO " & _
"WHERE " & sProd & "=?"

' Item cannot be found in the collection corresponding to the
requested name or ordinal
sSQL = "SELECT i.PART_NO AS " & sProd & ", i.REV_NO, i.DESCRIP " & _
"FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
b.PART_NO " & _
"WHERE i.PART_NO=?"

sPartNo = "CPA0205" ' parameter used by the main query
sClientNo = "(416)654-4444" ' parameter used by QBasket sub
query


I would realy appreciate a reference showing plenty of examples for
Access 2000.
Thank you in advance
Serge

[Serge Myrand]

Hi,

The way I have found to get this query to run is adding the field twice ie:

sSQL = "SELECT i.PART_NO, i.PART_NO AS " & sProd & " i.REV_NO, i.DESCRIP "
& _
"FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
b.PART_NO " & _
"WHERE i.PART_NO =?"

After I display only the column I want (if F.Name <> PATR_NO)

Any other idea?
thank's
serge

Serge Myrand wrote:

> Hi,
>
> I realy need help on that one, I loose a lot of time every time I use
> parameters in ASP/JET/ADO/Access 2000.
> I like to work having the SQL text in the my code (for now). The
> following SQL query works only when I do not change the columns name.
> But I do need to change them because the application is multi-language.
> The query require 2 parameters, one of them is need by the sub-query
> named QBasket.
>
> The parameters are created like that one:
>
> set prm = cmd.CreateParameter("@prmCLIENT_NO", adChar, adParamInput,
> 13)
> cmd.Parameters.Append prm
> cmd.Parameters("@prmCLIENT_NO").Value = sClientNo
>
> sProd = "Product" ' example of a column name
>
> ' Works find (no column name have changed)
> sSQL = "SELECT i.PART_NO, i.REV_NO, i.DESCRIP " & _
> "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
> b.PART_NO " & _
> "WHERE i.PART_NO =?"
>
> 'Join expression not supported.
> sSQL = "SELECT i.PART_NO AS " & sProd & ", i.REV_NO, i.DESCRIP " & _
> "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON " & sProd & " =
> b.PART_NO " & _
> "WHERE " & sProd & "=?"
>
> ' No value given for one or more required parameters.
> sSQL = "SELECT i.PART_NO AS " & sProd & ", i.REV_NO, i.DESCRIP " & _
> "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
> b.PART_NO " & _
> "WHERE " & sProd & "=?"
>
> ' Item cannot be found in the collection corresponding to the
> requested name or ordinal
> sSQL = "SELECT i.PART_NO AS " & sProd & ", i.REV_NO, i.DESCRIP " & _
> "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
> b.PART_NO " & _
> "WHERE i.PART_NO=?"
>
> sPartNo = "CPA0205" ' parameter used by the main query
> sClientNo = "(416)654-4444" ' parameter used by QBasket sub
> query
>
> I would realy appreciate a reference showing plenty of examples for
> Access 2000.
> Thank you in advance
> Serge

[Bob Barrows [MVP]]

Joins are processed before the select clause, therefore you cannot use
column aliases in joins. here is a post from Joe Celko that you may find
informative:

[url]http://groups.google.com/groups?q=CELKO+%22How+SQL+works%22&hl=en&lr=&ie=UT F-8&c2coff=1&selm=c0d87ec0.0307121332.5523aae%40post ing.google.com&rnum=6[/url]

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Serge Myrand wrote:

> Hi,
>
> The way I have found to get this query to run is adding the field
> twice ie:
>
> sSQL = "SELECT i.PART_NO, i.PART_NO AS " & sProd & " i.REV_NO,
> i.DESCRIP " & _
> "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
> b.PART_NO " & _
> "WHERE i.PART_NO =?"
>

You don't need to retrieve the data twice. This will work:

sSQL = "SELECT i.PART_NO AS " & sProd & " i.REV_NO, i.DESCRIP "
& _
"FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
b.PART_NO " & _
"WHERE i.PART_NO =?"

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Serge Myrand]

Hi,

With that select statement I get the following message, This is why I
repeat the PART_NO field twice. From within Access it works (it's ODBC I
think) but from my ASP code I use ADO. Am'I correct?

Msg: Item cannot be found in the collection corresponding to the requested
name or ordinal

I use Window XP pro
Jet OLEDB 4.0 SP 8
MDAC 2.7

Serge


"Bob Barrows [MVP]" wrote:

> Serge Myrand wrote:

> > Hi,
> >
> > The way I have found to get this query to run is adding the field
> > twice ie:
> >
> > sSQL = "SELECT i.PART_NO, i.PART_NO AS " & sProd & " i.REV_NO,
> > i.DESCRIP " & _
> > "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
> > b.PART_NO " & _
> > "WHERE i.PART_NO =?"
> >

>
> You don't need to retrieve the data twice. This will work:
>
> sSQL = "SELECT i.PART_NO AS " & sProd & " i.REV_NO, i.DESCRIP "
> & _
> "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
> b.PART_NO " & _
> "WHERE i.PART_NO =?"
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

[Serge Myrand]

Hi,

Thank's I read the article, very interesting. The paragraph 'e' points exactly the problem I have. I do not know why, may by there is a bug in MDAC 2.7
or JET 4.0 sp8 but when I use a field name in the WHERE clause it does not work if that field name was 'renamed' in the SELECT using AS 'NewFieldName'

sorry for my english it's a bad day...
thank's
serge

"Bob Barrows [MVP]" wrote:

> Joins are processed before the select clause, therefore you cannot use
> column aliases in joins. here is a post from Joe Celko that you may find
> informative:
>
> [url]http://groups.google.com/groups?q=CELKO+%22How+SQL+works%22&hl=en&lr=&ie=UT F-8&c2coff=1&selm=c0d87ec0.0307121332.5523aae%40post ing.google.com&rnum=6[/url]
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Please show the line of code that gives you that message. You should not
have to return the same data twice.

Bob Barrows
Serge Myrand wrote:

> Hi,
>
> With that select statement I get the following message, This is why I
> repeat the PART_NO field twice. From within Access it works (it's
> ODBC I think) but from my ASP code I use ADO. Am'I correct?
>
> Msg: Item cannot be found in the collection corresponding to the
> requested name or ordinal
>
> I use Window XP pro
> Jet OLEDB 4.0 SP 8
> MDAC 2.7
>
> Serge
>
>
> "Bob Barrows [MVP]" wrote:
>

>> Serge Myrand wrote:

>>> Hi,
>>>
>>> The way I have found to get this query to run is adding the field
>>> twice ie:
>>>
>>> sSQL = "SELECT i.PART_NO, i.PART_NO AS " & sProd & " i.REV_NO,
>>> i.DESCRIP " & _
>>> "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO
>>> = b.PART_NO " & _
>>> "WHERE i.PART_NO =?"
>>>

>>
>> You don't need to retrieve the data twice. This will work:
>>
>> sSQL = "SELECT i.PART_NO AS " & sProd & " i.REV_NO, i.DESCRIP "
>> & _
>> "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
>> b.PART_NO " & _
>> "WHERE i.PART_NO =?"
>>
>> Bob Barrows
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Serge Myrand]

Hi,

here is the code.


sSQL = "SELECT i.PART_NO AS " & sProd & ", i.REV_NO, i.DESCRIP " & _
"FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
b.PART_NO " & _
"WHERE i.PART_NO=?"


error msg:' Item cannot be found in the collection corresponding to the
requested name or ordinal

the parameters:

set prm = cmd.CreateParameter("@prmCLIENT_NO", adChar, adParamInput, 13)
cmd.Parameters.Append prm
cmd.Parameters("@prmCLIENT_NO").Value = sClientNo

set prm = cmd.CreateParameter("@prmPART_NO", adChar, adParamInput, 22)
cmd.Parameters.Append prm
cmd.Parameters("@prmPART_NO").Value = sPartNo

the variables: sPartNo = "CPA0205" ' parameter used by the main
query
sClientNo = "(416)654-4444" ' parameter used by
QBasket subquery

thank's
serge




"Bob Barrows [MVP]" wrote:

> Please show the line of code that gives you that message. You should not
> have to return the same data twice.
>
> Bob Barrows
> Serge Myrand wrote:

> > Hi,
> >
> > With that select statement I get the following message, This is why I
> > repeat the PART_NO field twice. From within Access it works (it's
> > ODBC I think) but from my ASP code I use ADO. Am'I correct?
> >
> > Msg: Item cannot be found in the collection corresponding to the
> > requested name or ordinal
> >
> > I use Window XP pro
> > Jet OLEDB 4.0 SP 8
> > MDAC 2.7
> >
> > Serge
> >
> >
> > "Bob Barrows [MVP]" wrote:
> >

> >> Serge Myrand wrote:
> >>> Hi,
> >>>
> >>> The way I have found to get this query to run is adding the field
> >>> twice ie:
> >>>
> >>> sSQL = "SELECT i.PART_NO, i.PART_NO AS " & sProd & " i.REV_NO,
> >>> i.DESCRIP " & _
> >>> "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO
> >>> = b.PART_NO " & _
> >>> "WHERE i.PART_NO =?"
> >>>
> >>
> >> You don't need to retrieve the data twice. This will work:
> >>
> >> sSQL = "SELECT i.PART_NO AS " & sProd & " i.REV_NO, i.DESCRIP "
> >> & _
> >> "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
> >> b.PART_NO " & _
> >> "WHERE i.PART_NO =?"
> >>
> >> Bob Barrows
> >> --
> >> Microsoft MVP -- ASP/ASP.NET
> >> Please reply to the newsgroup. The email account listed in my From
> >> header is my spam trap, so I don't check it very often. You will get
> >> a quicker response by posting to the newsgroup.

>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Serge Myrand wrote:

> Hi,
>
> here is the code.
>
>
> sSQL = "SELECT i.PART_NO AS " & sProd & ", i.REV_NO, i.DESCRIP " & _
> "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
> b.PART_NO " & _
> "WHERE i.PART_NO=?"
>
>
> error msg:' Item cannot be found in the collection corresponding to
> the requested name or ordinal
>

Which line of code causes this error?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Serge Myrand]

Hi,

Excuse me! I am sorry, I had a Response.Write refering to a wrong field
name. You are correct it works.
I thought the line calling the 'execute'.

thank you
serge

"Bob Barrows [MVP]" wrote:

> Serge Myrand wrote:

> > Hi,
> >
> > here is the code.
> >
> >
> > sSQL = "SELECT i.PART_NO AS " & sProd & ", i.REV_NO, i.DESCRIP " & _
> > "FROM INVENTORY AS i LEFT JOIN QBASKET AS b ON i.PART_NO =
> > b.PART_NO " & _
> > "WHERE i.PART_NO=?"
> >
> >
> > error msg:' Item cannot be found in the collection corresponding to
> > the requested name or ordinal
> >

>
> Which line of code causes this error?
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

That's ok. Glad it's working for you now.
Bob
Serge Myrand wrote:

> Hi,
>
> Excuse me! I am sorry, I had a Response.Write refering to a wrong
> field name. You are correct it works.
> I thought the line calling the 'execute'.
>

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.