Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
Course Of The Month
11 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Receiving error message: Error converting data type nvarchar to float.
Posted on 2006-11-16
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_PDMM
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.
Thanks!
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_PDMM
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.
Thanks!
[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
-
6
4-
2
- +3
16 Comments
Can you try putting this on your SQL:
SEHours = CASE pmoApps.dbo.ss_PDMMAST.act
WHEN pmoApps.dbo.ss_PDMMAST.act
ELSE pmoApps.dbo.ss_PDMMAST.act
END
SEHours = CASE pmoApps.dbo.ss_PDMMAST.act
WHEN pmoApps.dbo.ss_PDMMAST.act
ELSE pmoApps.dbo.ss_PDMMAST.act
END
Thanks for the suggestion. I tried it and I'm getting another error message that says: Incorrect syntax near 'ISNULL'
Basically you cannot mix datatypes in this way. If there is data it's a float, it there is not it's a string.
You'll need to catch this is the place that displays it not at the source. The exact method you choose depends on what you're doing with the data.
You'll need to catch this is the place that displays it not at the source. The exact method you choose depends on what you're doing with the data.
Sorry, can you try this:
SEHours = CASE pmoApps.dbo.ss_PDMMAST.act
WHEN ISNULL(pmoApps.dbo.ss_PDMM
ELSE pmoApps.dbo.ss_PDMMAST.act
END
SEHours = CASE pmoApps.dbo.ss_PDMMAST.act
WHEN ISNULL(pmoApps.dbo.ss_PDMM
ELSE pmoApps.dbo.ss_PDMMAST.act
END
Sorry, you sid the datatype is an nvarchar, is this what you are doing?
select ISNULL(pmoApps.dbo.ss_PDMM
select ISNULL(pmoApps.dbo.ss_PDMM
kraffay,
I tried that and received a new error: The isnull function requires 2 arguments
ethoths - this is what I was working with: ISNULL(pmoApps.dbo.ss_PDMM
I tried that and received a new error: The isnull function requires 2 arguments
ethoths - this is what I was working with: ISNULL(pmoApps.dbo.ss_PDMM
Active 3 days ago
That previous attempt was close, so try this:
SEHours = CASE pmoApps.dbo.ss_PDMMAST.act
WHEN pmoApps.dbo.ss_PDMMAST.act
ELSE pmoApps.dbo.ss_PDMMAST.act
END
Another Transact-SQL statement that you can use is COALESCE:
COALESCE
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_9dph.asp
Returns the first non-null expression among its arguments.
Bob
SEHours = CASE pmoApps.dbo.ss_PDMMAST.act
WHEN pmoApps.dbo.ss_PDMMAST.act
ELSE pmoApps.dbo.ss_PDMMAST.act
END
Another Transact-SQL statement that you can use is COALESCE:
COALESCE
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_9dph.asp
Returns the first non-null expression among its arguments.
Bob
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.
Thanks!
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.
Thanks!
Active 3 days ago
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
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
The LearnedOne,
That's basically what I previously had:
ISNULL(pmoApps.dbo.ss_PDMM
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.
That's basically what I previously had:
ISNULL(pmoApps.dbo.ss_PDMM
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.
try this:
SEHours = case pmoApps.dbo.ss_PDMMAST.act
This will not work in an order by though
SEHours = case pmoApps.dbo.ss_PDMMAST.act
This will not work in an order by though
Active 3 days ago
Aaah, I get it, pmoApps.dbo.ss_PDMMAST.act
Do you need the float values on the front end, or could you cast to nvarchar?
Bob
Do you need the float values on the front end, or could you cast to nvarchar?
Bob
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.
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.act
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.act
isnull(convert(nvarchar, pmoApps.dbo.ss_PDMMAST.act
And if nothing else trying this convert will determine whether the problem lies here or elsewhere.
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.act
isnull(convert(nvarchar, pmoApps.dbo.ss_PDMMAST.act
And if nothing else trying this convert will determine whether the problem lies here or elsewhere.
Featured Post
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method.
So in this a…
This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month11 days, 1 hour left to enroll
628 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.