Update DataTable - Error

thenndral

Hi,

I'm using C#2008
DB - Oracle11G

I have a problem while update data using Datatable.
Herewith i Attach my code. please check where I did mistake.

private void btnSave_Click(object sender, EventArgs e)
{
connectionString = "User Id=tom;Password=pwd4tom;Data Source=remote_oracle";
cn = new OracleConnection(connectionString);

OracleCommand command;
string insertQuery = @"insert into employee(id, name, dept, address, active) Values
(@id, @name, @dept, @address, @active)";
command = new OracleCommand(insertQuery, cn);
command.Parameters.Add("@id", OracleDbType.Int16, 5, "id");
command.Parameters.Add("@name", OracleDbType.Varchar2, 50, "name");
command.Parameters.Add("@dept", OracleDbType.Varchar2, 3, "dept");
command.Parameters.Add("@address", OracleDbType.Varchar2, 50, "address");
command.Parameters.Add("@active", OracleDbType.Varchar2, 1, "active");
adapter.InsertCommand = command;

string updateQuery = @"update employee set name=@name, dept=@dept, address=@address,
active=@active where id=@id";
command = new OracleCommand(updateQuery, cn);
command.Parameters.Add("@name", OracleDbType.Varchar2, 50, "name");
command.Parameters.Add("@dept", OracleDbType.Varchar2, 3, "dept");
command.Parameters.Add("@address", OracleDbType.Varchar2, 50, "address");
command.Parameters.Add("@active", OracleDbType.Varchar2, 10, "active");
OracleParameter param1 = command.Parameters.Add("@id", OracleDbType.Int16, 5, "id");
param1.SourceVersion = DataRowVersion.Original;
adapter.UpdateCommand = command;

string deleteQuery = @"Delete employee where id=@id";
command = new OracleCommand(deleteQuery, cn);
OracleParameter paramd1 = command.Parameters.Add("@id", OracleDbType.Int16, 5, "id");
paramd1.SourceVersion = DataRowVersion.Original;
adapter.DeleteCommand = command;

adapter.Update(empTbl); // What goes wrong here....

}

thanks,
thenndral.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Javeed M Shaikh15y ago
Why are you not considering writing stored procedure and calling the same from your app. Where you can accomplish task like updating multiple tables and other database related activities very easily.
0
thenndral15y ago
Hi suthish,

thanks for your reply.

Eventhough I add semicolon[;] its not working. after i add commandbuilder its working like a charm.

now I have another question, This works for single table with single datagridview.
how to code for insert/update/delete multitable with single datagridview.

give me some suggestion.

Thanks,
thenndral.
0
Suthish Nair15y ago
Am talking about like below (red font)..

@"insert into employee(id, name, dept, address, active) Values
(@id, @name, @dept, @address, @active);";

@"update employee set name=@name, dept=@dept, address=@address,
active=@active where id=@id;";

@"Delete employee where id=@id;";
0
thenndral15y ago
Hi suthish,

I add below code before of adapter.Update(empTbl);
Its works well.

OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();

I have another question, This works for single table with single datagridview.
how to code for insert/update/delete multitable with single datagridview.

give me some suggestion.

Thanks,
thenndral.
0
Suthish Nair15y ago
try, to have a semicolon ";" after each insert, update, delete statement.
0
thenndral15y ago
Hi,

thanks for your reply.

If I update date through dataset i can code like this way //adapter.Update(ds, "employee");//
but, here i update through datatable. // datatable name = empTbl.
when i debug, in that line got an error.//ORA-00936: missing expression//

what it means? i did some mistake cant able to find out.

please let me know.

thanks,
thendral
0
Prabhu Raja15y ago
Hi,

adapter.Update(ds);

0