.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

Sql Order by not working with decimal/money type columns

Posted By:      Posted Date: September 09, 2010    Points: 0   Category :ASP.Net
Error Message:- Cannot convert a char value to money. The char value has incorrect syntax. DECLARE @Order NVARCHAR(50) = 'asc', @OrderBy NVARCHAR(50) = 'Shape'SELECTtop 1 Row_Number() OVER(ORDER BY  CASE WHEN @Order IS NULL OR @Order = 'asc' THEN    CASE WHEN @OrderBy IS null THEN Shape    WHEN @OrderBy = 'Shape' THEN Shape    WHEN @OrderBy = 'Cut' THEN Cut    WHEN @OrderBy = 'Color' THEN Color    WHEN @OrderBy = 'Clarity' THEN Clarity    WHEN @OrderBy = 'Price' THEN FinalPrice    --MONEY    WHEN @OrderBy = 'Carat' THEN [Weight]   --DECIMAL   END  END ASC,  CASE WHEN @Order = 'desc' THEN    CASE WHEN @OrderBy IS null THEN Shape    WHEN @OrderBy = 'Shape' THEN Shape    WHEN @OrderBy = 'Cut' THEN Cut    WHEN @OrderBy = 'Color' THEN Color    WHEN @OrderBy = 'Clarity' THEN Clarity    WHEN @OrderBy = 'Price' THEN FinalPrice  --MONEY   WHEN @OrderBy = 'Carat' THEN [Weight]  --DECIMAL   END  

View Complete Post

More Related Resource Links

Which one is better: Money or Decimal?



I want a field to keep money value (up to 100,000,000.00). And I didn't  work with money or decimal data type.

Money data Type is 8 byte

Decimal (9, 2) is 5 byte. (Exactly msdn only say Decimal (9, 0) is 5 byte and I don't really know how byte is Decimal (9, 2))

Please tell me which one is better to work with in sql and VS.net (handling sql in VS)

Best Regards. Morteza

DataAnnotations.Display.Order not working with MVC 2 (using Futures)

Hi All!I have these two classes in my model: public partial class Customer { #region Primitive Properties [Display(Name="Note", Order=10)] public virtual string Notes { get; set; } ... public partial class AdvisoryCentre : Customer { #region Primitive Properties [Display(Name="Denominazione", Order=-10)] public virtual string BusinessName { get; set; } ... In the editor for the AdvisoryCentre model, using Html.EditorForModel, the behavior is not what i expected. When creating a new AdvisoryCentre object, BusinessName and Notes fields are shown in correct order, but when i edit an existing AdvisoryCentre with empty Notes property, the Notes field is always shown before BusinessName field. If Notes property is not empty, the fields order is correct.There is a workaround for this problem?Thankyou

Image stretch inside a grid stops working horizontally once columns are added to grid.

I have a simple setup of a window. Window contains a grid, inside the grid there is an image. I want the image to always stretch across the entire cell. I've set it's Stretch property to Fill and it works fine, as long there is only one column in the parent grid. As the window is resized, the image stretches correctly. As soon as I add another column, the image stops stretching horizontally. Vertially it works just like before, horizontally it now appears with a fixed width corresponding to the width of the picture used as the source. Does not matter which column it's positioned in, and I've tried encapsulating it into another grid but behaviour does not change. It behaves the same in both Visual studio 2010 designer and at runtime. In addition when I tried to encapsulate it in a second grid, this seond grid also now has altered behavior - instead of sizing itself to it's container (both height and width are not set) as it's parent grid does, it now sizes itself to its contents - the image. But only horizontally. What is going wrong here, is this a bug or am I missing something, and any advice on how I can get the image to fill one of the cells of a grid with more then one column?

Sharepoint export to spreadsheet gives two extra columns in excel 2007 named as type and path, is th

When i export a list in excel 2007, it creates two extra columns named as type and path. At the same time this behaviour is not exhibited in office 2000. Is there any way to remove these two columns without using code ? Is there any configuration settings required for this, to remove it on the fly ? Thanks Ravish Verma

Removing columns inherited from parent not working

I'm trying to install a couple of custom workflow task content types that are based on the standard workflow task type, but I want to remove some of the attributes defined on the std worfklow task type.  Per the MSDN online info you can use the <RemoveFieldRef/> tag in your XML content type definition file to remove such columns.  For some reason mine does not appear to work, however.  In addition to not removing the columns specified by the <RemoveFieldRef/> tags, it also does not create my custom "Comments" column -- It DOES create the custom "Respoinse" column. The following are my XML definition files for the feature (which installs fine and the content types are created, but the columns I want to remove still show up).  Any idea what I'm missing to remove these columns from the custom types?  I pulled the IDs for the columns I want to remove from the std type definition XML under the 12-hive FEATURES folder Type Definition File <?xml version="1.0" encoding="utf-8"?> <!-- See this blog for specifics: http://www.scribd.com/doc/2278897/Part-4-Custom-Workflow-Forms-TaskEdit-Form --> <Elements xmlns="http://schemas.microsoft.com/sharepoint/"> <ContentType ID="0x010801003D9E4ED01F024d28AF0056E2654CECB0" Name="MoreInfo Task" Group="

Change order of columns in a list


Hi there

I'm looking for a way to change the order of the colums in a list in SharePoint 2010. I only have access to SharePoint Designer 2010 for editing the site.

I already looked at the function to create custom views for lists. However this are only useful when viewing the list as a whole, however when you edit an existing, or create a new entry you will see

  • first: the columns in the order in which they were created.
  • and second: all of the columns. i cant find a way to make certain columns invisible for the user.
I already looked through the functions that are given to me by SharePoint Designer 2010 when editing the columns of a list and I also checked for a way to change the order of the columns when surfing the actual website with Internet Explorer.

Does anyone know how to do this? Or am I just trying to find a function that doesn't make sense because it's covered by another feature?

Thanks for your help!

find amounts with more than 2 decimal places in a float type column

I was trying to use charindex but that didnt work.  Also what happens if i change the column to money data type? does it round or truncate?

Filter on web part not working for certain columns


Hi all,

I have a list of "Faults" - this contains the Shop Number that the fault was logged against (which is a lookup column to the "Shops" list), who logged the fault ("Created By") and the details of the fault ("Title").

If I add the list as a web part and filter by Created By, it works perfectly - I get multiple entries in the list for all faults logged by a certain person.

However, if I filter by a Shop Number - I only get a handful of entries in the list, going back a few days - anything older than that is simply not there.

Yet if I go to the main page for the Faults List (AllItems.aspx) and change to Datasheet view - the filter works perfectly, I can see EVERY entry for a particular Shop when filtering on Shop Number.

Any ideas? It only seems to happen when using a Web part OR a DFWP - as a result, I can't pass the Shop Number as a filter parameter to it, nor can the users see a complete history of faults when viewing individual shops.

I've no idea what has changed recently, other than deletng some custom views we had created in the Faults list - could this have caused a problem? Can the Faults list be "reset" somehow without losing any data? To clarify - it's ONLY happenning when the list is viewed as a web part of DFWP, when it's viewed in Datasheet m

Retention policy for content type not working


I have created a retention policy for the document content type and set it to delete a file permanently 3 days after the created date. It does not seem to be working, I have multiple documents in the library that have a created date over the 3 days. How often is the retention policy run? daily? weekly? Can it be changed?


Also, is it possible to take into account weekend days to exclude them from the counter?


Thank you,



Not all content type columns show up in DisplayForm.aspx


I have a list that when displaying the DisplayForm.asp does not show all the columns of my content type.  I double checked that the column Status was Optional( not Hidden), so I don't understand why they don't show up.  Can anyone advise please?  For site visitors that only have View rights, DisplayForm is the logical way to view the list item, so they need to see all columns.


Edit - I was able to get the missing elements to display by switching the column to Required rather than Optional.  While I think this will work in my case because the new items are coming from a webservice that provides this data, it doesn't seem like this should be required.  I thought by default ALL columns/fields displayed on the forms UNLESS marked as hidden.

There are no mistakes; every result tells you something of value about what your are trying to accomplish.

columns order reverses in matrix when exported to excel


Hi eb

I haver a simple matrix with header that holds description for every column

in matrix. Lets say header looks like:

col1Desc col2Desc col3Desc.

My problem: when report is exported to pdf or excel columns order reverses! so now it is:col3Desc col2Desc col1Desc

The only workaround i found to keep columns headres in place was to place header in a table on top of matrix.

This is fine but now matrix itself has no header so i can't display the columns header in every page of the report just on the first one..

I would very much appriciate a hint for solving any of these problems

either prevent columns reverse in matrix header when report is exported

or some how force the column to appear on top of each page

when contained in a separate table.

Thanks alot!




What new in XML Data Type if we can store data into separate columns?


Being new to XML Data Type I want to know what is the need to store our data in XML format if we can save it into separate Columns?

1. Is this practice being widely using in creating databases and maintaining information more efficiently?

2. Does this process follows Normalization rules?

3. What are the pros & cons?


Adding columns to content type

I am usine the Bugs Database site template.  On the Bugs List I want to add some additional columns.  If I add those columns to the content type, am I changing the content type throughout the site collection for anyone else that uses the Bugs List, or is my change just local to my site?
There are no mistakes; every result tells you something of value about what your are trying to accomplish.

Trouble working examples of Cumulative columns in SQL Query


I can run the following query without the built in query in the select statement and it works fine.  All i am trying to do is to create a cumulative total for each day for a specific date range.  Any ideas?


SELECT OrderHed.OrderDate,

     (SELECT SUM(OD.OrderQty * OD.ListPrice)

        FROM   MFGSYS.PUB.OrderDtl OD INNER JOIN MFGSYS.PUB.OrderHed OH ON (OD.Company=OH.Company) AND (OD.OrderNum=OH.OrderNum)

        WHERE OH.orderdate<=OrderHed.OrderDate)

 FROM   MFGSYS.PUB.OrderDtl OrderDtl INNER JOIN MFGSYS.PUB.OrderHed OrderHed ON (OrderDtl.Company=OrderHed.Company)

    AND (OrderDtl.OrderNum=OrderHed.OrderNum)

 WHERE  (OrderHed.OrderDate>={d '2010-10-01'}

    AND OrderHed.OrderDate<={d '2010-10-31'})

    AND OrderDtl.Company='HEAPG'

    AND OrderDtl.RMANum<1

 GROUP BY OrderHed.OrderDate

 ORDER BY OrderHed.OrderDate

Content Type or Lists Lookup Column can't show additional fields which are Choice or lookup columns


I am trying to create some content types, but this also happens when creating new columns in a list.

Ok, here is my setup (which is the most basic way to replicate the issue):

List 1: Department List
Columns: Department Name (Single Line of Text)

List 2: Document Owners
Columns: Document Owner (single line of text), Department (Choice or Lookup from Department List), Email (single line of text), Lookup Field (Calculated).

Content Type: Quality Document
Columns: File Name, Title, Document Owners (Lookup from Document Owners List), Email (pulled in with Document Owner), Then i want to also pull in Department from the Document Owners list.

That is where my problem is.  When i select the Document Owners List as the list to get the information from, there isn't the option under "Add a column to show each of these additional fields" to pull in the Department column.  This occurs if i use a Choice in the Document Owners list, or if i do a lookup from the Department List.

My preferred method of implementing this system would be to have a multi-value lookup field in the Department List for Document Owners so that the Owners are attached to the Department, and if the owners change for the department they also change for the document that is for said department.  But, i run into the same issue that i c

Sharepoint SPDatasource select command where clause not working for some columns



       I'm using MOSS 2007. I'm using spdatasource to bind a custom list to gridview using webuser control(webpart). spdatasource select command working for all columns except the status column. my status column is a choice- radio with the following options. Pending Review, Checked and Approved. I have checked the field internal name, every thing is correct, but only for status column filtering not happening. Gridview coming as blank

i tried using 

             <FieldRef Name='Status'/>
             <Value Type='Choice'>Pending Review</Value>

Any idea?????




Parent Columns and Child Columns don't have type-matching columns.



i am making  relation between two datatables and m getting this error

in this ds.Tables(0).Columns("In_ID") datatype is string

ds.Tables(1).Columns("row_id") datatype is integer

how i do the type casting here to make dataset relation

ds.Relations.Add("Rel_1", ds.Tables(0).Columns("In_ID"), ds.Tables(1).Columns("row_id"), True)

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