Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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
0
sachin_raorane
Asked:
sachin_raorane
  • 3
  • 3
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now