Solved

Linq To SQL order by vb.net

Posted on 2010-08-20
12
755 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
[X]
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
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

733 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