Urgent ??? - Update Data Source

Guest · Mar 18, 2004

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 muc

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 UpdateD

public class Form1 : System.Windows.Forms.For

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 cod
/// <summary
/// Required method for Designer support - do not modif
/// 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()
//
// btnRetriev
//
this.btnRetrieve.Location = new System.Drawing.Point(30, 248)
this.btnRetrieve.Text = "select"
this.btnRetrieve.Click += new System.EventHandler(this.btnRetrieve_Click)
//
// btnUpdat
//
this.btnUpdate.Location = new System.Drawing.Point(126, 248)
this.btnUpdate.Text = "Update"
this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click)
//
// txtTas
//
this.txtTask.Location = new System.Drawing.Point(6, 184)
this.txtTask.Size = new System.Drawing.Size(224, 22)
this.txtTask.Text = ""
//
// txtTim
//
this.txtTime.Location = new System.Drawing.Point(134, 152)
this.txtTime.Text = ""
//
// txtDat
//
this.txtDate.Location = new System.Drawing.Point(6, 152)
this.txtDate.Text = ""
//
// g
//
this.g1.Size = new System.Drawing.Size(240, 112)
this.g1.Text = "g1"
//
// btnSho
//
this.btnShow.Location = new System.Drawing.Point(32, 216)
this.btnShow.Text = "show"
this.btnShow.Click += new System.EventHandler(this.btnShow_Click)
//
// Form
//
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)

#endregio

static void Main()

Application.Run(new Form1())

private void btnUpdate_Click(object sender, System.EventArgs e

tr

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();
}
}

}
}
}

Jon Skeet [C# MVP] · Mar 18, 2004

Lianna said:
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

You're currently just dropping any SQL CE exception which is thrown by
update. I suggest that instead, you put a breakpoint in the code and
see whether an exception is being thrown, and if it is, what that
exception is.

William Ryan eMVP · Mar 18, 2004

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 said:
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();
}
}

}
}
}

Guest · Mar 19, 2004

Thanks for have a look But I've try The code don't have any error ???

Urgent ??? - Update Data Source

Guest

Jon Skeet [C# MVP]

William Ryan eMVP

Guest