Solved

Build a Webform using ASP.NET Insert/update data into a  MS SQL table

Posted on 2016-08-15
14
39 Views
Last Modified: 2016-09-09
Hi,

Has some one give me some guidance to build a simple WEbform to connect to the a single table and insert and update data
0
Comment
Question by:ken hanse
  • 8
  • 5
14 Comments
 
LVL 18

Expert Comment

by:Rajar Ahmed
ID: 41757381
1
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 41757434
Kindly go through the below

1. First you need to create a table in Database like below

Column Name          Data Type                   Allow Nulls
productid            Int(IDENTITY=TRUE)                Yes
productname           varchar(50)                         Yes
price                           varchar(50)                        Yes

2. Now create one new stored procedure “Crudoperations
 CREATE PROCEDURE CrudOperations 
@productid int = 0, 
@productname varchar(50)=null, 
@price int=0, 
@status varchar(50) 
AS 
BEGIN 
SET NOCOUNT ON; 
--- Insert New Records 
IF @status='INSERT' 
BEGIN 
INSERT INTO productinfo1(productname,price) VALUES(@productname,@price) 
END 
--- Select Records in Table 
IF @status='SELECT' 
BEGIN 
SELECT productid,productname,price FROM productinfo1 
END 
--- Update Records in Table  
IF @status='UPDATE' 
BEGIN 
UPDATE productinfo1 SET productname=@productname,price=@price WHERE productid=@productid 
END 
--- Delete Records from Table 
IF @status='DELETE' 
BEGIN 
DELETE FROM productinfo1 where productid=@productid 
END 
SET NOCOUNT OFF 
END

Open in new window



3.  Now open your .aspx Page and write a code like below
 <html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>GridView Crud (Select Insert Edit Update Delete) Operations using Single Stored Procedure in ASP.Net</title>
<style type="text/css">
.GridviewDiv {font-size: 100%; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
.headerstyle
{
color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;background-color: #df5015;padding:0.5em 0.5em 0.5em 0.5em;text-align:center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="GridviewDiv">
<asp:GridView runat="server" ID="gvDetails" ShowFooter="true" AllowPaging="true" PageSize="10" AutoGenerateColumns="false" DataKeyNames="productid,productname" OnPageIndexChanging="gvDetails_PageIndexChanging" OnRowCancelingEdit="gvDetails_RowCancelingEdit"
OnRowEditing="gvDetails_RowEditing" OnRowUpdating="gvDetails_RowUpdating" OnRowDeleting="gvDetails_RowDeleting" OnRowCommand ="gvDetails_RowCommand" >
<HeaderStyle CssClass="headerstyle" />
<Columns>
<asp:BoundField DataField="productid" HeaderText="Product Id" ReadOnly="true" />
<asp:TemplateField HeaderText="Product Name">
<ItemTemplate>
<asp:Label ID="lblProductname" runat="server" Text='<%# Eval("productname")%>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtProductname" runat="server" Text='<%# Eval("productname")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtpname" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText = "Price">
<ItemTemplate>
<asp:Label ID="lblPrice" runat="server" Text='<%# Eval("price")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtProductprice" runat="server" Text='<%# Eval("price")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtprice" runat="server" />
<asp:Button ID="btnAdd" CommandName="AddNew" runat="server" Text="Add" />
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" ShowDeleteButton="true" />
</Columns>
</asp:GridView>
<asp:Label ID="lblresult" runat="server"></asp:Label>
</div>
</form>
</body>
</html>

Open in new window


4. After completion of aspx page add following namespaces in codebehind
using System;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;

Open in new window


5. After completion of adding namespaces you need to write the code like as shown below

 protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridview();
}
}
protected void BindGridview()
{
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection("Data Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("crudoperations", con);
cmd.CommandType= CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@status","SELECT");
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
gvDetails.DataSource = ds;
gvDetails.DataBind();
}
else {
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvDetails.DataSource = ds;
gvDetails.DataBind();
int columncount = gvDetails.Rows[0].Cells.Count;
gvDetails.Rows[0].Cells.Clear();
gvDetails.Rows[0].Cells.Add(new TableCell());
gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
gvDetails.Rows[0].Cells[0].Text = "No Records Found";
}
}
}
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtname = (TextBox)gvDetails.FooterRow.FindControl("txtpname");
TextBox txtprice = (TextBox)gvDetails.FooterRow.FindControl("txtprice");
crudoperations("INSERT", txtname.Text, txtprice.Text, 0);
}
}
protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
gvDetails.EditIndex = e.NewEditIndex;
BindGridview();
}
protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvDetails.EditIndex = -1;
BindGridview();
}
protected void gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvDetails.PageIndex = e.NewPageIndex;
BindGridview();
}
protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int productid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["productid"].ToString());
TextBox txtname = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtProductname");
TextBox txtprice = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtProductprice");
crudoperations("UPDATE",txtname.Text,txtprice.Text,productid);
}
protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int productid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["productid"].ToString());
string productname = gvDetails.DataKeys[e.RowIndex].Values["productname"].ToString();
crudoperations("DELETE",productname,"",productid);
}
protected void crudoperations(string status, string productname, string price, int productid)
{
using (SqlConnection con = new SqlConnection("Data Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("crudoperations", con);
cmd.CommandType= CommandType.StoredProcedure;
if(status=="INSERT")
{
cmd.Parameters.AddWithValue("@status",status);
cmd.Parameters.AddWithValue("@productname",productname);
cmd.Parameters.AddWithValue("@price",price);
}
else if(status=="UPDATE")
{
cmd.Parameters.AddWithValue("@status",status);
cmd.Parameters.AddWithValue("@productname",productname);
cmd.Parameters.AddWithValue("@price",price);
cmd.Parameters.AddWithValue("@productid",productid);
}
else if(status=="DELETE")
{
cmd.Parameters.AddWithValue("@status",status);
cmd.Parameters.AddWithValue("@productid",productid);
}
cmd.ExecuteNonQuery();
lblresult.ForeColor = Color.Green;
lblresult.Text = productname+" details "+status.ToLower()+"d successfully";
gvDetails.EditIndex = -1;
BindGridview();
}
}

Open in new window

1
 

Author Comment

by:ken hanse
ID: 41757440
where we add the add  namespaces, just after loading default page?
0
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 41757450
On the top your back end code i.e.   in aspx.cs file
0
 

Author Comment

by:ken hanse
ID: 41758687
1.PNG
0
 

Author Comment

by:ken hanse
ID: 41758689
first step
0
 

Author Comment

by:ken hanse
ID: 41758691
please see my screenshot. where I'm I start with. Sorry, I'm a beginner for this.
2.PNG
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 19

Accepted Solution

by:
Manoj Patil earned 500 total points
ID: 41759211
Check attached sample code file
CRUD.zip
0
 

Author Closing Comment

by:ken hanse
ID: 41759226
great help. thank you.
0
 

Author Comment

by:ken hanse
ID: 41787260
How to run this application, can you please provide me the step by step guide?
0
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 41787334
Hit F5 to build and run the project in debug mode.
0
 

Author Comment

by:ken hanse
ID: 41788978
Here what I'm getting after Hit F5.
Doc1.docx
0
 

Author Comment

by:ken hanse
ID: 41790593
any update on this Manoj
0
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 41791145
Hey, it is the Default.aspx page which is running by default.
You can change it in Solution Explorer  by simply right click on the page which you wanted to Run  and select option Set As Start Page   and just run it
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now