Bind variable in asp

[Hermes]

Hi all,

I'm using bind variables in sql in some asp pages, but something strange is
happening with a few of the pages.
I'm trying to add a bind variable to do an arrange and order of the output.
When I run the query it does not do the arrange and order, but if I type it
in how the bind sends it, it works. If I do a
response.write( cmd("b_arr").va lue ) it displays the correct information for
the arrange and order. In some other pages I do exactly the same and it
works. Has anyone come across something like this before?

Thanks in advance,
Hermes

Set b_arr = cmd.CreateParam eter("b_arr", adVarChar, adParamInput,50 )
cmd.Parameters. Append b_arr
cmd("b_arr").va lue = p_arrange & " " & p_order

strsql = "select cons.consignmen t_no, "
strsql = strsql & " cons.internal_c on_no, "
strsql = strsql & " cons.item_count , "
strsql = strsql & " cons.declared_w eight, "
strsql = strsql & " cons.item_type_ code, "
strsql = strsql & " cons.updated_by , "
strsql = strsql & " acc.code as acc_code, "
strsql = strsql & " acc.name as acc_name, "
strsql = strsql & " ser.name as service, "
strsql = strsql & " bch_col.name as collection_bran ch, "
strsql = strsql & " bch_col.code as collection_bran ch_code, "
strsql = strsql & " to_char(cons.ac tual_collection _date, 'DD/MM/YYYY') as
collection_date , "
strsql = strsql & " bch_del.name as delivery_branch , "
strsql = strsql & " bch_del.code as delivery_branch _code, "
strsql = strsql & " to_char(cons.de fault_delivery_ date, 'DD/MM/YYYY') as
delivery_date "
strsql = strsql & "from consignments cons "
strsql = strsql & " join accounts acc on acc.id = cons.account_id _for "
strsql = strsql & " left outer join services ser on ser.code =
cons.service_co de_current "
strsql = strsql & " left outer join branches bch_col on bch_col.code =
cons.branch_cod e_collection "
strsql = strsql & " left outer join branches bch_del on bch_del.code =
cons.branch_cod e_delivery "
strsql = strsql & "where cons.id is not null "
strsql = strsql & "order by ? ;"

[Patrice]

What if you run the same query directly instead if using a parameter ? Does
it work then ?

Be aware also of sorting. I noticed you convert some dates to chars in your
query. Keep in mind that sorting a date or the text of a date is not the
same...

For now it would be to find out if hti s has somethign to do with the use of
parameters (which I rarely saw in an order by clause) or if this is related
to something else.

Patrice
--

"Hermes" <no****@nospam. com> a écrit dans le message de
news:co******** **@sparta.btint ernet.com...

Hi all,

I'm using bind variables in sql in some asp pages, but something strange is happening with a few of the pages.
I'm trying to add a bind variable to do an arrange and order of the output. When I run the query it does not do the arrange and order, but if I type it in how the bind sends it, it works. If I do a
response.write( cmd("b_arr").va lue ) it displays the correct information for the arrange and order. In some other pages I do exactly the same and it
works. Has anyone come across something like this before?

Thanks in advance,
Hermes

Set b_arr = cmd.CreateParam eter("b_arr", adVarChar, adParamInput,50 )
cmd.Parameters. Append b_arr
cmd("b_arr").va lue = p_arrange & " " & p_order

strsql = "select cons.consignmen t_no, "
strsql = strsql & " cons.internal_c on_no, "
strsql = strsql & " cons.item_count , "
strsql = strsql & " cons.declared_w eight, "
strsql = strsql & " cons.item_type_ code, "
strsql = strsql & " cons.updated_by , "
strsql = strsql & " acc.code as acc_code, "
strsql = strsql & " acc.name as acc_name, "
strsql = strsql & " ser.name as service, "
strsql = strsql & " bch_col.name as collection_bran ch, "
strsql = strsql & " bch_col.code as collection_bran ch_code, "
strsql = strsql & " to_char(cons.ac tual_collection _date, 'DD/MM/YYYY') as
collection_date , "
strsql = strsql & " bch_del.name as delivery_branch , "
strsql = strsql & " bch_del.code as delivery_branch _code, "
strsql = strsql & " to_char(cons.de fault_delivery_ date, 'DD/MM/YYYY') as
delivery_date "
strsql = strsql & "from consignments cons "
strsql = strsql & " join accounts acc on acc.id = cons.account_id _for "
strsql = strsql & " left outer join services ser on ser.code =
cons.service_co de_current "
strsql = strsql & " left outer join branches bch_col on bch_col.code =
cons.branch_cod e_collection "
strsql = strsql & " left outer join branches bch_del on bch_del.code =
cons.branch_cod e_delivery "
strsql = strsql & "where cons.id is not null "
strsql = strsql & "order by ? ;"