g_johnson
asked on
Linq To SQL order by vb.net
In the attached code, if item_desc_2 is null, I don't get the correct results. How do I account for that?
Is there anything in linq equivalent to isnull(item_desc_2,'') ?
Thanks.
Is there anything in linq equivalent to isnull(item_desc_2,'') ?
Thanks.
Dim VchrItems = From r _
In ap3.imrechst_sqls _
Join i In ap3.imitmidx_sqls _
On r.item_no Equals i.item_no _
Order By i.item_desc_2.Trim() & "/" & i.item_desc_1.Trim() _
Where r.pack_no = f.billNo.Text _
Select r.user_def_1, r.ord_no, r.item_no, i.item_desc_1, i.item_desc_2, r.loc, r.vend_item_no, r.qty_ordered, _
r.qty_received, r.qty_rejected, r.rej_reason_cd, r.actual_cost, r.extra_10, r.extra_11, r.extra_12, _
r.ctl_no, r.vchr_dt, r.vchr_no, r.ID
You can use WHERE item_desc_2 IS Nothing
ASKER
what I need is something to concatenate desc1 and 2 when 2 is null.
In classic sql it would look like isnull(i.item_desc_2,'') + item_desc_1
In classic sql it would look like isnull(i.item_desc_2,'') + item_desc_1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this.
Arun
Arun
Dim VchrItems = From r _
In ap3.imrechst_sqls _
Join i In ap3.imitmidx_sqls _
On r.item_no Equals i.item_no _
Order By IIF(i.item_desc_2.Trim() = Nothing, "/" & i.item_desc_1.Trim(), i.item_desc_2.Trim() & "/" & i.item_desc_1.Trim())
Where r.pack_no = f.billNo.Text _
Select r.user_def_1, r.ord_no, r.item_no, IIF(i.item_desc_2.Trim() = Nothing, "/" & i.item_desc_1.Trim(), i.item_desc_2.Trim() & "/" & i.item_desc_1.Trim()), r.loc, r.vend_item_no, r.qty_ordered, r.qty_received, r.qty_rejected, r.rej_reason_cd, r.actual_cost, r.extra_10, r.extra_11, r.extra_12, r.ctl_no, r.vchr_dt, r.vchr_no, r.ID
@Arun;
Your solution looks very much as the one I posted above.
Fernando
Your solution looks very much as the one I posted above.
Fernando
ASKER
I get the exception:
Cannot order by type 'System.Object'
Cannot order by type 'System.Object'
Hi g_johnson;
That sounds correct because IIF( ... ) retuens an object, append the ToString() methiod to IIF as shown below.
Order By IIF(i.item_desc_2.Trim() = Nothing, "/" & i.item_desc_1.Trim(), i.item_desc_2.Trim() & "/" & i.item_desc_1.Trim()).ToSt ring() _
Fernando
That sounds correct because IIF( ... ) retuens an object, append the ToString() methiod to IIF as shown below.
Order By IIF(i.item_desc_2.Trim() = Nothing, "/" & i.item_desc_1.Trim(), i.item_desc_2.Trim() & "/" & i.item_desc_1.Trim()).ToSt
Fernando
ASKER
My code now looks like the attached, and my data looks like this:
item_desc_2 item_desc_1
NULL BOLT
16", Women's Special Frame, XLR Aluminum
NULL Nut
but my sort comes out
Nut
BOLT
Nut
16" ...
16" ...
Doesn't make sense, does it?
item_desc_2 item_desc_1
NULL BOLT
16", Women's Special Frame, XLR Aluminum
NULL Nut
but my sort comes out
Nut
BOLT
Nut
16" ...
16" ...
Doesn't make sense, does it?
Dim VchrItems = From r _
In ap3.imrechst_sqls _
Join i In ap3.imitmidx_sqls _
On r.item_no Equals i.item_no _
Order By IIf(i.item_desc_2.Trim() = Nothing, i.item_desc_1.Trim(), i.item_desc_2.Trim() & "/" & i.item_desc_1.Trim()).ToString() _
Where r.pack_no = f.billNo.Text _
Select r.user_def_1, r.ord_no, r.item_no, i.item_desc_1, i.item_desc_2, r.loc, r.vend_item_no, r.qty_ordered, _
r.qty_received, r.qty_rejected, r.rej_reason_cd, r.actual_cost, r.extra_10, r.extra_11, r.extra_12, _
r.ctl_no, r.vchr_dt, r.vchr_no, r.ID
'Order By i.item_desc_2.Trim() & "/" & i.item_desc_1.Trim() _
It is difficult to tell seeming I can not tell how the two tables are being joined?
ASKER
I don't know if this is enough info or not, but imrechst_sql contains the item_no field, bill_no and of course other information. There will be a many to one relationship on item_no that I am joining with. item_no is never null in either table, but bill_no can be null in imrechst (and does not appear in imitmidx_sql. So that explains the join column and the where column. If there is anything else I can provide to help so it, I would be happy to.
Do you have a small database which you can zip and upload to here? If so I will have a look.
ASKER
In spite of the efforts, I don't know if I have the correct answer or not. Nothing seems to work. Because of the proprietary nature of the data, I cannot upload a database. However, I am going to try to find time in the next couple of days to put a scenario together with limited (fake) data. Thanks for your efforts so far. Please leave the quesiton open if you can.