?
Solved

Linq To SQL order by vb.net

Posted on 2010-08-20
12
Medium Priority
?
771 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 64

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 64

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 64

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 64

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 64

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

612 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