.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

Using Cast and Sum on VarChar Column

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :ASP.Net

Okay, so I have a little problem developing a SQL Select Statement on a column that is formatted pretty badly. Firstly, the Database has already been created and filled with Data in a Test Environment, I'm not sure if the format is the same or has been updated in Production but since I'm only able to work with the Test Environment right now I'm stuck with using what I have. The Column in one Table shows a Snapshot of a clients account which is set to VarChar(50) but the actual data in the column is formatted like a money format (with a $ and decimal point so for example "$5,000.00" would be an entry in one Row). 

What I'm trying to do is a create a WebService that will be used in a iPhone Application and one of the Methods needs to just give a quick display of the total value of all Clients with each specific company but I can't perform a SUM function on a VarChar column and I've tried to CAST and CONVERT the column into money, int, decimal and just about every format I can think of but I keep getting an error stating that I convert the data type varchar to which ever one I'm trying. I think it has something to do with the $ already listed in the Column, so I wanted to know what I could do to either remove the $ sign (like a Trim option) or if there is a better way of doing this that I can't think of.

View Complete Post

More Related Resource Links

SQL Server 2008 multiple lines in a varchar(MAX) column?



I'm using varchar(Max) to store a text file content. It's not taking the newline character. I tried \n and char(13) etc.. whatever solution posted in the net.

ex : update test set f='This is line 1.' + CHAR(13) + CHAR(10) + 'This is line 2.' 

after updating it doesn't have any newline or linebreak. Can anyone help me in this how to have linebreaks in the varchar(max) field.

Thanks a lot.

Update statement with CASE inconsistently updates VARCHAR column, stripping leading zeroes.


I have an update statement below that strips the leading zeroes off the front of AU. This value will have typically '0001234' or something like this. In my case statement, I have 2 scenarios: 1) when I use the commented out phrase (case when 1=1), the statement leaves AU alone as it should when the condition is met. 2) When I use the other statement, it STRIPS the leading zeroes off AU - I don't want it. I am just telling it to set it as is. Why is this doing this and what can I do to stop it. I was just attempting to set AU = AU in these conditions.

SELECT * from msdn.MSDN_Import1
UPDATE msdn.MSDN_Import1 SET MSDN.MSDN_Import1.AU =
--CASE WHEN 1=1                                                                       -- when I use this line, AU is untouched and stays AU = '0001234'
CASE WHEN NullIf(MSDN.MSDN_Import1.LOBCC,'') IS NULL            -- when I use this line, AU='0001234' becomes AU = '

Search for Clustered Index in database on varchar, nvarchar column



I have very big database, i am running a Re-index job and it's keep failing throwing everyday error stating "Online index operation cannot be performed on clustered index as index contains column of data type text, nvarchar.

is there any way i can search and change all such datatype of culstered into non-clustered indexes or any better solution i have.

Syntax error converting varchar value to a column od data type int


Hi everyone,

I'm having a problem where I've created a stored procedure with a dynamic ORDER BY clause and it's returning a syntax error:

Server: Msg 245, Level 16, State 1, Procedure usp_OrderBy, Line 4

Syntax error converting the varchar value 'Closed' to a column of data type int.

Here is the code that I'm trying to execute. It works fine if I take out the ID line:

CREATE PROCEDURE [Task_User].[usp_OrderBy]
  @orderBy varchar(100)
  SELECT ID, Status, PostedBy, Assigned, Subject
  FROM mainTable
    WHEN @orderBy = 'ID' THEN ID --When I take this out, it works
    WHEN @orderBy = 'Status' THEN Status
    WHEN @orderBy = 'PostedBy' THEN PostedBy
    WHEN @orderBy = 'Assigned' THEN Assigned
    WHEN @orderBy = 'Subject' THEN Subject
Any ideas as to what I can do to fix it?

How to retrieve list of values from an XML / Varchar(MAX) column along with main column?



I have a table with columns like ContactID, MainEmailAddress, OtherEmails  (as of type varchar(Max)). 
{At this point of project , cannot convert column to type XML}

Column OtherEmails may be either null  or may contain data in structure shown below.  

<bpDataSet xmlns="http://tempuri.org/bpDataSet.xsd">

I want to write a SQL Query to retrieve data in following manner

CotnactID  MainEmailAddress   Others
       1        main@main.com     abc@abc.com

how to sort a column varchar(50) that contains number


somebody please tell me how to write Sql querry to sort a column of type varchar (50) that contains number







instead of






how do i sort varchar column with number


i have a column in my table  of type varchar (50)







i want to sort in my sql querry like this







please help

How to remove spaces from varchar column


Hello guyz;

I got a varchar column that I want to remove extra spaces from its contents.

what I want to do is keeping only 1 space between each 2 words ...I don`t want to keep more than one space between each 2 words

Tooltip for GridView Column Headers (ASP.Net)

I just wanted to add auto tooltips for my GridView headers. There are couple of ways but I don't want to use javascript and wanted to use simple 'title' attribute. It took couple of hours to get the working code supporting sortable columns. I thought it will be very useful for others. Here's the code and logic behind it.

Multiple Column Dropdownlist for the ASP.NET DataGrid

Based on my previous control "Multiple Column DropDownList for ASP.NET", I received many emails asking for the same control to be used in the DataGrid for web applications. Here we go.. This control can be used as the regular MS DropDownList in the DataGrid and also as a regular dropdownlist. It has all the properties, like DataTextField, DataValueField, DataSource, SelectedIndex etc. The download file contains the samples both in VB.NET and C#. In this sample, I have used the Northwind database of SQL Server.

Alternating row background for first column of a report


is there a way to make the first column of a report use the an alternating background color? 

I have the background color set for the entire row however it does not work for the first column if the color is set to an expression.  If I just set it to a color i.e. red it works just fine. If I insert a dummy column to the left it works just fine (but the dummy column does not work, presumably because it is first??).

In the file below only the textbox for part number does not show an alternating background.  All others work correctly.

                    <Textbox Name="PartNumber">

Use a column as parameter for a sds in another column; gridview


Hola, I have a gridview which is bound to a sds which returns 7 columns and I added two columns from the GUI.  What I want is to fill up the 9th column with DDLs and these  DDLS will get their data from a different sds which is using a stored procedure with a single parameter.  I created the 9th row, used template field, made the sds for 9th row, the stored_procedure, threw a DDL in Item template and all  I need now is to pass a parameter in the first column to the sds in the 9th column.  I can code this but i'm getting tired of aspx.cs :) and would like to do it from the GUI.

Pass a grid column value to a Modal


Hi, I have a requirement to hide a field called details on a gridview and show it in a jQuery modal dialog when users click on the Customer ID hyperlink. This grid is created programmatically. I will be hiding the details column via the CSS display:none property. Does some one know how can I pass the grid's selected hidden column value to the modal? Here is some of the code I started:

            GridView mygrid = new GridView();
            //.....................................More columns code for the grid

            //Here is where I need to pass the Details column value associated with the customer ID field selected to the Modal and open the Modal dialog.            
            HyperLinkField linkField = new HyperLinkField();
            string[] dataNavigateUrlFields = { "CustomerID" };
            linkField.DataTextField = "CustomerID";

Bug: Does not change column type from nvarchar to ntext


Scenario: If you create a table with a column of type nvarchar, populate it with some content and try to modify it using the WebMatrix editor, from nvarchar to ntext, it does not display any error message or warning that this is not possible. It silently saves and reverts the change, back to nvarchar. Wasted some hours on this issue as I thought my column was ntext, but in fact was still nvarchar.

Expected behavior: It should change to ntext, or if SQL CE doesn't support that, it should notify the user that no change was applied.

Display column from child table. Possible ?



I'am using dnamic entity with EF4. On a list page of a table, I would like to display a column containing information from a child table of the current element. Sample :

Order List :

Order Data | Required Date | Shipped Date | etc... | ... | Customer Name (foreign key with tostring() method override) | Customer Postal Code (Column that I want to add) |

I don't known how to do that. Is it possible ? Maybe I have to to create my own Metadata proxy that will add dynamicly a column on the MetaColumn list of the table.

Does someone have an easier or better idea ?

Thank you for any help.

Rendering data with HTML tags in the DD Gridview for a selected column


I am having trouble finding out where and how to HTML Encode a cell's data on the Dynamic Data (v4.0) gridview of List.aspx.  As a simple case, suppose I have formatted cell data that is A<br/>B in the DB.    Obviously, I want A stacked on B in the cell.

It seems gridView1.HtmlEncode = true has gone away.

So maybe I'll try to catch it on the RowDataBound event:

protected void GridView1_RowDataBound(Object sender, GridViewRowEventArgs e)

            if (e.Row.RowType == DataControlRowType.DataRow)
                // Html Encode the cells


but this event never fires?

Has anyone figured out how to properly render HTML tags data in the List.aspx's GridView1? 

If I figure this out, then I can add a MetaAttribute called something like [EncodeAsHtml(true)] and be on my way.  Thanks!

How to cast integer values as an array of enum values?


Hello all!

I'm working with VS2010 Beta2, ASP.NET and Dynamic Data. I'm using VB but will certainly be happy to receive a C# example if necessary. Laughing

I used a few of Steve's examples (Writing Attributes and Extension Methods for Dynamic Data and Dynamic Data - Hiding Columns in selected PageTemplates) to create a custom property attribute to be applied to a dynamic data column and want it to accept an array of enum values (each value will represent a dynamic data page template where this column should not be displayed).  My new attribute works fine when I hardcode it like this, meaning the HideColumnIn attribute is properly populated with the array of enum values:

<HideColumnIn(PageTemplate.List, PageTemplate.Edit)> _
Public Property My_DD_Column As Object


However, I want to store the values to be used for the HideColumnIn in the DB as integer values that correspond to the

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