Solved

Receiving error message: Error converting data type nvarchar to float.

Posted on 2006-11-16
16
539 Views
Last Modified: 2008-01-09
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.

Thanks!

0
Comment
Question by:khawkins96
  • 6
  • 4
  • 2
  • +3
16 Comments
 
LVL 9

Expert Comment

by:kraffay
Comment Utility
Can you try putting this on your SQL:

SEHours = CASE pmoApps.dbo.ss_PDMMAST.acthours
         WHEN pmoApps.dbo.ss_PDMMAST.acthours ISNULL 'N/A'
          ELSE pmoApps.dbo.ss_PDMMAST.acthours
        END
0
 

Author Comment

by:khawkins96
Comment Utility
Thanks for the suggestion. I tried it and I'm getting another error message that says: Incorrect syntax near 'ISNULL'
0
 
LVL 11

Expert Comment

by:ethoths
Comment Utility
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.
0
 
LVL 9

Expert Comment

by:kraffay
Comment Utility
Sorry, can you try this:

SEHours = CASE pmoApps.dbo.ss_PDMMAST.acthours
         WHEN ISNULL(pmoApps.dbo.ss_PDMMAST.acthours)  'N/A'
          ELSE pmoApps.dbo.ss_PDMMAST.acthours
        END
0
 
LVL 11

Expert Comment

by:ethoths
Comment Utility
Sorry, you sid the datatype is an nvarchar,  is this what you are doing?

select ISNULL(pmoApps.dbo.ss_PDMMAST.acthours, N'N/A') as SEHours from whatever
0
 

Author Comment

by:khawkins96
Comment Utility
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_PDMMAST.acthours, N'N/A') as SEHours
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
That previous attempt was close, so try this:

       SEHours = CASE pmoApps.dbo.ss_PDMMAST.acthours
         WHEN pmoApps.dbo.ss_PDMMAST.acthours IS NULL 'N/A'
          ELSE pmoApps.dbo.ss_PDMMAST.acthours
        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
0
 

Author Comment

by:khawkins96
Comment Utility
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!
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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
 

Author Comment

by:khawkins96
Comment Utility
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.
0
 
LVL 28

Expert Comment

by:strickdd
Comment Utility
try this:

SEHours = case pmoApps.dbo.ss_PDMMAST.acthours when null then 'N/A' else pmoApps.dbo.ss_PDMMAST.acthours end


This will not work in an order by though
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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
 

Author Comment

by:khawkins96
Comment Utility
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.
0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 350 total points
Comment Utility
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.

isnull(convert(nvarchar, pmoApps.dbo.ss_PDMMAST.acthours), 'N/A')

And if nothing else trying this convert will determine whether the problem lies here or elsewhere.
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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
 

Author Comment

by:khawkins96
Comment Utility
Dale,

It worked! isnull(convert(nvarchar, pmoApps.dbo.ss_PDMMAST.acthours), 'N/A')

Thank you so much!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
crm written in asp.net / c# 1 41
ASP.net VB.net hide footer cells of GridView 2 31
Hovering effect 9 28
Hidden Field Value 10 34
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…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now