What is the correct syntax of using "ISNULL" inside ASP.NET code?

Question

I'm trying to execute this chunk of code:

public List<CustomerCaseSearch> FindCustomerInfo(string searchCondition)
{
    var Query = "SELECT  CustomerProfile.CustomerID,CustomerProfile.CustomerName,    isNull(k.CustomerSince, '''') as CustomerSince, isNull(k.CustomerStatus,'''') as CustomerStatus," +
                "CustomerProfile.CustomerType, CustomerCase.CaseID" +
                " FROM CustomerProfile INNER JOIN" +
                " CustomerCase ON CustomerProfile.CustomerID = CustomerCase.CustomerID "+ searchCondition;

    var customerCaseSearch = _CustomerCaseSearchRepository.ExecuteQueryData<CustomerCaseSearch>(@Query);
    List<CustomerCaseSearch> aList = customerCaseSearch.ToList();
    return aList;
}

And I keep getting this exception message:

SQLException was unhandled by user code. 
Incorrect syntax near 'isNull'

I need to know, what is the correct syntax of using isNull inside an ASP.NET code where I need to put SQL Query?

Please note that I am a newby to professional development so my knowledge is quite low. Kind help anticipated and will be appreciated.

The syntax seems correct, what I don't see in your statement is aliasing anything to be k. — tpeczek
– tpeczek, Commented Nov 26, 2013 at 13:48
ISNULL is a valid MSSQL function, problem is something else. — Kuzgun
– Kuzgun, Commented Nov 26, 2013 at 13:48

Rowland Shaw · Accepted Answer · 2013-11-26 13:51:29Z

3

Your query comes out as:

SELECT      CustomerProfile.CustomerID,
            CustomerProfile.CustomerName,    
            isNull(k.CustomerSince, '''') as CustomerSince, 
            isNull(k.CustomerStatus,'''') as CustomerStatus,
            CustomerProfile.CustomerType, CustomerCase.CaseID 

FROM        CustomerProfile 

INNER JOIN  CustomerCase 
    ON      CustomerProfile.CustomerID = CustomerCase.CustomerID

Your calls to ISNULL refer to a table with alias k, but there is no table with that alias in your query.

It's worth noting that you've asked it to return a value that is a single apostrophe, if the value were NULL, so you may want to change that as well

answered Nov 26, 2013 at 13:51

Rowland Shaw

38.2k15 gold badges104 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Choudhury Saadmaan Mahmid Over a year ago

Ok I edited my code as per your instruction, but still getting the same error. :/

Rowland Shaw Over a year ago

what is the value of searchCondition?

Choudhury Saadmaan Mahmid Over a year ago

I need to send only either of two values: open or close. Any one of them will be sent to searchCondition. I'm sending this value from a javascript file where I defined the query string.

gzaxx · Accepted Answer · 2013-11-26 13:54:07Z

1

You are using table alias k that is nowhere found in your query that is why it is failing.

I reformatted it so it is easier to read, below is proper query:

SELECT cp.CustomerID, cp.CustomerName, isNull(cp.CustomerSince, '') as CustomerSince,
       isNull(cp.CustomerStatus, '') as CustomerStatus, cp.CustomerType, cc.CaseID
FROM CustomerProfile cp
INNER JOIN CustomerCase cc ON cp.CustomerID = cc.CustomerID

then you have to add your search string to the end.

edited Nov 26, 2013 at 13:54

answered Nov 26, 2013 at 13:48

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

Comments

Ringo · Accepted Answer · 2013-11-26 14:21:17Z

0

may be you missing a space:

var Query = "SELECT  CustomerProfile.CustomerID,CustomerProfile.CustomerName,    isNull(k.CustomerSince, '''') as CustomerSince, isNull(k.CustomerStatus,'''') as CustomerStatus," +
                " CustomerProfile.CustomerType, CustomerCase.CaseID" +
                " FROM CustomerProfile INNER JOIN" +
                " CustomerCase ON CustomerProfile.CustomerID = CustomerCase.CustomerID "+ searchCondition;

edited Nov 26, 2013 at 14:21

answered Nov 26, 2013 at 13:49

Ringo

3,9973 gold badges26 silver badges39 bronze badges

4 Comments

Choudhury Saadmaan Mahmid Over a year ago

No I still keep getting errors. I corrected it, used '', but still the exception is thrown.

Ringo Over a year ago

So please tell me is k alias for CustomerProfile?

Choudhury Saadmaan Mahmid Over a year ago

No. That was a mistake. I wanted to use k as alias, but later decided not to use any alias, forgot to remove that k. :) :)

Ringo Over a year ago

please try this answer again without any alias :D. I think it work now

Collectives™ on Stack Overflow

What is the correct syntax of using "ISNULL" inside ASP.NET code?

3 Answers 3

3 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related