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

André Giesing

Hello Eirik!

Thank you, but I knew that.
I'm programming an application for a smart device and must use the Compact
Framework. And these Funktion GetChanges() is not available there!

Any other idea?


Eirik M. said:
One thing is that you don't have to iterate over the rows to find the
changed ones. I'm not sure that you get the values for the original row(s)
that way. Try

changedDS = ds.GetChanges (); // returns all Additions, Modifications
and Deletes

or

changedDS = ds.GetChanges (DataRowState.Modified); // returns
Modifications

Eirik M

André Giesing said:
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
Click to expand...
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());
}
}
Click to expand...
Click to expand...
 
E

Eirik M.

André Giesing said:
Hello Eirik!
Click to expand...

Hello André. Wie gets? :)
Thank you, but I knew that.
I'm programming an application for a smart device and must use the Compact
Framework. And these Funktion GetChanges() is not available there!
Click to expand...


Ok, didn't know that :)

We'll just wait and see then if someone else can figure out whats wrong. But
I still think your back-end processing needs the values of the original
row(s) for the SqlDataAdapter to work.
Any other idea?
Click to expand...

Well, one thing I noticed is that you only tell the data adapter how to get
the data from the DB, but not how to write it back. Remember, the data
adapter "depends" on four SqlCommands, one for Select, one for Insert, one
for Update and one for Delete. I can't see that you are providing any of the
last three SqlCommands in 'SetKunden()'. Maybe you should try adding the
ones you need?

Eirik M.
Eirik M. said:
One thing is that you don't have to iterate over the rows to find the
changed ones. I'm not sure that you get the values for the original row(s)
that way. Try

changedDS = ds.GetChanges (); // returns all Additions, Modifications
and Deletes

or

changedDS = ds.GetChanges (DataRowState.Modified); // returns
Modifications

Eirik M

André Giesing said:
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
Click to expand...
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());
}
}
Click to expand...
Click to expand...
Click to expand...
 
S

Severin

I have created a webservice and client program to do exactly that.

What you have to do in the webservice, is make a function that returns an
integer, like so

<WebMethod()>Public Function UpdateData(ByVal [each column] as [datatype])
as Integer
Try
dbConnection.Open()
dbUpdateCommand.CommandText = "UPDATE table SET "
dbUpdateCommand.CommandText &= "[first column]=" & value & ", "
dbUpdateCommand.CommandText &= "[next column]=" & value & " "
dbUpdateCommand.CommandText &= "WHERE [primaryKey]=" & value &";"
' RETURN VALUE IS THE NUMBER OF ROWS AFFECTED
Return dbUpdateCommand.ExecuteNonQuery()
Catch ex as Exception
Throw ex
Finally
dbConnection.Close()
End Try
End Function

Then on the Client program iterate through the datarows, like so...

Public Sub UpDate
Dim dr as DataRow
Try
For each dr in [dataset].Table(ndx).Rows
' THIS IS THE CALL TO THE WEBMETHOD
If UpdateData([each column data]) <> 0 Then
' Update worked, changed a record
Else
' Update did not work, no records changed
End If
' READY TO UPDATE NEXT RECORD
Next
Catch ex as Exception
' ERROR CODE
End Try
End Sub

You can only update ONE record at a time, the webservice will not accept a
large dataset as an argument (at least I haven't been able to send an entire
dataset) you must declare each column as a variable, assign it's value from
the datarow object and send that one datarow to the webservice. If
webservice returns any integer other than 0, then an update has occurred.

Hope this is clearly explained, and Helps.
Severin


André Giesing said:
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());
}
}
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update underlying data using a DataSet and Webservices 0
Uploading/Downloading Encrypted Files to a Database 1
Problem with a DataSet 1
Draw a real-time flot chart by fetching data from database and store it into an array using JavaScri 0
Update One Database from Another Using a WebService and Two DataSets... 1
[MSSQL] [WebService] Last inserted ID in transaction 0
wxPython GUI update with data from a MySQL database 3
MDB Database won't update 4

Members online

Total: 28 (members: 2, guests: 26)
Robots: 470

Forum statistics

Threads
473,769
Messages
2,569,580
Members
45,053
Latest member
BrodieSola

Latest Threads

Top