ISNULL function within select command of formview

Question

This is part of my select statement within an formview which works fine till it hits a null value during the update process.

 (SELECT TOP 1 F.tel_id FROM TELEPHONE as F where F.tel_type_id = 3 AND F.client_id = @id
 ORDER BY sort_no ) AS faxid

so i tried using the ISNULL function in the following way but it throws error. how can it be done?

ISNULL((SELECT TOP 1 F.tel_id FROM TELEPHONE as F where F.tel_type_id = 3 AND F.client_id= @id ORDER BY sort_no ) AS faxid ,0) AS faxid

I "like it" when people say "it throws error". Please tell us what error was thrown. — Tim Schmelter, Nov 29 '11 at 11:10

score 10 · Accepted Answer · answered Nov 29 '11 at 13:32

10

I think I've found the cause of the problem. And if that is really the one then I'm almost certain that someone else would have spotted it sooner than I did if you had formatted your second code snippet more nicely.

So, here goes your code snippet, slightly reformatted:

ISNULL(
  (
    SELECT TOP 1 F.tel_id
    FROM TELEPHONE as F
    where F.tel_type_id = 3
      AND F.client_id= @id
    ORDER BY sort_no
  ) AS faxid,
  0
) AS faxid

The highlighted part, the AS fixed bit immediately after the subquery, is erroneous, it just shouldn't be there. Probably you just overlooked it.

answered Nov 29 '11 at 13:32

Andriy M

76,112
17
94
154

exactly, as i said to the other comment i made two alias :) thanx for rectifying!! – debutante Nov 29 '11 at 14:05
Oh, I'm sorry, I didn't read the comments carefully. You never said explicitly about the two aliases but your corrected code in one of the comments shows that you had already found the problem by the time I posted my answer. You should have posted that either as an answer or at least as an update to your question so it would be more obvious for others to see that the problem was solved. In any event, congratulations on figuring that out! :) – Andriy M Nov 29 '11 at 14:16
hi andriy...i tried to post it as an answer but the site didn't allow me to post as "answer" to my own question within like 8 hrs. anyway thx for sticking with me !! – debutante Nov 29 '11 at 14:30

score 2 · Answer 2 · answered Nov 29 '11 at 11:09

2

Your ISNULL needs to be around F.tel_id

Try

SELECT TOP 1 ISNULL(F.tel_id, 0) FROM TELEPHONE as F where F.tel_type_id = 3 AND F.client_id = @id ORDER BY sort_no

answered Nov 29 '11 at 11:09

Ash Burlaczenko

24,778
15
68
99

this doesnt change the null value to zero when i executed in the sql query. but i tried this and it works fine!! ISNULL((SELECT TOP 1 F.tel_id FROM TELEPHONE as F where F.tel_type_id = 3 AND F.client_id = 1 ORDER BY sort_no ),0)AS faxid thanx for the response! – debutante Nov 29 '11 at 11:30
@debutante, you need to give us some more information then. What is your error, and what is your update command? – Ash Burlaczenko Nov 29 '11 at 11:34
My error was when the selected value returned NULL i couldn't convert it to int in the code behind file. so i need to replace the null value by zero. and as i said the above code works fine! thanx again! – debutante Nov 29 '11 at 11:55

score 0 · Answer 3 · answered Nov 29 '11 at 11:10

0

You can use the COALESCE function to return something other than null. So when your query executes and has a value it is returned else the value you specify is returned.

answered Nov 29 '11 at 11:10

Rajesh

7,766
5
22
35

ISNULL function within select command of formview

3 Answers3