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
