Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Updating datagrid with C#.net Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 12:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 8:33 AM
Points: 50, Visits: 129
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!!!!
Post #1442935
Posted Tuesday, April 16, 2013 12:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:02 PM
Points: 12,890, Visits: 31,853
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442948
Posted Tuesday, April 16, 2013 12:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1442951
Posted Tuesday, April 16, 2013 12:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1442952
Posted Tuesday, April 16, 2013 12:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 8:33 AM
Points: 50, Visits: 129
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!!
Post #1442955
Posted Tuesday, April 16, 2013 12:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 8:33 AM
Points: 50, Visits: 129
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.
Post #1442958
Posted Tuesday, April 16, 2013 1:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1442963
Posted Tuesday, April 16, 2013 4:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 8:33 AM
Points: 50, Visits: 129
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);
Post #1443047
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse