.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

if statement in sqlserver SP ?

Posted By:      Posted Date: September 14, 2010    Points: 0   Category :Sql Server
hi all i have this problem !!! i select data from database column called Salary this columns contains ( 1.7  & 1.99 ) after i select this tow row , they must display as 1.70 and 1.99 i added this Zero after 1.7 because 1.99 is large than it :) so how can i do it ?

View Complete Post

More Related Resource Links

How to Encrypt and Decrypt a Password using SQLSERVER 2005?(Video)

Encypt and Decrypt a Password using SQLSERVER 2005(Video)

Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Data Types - Date and Time in SqlServer

Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

Passing Linq Statement between master and content page


I usually work with windows forms, and am just starting to work with ASP.Net, so appologies if this is a dumb question!

I have a master page which has a search button on it. The results of this are built up using a Linq statement and if there are any results, I want to display the results in a contents page.

On the masterpage, I have the following code:

Private _res

    Public Overridable ReadOnly Property SearchRes()
            Return _res
        End Get
    End Property

 Private Sub DoSearch()
   'some code to get a linq result

    If res.Any Then
            _res = res
            Server.Transfer("SearchResults.aspx", True)
            'no results
            LabelNoRes.Visible = True
        End If
 End Sub

On the SearchResults.aspx page I have this code.

Dim m As pxSite = CType(Page.Master, MasterPage)
        If m.SearchRes IsNot Nothing Then
            Label1.Text = "Results found = " & m.SearchRes.Count
        End If

m.searchRes is always nothing. 

What am I doing wrong here?


SqlServer - Windows Account to install Application from Gallery


Referring to this tutorial: http://www.asp.net/webmatrix/tutorials/download-and-install-an-asp-net-application

I selected umbraco cms and got this wizard:

I already have Standard Sql Server 2005 on my machine which I wanted to us for above DB and not SqlExpress.

I did not remember "sa" account pwd nor I use explicitly any account to connect to my Sqlserver. I use Windows Authentication account i.e implicit authentication. So I created new account and gave all rights and used it in the above wizard and was able to install the database after some struggle.

My question is: What if I want to use my Windows Account to install the database above? Why the wizard don't have any option to use Windows Integrated Authentication?

Note: I can create the DB with external user I created, just looking specifics for using Windows Authentication for above installation.

ReportViewer :: Failed to enable constraints. But my statement is valid!!

  • A
  • n error has occurred during report processing.
      • Exception has been thrown by the target of an invocation.
        • Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

    Why do I keep getting this error, even when not using any data on the report. When I run the query in SQL server I get a resultset. However when I try to render the report I get this error.


    Whats wrong with this statement


    Im trying to updated some old ASP pages with some new .net pages and cant seem to get this update statement to work. I need to update the current ASP code below and then once thats working im going to use that same statement in a procedure for all our new .Net pages..

    So can anyone see what im doing wrong by inserting the 2nd column to the update statement?

    strSqual = "update tas set date_completed='" & TransDate & ", trans_id=" & InsertedTransID & "' where date_completed is null and sercontract_id="& strContractID & " and DATEDIFF(dd,task_due_date,'" & strProcDate & "')<=14"


    I am trying to add an extra column to the update, but not sure if the syntax is correct.

    If Statement Issue



    This is very strange to me. I have spent 2hrs looking into this inside out , but dont seem to get it

    I have an if statement that inserts data into the database. But i check to make sure the name is not entered twice. Now when the name is already in the db, then it should insert else put out a message saying its already in the db

    My code works fine except that  when its not in the db, its inserts but still puts out the message that says "Name is already in use" Not sure if am not seeing something. But thought i did bring it to the forum. Here is my code...Pretty simple

    Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click

            Dim objConnection As SqlConnection
            Dim objCommand As SqlCommand
            Dim strSQLQuery1 As String


            ' If there's nothing to Add for then don't search
            ' o/w build our SQL Query and execute it.
            If String.IsNullOrEmpty(txtAdd.Text) Or Leadbank.SelectedValue = "000" Then


    statement cannot appear outside of a method body


    hi all,

      i am posting my vb.net problem .plz tell me how to resolve this :-

    Dim conn As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
        'Dim mysqlconnection As New SqlConnection(conn)
        'If mysqlconnection.State=ConnectionState.closed Then mysqlconnection.Open();
        'Dim mySqlcommand As New SqlCommand("select CategoryId,CategoryName from Categories", mysqlconnection)
        'Dim mySqlDataAdapter As New SqlDataAdapter(mySqlcommand)
        'Dim mydataSet As New DataSet()

        'if mysqlconnection.State=ConnectionState.closed Then mysqlconnection.Open();

    i m getting 5 errors in this code

    declaration expected in mySqlDataAdapter.Fill method; Gridview1.DataSource=ds; Gridview1.DataBind();

    how to multiple condition in if statement in asp.net C#



    i am using if condition to find out a time

    if(time=="08 AM)


    response.write"shift A";


    but problem is how can i put multiple condition in if like

    if(time=="06 AM" and "08 AM" and "07 AM")



    Hello, i need help changing the following code into a switch...case statementUndecided

    <script runat="server">
    void Page_Load()
        if (Page.IsPostBack)
        public String toSring()
        int intcomputerChoice=1;
           if (Page.IsPostBack)

    Using a If Else Statement


    I'm working in a Wizard control and need a little help. I'm pulling in a labels text through a databound field and if it equals a certain text then I need a textbox field to be less than or equal to a certain amount. I would also like to have a message appear to prompt the user that an invalid number has been entered. I'm not sure how to go about this. Is it better to use client side javascript or use page load and perform some kind of control validation. The code I've tried isn't working so any help would be greatly appreciated.

    For instance...

    Protected Sub Page_Load(ByVal Sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If label1 = "Refrigerator" then

    TextBox1 <= "700"

    Else Label1 = "Washer" then

    TextBox1 <= "500"

    End if

    End Sub

    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.


    Hi All

    I have the below sql scripts that give me the error:
    "The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

    Ive tried using OPTION ( MAXRECURSION 0 ) or 1000 but it still  give me the same error.

    WITH cte as (SELECT     ticketid, setstatus, setdate, row_number() OVER (partition BY (ticketid)
    ORDER BY setdate ASC) AS rn
    FROM         [OneForm].[dbo].[TFAM_SLA_Times]
    SELECT     TicketID, 'Submitted', SubmittedON, 0
    FROM         [OneForm].[dbo].vw_TFAM_ALL)
        SELECT     a.ticketid, SUM(OneForm.dbo.[CalcMinutesSLA_TFAM](a.setdate, b.setdate)) AS SLA_in_Min
         FROM         vw_TFAM_SLA_Combined AS a LEFT JOIN
                                vw_TFAM_SLA_Combined AS b ON a.ticketid = b.ticketid AND a.rn = b.rn - 1
         WHERE     (a.SetStatus = 'Submitted' OR
                                a.SetStatus = 'Acknowledged' OR
                                a.SetStatus = 'Pending')
         GROUP BY a.TicketID

    If i remove the call to the function [CalcMinutesSLA_TFAM] or the b.rn - 1 it works fine.

    Here is the code for the function:

    CREATE FUNCTION [dbo].[CalcMinutesSLA2

    Where to place if statement


    I have a design problem I could use some help determining how to code this problem.

    I have a table with 3 fields, catCode, startDate and endDate. I am having trouble deciding how to query my DB based on the following...

    By default a gridview will return all records that match the catCode selected from a dropdown list.


    If the user checks a check box, only records are returned where....

    startDate <= today AND endDate >= today


    How can I apply the If condition to compare when the check box is selected? I used the wizard to build the select statment for the gridview, but I can switch that to a SP if that makes life easier.


    Thanks for any and all help and suggestions.

    The DELETE statement conflicted with the REFERENCE constraint


    I have created ASP.Net Dynamic data web site using scaffolding in Visual Studio 2010. When I click on delete in data grid for customer table I got below error message.

    The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occured in database "../NORTHWIND.MDF", table "dbo.order details", column 'ProductId'

    The statement has been terminated


    SQL statement to get data like outlook


     I have been struggling to write an sql statement that will give me data like outlook format., especially in term of grouping like





    Last week

    two weeks ago

    Last Month


    Is there anyone that can help me on this please? I appreciated

    Create excel file from Binary data on SqlServer



    My requirement is that i upload an excel file then i need to validate that data.


    1. Uploading file using using upload control saving binary in SqlServer

    2. Then i am creating file from binary data  on Sql Server using below command and then using OPENROWSET to dump data to Sql table

    3. Then reading Sql Table row wise and validating data.


    Alter Procedure spUploadExcelFile
      @PKID int,
      @BatchID int
    	Declare @VarBin varbinary(max)
    	Declare @FileName varchar(100)
    	DECLARE @sql nvarchar(MAX)
                    DECLARE @errMsg nvarchar(MAX)
    	DECLARE @ObjectReturn INT
    	DECLARE @ErrorSource VARCHAR(255)
    	DECLARE @ErrorDesc VARCHAR(255)
    	DECLARE @ObjectToken INT
    	Select @VarBin  = Content , @FileName = [FileName] from MultilangBinaryData Where PKID = @PKID
    	SET @FileName = 'C:\'  + @FileName
    	EXEC sp_OACreate 'ADODB.Stream' @ObjectToken OUTPUT
    	EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    	EXEC sp_OAMethod @ObjectToken, 'Open'
    	EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @VarBin
    	EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FileName, 2
    	EXEC sp_OAMethod @ObjectToken, 'Close'
    	EXEC sp_OADestroy @ObjectToken  
    	SET @sql = 'INSERT INTO dbo.UserBulkData SELECT 0,NULL,'+ @BatchI
    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