sachin_raorane
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.Paramete rs.Add("@R egionID", SqlDbType.Int);
RegionId.SourceColumn = "RegionId";
SqlParameter RegionName = oDA.InsertCommand.Paramete rs.Add("@R egionName" , SqlDbType.NVarChar, 15, "RegionName");
DataTable oDT = new DataTable();
oDA.Fill(oDT);
DataRow oDr;
oDr = oDT.NewRow();
oDr[0] = Convert.ToInt32(txtRegionI d.Text);
oDr[1] = Convert.ToString(txtRegion Name.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
{
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.Paramete
RegionId.SourceColumn = "RegionId";
SqlParameter RegionName = oDA.InsertCommand.Paramete
DataTable oDT = new DataTable();
oDA.Fill(oDT);
DataRow oDr;
oDr = oDT.NewRow();
oDr[0] = Convert.ToInt32(txtRegionI
oDr[1] = Convert.ToString(txtRegion
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Paramete rs.Add("@R egionID", SqlDbType.Int);
RegionId.SourceColumn = "RegionId";
SqlParameter RegionName = oDA.InsertCommand.Paramete rs.Add("@R egionName" , SqlDbType.NVarChar, 15, "RegionName");
The RegionName parameter needs a source column.
Does that work?
Andy
I think that your insert command setup is incomplete:
SqlParameter RegionId = oDA.InsertCommand.Paramete
RegionId.SourceColumn = "RegionId";
SqlParameter RegionName = oDA.InsertCommand.Paramete
The RegionName parameter needs a source column.
Does that work?
Andy
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.Paramete rs.Add("@R egionID", SqlDbType.Int);
RegionId.SourceColumn = "RegionId";
SqlParameter RegionName = oDA.InsertCommand.Paramete rs.Add("@R egionName" , SqlDbType.NVarChar, 15,"RegionDescription");
DataTable oDT = new DataTable();
oDA.Fill(oDT);
DataRow oDr;
oDr = oDT.NewRow();
oDr[0] = Convert.ToInt32(txtRegionI d.Text);
oDr[1] = Convert.ToString(txtRegion Name.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.
{
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.Paramete
RegionId.SourceColumn = "RegionId";
SqlParameter RegionName = oDA.InsertCommand.Paramete
DataTable oDT = new DataTable();
oDA.Fill(oDT);
DataRow oDr;
oDr = oDT.NewRow();
oDr[0] = Convert.ToInt32(txtRegionI
oDr[1] = Convert.ToString(txtRegion
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.
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
Or
I can do this without giving the Insert and Update commands to Data Adapter
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
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
ASKER
The foloowing error shown...
Prepared statement '(@RegionID int,@RegionName nvarchar(15))INSERT INTO Region VALUE' expects parameter @RegionName, which was not supplied.