Rich

Rich

  • NA
  • 3
  • 0

Update, Insert, & Delete Using The Visual Studio On TextBoxes (not datagrid)

Aug 8 2005 10:40 AM

I've modified one of the Microsoft C# Walkthroughs using a parameterized query to use stored
procedures instead of sql statements to pull data from the author table of the pubs database. 
I've added Update, Insert, and Delete buttons to the form but I have no idea on how to wire them
up to pass and retrieve data from the stored procedures (the stored procedures were generated
by the Data Adapter Wizard).  Everything to load and page records works as in the walkthrough.
Any help would be greatly appreciated.

C# Code Follows
=========================================================================

using
System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.Data;

namespace DataModel

{

/// <summary>

/// Summary description for Form1.

/// </summary>

public class Form1 : System.Windows.Forms.Form

{

private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;

private System.Data.SqlClient.SqlCommand sqlSelectCommand1;

private System.Data.SqlClient.SqlCommand sqlInsertCommand1;

private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;

private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;

private System.Data.SqlClient.SqlConnection sqlConnection1;

private DataModel.dsDataSet dsDataSet1;

private System.Windows.Forms.TextBox txtStateParameter;

private System.Windows.Forms.TextBox txtAuthorID;

private System.Windows.Forms.TextBox txtAuthorFName;

private System.Windows.Forms.TextBox txtAuthorLName;

private System.Windows.Forms.TextBox txtAuthorState;

private System.Windows.Forms.Label label1;

private System.Windows.Forms.Label label2;

private System.Windows.Forms.Label label3;

private System.Windows.Forms.Label label4;

private System.Windows.Forms.Label label5;

private System.Windows.Forms.Button btnShow;

private System.Windows.Forms.Button btnPrevious;

private System.Windows.Forms.Button btnNext;

private System.Windows.Forms.TextBox txtPosition;

private System.Windows.Forms.Button btnUpdate;

private System.Windows.Forms.Button btnInsert;

private System.Windows.Forms.Button btnDelete;

/// <summary>

/// Required designer variable.

/// </summary>

private System.ComponentModel.Container components = null;

public Form1()

{

//

// Required for Windows Form Designer support

//

InitializeComponent();

//

// TODO: Add any constructor code after InitializeComponent call

//

}

/// <summary>

/// Clean up any resources being used.

/// </summary>

protected override void Dispose( bool disposing )

{

if( disposing )

{

if (components != null)

{

components.Dispose();

}

}

base.Dispose( disposing );

}

#region Windows Form Designer generated code

/// <summary>

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

/// </summary>

private void InitializeComponent()

{

this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();

this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();

this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();

this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();

this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();

this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();

this.dsDataSet1 = new DataModel.dsDataSet();

this.txtStateParameter = new System.Windows.Forms.TextBox();

this.txtAuthorID = new System.Windows.Forms.TextBox();

this.txtAuthorFName = new System.Windows.Forms.TextBox();

this.txtAuthorLName = new System.Windows.Forms.TextBox();

this.txtAuthorState = new System.Windows.Forms.TextBox();

this.label1 = new System.Windows.Forms.Label();

this.label2 = new System.Windows.Forms.Label();

this.label3 = new System.Windows.Forms.Label();

this.label4 = new System.Windows.Forms.Label();

this.label5 = new System.Windows.Forms.Label();

this.btnShow = new System.Windows.Forms.Button();

this.btnPrevious = new System.Windows.Forms.Button();

this.btnNext = new System.Windows.Forms.Button();

this.txtPosition = new System.Windows.Forms.TextBox();

this.btnUpdate = new System.Windows.Forms.Button();

this.btnInsert = new System.Windows.Forms.Button();

this.btnDelete = new System.Windows.Forms.Button();

((System.ComponentModel.ISupportInitialize)(this.dsDataSet1)).BeginInit();

this.SuspendLayout();

//

// sqlDataAdapter1

//

this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;

this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;

this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;

this.sqlDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {

new System.Data.Common.DataTableMapping("Table", "authors", new System.Data.Common.DataColumnMapping[] {

new System.Data.Common.DataColumnMapping("au_id", "au_id"),

new System.Data.Common.DataColumnMapping("au_lname", "au_lname"),

new System.Data.Common.DataColumnMapping("au_fname", "au_fname"),

new System.Data.Common.DataColumnMapping("state", "state")})});

this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;

//

// sqlSelectCommand1

//

this.sqlSelectCommand1.CommandText = "[DataModelSelectCommand]";

this.sqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure;

this.sqlSelectCommand1.Connection = this.sqlConnection1;

this.sqlSelectCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));

this.sqlSelectCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Param2", System.Data.SqlDbType.VarChar, 2, "state"));

//

// sqlInsertCommand1

//

this.sqlInsertCommand1.CommandText = "[DataModelInsertCommand]";

this.sqlInsertCommand1.CommandType = System.Data.CommandType.StoredProcedure;

this.sqlInsertCommand1.Connection = this.sqlConnection1;

this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));

this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@au_id", System.Data.SqlDbType.VarChar, 11, "au_id"));

this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"));

this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"));

this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@state", System.Data.SqlDbType.VarChar, 2, "state"));

//

// sqlUpdateCommand1

//

this.sqlUpdateCommand1.CommandText = "[DataModelUpdateCommand]";

this.sqlUpdateCommand1.CommandType = System.Data.CommandType.StoredProcedure;

this.sqlUpdateCommand1.Connection = this.sqlConnection1;

this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));

this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@au_id", System.Data.SqlDbType.VarChar, 11, "au_id"));

this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"));

this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"));

this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@state", System.Data.SqlDbType.VarChar, 2, "state"));

this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "au_id", System.Data.DataRowVersion.Original, null));

this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "au_fname", System.Data.DataRowVersion.Original, null));

this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "au_lname", System.Data.DataRowVersion.Original, null));

this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_state", System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "state", System.Data.DataRowVersion.Original, null));

//

// sqlDeleteCommand1

//

this.sqlDeleteCommand1.CommandText = "[DataModelDeleteCommand]";

this.sqlDeleteCommand1.CommandType = System.Data.CommandType.StoredProcedure;

this.sqlDeleteCommand1.Connection = this.sqlConnection1;

this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));

this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "au_id", System.Data.DataRowVersion.Original, null));

this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "au_fname", System.Data.DataRowVersion.Original, null));

this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "au_lname", System.Data.DataRowVersion.Original, null));

this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_state", System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "state", System.Data.DataRowVersion.Original, null));

//

// sqlConnection1

//

this.sqlConnection1.ConnectionString = "workstation id=COLOSSUS;packet size=4096;user id=sa;data source=QUEEN01;persist s" +

"ecurity info=True;initial catalog=pubs;password=\"SNWb@Ckd0Re\"";

//

// dsDataSet1

//

this.dsDataSet1.DataSetName = "dsDataSet";

this.dsDataSet1.Locale = new System.Globalization.CultureInfo("en-US");

//

// txtStateParameter

//

this.txtStateParameter.Location = new System.Drawing.Point(136, 24);

this.txtStateParameter.Name = "txtStateParameter";

this.txtStateParameter.Size = new System.Drawing.Size(48, 20);

this.txtStateParameter.TabIndex = 0;

this.txtStateParameter.Text = "";

//

// txtAuthorID

//

this.txtAuthorID.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dsDataSet1, "authors.au_id"));

this.txtAuthorID.Location = new System.Drawing.Point(120, 64);

this.txtAuthorID.Name = "txtAuthorID";

this.txtAuthorID.TabIndex = 1;

this.txtAuthorID.Text = "";

//

// txtAuthorFName

//

this.txtAuthorFName.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dsDataSet1, "authors.au_fname"));

this.txtAuthorFName.Location = new System.Drawing.Point(120, 104);

this.txtAuthorFName.Name = "txtAuthorFName";

this.txtAuthorFName.TabIndex = 2;

this.txtAuthorFName.Text = "";

//

// txtAuthorLName

//

this.txtAuthorLName.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dsDataSet1, "authors.au_lname"));

this.txtAuthorLName.Location = new System.Drawing.Point(120, 144);

this.txtAuthorLName.Name = "txtAuthorLName";

this.txtAuthorLName.TabIndex = 3;

this.txtAuthorLName.Text = "";

//

// txtAuthorState

//

this.txtAuthorState.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dsDataSet1, "authors.state"));

this.txtAuthorState.Location = new System.Drawing.Point(120, 184);

this.txtAuthorState.Name = "txtAuthorState";

this.txtAuthorState.TabIndex = 4;

this.txtAuthorState.Text = "";

//

// label1

//

this.label1.Location = new System.Drawing.Point(40, 24);

this.label1.Name = "label1";

this.label1.Size = new System.Drawing.Size(88, 23);

this.label1.TabIndex = 5;

this.label1.Text = "Enter state code";

//

// label2

//

this.label2.Location = new System.Drawing.Point(40, 64);

this.label2.Name = "label2";

this.label2.Size = new System.Drawing.Size(64, 23);

this.label2.TabIndex = 6;

this.label2.Text = "Author ID";

//

// label3

//

this.label3.Location = new System.Drawing.Point(40, 104);

this.label3.Name = "label3";

this.label3.Size = new System.Drawing.Size(64, 23);

this.label3.TabIndex = 7;

this.label3.Text = "First Name";

//

// label4

//

this.label4.Location = new System.Drawing.Point(40, 144);

this.label4.Name = "label4";

this.label4.Size = new System.Drawing.Size(64, 23);

this.label4.TabIndex = 8;

this.label4.Text = "Last Name";

//

// label5

//

this.label5.Location = new System.Drawing.Point(40, 184);

this.label5.Name = "label5";

this.label5.Size = new System.Drawing.Size(48, 23);

this.label5.TabIndex = 9;

this.label5.Text = "State";

//

// btnShow

//

this.btnShow.Location = new System.Drawing.Point(192, 24);

this.btnShow.Name = "btnShow";

this.btnShow.TabIndex = 10;

this.btnShow.Text = "Show";

this.btnShow.Click += new System.EventHandler(this.btnShow_Click);

//

// btnPrevious

//

this.btnPrevious.Location = new System.Drawing.Point(48, 232);

this.btnPrevious.Name = "btnPrevious";

this.btnPrevious.TabIndex = 11;

this.btnPrevious.Text = "Previous";

this.btnPrevious.Click += new System.EventHandler(this.btnPrevious_Click);

//

// btnNext

//

this.btnNext.Location = new System.Drawing.Point(224, 232);

this.btnNext.Name = "btnNext";

this.btnNext.TabIndex = 12;

this.btnNext.Text = "Next";

this.btnNext.Click += new System.EventHandler(this.btnNext_Click);

//

// txtPosition

//

this.txtPosition.Enabled = false;

this.txtPosition.Location = new System.Drawing.Point(144, 232);

this.txtPosition.Name = "txtPosition";

this.txtPosition.Size = new System.Drawing.Size(56, 20);

this.txtPosition.TabIndex = 13;

this.txtPosition.Text = "";

//

// btnUpdate

//

this.btnUpdate.Location = new System.Drawing.Point(256, 64);

this.btnUpdate.Name = "btnUpdate";

this.btnUpdate.TabIndex = 14;

this.btnUpdate.Text = "Update";

this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);

//

// btnInsert

//

this.btnInsert.Location = new System.Drawing.Point(256, 104);

this.btnInsert.Name = "btnInsert";

this.btnInsert.TabIndex = 15;

this.btnInsert.Text = "Insert";

this.btnInsert.Click += new System.EventHandler(this.btnInsert_Click);

//

// btnDelete

//

this.btnDelete.Location = new System.Drawing.Point(256, 144);

this.btnDelete.Name = "btnDelete";

this.btnDelete.TabIndex = 16;

this.btnDelete.Text = "Delete";

this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click);

//

// Form1

//

this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

this.ClientSize = new System.Drawing.Size(368, 273);

this.Controls.Add(this.btnDelete);

this.Controls.Add(this.btnInsert);

this.Controls.Add(this.btnUpdate);

this.Controls.Add(this.txtPosition);

this.Controls.Add(this.btnNext);

this.Controls.Add(this.btnPrevious);

this.Controls.Add(this.btnShow);

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.txtAuthorState);

this.Controls.Add(this.txtAuthorLName);

this.Controls.Add(this.txtAuthorFName);

this.Controls.Add(this.txtAuthorID);

this.Controls.Add(this.txtStateParameter);

this.Name = "Form1";

this.Text = "Form1";

((System.ComponentModel.ISupportInitialize)(this.dsDataSet1)).EndInit();

this.ResumeLayout(false);

}

#endregion

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main()

{

Application.Run(new Form1());

}

private void btnShow_Click(object sender, System.EventArgs e)

{

sqlDataAdapter1.SelectCommand.Parameters["@Param2"].Value = txtStateParameter.Text;

dsDataSet1.Clear();

sqlDataAdapter1.Fill(dsDataSet1);

ShowPosition();

}

private void btnPrevious_Click(object sender, System.EventArgs e)

{

this.BindingContext[dsDataSet1, "authors"].Position -=1;

ShowPosition();

}

private void btnNext_Click(object sender, System.EventArgs e)

{

this.BindingContext[dsDataSet1, "authors"].Position +=1 ;

ShowPosition();

}

private void ShowPosition()

{

int iCnt;

int iPos;

iCnt = this.BindingContext[dsDataSet1, "authors"].Count;

iPos = this.BindingContext[dsDataSet1, "authors"].Position + 1;

if(iCnt == 0)

{

txtPosition.Text = "(No records)";

}

else

{

txtPosition.Text = iPos.ToString() + " of " + iCnt.ToString() ;

}

}

private void btnUpdate_Click(object sender, System.EventArgs e)

{

}

private void btnInsert_Click(object sender, System.EventArgs e)

{

}

private void btnDelete_Click(object sender, System.EventArgs e)

{

}

}

}