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

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

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

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
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?

View Complete Post

More Related Resource Links

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.

VS2005 calling VS2010 asmx service returns empty string


Anyone have any experience with this?

We are using VS 2005 for the front end, and vs 2010 for the web services part (asmx), not wcf yet.


Web service works just like the vs2005 when run locally, no problems.


Connecting to it from a test app returns an empty string. Connecting to a 2005 app doesn't!

Anyone run into this before or have a suggestion? Surely vs 2005 can connect to web services from a later version?






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

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

Cleanest way of setting max string length for text in GridView cells?

I want to make sure that each row in the table is the height of one line of text only.  I know you can set RowStyle-Wrap="false", but I also don't want any really wide rows.  I know I can go through each cell on RowDataBound or something and cut it to a particular length and then append an ellipsis on the end, but is there a neater way of doing the same thing?

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

Replace string in xml without converting to varchar using SQL Server

We have a xml datatype in SQL server which gets a xml file loaded into it. We want to replace some of the content in the xml datatype, but are running into problems converting from xml to varchar(for manipulation purposes), back to xml to output. Does anyone know of a way to replace xml without converting to varchar?Thanks in advance

dealing with nulls in a Int32.Parse(string) statement

How do I deal with null values in this section it throws errors when they leave the field blank it is a small order form and they don't have too add data to all the fields .   comm.Parameters.Add("@item_9223", System.Data.SqlDbType.Int);                comm.Parameters["@item_9223"].Value = Int32.Parse(txtitem_9223.Text);                comm.Parameters.Add("@item_9129", System.Data.SqlDbType.Int);                comm.Parameters["@item_9129"].Value = Int32.Parse(txtitem_9129.Text);                comm.Parameters.Add("@item_42795", System.Data.SqlDbType.Int);                comm.Parameters["@item_42795"].Value = Int32.Parse(txtitem_42795.Text);                comm.Parameters.Add("@item_30695", System.Data.SqlDbType.Int);                comm.Parameters["@item_30695"].Value = Int3

replace empty string with value if it is empty in ssrs

I need to place a "Days Not Calculated" of the string is empty, if it is not empty then a need to use the days calculated and put "days old" on the end of the number....SSRS 2K5 iif (Fields!AGE.Value, "", "Days Not Calculated")& " Days Old "

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

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 string to float when NULL

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.  
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