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").value ) 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.CreateParameter("b_arr", adVarChar, adParamInput,50)
cmd.Parameters.Append b_arr
cmd("b_arr").value = p_arrange & " " & p_order

strsql = "select cons.consignment_no, "
strsql = strsql & " cons.internal_con_no, "
strsql = strsql & " cons.item_count, "
strsql = strsql & " cons.declared_weight, "
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_branch, "
strsql = strsql & " bch_col.code as collection_branch_code, "
strsql = strsql & " to_char(cons.actual_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.default_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_code_current "
strsql = strsql & " left outer join branches bch_col on bch_col.code =
cons.branch_code_collection "
strsql = strsql & " left outer join branches bch_del on bch_del.code =
cons.branch_code_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.btinternet.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").value ) 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.CreateParameter("b_arr", adVarChar, adParamInput,50)
cmd.Parameters.Append b_arr
cmd("b_arr").value = p_arrange & " " & p_order

strsql = "select cons.consignment_no, "
strsql = strsql & " cons.internal_con_no, "
strsql = strsql & " cons.item_count, "
strsql = strsql & " cons.declared_weight, "
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_branch, "
strsql = strsql & " bch_col.code as collection_branch_code, "
strsql = strsql & " to_char(cons.actual_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.default_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_code_current "
strsql = strsql & " left outer join branches bch_col on bch_col.code =
cons.branch_code_collection "
strsql = strsql & " left outer join branches bch_del on bch_del.code =
cons.branch_code_delivery "
strsql = strsql & "where cons.id is not null "
strsql = strsql & "order by ? ;"