Fetch msg from sql server to c# code behind using execute scalar with validation

hi Everyone in this article i have shown how to get string or message from sql sever and to

display in c# code behind page with using output parameter with Execute Scalar method with

Insert and update and delete with Gridview with .aspx and .cs code.


so we can use execute non query also but that will return only zero & 1.
so we have used Execute Scalar so we can fetch message which we have return in sql server.
i have also shown execution part in  code behind.



default.aspx page.


<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeFile="Default.aspx.cs" Inherits="_Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <h2>
        Inert update and delete in gridview
    </h2>
  <div>
  <asp:Label runat="server" Text="Enter Name" style="width:290px;"></asp:Label><input type="text"  runat="server" id="inptnm" placeholder="Enter  name"/>
  <asp:RequiredFieldValidator  runat="server"  id="rqres" ControlToValidate="inptnm" Text="*" ValidationGroup="nm"   ForeColor="Red" InitialValue=""></asp:RequiredFieldValidator>
  </div>
  <div>
  <br />
  <asp:Label runat="server"  Text="Enter number"></asp:Label><input type="text" runat="server" id="txtnumber"  text="*" placeholder="Enter  number" maxlength="10"/>
  <asp:RequiredFieldValidator  runat="server"  id="RequiredFieldValidator1" ControlToValidate="txtnumber" Text="*" ValidationGroup="nm"   ForeColor="Red" InitialValue=""></asp:RequiredFieldValidator>
      <asp:CompareValidator ID="CompareValidator1" runat="server"
          ErrorMessage="Pleas Enter Integer" ControlToValidate="txtnumber"
          Operator="DataTypeCheck" Type="Integer" ValidationGroup="nm"></asp:CompareValidator>
 

  </div>

    <div>
  <br />
  <asp:Label ID="Label1" runat="server"  Text="Enter Email"></asp:Label><input type="text" runat="server" id="txtmail"  text="*" placeholder="Enter  Mail"/>

<asp:RequiredFieldValidator  runat="server"  id="RequiredFieldValidator2" ControlToValidate="txtmail" Text="*" ValidationGroup="nm"   ForeColor="Red" InitialValue=""></asp:RequiredFieldValidator>
            <asp:RegularExpressionValidator
                ID="RegularExpressionValidator1" runat="server"
            ErrorMessage="Please enter Proper address" ControlToValidate="txtmail"  ValidationGroup="nm"
           
            ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator>

 

  </div>
  <asp:Button runat="server" ID="btnsubmit"  Text="Submit" OnClick="btnsubmit_onclick" ValidationGroup="nm" />
  <br />
  <asp:GridView runat="server" ID="grddata" AllowPaging="True" AutoGenerateColumns="false" DataKeyNames="NAME">
      <Columns>
          <asp:BoundField DataField="NAME" HeaderText="Name" ReadOnly="True"
              SortExpression="NAME" />
               <asp:BoundField DataField="NUMBER" HeaderText="Number" ReadOnly="True"
              SortExpression="NUMBER" />
               <asp:BoundField DataField="MAIL" HeaderText="Email Id" ReadOnly="True"
              SortExpression="MAIL" />
              <asp:TemplateField HeaderText="Delete">
              <ItemTemplate>
              <asp:LinkButton runat="server" ID="lnkdele" Text="delete" OnClick="lnkdele_click"></asp:LinkButton>
              </ItemTemplate>
              </asp:TemplateField>
                <asp:TemplateField HeaderText="Edit">
              <ItemTemplate>
              <asp:LinkButton runat="server" ID="lnkedit" Text="Edit" OnClick="lnkedit_click"></asp:LinkButton>
              </ItemTemplate>
              </asp:TemplateField>
      </Columns>
      <EmptyDataRowStyle BackColor="#FF9999" BorderStyle="Dashed" />
     
    </asp:GridView>
    <asp:HiddenField runat="server" ID="hdnval" />
    <asp:HiddenField runat="server" ID="hdngetid" />

</asp:Content>






using System;
using System.Web.UI;
using  System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Web.Configuration;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
   SqlConnection sqcon=new SqlConnection(WebConfigurationManager.ConnectionStrings["constr"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack == true)
        {
            hdnval.Value = "I";
            DiplayAllREcord();
        }
    }

    protected void btnsubmit_onclick(object sender, EventArgs e)
    {

         try
        {
             SqlCommand cmd = new SqlCommand("Sp_InsertUpadetData", sqcon);
            cmd.CommandType = CommandType.StoredProcedure;
            if (hdnval.Value == "U")
            {
                cmd.Parameters.AddWithValue("@Action", "UPDATE");
                hdnval.Value = "I";
                btnsubmit.Text = "Submit";
            }
            else
            {
                cmd.Parameters.AddWithValue("@Action", "INSERT");
            }
            cmd.Parameters.AddWithValue("@name", inptnm.Value);
            cmd.Parameters.AddWithValue("@number", txtnumber.Value);
            cmd.Parameters.AddWithValue("@Mail", txtmail.Value);
            cmd.Parameters.AddWithValue("@Msg" ,"").Direction = ParameterDirection.Output; 
           
            object val;
                  sqcon.Open();

                val = cmd.ExecuteScalar();
               
                if (Convert.ToString(val) == "SU")
                {
                    //Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "msg", "'" + "<script>alert('Data inseted succesfully');</script>");
                    Response.Write("<script>alert('Request Saved Successfully')</script>");
                   
                    inptnm.Value = "";
                    txtnumber.Value = "";
                    txtmail.Value = "";
                    DiplayAllREcord();
                }
                else
                {
                    Response.Write("<script>alert('Please Add Proper Data')</script>");
                    //Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "msg", "'" + "<script>alert(Email id Exists);<script>");
                }

           
        }
        catch (Exception ex)
        { Response.Write (ex.ToString());}
        finally{
            sqcon.Close();
            sqcon.Dispose();
        }

    }
    private void DiplayAllREcord()
    {
       
        SqlDataAdapter sda = new SqlDataAdapter("Sp_InsertUpadetData 'SELECT','','','',''", sqcon);
        DataTable DtGetAllEmoRecord = new DataTable();
        sda.Fill(DtGetAllEmoRecord);
        grddata.DataSource = DtGetAllEmoRecord;
        grddata.DataBind();
    


    }

    protected void lnkdele_click(object sender, EventArgs e)
    {

        LinkButton lnkbtn = sender as LinkButton;
        //getting particular row linkbutton

        GridViewRow gvrow = lnkbtn.NamingContainer as GridViewRow;

        //getting userid of particular row
       
        string username =Convert.ToString(grddata.DataKeys[gvrow.RowIndex].Value.ToString());
      
      

        SqlCommand  sda = new  SqlCommand("Sp_InsertUpadetData 'Delete',"+username+",'','',''", sqcon);
        int del = 0;
        sqcon.Open();
        del = sda.ExecuteNonQuery();
        sqcon.Close();
        if (del > 0)
        {
            Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "Msg", "'" + "<script>alert('REcored deleted Succesfully');</script>");
           
        }
       
        DiplayAllREcord();
    }
    protected void lnkedit_click(object sender, EventArgs e)
    {
        LinkButton lnkedit = sender as LinkButton;
        GridViewRow grrow = lnkedit.NamingContainer as GridViewRow;
        string strupdateval = Convert.ToString(grddata.DataKeys[grrow.RowIndex].Value.ToString());
        hdnval.Value = "U";
        hdngetid.Value = strupdateval.ToString();
        SqlDataAdapter sda = new SqlDataAdapter("Sp_InsertUpadetData 'EDIT',"+strupdateval.ToString()+",'','',''", sqcon);
        DataTable DtGetAllEmoRecord = new DataTable();
        sda.Fill(DtGetAllEmoRecord);
        inptnm.Value = DtGetAllEmoRecord.Rows[0]["NAME"].ToString();
        txtmail.Value = DtGetAllEmoRecord.Rows[0]["MAIL"].ToString();
        txtnumber.Value = DtGetAllEmoRecord.Rows[0]["NUMBER"].ToString();
        btnsubmit.Text = "Update";
      
    }
  
}

stored procedure


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[Sp_InsertUpadetData]
(
@Action varchar(20),
@name varchar(20),
@number  int ,
@Mail varchar(20),
@Msg varchar(20) out
)
AS
BEGIN
DECLARE @RETURN AS VARCHAR(20)
IF (@Action='INSERT')
    BEGIN
    IF EXISTS(SELECT @Mail FROM insertstudbvalue where MAIL=@Mail )
    BEGIN
    SET @Msg='ER'
    END
    ELSE
    BEGIN
    INSERT INTO insertstudbvalue VALUES(@name , @number, @Mail)
UPDATE insertstudbvalue SET NUMBER=@NUMBER , MAIL=@MAIL WHERE
NAME=@NAME
    SET @Msg='SU'
    END
SELECT @Msg
END
ELSE IF (@Action='SELECT')
BEGIN
SELECT NAME , NUMBER , MAIL FROM insertstudbvalue
END

ELSE IF (@Action='DELETE')
BEGIN
DELETE FROM insertstudbvalue WHERE  NAME=@NAME
END
ELSE IF (@Action='UPDATE')
BEGIN
   
    IF EXISTS(SELECT @Mail FROM insertstudbvalue where MAIL=@Mail And  NAME = @NAME)
    BEGIN
        UPDATE insertstudbvalue SET NUMBER=@NUMBER , MAIL=@MAIL WHERE
        NAME=@NAME
        SET @Msg='SU'
    END
    ELSE
    BEGIN
        IF EXISTS(SELECT @Mail FROM insertstudbvalue where MAIL=@Mail And  NAME <> @NAME)
            BEGIN
               
                SET @Msg='ER'
            END
    END
   
SELECT @Msg
END
ELSE IF (@Action='EDIT')
BEGIN
SELECT NAME , NUMBER , MAIL FROM insertstudbvalue WHERE
NAME=@NAME
END
END

Share This