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
LVL 5
sachin_raoraneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AGBrownCommented:
DbDataAdapter.Update first looks through the datatable for any rows with a state of Modified, Added or Deleted. It will then do the appropriate actions on these rows, and finally calls AccetpChanges on the row itself. So I think that, by calling oDT.AcceptChanges() before you do the oDA.Update(oDT) call, you have effectively changed the RowState property of each row in your datatable so that it is now Unchanged instead of Added/Deleted/Modified. This would mean that your dataadapter has no indication of the new rows and it doesn't therefore do the insert into the database.

Does getting rid of the oDT.AcceptChanges() line fix your problem?

Andy
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sachin_raoraneAuthor Commented:
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.


0
AGBrownCommented:
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
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

sachin_raoraneAuthor Commented:
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.

0
sachin_raoraneAuthor Commented:
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

0
AGBrownCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.