Update Data in a remote Database

A

André Giesing

Hello Newsgroup!

I want to write a little WebService and an Application for a mobile Device
(Pocket PC 2002), which gets Data (a DataSet) from the WebService, which
reads the Data out of a SQL 2000 Server.
My little Application gets the DataSet and shows the Data in a DataGrid.
That works.

But now I want to edit the DataSet (not in the DataGrid, but over Textboxes
which are bound to the DataGrid-Collums) and send the edited DataSet back to
the WebService, which writes the Data back into the database.

I've tried a lot. At the end of this Mail you can the Source i have written.
But it does not work. The Database does not get the modifications of the
Data :-(

And this is my Problem. I hope someone can help me.


Here the Source-Code of my WebService:

[WebMethod]
public void SetKunden(DataSet dsKunde)
{
SqlConnection con;
SqlDataAdapter da;
SqlCommandBuilder cb;

string connectionString =
@"server=masp2;uid=sa;pwd=sa;database=MASPdaten;";

try
{
con = new SqlConnection(connectionString);

da = new SqlDataAdapter("SELECT Name, Strasse, Plz, Ort FROM Kunde", con);
da.ContinueUpdateOnError = true;
cb = new SqlCommandBuilder(da);

da.Update(dsKunde.Tables["Kunde"]);
}
catch(Exception ex)
{
}
}


And here the Source-Code out of my Client:

private void btnUpdate_Click(object sender, System.EventArgs e)
{
WebReference.Service ws = new WebReference.Service();

try
{
int rowIndex = dgKunden.CurrentRowIndex;

if(rowIndex >= 0 & rowIndex < m_dsDaten.Tables["Kunde"].Rows.Count)
{
DataRow tableRow = m_dsDaten.Tables["Kunde"].Rows[rowIndex];

if(txbName.Modified ||
txbStrasse.Modified ||
txbPlz.Modified ||
txbOrt.Modified)
{

string message = "Wollen Sie die Änderungen speichern?";
string caption = "Speichern";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
MessageBoxDefaultButton defaultbtn = MessageBoxDefaultButton.Button1;
MessageBoxIcon icon = MessageBoxIcon.Question;
DialogResult result;

result = MessageBox.Show(message, caption, buttons, icon, defaultbtn);

if(result == DialogResult.Yes)
{
tableRow["Name"] = txbName.Text;
tableRow["Strasse"] = txbStrasse.Text;
tableRow["Plz"] = txbPlz.Text;
tableRow["Ort"] = txbOrt.Text;

Refresh();
dgKunden.Refresh();

MessageBox.Show("Daten wurden gespeichert.");
}
if(result == DialogResult.No)
{
txbName.Text = name;
txbStrasse.Text = strasse;
txbPlz.Text = plz;
txbOrt.Text = ort;
Refresh();
}
}
}
txbName.ReadOnly = true;
txbStrasse.ReadOnly = true;
txbOrt.ReadOnly = true;
txbPlz.ReadOnly = true;

try
{
Cursor.Current=Cursors.WaitCursor;
DataSet changedDs = m_dsDaten.Clone();

foreach(DataRow row in m_dsDaten.Tables["Kunde"].Rows)
{
if(row.RowState!=System.Data.DataRowState.Unchanged)
{
changedDs.Tables["Kunde"].ImportRow(row);
}
if(changedDs.Tables["Kunde"].Rows.Count > 0)
{
ws.SetKunden(changedDs);
}
}
catch(Exception excp)
{
MessageBox.Show(excp.Message.ToString());
}
finally
{
Cursor.Current=Cursors.Default;
}
}
catch (Exception ex)
{
MessageBox.Show("Fehler in btnUpdate_Click: \n\n" + ex.ToString());
}
}
 
I

Ilya Tumanov [MS]

Andre,

CommandBuilder builds commands like "Update ... Where PrimaryKey =
PrimaryKey Of Changed Row".
So, unless Name, Strasse, Plz or Ort is a primary key, your update won't
work.

If you do have a primary key column on this table, you should add it to
DataTable and to a select statement.
If you do not have a primary key, I would suggest you add one.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.




--------------------
From: "André Giesing" <[email protected]>
Subject: Update Data in a remote Database
Date: Thu, 11 Sep 2003 14:59:18 +0200
Lines: 135
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <evt#[email protected]>
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: 193.175.175.175
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:33327
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Hello Newsgroup!

I want to write a little WebService and an Application for a mobile Device
(Pocket PC 2002), which gets Data (a DataSet) from the WebService, which
reads the Data out of a SQL 2000 Server.
My little Application gets the DataSet and shows the Data in a DataGrid.
That works.

But now I want to edit the DataSet (not in the DataGrid, but over Textboxes
which are bound to the DataGrid-Collums) and send the edited DataSet back to
the WebService, which writes the Data back into the database.

I've tried a lot. At the end of this Mail you can the Source i have written.
But it does not work. The Database does not get the modifications of the
Data :-(

And this is my Problem. I hope someone can help me.


Here the Source-Code of my WebService:

[WebMethod]
public void SetKunden(DataSet dsKunde)
{
SqlConnection con;
SqlDataAdapter da;
SqlCommandBuilder cb;

string connectionString =
@"server=masp2;uid=sa;pwd=sa;database=MASPdaten;";

try
{
con = new SqlConnection(connectionString);

da = new SqlDataAdapter("SELECT Name, Strasse, Plz, Ort FROM Kunde", con);
da.ContinueUpdateOnError = true;
cb = new SqlCommandBuilder(da);

da.Update(dsKunde.Tables["Kunde"]);
}
catch(Exception ex)
{
}
}


And here the Source-Code out of my Client:

private void btnUpdate_Click(object sender, System.EventArgs e)
{
WebReference.Service ws = new WebReference.Service();

try
{
int rowIndex = dgKunden.CurrentRowIndex;

if(rowIndex >= 0 & rowIndex < m_dsDaten.Tables["Kunde"].Rows.Count)
{
DataRow tableRow = m_dsDaten.Tables["Kunde"].Rows[rowIndex];

if(txbName.Modified ||
txbStrasse.Modified ||
txbPlz.Modified ||
txbOrt.Modified)
{

string message = "Wollen Sie die Änderungen speichern?";
string caption = "Speichern";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
MessageBoxDefaultButton defaultbtn = MessageBoxDefaultButton.Button1;
MessageBoxIcon icon = MessageBoxIcon.Question;
DialogResult result;

result = MessageBox.Show(message, caption, buttons, icon, defaultbtn);

if(result == DialogResult.Yes)
{
tableRow["Name"] = txbName.Text;
tableRow["Strasse"] = txbStrasse.Text;
tableRow["Plz"] = txbPlz.Text;
tableRow["Ort"] = txbOrt.Text;

Refresh();
dgKunden.Refresh();

MessageBox.Show("Daten wurden gespeichert.");
}
if(result == DialogResult.No)
{
txbName.Text = name;
txbStrasse.Text = strasse;
txbPlz.Text = plz;
txbOrt.Text = ort;
Refresh();
}
}
}
txbName.ReadOnly = true;
txbStrasse.ReadOnly = true;
txbOrt.ReadOnly = true;
txbPlz.ReadOnly = true;

try
{
Cursor.Current=Cursors.WaitCursor;
DataSet changedDs = m_dsDaten.Clone();

foreach(DataRow row in m_dsDaten.Tables["Kunde"].Rows)
{
if(row.RowState!=System.Data.DataRowState.Unchanged)
{
changedDs.Tables["Kunde"].ImportRow(row);
}
if(changedDs.Tables["Kunde"].Rows.Count > 0)
{
ws.SetKunden(changedDs);
}
}
catch(Exception excp)
{
MessageBox.Show(excp.Message.ToString());
}
finally
{
Cursor.Current=Cursors.Default;
}
}
catch (Exception ex)
{
MessageBox.Show("Fehler in btnUpdate_Click: \n\n" + ex.ToString());
}
}
 
Top