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

Top 5 Contributors of the Month
Post New Web Links

How to update data and store a value based on a comparison

Posted By:      Posted Date: November 04, 2010    Points: 0   Category :Sql Server


i want to implement a stored procedure that updates values from a field named MYVALUE with


but if the result MYVALUE is greater than 100 (MYVALUE > 100) then set MYVALUE equal to 100.

I can't figure a way to do this. I am trying to implement it into a stored procedure so that i schedule it to run every dat.

Thanks in advance

View Complete Post

More Related Resource Links

SQL Server CE: New Version Lets You Store and Update Data on Handheld Devices


Handheld device users need to be able to synchronize with a main data store when it's convenient and, preferably, when the back-end database server isn't busy. SQL Server 2000 Windows CE Edition allows you to build a traveling data store that can be displayed and run on a variety of devices. SQL Server CE supports a subset of the full SQL Server package, and can be used as a standalone server or in tandem with SWL Server and IIS. The architecture of SQL Server CE, along with data manipulation, synchronization, and connectivity issues, are discussed in this article. Topics such as making your data public, choosing the right type of replication, and handling errors are also covered.

Paul Yao and David Durant

MSDN Magazine June 2001

How to store more than 4k bytes of data in SQL CE 4?


I need to store more than 4k of bytes in my SQLCE 4 database. How can I do that with the ASP.NET Web Pages 1.0?

1: The Microsoft.Data provider fails when I try to populate a parameter with more than 4k bytes.
2: Referencing SqlServerCe assembly and using SqlCeConnection/SqlCeCommand/SqlCeParameter throws exception:  SQL Server Compact is not intended for ASP.NET development.

How can I somehow map the generic DbType, which does not have NText as an option, to the ntext column in my database? (SqlCeParameter supports DbSqlType, which has ntext, but see problem #2)

How to get data from table based on four table



I need to know how to show top 30 records from four table

with fastest speed.. in ms sql server 2005..

hope You do the needfull


Spider in .NET: Crawl Web Sites and Catalog Info to Any Data Store with ADO.NET and Visual Basic .NE


Visual Basic .NET comes loaded with features not available in previous versions, including a new threading model, custom class creation, and data streaming. Learn how to take advantage of these features with an application that is designed to extract information from Web pages for indexing purposes. This article also discusses basic database access, file I/O, extending classes for objects, and the use of opacity and transparency in forms.

Mark Gerlach

MSDN Magazine October 2002

Beyond ASP: XML and XSL-based Solutions Simplify Your Data Presentation Layer


The combination of XML and XSL can provide a powerful alternative to ASP development. This article presents arguments for building even small-scale Internet applications on the XML model. An example written with traditional ASP programming is compared to the same example written with XML and XSL in order to show the benefits of this approach. The example is followed by nine good reasons to make the switch. These reasons include separation of presentation and data, reusability, extensibility, division of labor, enhanced testing, and legacy integration. The XML/XSL solutions described hold the promise of greater simplicity, flexibility, and durability than ASP solutions built the traditional way.

Scott Howlett and Jeff Dunmall

MSDN Magazine November 2000

Insert data based on gridview user value on row.. how to



I have three column comes from database table(Table1) and one column is for entering data for the user let us consider it look like this 

ItemNo ItemName Qty  uservalue

001       A               50       20

002       A               20       5

003       B               50  

004       C               60      10

005       D               40 

006       E               90      15 


If its 300 row from database to gridview it will show 50 record with page navigation..

What i need is i need to insert data in to database table which gridview row has user value..

The output of the another table(Table2) will look like this after insert

ItemNo ItemName   uservalue

001       A                    20

002       A                      5  

Best practice? Accessing subset of data based on user


I have a DB and associated Asp.net 3.5 web app. The database holds information on our clients. We're adding client access functionality and I need to know how best to restrict user access to data only pertaining to their client.

The clients are stored in a hierarchy and each user can be associated with multiple clients. The user should only be able to access the data for their associated clients and any sub clients thereof.

My initial thought is to add a collection of clients to their profile, then use that to filter all queries; however, my gut instinct tells me that using the profile for such a security function is inadvisable.

Could anyone provide any insight on a best practice for this? I'm sure it must be a pretty common need.


Store Null Data In image datatype in sql server 2005



i wanted to store image in sqlserver database image field and i did. But now in some cases i want to store null value in that image field please let me know how to do this using file uploader.please give complete sample code.


store update sum

Okay, so i created the following query select a.cusip, SUM([UnitsHeld]) AS unitsSum From [GTC_PA_SQL2].[DW].[dbo].[HoldingsC] h inner join [GTC_PA_SQL2].[DW].[dbo].[AssetsC] A ON h.securityID = a.securityID Where cusip IS NOT Null AND MinSecClass In (9,10,58,59,60,64,65,69,70,79,80,85,87,215,216,220,229) group by a.cusip However, now i need i need to change this so that instead of a on the fly query, the values are stored and the query will run only once a quarter or so.  This is what i have so far: update dbo.finance set PAR= (select SUM([UnitsHeld]) AS unitsSum From [GTC_PA_SQL2].[DW].[dbo].[HoldingsC] h inner join [GTC_PA_SQL2].[DW].[dbo].[AssetsC] A ON h.securityID = a.securityID Where cusip IS NOT Null AND MinSecClass In (9,10,58,59,60,64,65,69,70,79,80,85,87,215,216,220,229) group by a.cusip) The table i need to store the UnitsSum value in the dbo.finance table under the field PAR which correlates with a PK of cusip, this table doesnt have any securityID reference. The issue with need these queries stems from the fact that i need a total UnitsHeld for each security class which has a cusip9 attached to it. However the database right now stored the unitsheld for each portfolio and not as a total for reach cusip which i need.

Transferring data (on update) from SQL to SLQL (2008

Hi there,   we are having 2 MS SQL 2008 servers, where the first one is our main machine where our ERP (Navision) is running on. Now, due to a customer request, I need to send data from our main sql server into our second sql server. But not everything, only some specific datasets (only if the specific customer is affected)... not only on inserts, but also on updates and on multiple tables.   I am not quite sure which technology to use for that, I tried to read something about Integration Services and Trigger, but as I am not really into both of them and not sure what technique would be the most suitable, I'd like to ask the experts here for some advice.   The affected rows might be around 2-10% of our total inserts / updates a day.   Any help would be appreciated!

Update XML data in SQL

Is there an easy quick way to update an xml value based on the contents of that value....? From my research so far this seems to be much more involved then I expected it to be. Here is my table CREATE TABLE [dbo].[ReportJobs]( [ReportJobId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [Enabled] [bit] NOT NULL, [Description] [nvarchar](500) NOT NULL, [ReportDefId] [int] NOT NULL, [Email] [nvarchar](500) NULL, [LastRunResult] [nvarchar](50) NULL, [ScheduleId] [int] NOT NULL, [LastRun] [datetime] NULL, [NextRun] [datetime] NULL, [PersonId] [int] NULL, [Parameters] [xml] NOT NULL, Here is an example of the xml contained in Parameters for one record. <ReportParams> <Param Name="EntityID">103</Param> <Param Name="RelDateFromNum">-3</Param> <Param Name="RelDateFromWhich">Day(s)</Param> <Param Name="RelDateToNum">0</Param> <Param Name="RelDateToWhich">Day(s)</Param> <Param Name="GeoID">288</Param> <Param Name="InboundFlag">False</Param> <Param Name="ShowTimeliness">True</Param> </ReportParams>  Here is what I'm trying to do.... What is the correct way to make the following statement? Update ReportJobs set <ReportParams/GeoID> = 12 w

How to store binary data as nvarchar(max)?

I’m trying to store a blob of binary data as nvarchar(max) and it gets truncated after just a few bytes. I wonder why and how can I prevent it. I don’t use varbinary(max) because I’m trying to keep existing DB schema intact. Current DB contains a huge chunks of text in nvarchar(max) column and I’m trying to compress (zip) this text and store it back to the same field to reduce DB size.   DB is accessed using Sync Framework (and Entity Framework) on the client side, so actual DB engine is SQL Server Compact 3.5 SP2   Thank you! KonstantinThank you! Konstantin

update data on table

i have  100000 record  found in the  tha table and  i want  to update the column SocialState when found male he replace  the text male to 1 and when foun Female  he replace the text to 2   update [Drugs].[db_accessadmin].[Data] set SocialState='1' where SocialState='Male' update [Drugs].[db_accessadmin].[Data] set SocialState='2' where SocialState='FeMale' he give me this message "(0 row(s) affected)"

Getting filtered data from Role based SSAS security

Hi everyone,I've got a heap of reports that are based on various SSAS cubes. I have roles defined on these cubes that restrict data via certain dimensions. Question is, will these restrictions filter through to the report...ie, if I have a sales person restricted in the SSAS cube to only see sales against their territory (restricted in the Territory dimension), when they run the report will it filter the result based on their SSAS credentials and only show the data they have access to (even though the SSRS report has no direct filters or parameters applied)?Cheers for any help!!

How can i update treeview root node every time based on the record entries

Hi all, I will have an mdi form and with a treeview which has it's initial property as visible =false. If i select an option from menu a child form will exists i will fill some details and click on save when ever click on save i will make the root node of the treeview to some name as File[batch count:0]. Now if i select another option from menu another form will appear and if i fill it with some details and click on save i would like to change the root node name to File[batch count:1] and a child node should exists with some name

Sys.Data.DataContext.saveChanges method doesn't send update to server after DataServiceException ex

Hello,I have a page with a simple form that updates a single record in a table in my database.The database is accessed via WCF Data Services. To load and save data from/to the server, I usethe Sys.Data.DataContext object provided by the ASP.NET AJAX library. When the "save" button on theform is clicked, I use the saveChanges method of the Sys.Data.DataContext object to send all changes to the server.Also, at the server side, I use an interceptor that checks the "Price" fields of the record, and throws a DataServiceException exception when the contents of the field is a negative number.Both the JavaScript and the interceptor (in C#) are copied below.Here is the interesting thing:When I enter a negative price in the form, the interceptor throws the exception and my form shows an error message. Good.However, if I then make one of the other fields in the form a bit shorter (for example from "abcd" to "abc"),the saveChanges method doesn't send the data to the server. But it does call the success callback that I passed in to saveChanges, creating the impressionthat the data has been saved. This happens while the Price field is still negative. I used Fiddler to make sure thatno message is sent to the server. Also, the interceptor doesn't get executed.The weird thing is that if I make one of the fields a bit longer instead (for example &q

Invalid data has been used to update the list Item.(List definition)

<nativehr>0x80070057</nativehr><nativestack></nativestack>Invalid data has been used to update the list item. The field you are trying to update may be read only. I have created a list definition and into that i have created lookup column when i am trying to add any item into the list then it gives me an error as above. Can anyone help me.? I made the readonly attributes to false for lookup columns.  
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