DataGrid Update/Delete not working

W

WebDevHokie

I have a very simple example that I am trying to get the update and delete
(through DataAdapter) working. I have the insert working just fine, but the
update/delete do not work. This is a win app if that matters. I get a
concurrency update (or delete) error saying that no rows are updated (or
deleted). Anyone have any idea what I could be doing wrong????

Thanks in advance!!

WebDevHokie

/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button button2;
private System.Data.DataSet _dataSet = new DataSet();

// puts the data in the datagrid
private void button1_Click(object sender, System.EventArgs e)
{
this._dataSet.Clear();

DDTek.Oracle.OracleConnection o_Connection = new
OracleConnection("Server Name=RPLANS;User ID=rplans_dev;Password=g00dwill");
o_Connection.Open();

DDTek.Oracle.OracleCommand o_Command = new OracleCommand("Select *
from MIKEW_QUERY_TEST", o_Connection);
DDTek.Oracle.OracleDataAdapter o_DataAdapter = new
OracleDataAdapter(o_Command);
o_DataAdapter.Fill(_dataSet);
dataGrid1.SetDataBinding(this._dataSet,
this._dataSet.Tables[0].TableName);

o_Connection.Close();
o_Connection.Dispose();
o_DataAdapter.Dispose();
o_Command.Dispose();
}

// performs the modification
private void button2_Click(object sender, System.EventArgs e)
{
DDTek.Oracle.OracleConnection o_Connection = new
OracleConnection("Server Name=Test_DB;User ID=Test_User;Password=Test");
DDTek.Oracle.OracleParameter o_Parameter;

o_Connection.Open();

// insert command
DDTek.Oracle.OracleCommand o_Command1 = new OracleCommand("Insert
into QUERY_TEST (ID, NAME, PHONE) values (?,?,?)", o_Connection);
o_Command1.Parameters.Add(new OracleParameter("ID",
OracleDbType.Number, 0, "ID"));
o_Command1.Parameters.Add(new OracleParameter("NAME",
OracleDbType.VarChar, 25, "NAME"));
o_Command1.Parameters.Add(new OracleParameter("PHONE",
OracleDbType.Number, 0, "PHONE"));

// delete command
DDTek.Oracle.OracleCommand o_Command2 = new OracleCommand("DELETE
from MIKEW_QUERY_TEST where ID = ?", o_Connection);
OracleParameterCollection o_ParameterCollection =
o_Command2.Parameters;
o_Parameter = new OracleParameter("ID", OracleDbType.Number, 0,
"ID");
o_Parameter.SourceVersion = DataRowVersion.Original;
o_Command2.Parameters.Add(o_Parameter);

// update command
DDTek.Oracle.OracleCommand o_Command3 = new OracleCommand("UPDATE
MIKEW_QUERY_TEST set ID = ?, NAME = ?, PHONE = ? WHERE ID = ?",
o_Connection);
o_Command3.Parameters.Add(new OracleParameter("ID",
OracleDbType.Number, 0, "ID"));
o_Command3.Parameters.Add(new OracleParameter("NAME",
OracleDbType.VarChar, 25, "NAME"));
o_Command3.Parameters.Add(new OracleParameter("PHONE",
OracleDbType.Number, 0, "PHONE"));

o_ParameterCollection = o_Command3.Parameters;
o_Parameter = o_ParameterCollection.Add("ID_Orig",
OracleDbType.Number, 0, "ID");
o_Parameter.SourceVersion = DataRowVersion.Original;

DDTek.Oracle.OracleDataAdapter o_DataAdapter = new
OracleDataAdapter();

o_DataAdapter.InsertCommand = o_Command1;
o_DataAdapter.DeleteCommand = o_Command2;
o_DataAdapter.UpdateCommand = o_Command3;
o_DataAdapter.Update(this._dataSet);

o_DataAdapter.Dispose();

DDTek.Oracle.OracleCommand o_Command = new OracleCommand("Select *
from MIKEW_QUERY_TEST", o_Connection);
o_DataAdapter = new OracleDataAdapter(o_Command);

this._dataSet.Clear();
o_DataAdapter.Fill(_dataSet);
dataGrid1.SetDataBinding(this._dataSet,
this._dataSet.Tables[0].TableName);

o_Connection.Close();
o_Connection.Dispose();
o_DataAdapter.Dispose();
o_Command.Dispose();
}
}
 
T

Terry Burns

Are you remembering to call the delete method on the rows rather than the
remove ?

tableEvents.Rows.Item(dgEvents.CurrentRowIndex).Delete()

daEvents.DeleteCommand = DeleteCmd

daEvents.Update(tableEvents)



WebDevHokie said:
I have a very simple example that I am trying to get the update and delete
(through DataAdapter) working. I have the insert working just fine, but the
update/delete do not work. This is a win app if that matters. I get a
concurrency update (or delete) error saying that no rows are updated (or
deleted). Anyone have any idea what I could be doing wrong????

Thanks in advance!!

WebDevHokie

/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button button2;
private System.Data.DataSet _dataSet = new DataSet();

// puts the data in the datagrid
private void button1_Click(object sender, System.EventArgs e)
{
this._dataSet.Clear();

DDTek.Oracle.OracleConnection o_Connection = new
OracleConnection("Server Name=RPLANS;User ID=rplans_dev;Password=g00dwill");
o_Connection.Open();

DDTek.Oracle.OracleCommand o_Command = new OracleCommand("Select *
from MIKEW_QUERY_TEST", o_Connection);
DDTek.Oracle.OracleDataAdapter o_DataAdapter = new
OracleDataAdapter(o_Command);
o_DataAdapter.Fill(_dataSet);
dataGrid1.SetDataBinding(this._dataSet,
this._dataSet.Tables[0].TableName);

o_Connection.Close();
o_Connection.Dispose();
o_DataAdapter.Dispose();
o_Command.Dispose();
}

// performs the modification
private void button2_Click(object sender, System.EventArgs e)
{
DDTek.Oracle.OracleConnection o_Connection = new
OracleConnection("Server Name=Test_DB;User ID=Test_User;Password=Test");
DDTek.Oracle.OracleParameter o_Parameter;

o_Connection.Open();

// insert command
DDTek.Oracle.OracleCommand o_Command1 = new OracleCommand("Insert
into QUERY_TEST (ID, NAME, PHONE) values (?,?,?)", o_Connection);
o_Command1.Parameters.Add(new OracleParameter("ID",
OracleDbType.Number, 0, "ID"));
o_Command1.Parameters.Add(new OracleParameter("NAME",
OracleDbType.VarChar, 25, "NAME"));
o_Command1.Parameters.Add(new OracleParameter("PHONE",
OracleDbType.Number, 0, "PHONE"));

// delete command
DDTek.Oracle.OracleCommand o_Command2 = new OracleCommand("DELETE
from MIKEW_QUERY_TEST where ID = ?", o_Connection);
OracleParameterCollection o_ParameterCollection =
o_Command2.Parameters;
o_Parameter = new OracleParameter("ID", OracleDbType.Number, 0,
"ID");
o_Parameter.SourceVersion = DataRowVersion.Original;
o_Command2.Parameters.Add(o_Parameter);

// update command
DDTek.Oracle.OracleCommand o_Command3 = new OracleCommand("UPDATE
MIKEW_QUERY_TEST set ID = ?, NAME = ?, PHONE = ? WHERE ID = ?",
o_Connection);
o_Command3.Parameters.Add(new OracleParameter("ID",
OracleDbType.Number, 0, "ID"));
o_Command3.Parameters.Add(new OracleParameter("NAME",
OracleDbType.VarChar, 25, "NAME"));
o_Command3.Parameters.Add(new OracleParameter("PHONE",
OracleDbType.Number, 0, "PHONE"));

o_ParameterCollection = o_Command3.Parameters;
o_Parameter = o_ParameterCollection.Add("ID_Orig",
OracleDbType.Number, 0, "ID");
o_Parameter.SourceVersion = DataRowVersion.Original;

DDTek.Oracle.OracleDataAdapter o_DataAdapter = new
OracleDataAdapter();

o_DataAdapter.InsertCommand = o_Command1;
o_DataAdapter.DeleteCommand = o_Command2;
o_DataAdapter.UpdateCommand = o_Command3;
o_DataAdapter.Update(this._dataSet);

o_DataAdapter.Dispose();

DDTek.Oracle.OracleCommand o_Command = new OracleCommand("Select *
from MIKEW_QUERY_TEST", o_Connection);
o_DataAdapter = new OracleDataAdapter(o_Command);

this._dataSet.Clear();
o_DataAdapter.Fill(_dataSet);
dataGrid1.SetDataBinding(this._dataSet,
this._dataSet.Tables[0].TableName);

o_Connection.Close();
o_Connection.Dispose();
o_DataAdapter.Dispose();
o_Command.Dispose();
}
}
Click to expand...
 
T

Terry Burns

Yes you can do it as a batch.

You may find that some of your UPDATE names need to be in brackets as they
may be reserved words

OracleCommand("UPDATE
MIKEW_QUERY_TEST set [ID] = ?, [NAME] = ?, [PHONE] = ? WHERE ID =
Click to expand...
Click to expand...
Click to expand...
?",

When a row has been updated you see a little red circle on the row header
with a white exclaimation mark. This signifies that the row has been
modified. If your update fails to take this away then it means your update
statement did not work for some reason.

Ididnt notice any table mappings and your select definition comes after your
update definition.

Try changing this and seeinf if this helps.




WebDevHokie said:
Thanks for your reply. Excuse my stupidity, but I don't understand why I
need an event handler? I basically want to allow the user to edit the
dataset how they like and then do a "batch" update all at once rather than
on a per row or per change basis. Can I do that with the way I have written
it below?

I appreciate all your information and help!!!!

WebDevHokie


Terry Burns said:
For the Update command parameters and text should be set following the
select statement otherwise it seems to fail, thats what I discovered.

You need to set up a handler if you have already done so to call the
data-adapter.update method when the row haws changed.




'During INIT
Example
' add a RowChanged event handler for the table.

'AddHandler tableEvents.RowChanged, New DataRowChangeEventHandler(AddressOf
Events_Row_Changed)



Dim eCmd As New OleDbCommand

'Initialise Events

Try

dgEvents.DataSource = tableEvents

eCmd.CommandType = CommandType.Text

eCmd.CommandText = "SELECT
[PersonIndex],[EventType],[Comments],[Action],[ID] FROM ([Events]) WHERE
PersonIndex=?"

eCmd.Connection = con

eCmd.Parameters.Add("@ID", dgPeople.Item(dgPeople.CurrentRowIndex, 0))

daEvents.SelectCommand = eCmd

daEvents.Fill(tableEvents)

Catch ex As OleDbException

MessageBox.Show(ex.Message)

Catch ex As ArgumentException

MessageBox.Show(ex.tostring)



End Try

'setup update command

cmd.CommandText = "UPDATE [Events] SET [Action] = ?, [Comments] = ?,
[EventType] = ?, [PersonIndex] = ? WHERE " & _

"([ID] = ?) AND ([Action] = ?) AND ([EventType] = ?) AND ([PersonIndex]
Click to expand...
=
?
OR ? IS NULL " & _

"AND [PersonIndex] IS NULL)"

cmd.Connection = con

cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Action",
System.Data.OleDb.OleDbType.VarWChar, 50, "Action"))

cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Comments",
System.Data.OleDb.OleDbType.VarWChar, 50, "Comments"))

cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("EventType",
System.Data.OleDb.OleDbType.VarWChar, 50, "EventType"))

cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("PersonIndex",
System.Data.OleDb.OleDbType.Integer, 0, "PersonIndex"))

cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_ID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"ID", System.Data.DataRowVersion.Original, Nothing))

cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Action",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Action", System.Data.DataRowVersion.Original, Nothing))

cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_EventType",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"EventType", System.Data.DataRowVersion.Original, Nothing))

cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_PersonIndex",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"PersonIndex", System.Data.DataRowVersion.Original, Nothing))

cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_PersonIndex1",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"PersonIndex", System.Data.DataRowVersion.Original, Nothing))

daEvents.UpdateCommand = cmd

'HANDLER











WebDevHokie said:
Well I am actually directly modifying the rows from the windows form.
Click to expand...
So
if
that is calling the remove, then I am guessing that is my problem for the
delete, but why would the update be failing in that case? When I
Click to expand...
Click to expand...
look
at
the dataset, it shows me what has been deleted/updated added and so forth.


Are you remembering to call the delete method on the rows rather
Click to expand...
than
the
remove ?

tableEvents.Rows.Item(dgEvents.CurrentRowIndex).Delete()

daEvents.DeleteCommand = DeleteCmd

daEvents.Update(tableEvents)



I have a very simple example that I am trying to get the update and
delete
(through DataAdapter) working. I have the insert working just
Click to expand...
fine,
but
the
update/delete do not work. This is a win app if that matters. I
Click to expand...
get
a
concurrency update (or delete) error saying that no rows are
Click to expand...
updated
(or
deleted). Anyone have any idea what I could be doing wrong????

Thanks in advance!!

WebDevHokie

/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button button2;
private System.Data.DataSet _dataSet = new DataSet();

// puts the data in the datagrid
private void button1_Click(object sender, System.EventArgs e)
{
this._dataSet.Clear();

DDTek.Oracle.OracleConnection o_Connection = new
OracleConnection("Server Name=RPLANS;User
ID=rplans_dev;Password=g00dwill");
o_Connection.Open();

DDTek.Oracle.OracleCommand o_Command = new OracleCommand("Select
*
from MIKEW_QUERY_TEST", o_Connection);
DDTek.Oracle.OracleDataAdapter o_DataAdapter = new
OracleDataAdapter(o_Command);
o_DataAdapter.Fill(_dataSet);
dataGrid1.SetDataBinding(this._dataSet,
this._dataSet.Tables[0].TableName);

o_Connection.Close();
o_Connection.Dispose();
o_DataAdapter.Dispose();
o_Command.Dispose();
}

// performs the modification
private void button2_Click(object sender, System.EventArgs e)
{
DDTek.Oracle.OracleConnection o_Connection = new
OracleConnection("Server Name=Test_DB;User ID=Test_User;Password=Test");
DDTek.Oracle.OracleParameter o_Parameter;

o_Connection.Open();

// insert command
DDTek.Oracle.OracleCommand o_Command1 = new
OracleCommand("Insert
into QUERY_TEST (ID, NAME, PHONE) values (?,?,?)", o_Connection);
o_Command1.Parameters.Add(new OracleParameter("ID",
OracleDbType.Number, 0, "ID"));
o_Command1.Parameters.Add(new OracleParameter("NAME",
OracleDbType.VarChar, 25, "NAME"));
o_Command1.Parameters.Add(new OracleParameter("PHONE",
OracleDbType.Number, 0, "PHONE"));

// delete command
DDTek.Oracle.OracleCommand o_Command2 = new
OracleCommand("DELETE
from MIKEW_QUERY_TEST where ID = ?", o_Connection);
OracleParameterCollection o_ParameterCollection =
o_Command2.Parameters;
o_Parameter = new OracleParameter("ID",
Click to expand...
OracleDbType.Number,
0,
"ID");
o_Parameter.SourceVersion = DataRowVersion.Original;
o_Command2.Parameters.Add(o_Parameter);

// update command
DDTek.Oracle.OracleCommand o_Command3 = new
OracleCommand("UPDATE
MIKEW_QUERY_TEST set ID = ?, NAME = ?, PHONE = ? WHERE ID = ?",
o_Connection);
o_Command3.Parameters.Add(new OracleParameter("ID",
OracleDbType.Number, 0, "ID"));
o_Command3.Parameters.Add(new OracleParameter("NAME",
OracleDbType.VarChar, 25, "NAME"));
o_Command3.Parameters.Add(new OracleParameter("PHONE",
OracleDbType.Number, 0, "PHONE"));

o_ParameterCollection = o_Command3.Parameters;
o_Parameter = o_ParameterCollection.Add("ID_Orig",
OracleDbType.Number, 0, "ID");
o_Parameter.SourceVersion = DataRowVersion.Original;

DDTek.Oracle.OracleDataAdapter o_DataAdapter = new
OracleDataAdapter();

o_DataAdapter.InsertCommand = o_Command1;
o_DataAdapter.DeleteCommand = o_Command2;
o_DataAdapter.UpdateCommand = o_Command3;
o_DataAdapter.Update(this._dataSet);

o_DataAdapter.Dispose();

DDTek.Oracle.OracleCommand o_Command = new OracleCommand("Select
*
from MIKEW_QUERY_TEST", o_Connection);
o_DataAdapter = new OracleDataAdapter(o_Command);

this._dataSet.Clear();
o_DataAdapter.Fill(_dataSet);
dataGrid1.SetDataBinding(this._dataSet,
this._dataSet.Tables[0].TableName);

o_Connection.Close();
o_Connection.Dispose();
o_DataAdapter.Dispose();
o_Command.Dispose();
}
}
Click to expand...
Click to expand...
Click to expand...
 
Top