asked on

Insert, Update data into table using DataTable of .NET 2005

protected void AddRecordWithDA()
{
SqlConnection oCn = new SqlConnection("Data Source=ServerName;Initial Catalog=Northwind;User ID=sa;Password=");

oCn.Open();
string strSQL = "SELECT * FROM Region";
SqlDataAdapter oDA = new SqlDataAdapter(strSQL, oCn);

oDA.InsertCommand = new SqlCommand("INSERT INTO Region VALUES(@RegionId, @RegionName)", oCn);

SqlParameter RegionId = oDA.InsertCommand.Parameters.Add("@RegionID", SqlDbType.Int);
RegionId.SourceColumn = "RegionId";

SqlParameter RegionName = oDA.InsertCommand.Parameters.Add("@RegionName", SqlDbType.NVarChar, 15, "RegionName");

DataTable oDT = new DataTable();
oDA.Fill(oDT);

DataRow oDr;

oDr = oDT.NewRow();
oDr[0] = Convert.ToInt32(txtRegionId.Text);
oDr[1] = Convert.ToString(txtRegionName.Text);

oDT.Rows.Add(oDr);

oDT.AcceptChanges();

oDA.Update(oDT);
oCn.Close();

GridView1.DataSource = oDT;
GridView1.DataBind();
}

This is my method, this Run fine, No error is shown, even last two lines shows the newly added record in grid, but if i check the Database the record is not found. I am not able to figure out why it is not adding the data into database.

Regards,
Sachin

ASKER CERTIFIED SOLUTION

AGBrown

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sachin_raorane

ASKER

I commented the oDT.AcceptChanges(); line

The foloowing error shown...

Prepared statement '(@RegionID int,@RegionName nvarchar(15))INSERT INTO Region VALUE' expects parameter @RegionName, which was not supplied.

AGBrown

Well, that's an improvement - you've pushed the problem down the line. Before, the AcceptChanges had set all the rows to Unchanged, as I suspected, so that the dataadapter was not even running the INSERT statement. Now you are actually running the insert statement.

I think that your insert command setup is incomplete:
SqlParameter RegionId = oDA.InsertCommand.Parameters.Add("@RegionID", SqlDbType.Int);
RegionId.SourceColumn = "RegionId";

SqlParameter RegionName = oDA.InsertCommand.Parameters.Add("@RegionName", SqlDbType.NVarChar, 15, "RegionName");

The RegionName parameter needs a source column.

Does that work?

Andy

sachin_raorane

ASKER

protected void AddRecordWithDA()
{
SqlConnection oCn = new SqlConnection("Data Source=NETCOM10;Initial Catalog=Northwind;User ID=sa;Password=nSnA");

oCn.Open();
string strSQL = "SELECT * FROM Region";
SqlDataAdapter oDA = new SqlDataAdapter(strSQL, oCn);

oDA.InsertCommand = new SqlCommand("INSERT INTO Region VALUES(@RegionId, @RegionName)", oCn);

SqlParameter RegionId = oDA.InsertCommand.Parameters.Add("@RegionID", SqlDbType.Int);
RegionId.SourceColumn = "RegionId";

SqlParameter RegionName = oDA.InsertCommand.Parameters.Add("@RegionName", SqlDbType.NVarChar, 15,"RegionDescription");

DataTable oDT = new DataTable();
oDA.Fill(oDT);

DataRow oDr;

oDr = oDT.NewRow();
oDr[0] = Convert.ToInt32(txtRegionId.Text);
oDr[1] = Convert.ToString(txtRegionName.Text);

oDT.Rows.Add(oDr);

//oDT.AcceptChanges();

oDA.Update(oDT);
oCn.Close();

GridView1.DataSource = oDT;
GridView1.DataBind();
}

This is my latest code, after your suggestion I have done the following changes

1. Commented the line oDT.AcceptChanges();
2. Change the Source column name from "RegionName" to "RegionDescription"

The last parameter in .Parameters.Add() method is "SourceColumn" so I don’t have to specify separately the way I did for “RegionId”

The issue was…In my previous code I was specified the wrong source column, If you check the Northwind database “Region” table the two columns are "RegionId" and "RegionDescription", where as I was given "ResionName" instead of "RegionDescription". I changed that and code work...

As your earlier suggestion was also necessary without which it don’t work. It is great help from you and you explained the problem very well. Thanks once again.

sachin_raorane

ASKER

Here is another quetion...you might not get point for this but will clear my doubt...is it always necessory to have commands defined (Insert, update) before doing the changes in data table?

Or

I can do this without giving the Insert and Update commands to Data Adapter

AGBrown

No problem - I hadn't spotted the RegionName source column in the original statement, sorry (it was 4 am :-s)

There are other ways to do the insert and update, but they would involve you manually finding the changed rows in the datatable and executing your own query on them.

Andy