how to insert delete update a record in our database

Question

i want to insert update delete a record in asp.net using dataset by calling a storeprocedure and shows the message that record saved successfully the message is to be handled by store procedure. all the fields are entered in textbox not in datagrid. thanking u ipsita.

Satyapriya Nayak · Answer

Create table student (sid varchar(50)sname varchar(50)smarks intsaddress varchar (50)year varchar(50)) CREATE PROCEDURE display AS select * from student CREATE PROCEDURE insert1 (@sid varchar(50)@sname varchar(50)@smarks int@saddress varchar (50)@year varchar(50)) AS insert student(sidsnamesmarkssaddressyear) values (@sid@sname@smarks@saddress@year) CREATE PROCEDURE update1 (@sid varchar(50)@sname varchar(50)@smarks int@saddress varchar (50)@year varchar(50)) AS update student set sname=@snamesmarks=@smarkssaddress=@saddressyear=@year where sid=@sid CREATE PROCEDURE delete1 (@sid varchar(50)) AS delete from student where sid=@sid using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace Ado_Project_Data_Insert_Update_Save_Delete_Using_Stored_Procedures_In_CSharp { /// /// Summary description for Form1. /// public class Form1 : System.Windows.Forms.Form { SqlConnection con=new SqlConnection(workstation id=\HOME-Z8CKE1NER2\;packet size=4096;user id=sa;initial catalog=pint + u;persist security info=False); SqlCommand com; SqlDataAdapter sqlda; DataSet ds=new DataSet(); // SqlCommandBuilder objcom; String str; int flag; // DataTable dt; // DataRow dr; BindingManagerBase bm; internal System.Windows.Forms.TextBox txtminmarks; internal System.Windows.Forms.Button btnminmarks; internal System.Windows.Forms.TextBox txtmaxmarks; internal System.Windows.Forms.Button btnmaxmarks; internal System.Windows.Forms.Button btnsave; internal System.Windows.Forms.Button btnload1; internal System.Windows.Forms.Button btnclose; internal System.Windows.Forms.Button btnload; internal System.Windows.Forms.Button btndelete; internal System.Windows.Forms.Button btnmodify; internal System.Windows.Forms.Button btnadd; internal System.Windows.Forms.Button btnlast; internal System.Windows.Forms.Button btnprevious; internal System.Windows.Forms.Button btnnext; internal System.Windows.Forms.Button btnfirst; internal System.Windows.Forms.Label Label5; internal System.Windows.Forms.Label Label4; internal System.Windows.Forms.Label Label3; internal System.Windows.Forms.Label Label2; internal System.Windows.Forms.Label Label1; internal System.Windows.Forms.TextBox txtyear; internal System.Windows.Forms.TextBox txtaddress; internal System.Windows.Forms.TextBox txtmarks; internal System.Windows.Forms.TextBox txtname; internal System.Windows.Forms.TextBox txtid; internal System.Windows.Forms.DataGrid dataGrid1; /// /// Required designer variable. /// private System.ComponentModel.Container components = null; public Form1() { // // Required for Windows Form Designer support // InitializeComponent(); // // TODO: Add any constructor code after InitializeComponent call // } /// /// Clean up any resources being used. /// protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); } #region Windows Form Designer generated code /// /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// private void InitializeComponent() { this.txtminmarks = new System.Windows.Forms.TextBox(); this.btnminmarks = new System.Windows.Forms.Button(); this.txtmaxmarks = new System.Windows.Forms.TextBox(); this.btnmaxmarks = new System.Windows.Forms.Button(); this.btnsave = new System.Windows.Forms.Button(); this.btnload1 = new System.Windows.Forms.Button(); this.btnclose = new System.Windows.Forms.Button(); this.btnload = new System.Windows.Forms.Button(); this.dataGrid1 = new System.Windows.Forms.DataGrid(); this.btndelete = new System.Windows.Forms.Button(); this.btnmodify = new System.Windows.Forms.Button(); this.btnadd = new System.Windows.Forms.Button(); this.btnlast = new System.Windows.Forms.Button(); this.btnprevious = new System.Windows.Forms.Button(); this.btnnext = new System.Windows.Forms.Button(); this.btnfirst = new System.Windows.Forms.Button(); this.Label5 = new System.Windows.Forms.Label(); this.Label4 = new System.Windows.Forms.Label(); this.Label3 = new System.Windows.Forms.Label(); this.Label2 = new System.Windows.Forms.Label(); this.Label1 = new System.Windows.Forms.Label(); this.txtyear = new System.Windows.Forms.TextBox(); this.txtaddress = new System.Windows.Forms.TextBox(); this.txtmarks = new System.Windows.Forms.TextBox(); this.txtname = new System.Windows.Forms.TextBox(); this.txtid = new System.Windows.Forms.TextBox(); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit(); this.SuspendLayout(); // // txtminmarks // this.txtminmarks.Location = new System.Drawing.Point(527 214); this.txtminmarks.Name = txtminmarks; this.txtminmarks.Size = new System.Drawing.Size(72 20); this.txtminmarks.TabIndex = 99; this.txtminmarks.Text = ; // // btnminmarks // this.btnminmarks.Location = new System.Drawing.Point(527 262); this.btnminmarks.Name = btnminmarks; this.btnminmarks.TabIndex = 98; this.btnminmarks.Text = MinMarks; this.btnminmarks.Click += new System.EventHandler(this.btnminmarks_Click); // // txtmaxmarks // this.txtmaxmarks.Location = new System.Drawing.Point(623 214); this.txtmaxmarks.Name = txtmaxmarks; this.txtmaxmarks.Size = new System.Drawing.Size(64 20); this.txtmaxmarks.TabIndex = 97; this.txtmaxmarks.Text = ; // // btnmaxmarks // this.btnmaxmarks.Location = new System.Drawing.Point(615 262); this.btnmaxmarks.Name = btnmaxmarks; this.btnmaxmarks.TabIndex = 96; this.btnmaxmarks.Text = Max Marks; this.btnmaxmarks.Click += new System.EventHandler(this.btnmaxmarks_Click); // // btnsave // this.btnsave.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)) ((System.Byte)(224)) ((System.Byte)(192))); this.btnsave.Location = new System.Drawing.Point(191 294); this.btnsave.Name = btnsave; this.btnsave.TabIndex = 95; this.btnsave.Text = Save; this.btnsave.Click += new System.EventHandler(this.btnsave_Click); // // btnload1 // this.btnload1.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)) ((System.Byte)(224)) ((System.Byte)(192))); this.btnload1.Location = new System.Drawing.Point(135 198); this.btnload1.Name = btnload1; this.btnload1.Size = new System.Drawing.Size(144 23); this.btnload1.TabIndex = 94; this.btnload1.Text = Load Data In Textbox; this.btnload1.Click += new System.EventHandler(this.btnload1_Click); // // btnclose // this.btnclose.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)) ((System.Byte)(224)) ((System.Byte)(192))); this.btnclose.Location = new System.Drawing.Point(407 294); this.btnclose.Name = btnclose; this.btnclose.Size = new System.Drawing.Size(72 23); this.btnclose.TabIndex = 93; this.btnclose.Text = Form Close; this.btnclose.Click += new System.EventHandler(this.btnclose_Click); // // btnload // this.btnload.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)) ((System.Byte)(224)) ((System.Byte)(192))); this.btnload.Location = new System.Drawing.Point(375 206); this.btnload.Name = btnload; this.btnload.Size = new System.Drawing.Size(96 23); this.btnload.TabIndex = 92; this.btnload.Text = Load Records; this.btnload.Click += new System.EventHandler(this.btnload_Click); // // dataGrid1 // this.dataGrid1.DataMember = ; this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText; this.dataGrid1.Location = new System.Drawing.Point(279 18); this.dataGrid1.Name = dataGrid1; this.dataGrid1.Size = new System.Drawing.Size(400 160); this.dataGrid1.TabIndex = 91; // // btndelete // this.btndelete.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)) ((System.Byte)(224)) ((System.Byte)(192))); this.btndelete.Location = new System.Drawing.Point(279 294); this.btndelete.Name = btndelete; this.btndelete.Size = new System.Drawing.Size(72 23); this.btndelete.TabIndex = 90; this.btndelete.Text = Delete; this.btndelete.Click += new System.EventHandler(this.btndelete_Click); // // btnmodify // this.btnmodify.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)) ((System.Byte)(224)) ((System.Byte)(192))); this.btnmodify.Location = new System.Drawing.Point(103 294); this.btnmodify.Name = btnmodify; this.btnmodify.Size = new System.Drawing.Size(72 23); this.btnmodify.TabIndex = 89; this.btnmodify.Text = Modify; this.btnmodify.Click += new System.EventHandler(this.btnmodify_Click); // // btnadd // this.btnadd.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)) ((System.Byte)(224)) ((System.Byte)(192))); this.btnadd.Location = new System.Drawing.Point(15 294); this.btnadd.Name = btnadd; this.btnadd.Size = new System.Drawing.Size(72 23); this.btnadd.TabIndex = 88; this.btnadd.Text = Add; this.btnadd.Click += new System.EventHandler(this.btnadd_Click); // // btnlast // this.btnlast.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)) ((System.Byte)(224)) ((System.Byte)(192))); this.btnlast.Location = new System.Drawing.Point(279 238); this.btnlast.Name = btnlast; this.btnlast.Size = new System.Drawing.Size(72 23); this.btnlast.TabIndex = 87; this.btnlast.Text = Last; this.btnlast.Click += new System.EventHandler(this.btnlast_Click); // // btnprevious // this.btnprevious.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)) ((System.Byte)(224)) ((System.Byte)(192))); this.btnprevious.Location = new System.Drawing.Point(191 238); this.btnprevious.Name = btnprevious; this.btnprevious.Size = new System.Drawing.Size(72 23); this.btnprevious.TabIndex = 86; this.btnprevious.Text = Previous; this.btnprevious.Click += new System.EventHandler(this.btnprevious_Click); // // btnnext // this.btnnext.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)) ((System.Byte)(224)) ((System.Byte)(192))); this.btnnext.Location = new System.Drawing.Point(103 238); this.btnnext.Name = btnnext; this.btnnext.Size = new System.Drawing.Size(72 23); this.btnnext.TabIndex = 85; this.btnnext.Text = Next; this.btnnext.Click += new System.EventHandler(this.btnnext_Click); // // btnfirst // this.btnfirst.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)) ((System.Byte)(224)) ((System.Byte)(192))); this.btnfirst.Location = new System.Drawing.Point(15 238); this.btnfirst.Name = btnfirst; this.btnfirst.Size = new System.Drawing.Size(72 23); this.btnfirst.TabIndex = 84; this.btnfirst.Text = First; this.btnfirst.Click += new System.EventHandler(this.btnfirst_Click); // // Label5 // this.Label5.Location = new System.Drawing.Point(19 146); this.Label5.Name = Label5; this.Label5.TabIndex = 83; this.Label5.Text = Year; // // Label4 // this.Label4.Location = new System.Drawing.Point(19 114); this.Label4.Name = Label4; this.Label4.TabIndex = 82; this.Label4.Text = Student Address; // // Label3 // this.Label3.Location = new System.Drawing.Point(19 82); this.Label3.Name = Label3; this.Label3.TabIndex = 81; this.Label3.Text = Student Marks; // // Label2 // this.Label2.Location = new System.Drawing.Point(19 50); this.Label2.Name = Label2; this.Label2.TabIndex = 80; this.Label2.Text = Student Name; // // Label1 // this.Label1.Location = new System.Drawing.Point(19 18); this.Label1.Name = Label1; this.Label1.TabIndex = 79; this.Label1.Text = Student Id; // // txtyear // this.txtyear.Location = new System.Drawing.Point(139 146); this.txtyear.Name = txtyear; this.txtyear.TabIndex = 78; this.txtyear.Text = ; // // txtaddress // this.txtaddress.Location = new System.Drawing.Point(139 114); this.txtaddress.Name = txtaddress; this.txtaddress.TabIndex = 77; this.txtaddress.Text = ; // // txtmarks // this.txtmarks.Location = new System.Drawing.Point(139 82); this.txtmarks.Name = txtmarks; this.txtmarks.TabIndex = 76; this.txtmarks.Text = ; // // txtname // this.txtname.Location = new System.Drawing.Point(139 50); this.txtname.Name = txtname; this.txtname.TabIndex = 75; this.txtname.Text = ; // // txtid // this.txtid.Location = new System.Drawing.Point(139 18); this.txtid.Name = txtid; this.txtid.TabIndex = 74; this.txtid.Text = ; // // Form1 // this.AutoScaleBaseSize = new System.Drawing.Size(5 13); this.ClientSize = new System.Drawing.Size(704 334); this.Controls.Add(this.txtminmarks); this.Controls.Add(this.btnminmarks); this.Controls.Add(this.txtmaxmarks); this.Controls.Add(this.btnmaxmarks); this.Controls.Add(this.btnsave); this.Controls.Add(this.btnload1); this.Controls.Add(this.btnclose); this.Controls.Add(this.btnload); this.Controls.Add(this.dataGrid1); this.Controls.Add(this.btndelete); this.Controls.Add(this.btnmodify); this.Controls.Add(this.btnadd); this.Controls.Add(this.btnlast); this.Controls.Add(this.btnprevious); this.Controls.Add(this.btnnext); this.Controls.Add(this.btnfirst); this.Controls.Add(this.Label5); this.Controls.Add(this.Label4); this.Controls.Add(this.Label3); this.Controls.Add(this.Label2); this.Controls.Add(this.Label1); this.Controls.Add(this.txtyear); this.Controls.Add(this.txtaddress); this.Controls.Add(this.txtmarks); this.Controls.Add(this.txtname); this.Controls.Add(this.txtid); this.Name = Form1; this.Text = Form1; ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit(); this.ResumeLayout(false); } #endregion /// /// The main entry point for the application. /// [STAThread] static void Main() { Application.Run(new Form1()); } private void btnload1_Click(object sender System.EventArgs e) { com = new SqlCommand(display con); sqlda = new SqlDataAdapter(com); ds = new DataSet(); sqlda.Fill(ds student); // 'dt = ds.Tables(student); txtid.DataBindings.Add(Text ds student.sid); txtname.DataBindings.Add(Text ds student.sname); txtmarks.DataBindings.Add(Text ds student.smarks); txtaddress.DataBindings.Add(Text ds student.saddress); txtyear.DataBindings.Add(Text ds student.year); bm = this.BindingContext[ds student]; bm.Position = 0; } private void btnfirst_Click(object sender System.EventArgs e) { bm.Position=0; } private void btnnext_Click(object sender System.EventArgs e) { bm.Position+=1; } private void btnprevious_Click(object sender System.EventArgs e) { bm.Position-=1; } private void btnlast_Click(object sender System.EventArgs e) { bm.Position=bm.Count-1; } private void btnadd_Click(object sender System.EventArgs e) { flag = 1; txtid.Text = ; txtname.Text = ; txtmarks.Text = ; txtaddress.Text = ; txtyear.Text = ; txtid.Focus(); } private void btnmodify_Click(object sender System.EventArgs e) { flag=2; } private void btnsave_Click(object sender System.EventArgs e) { if (flag==1) { con.Open(); com = new SqlCommand(insert1 con); com.CommandType = CommandType.StoredProcedure; com.Parameters.Add(@sid txtid.Text); com.Parameters.Add(@sname txtname.Text); com.Parameters.Add(@smarksint.Parse (txtmarks.Text)); com.Parameters.Add(@saddress txtaddress.Text); com.Parameters.Add(@year txtyear.Text); com.ExecuteNonQuery(); con.Close(); MessageBox.Show(record added); } else if(flag==2) { con.Open(); com = new SqlCommand(update1 con); com.CommandType = CommandType.StoredProcedure; com.Parameters.Add(@sid txtid.Text); com.Parameters.Add(@sname txtname.Text); com.Parameters.Add(@smarks int.Parse(txtmarks.Text)); com.Parameters.Add(@saddress txtaddress.Text); com.Parameters.Add(@year txtyear.Text); com.ExecuteNonQuery(); con.Close(); MessageBox.Show(record updated); } } private void btndelete_Click(object sender System.EventArgs e) { con.Open(); com = new SqlCommand(delete1 con); com.CommandType = CommandType.StoredProcedure; com.Parameters.Add(@sid txtid.Text); com.ExecuteNonQuery(); con.Close(); MessageBox.Show(record Deleted); } private void btnload_Click(object sender System.EventArgs e) { con.Open(); com = new SqlCommand(display con); com.CommandType = CommandType.StoredProcedure; sqlda = new SqlDataAdapter(com); ds = new DataSet(); sqlda.Fill(ds student); dataGrid1.DataSource = ds; dataGrid1.DataMember = student; con.Close(); } private void btnminmarks_Click(object sender System.EventArgs e) { con.Open(); str = select min(smarks) from student; com = new SqlCommand(str con); txtminmarks.Text = com.ExecuteScalar().ToString(); con.Close(); } private void btnmaxmarks_Click(object sender System.EventArgs e) { con.Open(); str = select max(smarks) from student; com = new SqlCommand(str con); txtmaxmarks.Text = com.ExecuteScalar().ToString(); con.Close(); } private void btnclose_Click(object sender System.EventArgs e) { this.Close(); } } }