April 16, 2013 at 12:30 pm
Hi,
I am a newbie and am trying to figure out how to update data from a datagrid to a database. I have copied exisiting code but it is not doing exactly what I would like.
My two questions that I have are:
1.) How do I put loop here to update all records in data grid????????????????????
2.) What is the correct syntax to pass an update from data grid to the database?????
int TestID = 999999;
DataSet ds_Assignment = new DataSet();
dataGridView1.DataSource = ds_Assignment;
SqlConnection myConnection = new SqlConnection(Assignment_Utility.Properties.Settings.Default.STH_D_TESTConnectionString);
try
{
myConnection.Open();
}
catch (Exception eConn)
{
MessageBox.Show("error opening database connection." + eConn.ToString());
return;
}
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("Select * from Assignment where TestID = " + TestID.ToString(), myConnection);
SqlCommandBuilder cb = new SqlCommandBuilder(mySqlDataAdapter);
cb.GetUpdateCommand();
mySqlDataAdapter.Fill(ds_Assignment, "Assignment");
// 1.) How do I put loop here to update all records in data grid????????????????????
// 2.) What is the correct syntax to pass an update from data grid to the database?????
ds_Assignment.Tables[0].Rows[0]["EndDt"] = WHAT DO I DO HERE TO GET UPDATE FROM DATA GRID?
ds_Assignment.Tables[0].Rows[0]["Active"] = "0";
ds_Assignment.Tables[0].Rows[0]["StatusType"] = "REVO";
try
{
mySqlDataAdapter.Update(ds_Assignment, "Assignment");
}
Thank you in advance!!!!
April 16, 2013 at 12:48 pm
ray is this for a web page or for a winform?
for Winforms, i usually create a binding source,
set the binding source.Datasource to your datatable, and finally set the the .Datasource property of the bindingsource to that.
form the, there grid auto-magically changes datarow values based on edits.
so if i change some data only in the third row of the grid , the DataRow.HasChanges() would be true, and when you call the Adapter.Update, it would build the commands behind the scenes to update only rows with changes.
is that the question?
Lowell
April 16, 2013 at 12:50 pm
rayh 98086 (4/16/2013)
My two questions that I have are:1.) How do I put loop here to update all records in data grid????????????????????
2.) What is the correct syntax to pass an update from data grid to the database?????
No offense but you are going about this entirely wrong. You don't loop through every row in a grid and update the database. You handle updates when the row is updated by handling the RowUpdating event.
The best way is to use a stored proc instead of pass through sql to protect against sql injection.
Do a quick google/bing search for "dot net datagrid update" you will find hundreds if not thousands of examples of how to do this.
I can help you tomorrow if you get stuck.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2013 at 12:51 pm
And here we can tell that Lowell tends be more of a WinForms guy and I am an ASP.NET guy. 😀
I always forget about the auto-magic updating when using WinForms. IIRC about all you have to do is have a primary key and define that in the grid for it to work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2013 at 12:54 pm
Hi Lowell,
Good to hear from you again. I am using a Windform.
This generally does answer my question, but do you have an example of this?
The trick is, the user should be able to update any EndDt that is visible after passing the ID. For any row that does have an updated EndDt, that specific row should also be updated with an Active of 0 and Status of REVO "behind the scenes".
Thanks again!!
April 16, 2013 at 12:55 pm
Thank you Sean, no offense taken. I am a newbie getting thrown into the fire of my first project so I aprpeciate your feedback.
For this project it is Forms, but I also plan to ASP.NET in the future.
April 16, 2013 at 1:05 pm
rayh 98086 (4/16/2013)
Hi Lowell,Good to hear from you again. I am using a Windform.
This generally does answer my question, but do you have an example of this?
The trick is, the user should be able to update any EndDt that is visible after passing the ID. For any row that does have an updated EndDt, that specific row should also be updated with an Active of 0 and Status of REVO "behind the scenes".
Thanks again!!
For this type of custom logic on the value you will probably need to handle the RowUpdating event. You can then add just about level of customization to the values that you want. You might be able to use a trigger for this too but the effects of that would apply to any updates to this table, not just those from within your application.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2013 at 4:27 pm
Hi,
Here is my new code. Am I on the right track?
Also, I am now getting an error message: "Unable to cast object of type 'System.Data.DataTable' to type ..." Do you know what this means?
int TestID = 999999;
BindingSource bindingSource1 = new BindingSource();
SqlConnection myConnection = new SqlConnection(Assignment_Utility.Properties.Settings.Default.ConnectionString);
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("Select * from Assignment where TestID = " + TestID.ToString(), myConnection);
SqlCommandBuilder cb = new SqlCommandBuilder(mySqlDataAdapter);
DataTable ds_Assignment = new DataTable();
mySqlDataAdapter.Fill(ds_Assignment);
bindingSource1.DataSource = ds_Assignment;
mySqlDataAdapter.Update((ds_Assignment)bindingSource1.DataSource);
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply