?
Solved

Linq To SQL order by vb.net

Posted on 2010-08-20
12
Medium Priority
?
762 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 2000 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 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