Re: Urgent ??? - Update Data Source

From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 03/18/04 

Date: Thu, 18 Mar 2004 09:30:00 -0500

Lianna:

Check out the question you asked 15 minutes ago. Get back to me on those
questions and I'll do what I can to help you.

Cheers,

Bill
"Lianna" <anonymous@discussions.microsoft.com> wrote in message
news:19162DA8-3007-4858-B005-1368BD9DB2A5@microsoft.com...
> I'm facing problem to update the data source after I make changes to the
data table. I try to call DataAdapter.Update() to transmit the changes
cached in DataSet to the database, but it didn't work ???
>
> Can any one help me ??? Bekow are the full program can anyone please kind
to check for me, I've been try to solve this problems more than 5 days
>
> Thankssssssss...very.....very much
>
> using System;
> using System.Drawing;
> using System.Collections;
> using System.ComponentModel;
> using System.Windows.Forms;
> using System.IO;
> using System.Data;
> using System.Text;
> using System.Data.SqlServerCe;
> using System.Data.Common;
>
> namespace UpdateDB
> {
> public class Form1 : System.Windows.Forms.Form
> {
> private System.Windows.Forms.Button btnRetrieve;
> private System.Windows.Forms.Button btnUpdate;
> private System.Windows.Forms.TextBox txtTask;
> private System.Windows.Forms.TextBox txtTime;
> private System.Windows.Forms.TextBox txtDate;
> private System.Windows.Forms.MainMenu mainMenu1;
> private System.Windows.Forms.Button btnShow;
> private System.Windows.Forms.DataGrid g1;
> SqlCeCommand cmdSelect = new SqlCeCommand();
> SqlCeCommand cmdUpdate = new SqlCeCommand();
> SqlCeCommand cmdInsert = new SqlCeCommand();
> SqlCeCommand cmdDelete = new SqlCeCommand();
> SqlCeParameter prm = new SqlCeParameter();
> SqlCeConnection cnn = new SqlCeConnection(cn);
> SqlCeDataAdapter da = new SqlCeDataAdapter();
> static string cn = @"Data Source = \My Documents\MSFA.sdf";
> string str = "Schedule Data\nDate: {0}\nTime: {1}\nTask: {2}";
> DataTable dt;
> DataSet ds;
>
> public Form1()
> {
> InitializeComponent();
> }
>
> protected override void Dispose( bool disposing )
> {
> 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.mainMenu1 = new System.Windows.Forms.MainMenu();
> this.btnRetrieve = new System.Windows.Forms.Button();
> this.btnUpdate = new System.Windows.Forms.Button();
> this.txtTask = new System.Windows.Forms.TextBox();
> this.txtTime = new System.Windows.Forms.TextBox();
> this.txtDate = new System.Windows.Forms.TextBox();
> this.g1 = new System.Windows.Forms.DataGrid();
> this.btnShow = new System.Windows.Forms.Button();
> //
> // btnRetrieve
> //
> this.btnRetrieve.Location = new System.Drawing.Point(30, 248);
> this.btnRetrieve.Text = "select";
> this.btnRetrieve.Click += new System.EventHandler(this.btnRetrieve_Click);
> //
> // btnUpdate
> //
> this.btnUpdate.Location = new System.Drawing.Point(126, 248);
> this.btnUpdate.Text = "Update";
> this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);
> //
> // txtTask
> //
> this.txtTask.Location = new System.Drawing.Point(6, 184);
> this.txtTask.Size = new System.Drawing.Size(224, 22);
> this.txtTask.Text = "";
> //
> // txtTime
> //
> this.txtTime.Location = new System.Drawing.Point(134, 152);
> this.txtTime.Text = "";
> //
> // txtDate
> //
> this.txtDate.Location = new System.Drawing.Point(6, 152);
> this.txtDate.Text = "";
> //
> // g1
> //
> this.g1.Size = new System.Drawing.Size(240, 112);
> this.g1.Text = "g1";
> //
> // btnShow
> //
> this.btnShow.Location = new System.Drawing.Point(32, 216);
> this.btnShow.Text = "show";
> this.btnShow.Click += new System.EventHandler(this.btnShow_Click);
> //
> // Form1
> //
> this.Controls.Add(this.btnShow);
> this.Controls.Add(this.g1);
> this.Controls.Add(this.btnRetrieve);
> this.Controls.Add(this.btnUpdate);
> this.Controls.Add(this.txtTask);
> this.Controls.Add(this.txtTime);
> this.Controls.Add(this.txtDate);
> this.Menu = this.mainMenu1;
> this.Text = "Form1";
> this.Load += new System.EventHandler(this.Form1_Load);
>
> }
> #endregion
>
> static void Main()
> {
> Application.Run(new Form1());
> }
>
> private void btnUpdate_Click(object sender, System.EventArgs e)
> {
> try
> {
> cmdSelect = cnn.CreateCommand();
> cmdSelect.CommandText = "SELECT * FROM Schedule";
> da.SelectCommand = cmdSelect;
>
> cmdUpdate = cnn.CreateCommand();
> cmdUpdate.CommandText = "UPDATE Schedule" +
> "SET DATE = ?,Time = ?, Task = ? " +
> "WHERE Date = ? AND Time = ? AND Task = ? ";
> SqlCeParameterCollection pc = cmdUpdate.Parameters;
> pc.Add("Date_New", SqlDbType.NVarChar,15, "Date");
> pc.Add("Time_New", SqlDbType.NVarChar,15, "Time");
> pc.Add("Task_New", SqlDbType.NVarChar,15, "Task");
>
> prm = cmdUpdate.Parameters.Add("Date_Orig", SqlDbType.NVarChar, 15,
"Date");
> prm = cmdUpdate.Parameters.Add("Time_Orig", SqlDbType.NVarChar, 15,
"Time");
> prm = cmdUpdate.Parameters.Add("Task_Orig", SqlDbType.NVarChar, 15,
"Task");
> prm.SourceVersion = DataRowVersion.Original;
> da.UpdateCommand = cmdUpdate;
>
> cmdDelete = cnn.CreateCommand();
> cmdDelete.CommandText = "DELETE FROM Schedule WHERE Date = ? AND Time = ?
AND Task = ? ";
> prm = cmdDelete.Parameters.Add("Date", SqlDbType.NVarChar, 15, "Date");
> prm = cmdDelete.Parameters.Add("Time", SqlDbType.NVarChar, 15, "Time");
> prm = cmdDelete.Parameters.Add("Task", SqlDbType.NVarChar, 15, "Task");
> prm.SourceVersion = DataRowVersion.Original;
> da.DeleteCommand = cmdDelete;
>
> cmdInsert = cnn.CreateCommand();
> cmdInsert.CommandText = "INSERT INTO Schedule(Date, Time, Task) " +
> "VALUES(?, ?, ?)";
> prm = cmdInsert.Parameters.Add("Date", SqlDbType.NVarChar, 15, "Date");
> prm = cmdInsert.Parameters.Add("Time", SqlDbType.NVarChar, 15, "Time");
> prm = cmdInsert.Parameters.Add("Task", SqlDbType.NVarChar, 15, "Task");
> da.InsertCommand = cmdInsert;
> }
>
> catch (SqlCeException)
> {
> }
> }
>
>
> private void btnShow_Click(object sender, System.EventArgs e)
> {
> try
> {
> da.Update(ds, "Schedule");
> }
>
> catch (SqlCeException){}
>
> }
>
> private void Form1_Load(object sender, System.EventArgs e)
> {
> SqlCeConnection conn = null;
> try
> {
> if (! File.Exists (@"\My Documents\MSFA.sdf"))
> {
> SqlCeEngine engine = new SqlCeEngine (
> @"Data Source = \My Documents\MSFA.sdf");
> engine.CreateDatabase();
> }
>
> conn = new SqlCeConnection (@"Data Source = \My Documents\MSFA.sdf");
> conn.Open();
>
> SqlCeCommand cmd = conn.CreateCommand();
>
> cmd.CommandText =
> "CREATE TABLE Schedule (Date NVARCHAR(15), Time NVARCHAR(15), Task
NVARCHAR(15))";
> cmd.ExecuteNonQuery();
>
> cmd.CommandText =
> "INSERT INTO Schedule (Date, Time, Task)" +
> "VALUES ('3/18/2004', '08.00 am', 'Do BCP')";
> cmd.ExecuteNonQuery();
>
> cmd.CommandText =
> "INSERT INTO Schedule (Date, Time, Task)" +
> "VALUES ('3/18/2004', '08.30 am', 'Sleep')";
> cmd.ExecuteNonQuery();
>
> cmd.CommandText =
> "INSERT INTO Schedule (Date, Time, Task)" +
> "VALUES ('3/19/2004', '09.00 am', 'Do')";
> cmd.ExecuteNonQuery();
>
> }
>
> catch (SqlCeException){}
>
>
> finally
> {
> if (conn.State == ConnectionState.Open)
> conn.Close();
> }
>
> string cn = @"Data Source = \My Documents\MSFA.sdf";
>
> using(SqlCeConnection cn1 = new SqlCeConnection(cn))
> {
> cn1.Open();
> string select = "SELECT * FROM Schedule";
> SqlCeDataAdapter da = new SqlCeDataAdapter();
>
> da.MissingMappingAction = MissingMappingAction.Passthrough;
> da.MissingSchemaAction = MissingSchemaAction.Add;
> da.SelectCommand = new SqlCeCommand(select, cn1);
>
> ds = new DataSet();
> da.Fill(ds, "Schedule");
> dt = ds.Tables["Schedule"];
> g1.DataSource = dt;
> txtDate.DataBindings.Clear();
> txtDate.DataBindings.Add("Text", dt, "Date");
> txtTime.DataBindings.Clear();
> txtTime.DataBindings.Add("Text", dt, "Time");
> txtTask.DataBindings.Clear();
> txtTask.DataBindings.Add("Text", dt, "Task");
> }
>
> }
>
>
> private void btnRetrieve_Click(object sender, System.EventArgs e)
> {
> SqlCeConnection conn = null;
> SqlCeCommand comm = null;
> using( conn = new SqlCeConnection(cn))
> {
> conn.Open();
> string q4 = "SELECT * FROM Schedule";
> SqlCeDataReader dr = null;
> try
> {
> comm = new SqlCeCommand(q4, conn);
> comm.CommandType = CommandType.Text;
> dr = comm.ExecuteReader(CommandBehavior.Default);
>
> while(dr.Read())
> {
> MessageBox.Show(string.Format(str, dr.GetString(0),dr.GetString(1),
dr.GetString(2)));
> }
> }
>
> catch (SqlCeException) {}
>
> finally
> {
> if (comm != null)
> comm.Dispose();
>
> if (dr != null)
> dr.Close();
> }
> }
>
> }
> }
> }

Loading