Solved

Linq To SQL order by vb.net

Posted on 2010-08-20
12
741 Views
Last Modified: 2013-11-11
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.
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

Open in new window

0
Comment
Question by:g_johnson
12 Comments
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33489446
You can use WHERE item_desc_2 IS Nothing
0
 
LVL 4

Author Comment

by:g_johnson
ID: 33489596
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

0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 33490226
Hi g_johnson;

Try and see if this works for you.

Fernando
Order By IIF(i.item_desc_2.Trim() = Nothing, "/" & i.item_desc_1.Trim(), i.item_desc_2.Trim() & "/" & i.item_desc_1.Trim()) _

Open in new window

0
 
LVL 27

Expert Comment

by:nmarun
ID: 33490286
Try this.

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

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33491285
@Arun;

Your solution looks very much as the one I posted above.

Fernando
0
 
LVL 4

Author Comment

by:g_johnson
ID: 33501255
I get the exception:

Cannot order by type 'System.Object'
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33501363
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()).ToString() _

Fernando
0
 
LVL 4

Author Comment

by:g_johnson
ID: 33501603
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?
             
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() _

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33501806
It is difficult to tell seeming I can not tell how the two tables are being joined?
0
 
LVL 4

Author Comment

by:g_johnson
ID: 33501969
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.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33502247
Do you have a small database which you can zip and upload to here? If so I will have a look.
0
 
LVL 4

Author Comment

by:g_johnson
ID: 33534405
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now