.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

convert string to float when NULL

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
Hi, I have a column MTD_PNL of type varchar(255) that may have NULL as a value along with numerical values. I want to write an sql to sum up MTD_PNL grouped by ACCT_NO   SELECT ACCT_NO,  SUM(Convert(FLOAT, ISNULL(MTD_PNL,0.0))) AS SUM_MTD_PNL GROUP BY ACCT_NO   The above SQL fails when encounters NULL.  

View Complete Post

More Related Resource Links

Convert float to String

HI, I am creating a derived column in my workflow. This derived column would have a concatenated value or Currency + [Account No]. Derived column is of type DT_STR. Currency is string (which is fine). I am having trouble converting [Account No] from float to string I tried Currency + (DT_WSTR)[Account No] and Currency + (DT_STR)[Account No] I am a newbie here... so, excuse my stupid question

Convert string to float

Hi, Sorry for posting this here. Wasnt getting enough attention in "Getting Started With SQL" forum.   I have a column MTD_PNL of type varchar(255) that may have NULL as a value along with numerical values. I want to write an sql to sum up MTD_PNL grouped by ACCT_NO   SELECT ACCT_NO,  SUM(Convert(FLOAT, ISNULL(MTD_PNL,'0.0'))) AS SUM_MTD_PNL GROUP BY ACCT_NO   The above SQL fails when encounters NULL.

convert any string to float

hi friends, i am struggling to convert simple string to float here. It is not very simple. I've database which is free text in the front end so people can enter any result. it should be numeric. It can be 0.1 or 3,4 or 33,,22 or 22.2 or 4.5 i can handle all combination when covert into float but when it comes to 3,4 or 22,,33 then it is difficult to conver into float. When i use isnumeric in where clause then it thinks 3,4 is numeric and then it cant convert that text into float. any suggestion? Thanks.

Convert XML to object returns null due to unwanted string


Hello All,

I am facing very odd problem, when I check my xml string than in header it contain one unwanted string square [], like below, due to that casting to object is not happening. Due to that it returns null

[]<?xml version="1.0"?>

But when I remove [] and convert to object then it works fine. Any one has any idea.

Failed to convert parameter value from a String to a Guid.


I am trying to get the UserRole from the logged in user and send it to the database table my code is:

SqlParameter FamilyFriendsOther = new SqlParameter("@Role", SqlDbType.UniqueIdentifier);
        string [] roles = System.Web.Security.Roles.GetRolesForUser();
        if (roles.Length > 0)
            FamilyFriendsOther.Value = roles[0];

Dropdown menu - Null or empty string??


I have drop dowm menu as follow.

<asp:ListItem Value="">none</asp:ListItem>
<asp:ListItem Value="STO">Stock</asp:ListItem>
<asp:ListItem Value="ORD">Order</asp:ListItem>

If I chose none, it stores data as "Null"

If I Query the data as below, I don't get the data has value of "Null"

SELECT tabale From type Where type <> STO or type <> ORD

I get data that has empty string but not Null.

Here is my question.

1, How do I write dropdown menu value in oder to get empty string instead of Null?
I did <asp:ListItem Value="">none</asp:ListItem> but this stores Null.

2, Why this Query won't pick up Null?
SELECT mytabale FROM type WHERE type <> STO or type <> ORD

Thank you for your help.

convert english string to hindi string

Code to convert english to hindi string

error in MVC2 on empty string form fields which are required: "This property cannot be set to a null

In model I have:[DisplayName("ISBN")] [Required(ErrorMessage = "ISBN is required.")] [StringLength(50, ErrorMessage = "ISBN book must have max {1} chars.")] [DisplayFormat(ConvertEmptyStringToNull = false)] public string isbn { get; set; }But when I submit form with empty field ISBN I have an error - ConstraintException: This property cannot be set to a null value.I have find that other people have the same problem and they say that debbugger in VS2010 works wrong - yes ?http://p2p.wrox.com/book-professional-asp-net-mvc-2/79788-constraintexception-unhandled-user-code.html#post259245http://efreedom.com/Question/1-3129080/Server-Side-Validation-REQUIRED-String-Property-MVC2-Entity-Framework-Workhttp://mvcmusicstore.codeplex.com/workitem/6604

Failed To Convert Parameter Value From A String To A Int32

 This is my stored procedure: PROCEDURE[dbo].[uspEXPENDITURE_DETAILS] ( @ExpDetailsID int=0 output, @ExpDet_ExpID int, @ExpDet_Municipality_ID nvarchar(50), @ExpDetGeneralFund money, @ExpDetSpecialRevenue money, @ExpDetCapitalProjects money, @ExpDetTotal money, @Exp_Reporting_Year varchar(10), @ExpComments varchar(max), @Contact_ID int ) AS BEGIN  -- SET NOCOUNT ON added to prevent extra result sets from  -- interfering with SELECT statements.  SET NOCOUNT ON; deletefrom [dbo].[TBL_EXPENDITURE_DETAILS] where ExpDet_ExpID=@ExpDet_ExpID and ExpDet_Municipality_ID=@ExpDet_Municipality_ID and Exp_Reporting_Year=@Exp_Reporting_Year If@ExpDetailsID >= 0 Begin INSERT     INTO [dbo].[TBL_EXPENDITURE_DETAILS] ([ExpDet_ExpID] ,[ExpDet_Municipality_ID] ,[ExpDetGeneralFund] ,[ExpDetSpecialRevenue] ,[ExpDetCapitalProjects] ,[ExpDetTotal] ,[Exp_Reporting_Year] ,[ExpComments] ,[ExpDetCreatedBy] ,[ExpDetCreateDate]) VALUES  (@ExpDet_ExpID , @ExpDet_Municipality_ID , @ExpDetGeneralFund, @ExpDetSpecialRevenue , @ExpDetCapitalProjects, @ExpDetTotal, @Exp_Reporting_Year, @ExpComments, @Contact_ID, GetDate () )    SET @ExpDetailsID = SCOPE_IDENTITY()  return @ExpDetailsID end   Public Sub CreateExpenseDetails() Dim item As GridViewRow Dim txtExpGenFund, txtExpD

Varchar string to int - how to convert empty cells to nulls

Hi, I have a large table from which I want to convert certain columns from varchar to int while loading the table to another table. However, I run into problems when the input column contains empty cells. The SSIS data conversion data flow item can't handle these so I tried the script component. With the script component I can convert these empty cells to zeros but after that I'd need to use SQL update to update the zeros to nulls which in the case of a large table with over 3 million rows takes unnecessarily long. So, is there another way?

failed to convert parameter value from a string to a int32

I am not sure where my app is failing but most probably this is where it is failing.. I think lblExpID.Text is the culprit. Here's the relvant part of my code: Dim lblExpID As Label For Each item In LSGrid.Rows lblExpID = item.FindControl("lblID") If txtExpGenFund.Text <> "" Then sqlArray(i) = ExpDetailsID & "," & (lblExpID.Text) & "," & MunID & "," & _ Val(txtExpGenFund.Text) & "," & Val(txtExpDetSpecialRevenue.Text) & "," & _ Val(txtExpDetCapitalProjects.Text) & "," & expTotal & "," & reportingYear & "," & "Test Comments" & "," & UserID End If Can u help me identify why i am getting this error looking at the sp below: PROCEDURE [dbo].[uspEXPENDITURE_DETAILS] ( @ExpDetailsID int=0 output, @ExpDet_ExpID int, @ExpDet_Municipality_ID nvarchar(50), @ExpDetGeneralFund money, @ExpDetSpecialRevenue money, @ExpDetCapitalProjects money, @ExpDetTotal money, @Exp_Reporting_Year varchar(10), @ExpComments varchar(max), @Contact_ID int ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; delete from [dbo].[TBL_EXPENDITURE_DETAILS] where ExpDet_ExpID=@ExpDet_ExpID and ExpDet_Municipality_ID=@ExpDet_Municipality_ID and Ex

How to pass a String and convert to xml

A Simple Query Pass to connection String 1 My Query is SELECT a.HD, a.HEAD, a.SH, a.SUBHEAD, a.TRK, a.TRACK, a.TGT, a.TARGET, a.PDC, a.DT_RELEASE, a.STATUS, a.COFROM V_HIERARCHY1 a   2,My Data is HD    HEAD    SH    SUBHEAD    TRK    TRACK    TGT    TARGET    PDC    DT_RELEASE    STATUS    COHR    HR    Rec    Records    Leave    Leave Records    HRL.0002    Quarterly Review    01.07.2010    04.07.2010    Planned    EdlinkHR    HR1    DB    Database    SAL    Salary    DDS.0001    Calculation of Monthly salary    08.08.2010    09.08.2010    WIP    EdlinkHR    HR    Rec    Records    Ind    Induction Records    HRI.0001    Completion of dossiers.    08.07.2010    10.07.2010    WIP    EdlinkHR    HR  

Convert a string (from xml) into date time

Is there a simple command to take this string: 2010-09-09T17:00:00.000Z and turn it into a date time? It is a date time value that is passed to us in an xml document, it has a type of 'unitless', when I read it I need to set a dateTime variable to this date and tim, I can parse it, but perhaps there is a simple single command that is already written for just such a thing. Thanks. 

Need to convert query string(date - 09/15/2010) to a text format - yyyymmdd

I am fairly new to C # and need some assistance with a form page.  I have created a simple form that requires the user to select a beginning date and ending date.  I then pass the values to my code behind page via query strings.  I have them passing, but I need the format to be change to a different format.  Unfortunately the database does not use the standard date format.  It requires the date to be in a text format as YYYYMMDD.Form: <tr> <td> Beginning Date:</td> <td><asp:TextBox runat="server" ID="begDate"></asp:TextBox> <asp:Image ID="Image2" ImageUrl="~/images/calendar_schedulehs.png" runat="server" /></td> </tr> Code Behind: protected void createReport_Click(object sender, EventArgs e) { StringBuilder url = new StringBuilder("Report/TopCustomer.aspx?"); url.Append("BegDate=" + begDate.Text); url.Append("&EndDate=" + endDate.Text); url.Append("&Brnch=" + ddlLocation.SelectedValue); Response.Redirect(url.ToString()); Current URL: TopCustomer.aspx?BegDate=09/15/2010&EndDate=09/30/2010&Brnch=300I need to have the above URL change to:TopCustomer.aspx?BegDate=20100915&E

How to convert string to double

Hello All!! Since I am pretty new to C sharp I am unable to solve simple conversion from string to double. Example would be like this: string sVal = "-7.0"; //This is negative number. double dVal = 0.0; Now I need to assign dVal with value from sVal and I did try those examples but none of them worked. dVal = double.parse(sval); nor it worked this way dVal = System.Convert.ToDouble(sVal); Please advise what am I doing wrong and how should I solve this pretty simple problem. Thanks in advance.

"String Concatenation": Not appear result Or 'Null v alue' if any column contain Null null value

I write This statement to display FullName of Person SELECT CardID,( FirstName + ':' + FatherName + ':' + GrandFatherName + ':' + FamilyName) as name from PersonalData but I found he dispalay null value if any part of any columns Concatenation contain null value what the exception to make them "solution"

error: cannot implicitly convert type 'string' to 'int?'

Hey guys,I am trying yo build the following class for which i get an error on the line this.id = e.id as cannot implicitly convert type 'string' to 'int?'. can anybody help me with it??many many Thanks,Chetan.using System;using System.Collections;using System.Collections.Generic;using System.Linq;using System.Data.Linq;using System.Text;using System.Runtime.Serialization;using HsInternet.Data.DAL;namespace HsInternet.Data.BLL{    public class Baby    {        public int id { get; set; }        public string name { get; set; }        public string parents { get; set; }        public int gender { get; set; }        public string weight { get; set; }        public string length { get; set; }        public DateTime date { get; set; }        public DateTime time { get; set; }        public string image { get; set; }        public string ipa { get; set; }        public string ipb { get; set; }        public Baby()  &
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend