Receiving error message: Error converting data type nvarchar to float.
I have a .NET application (SQL Server backend). I am using a SQL statement to retrieve data and then display it on my web page.
I'm working with a field that is nvarchar. I need to be able to display an N/A when there is no data for that particular field. Here's the portion of the SQL Statement that I have so far:
ISNULL(pmoApps.dbo.ss_PDMMAST.acthours, N'N/A') as SEHours
When I run it, I'm receiving the error: Error converting data type nvarchar to float. What do I need to add to it to make it display N/A.
Returns the first non-null expression among its arguments.
Bob
0
khawkins96Author Commented:
TheLearnedOne - I tried the code you listed there and I"m getting an error:
Incorrect syntax near 'IS NULL'
I haven't tried your other suggestion yet. I will do so. If you have another other suggestion regarding the code you listed and the error I"m receiving, please let me know.
Man, I always forget that IS NULL can't be used in a CASE:
Here is an example of using the ISNULL function to your benefit, without the CASE:
SELECT ISNULL(userid, 'N/A') AS userid FROM profile
The 2nd argument is the replacement string if the value is null.
Bob
0
khawkins96Author Commented:
The LearnedOne,
That's basically what I previously had:
ISNULL(pmoApps.dbo.ss_PDMMAST.acthours, N'N/A') as SEHours
I was getting an error that said it couldn't convert data type nvarchar to float. I'm working with an existing table that I didn't create and the data type for this field is nvarchar.
I need to display N/A when the field is null. That's a requirement of the user - I don't think COALESCE will work for me.
Aaah, I get it, pmoApps.dbo.ss_PDMMAST.acthours is a float, and you are trying to get a nvarchar value N'N/A'.
Do you need the float values on the front end, or could you cast to nvarchar?
Bob
0
khawkins96Author Commented:
strickdd - that brought me a lot closer to the solution! I didn't receive an error message this time and my record came up on my web page, but the field that was null was empty. It should have had N/A in it. Any ideas?
The LearnedOne - the field is nvarchar in the table. I'm trying to get it to display N/A but it says it can't convert nvarchar to float. Not sure what that message means since I didn't have a convert statement in my sql statement.
Are you absolutely sure that pmoApps.dbo.ss_PDMMAST.acthours is an nvarchar? The fact that you are storing a number (i.e. number of hours) makes that unlikely. And if it is that piece of SQL that is causing the problem then it certainly points towards pmoApps.dbo.ss_PDMMAST.acthours being a float because the isnull function will always return the type of the first parameter which means that the second parameter must be convertable to the datatype of the first parameter so that it can return the same type in all cases.
Personally, assuming that is the case, I'd return the null because then in the code you can a) format your number into a string as you desire and b) replace the null with 'N/A' thereby leaving the formating to the correct layer of code.
Otherwise if this is being used directly and you need the returned recordset to be as it will display then convert the pmoApps.dbo.ss_PDMMAST.acthours into a varchar first and e.g.
I am starting to question where the problem really is. We are looking very closely at a specific piece of an SQL statement, but what is telling you that it is the one causing the problems. The syntax and the error don't match.
Bob
0
khawkins96Author Commented:
Dale,
It worked! isnull(convert(nvarchar, pmoApps.dbo.ss_PDMMAST.acthours), 'N/A')
Thank you so much!
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
SEHours = CASE pmoApps.dbo.ss_PDMMAST.act
WHEN pmoApps.dbo.ss_PDMMAST.act
ELSE pmoApps.dbo.ss_PDMMAST.act
END