Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Linq To SQL order by vb.net

Posted on 2010-08-20
12
752 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 63

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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 63

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
 
LVL 63

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 63

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 63

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

861 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