DataGridView Add/Edit/Delete

[Kali]

Could someone please tell me how I'd create the same datagrid functionality
(bind,add/edit/delete) from an asp.net page into a Windows form using the
DataGridView control? I am able to bind the control in the windows form but
not able to get add/edit/delete functionality working? Also need drop
downs, etc. in the grid. Event handlers? Buttons for each task? I'd
certainly appreciate any feedback?

 

[RayLopez99]

Could someone please tell me how I'd create the same datagrid functionality
(bind,add/edit/delete) from an asp.net page into a Windows form using the
DataGridView control?  I am able to bind the control in the windows form but
not able to get add/edit/delete functionality working?  Also need drop
downs, etc. in the grid.  Event handlers?  Buttons for each task?  I'd
certainly appreciate any feedback?

What are you trying to do? You can do a datagrid with ASP.NET. You
can do it with Forms / WPF. You can do it with Silverlight. I've
done it with all three. So is that your problem? Just check out any
decent textbook, for example by MacDonald.

RL

 

[Kali]

Thanks for the reply. I'm just finding it extremely difficult to create the
same datagrid on a windows form as I do on an asp.net page. On an asp.net
page I'm able to explicitly define the columns and controls within the
column, etc. but not so in a windows form. When editing the asp.net page
datagrid (via click of an edit button) I can set the control types and bind
them based on existing record in sql table, etc, but not so in a windows
form. Can you point me to a good reference for accomplishing these tasks?

 

[Kali]

Would you happen to have a sample project or code illustrating a
datagridview with dropdownlists, textboxes, etc. in the datagridview and
add/edit/delete fruntionality?

 

[RayLopez99]

Thanks for the reply.  I'm just finding it extremely difficult to create the
same datagrid on a windows form as I do on an asp.net page.  On an asp.net
page I'm able to explicitly define the columns and controls within the
column, etc. but not so in a windows form.  When editing the asp.net page
datagrid (via click of an edit button) I can set the control types and bind
them based on existing record in sql table, etc, but not so in a windows
form.  Can you point me to a good reference for accomplishing these tasks?

I use the book by Karli Watson, "Beginning C#2005 Databases". This is
the best book for Forms. It's out of print but buy it anyway--worth
it*. But you know it's been a while since I used forms--I've since
moved onto WPF, which is superior since you can define the columns
more like ASP.NET, like you say.

I recall (from memory) the trick was to click on the upper right
corner of the box that defines the drag-and-drop for the datagrid--you
get a bunch of properties here.

It's all explained in Watson's book.

RL

* just checked Amazon.com:

Beginning C# 2005 Databases (Programmer to Programmer) by Karli
Watson (Paperback - Oct. 16, 2006)
Buy new: $39.99 $26.39

28 new from $11.08
14 used from $11.03

 

[Andy O'Neill]

Thanks for the reply. I'm just finding it extremely difficult to create
the same datagrid on a windows form as I do on an asp.net page. On an
asp.net page I'm able to explicitly define the columns and controls within
the column, etc. but not so in a windows form. When editing the asp.net
page datagrid (via click of an edit button) I can set the control types
and bind them based on existing record in sql table, etc, but not so in a
windows form. Can you point me to a good reference for accomplishing
these tasks?

It's different, but easy.

You don't need buttons,
It works like Access.
To edit an editable windows datagrid row you just click on the row.
You get nice edited icons to the left of each row and all sorts of stuff
dead easy.
Insertion?
Click on that star row at the end of your grid.

Tou can specify what's going on with the columns in code or there's a
columns collection
if you look under properties.

I'm guessing you're binding to something that's read only.
If you bind to a dataset/table you just get a load of functionality that
you have to work for on an asp.net datagrid.
This is VB but will give you the idea.
http://www.code-magazine.com/Article.aspx?quickid=0301071
Google "Introduction windows datagrid" for a shed load more topics.
( Do that first next time mate ).

All that retaining state and reducing load on the web server stuff largely
goes out the window.
So (small) datasets are a much more acceptable solution in windows than web
IMO.
Editing a dataset in a web app would be crazy because each time you submit
there's like 3 versions of all your data flying down the wire.
Then it's held on the web server.
So you use datareaders or whatever.
With windows the client reads data into a dataset, the 3 copies are held on
the client and only the changed data needs to be written back.
So strongly datasets are a dead easy solution and also a pretty good one.

The odd thing to throw to the back of your mind is that there's implicitly a
dataview which dotnet creates and sits between your table and the grid.
For some manipulation you need to work with that.
Or I did back in 2004 anyhow.

Hope this helps.

 

[Kali]

Thank you, Andy. Will take a look at the article. Some of the issues I'm
having are the event handlers compared to asp.net... onitemdatabound, etc.
I am able to add a combobox to a datagridview and populate its listitems,
however, I'm not able to keep that combobox when binding the data from an
existing set of data.

 

[Kali]

The biggest problem I am having is figuring out where I do the ItemDataBound
on the datagridview. In asp.net I'd have...

protected void dgBooks_ItemDataBound(object sender,
DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.EditItem)
{
//bind drop down list of books
SqlConnection cn = new
SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("cn"));
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from Books order by book_title";
cn.Open();

DropDownList ddlBook;
ddlBook = (DropDownList)e.Item.FindControl("ddlBook");
ddlBook.DataSource =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
ddlBook.DataBind();
ddlBook.Items.Insert(0, "");
cn.Close();
//end of binding drop down list of books

//set values
SqlConnection cn2 = new
SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("cn"));
SqlDataReader dtr;
SqlCommand objCmd;
string strSql;
strSql = "select * from ordered_books where record_id='" +
dgBooks.DataKeys[e.Item.ItemIndex] + "'";
objCmd = new SqlCommand(strSql, cn2);
cn2.Open();
dtr = objCmd.ExecuteReader();
dtr.Read();

ddlBook.Items.FindByValue(dtr["book_id"].ToString()).Selected =
true;

DropDownList ddlQuantityOfBook =
(DropDownList)e.Item.FindControl("ddlQuantityOfBook");
ddlQuantityOfBook.Items.FindByValue(dtr["quantity_of_book"].ToString()).Selected
= true;

cn2.Close();
}
}

However, I cannot locate an itemdatabound event arg in the datagridview in a
windows form.

Am I missing something basic? I may be going about this wrong.

I'm binding to a table that is both read and write. User can update.

I'd certainly appreciate any feedback you may have. Thank you very much,

 

[Andy O'Neill]

Thank you, Andy. Will take a look at the article. Some of the issues I'm
having are the event handlers compared to asp.net... onitemdatabound, etc.
I am able to add a combobox to a datagridview and populate its listitems,
however, I'm not able to keep that combobox when binding the data from an
existing set of data.

The way binding works in windows forms is different now, I think.
What I used to do is apply a style to the table.
I can't recall why, but there was an advantage to doing this in code rather
than the column collection.

Here's some old code which has a combo box in it.
=======
Dim ts1 As New DataGridTableStyle()

ts1.MappingName = "Salesmen"

ts1.AlternatingBackColor = Color.LightBlue

Dim TextCol_0 As New DataGridTextBoxColumn()

TextCol_0.MappingName = "Salesman_Id"

TextCol_0.HeaderText = "Salesman Id"

TextCol_0.Width = 70

TextCol_0.TextBox.MaxLength = 8

ts1.GridColumnStyles.Add(TextCol_0)

'=========

Dim cboReg As New DataGridBoundComboColumn

cboReg.MappingName = "Region_Id"

cboReg.HeaderText = "Region"

cboReg.Width = 120

Try

Dim sconn As String = Conn_String()

Dim conn As SqlConnection = New SqlConnection(sconn)

Dim SqlString As String = "select null as region_id, '(Null)' as Region " &
_

"Union " & _

"Select Region_id, Region from Regions " & _

"order by Region"

Dim daRg = New SqlDataAdapter(SqlString, conn)

Dim dsRg = New DataSet

daRg.Fill(dsRg, "Regions")

With cboReg.ColumnBoundComboBox

..DataSource = dsRg.Tables("Regions").DefaultView

..DisplayMember = "Region"

..ValueMember = "Region_id"

End With

Catch ex As SqlException

MsgBox("Error reading Regions " & ex.Number & " " & ex.Message())

End Try

ts1.PreferredRowHeight = cboReg.ColumnBoundComboBox.Height + 3

ts1.GridColumnStyles.Add(cboReg)

'=====

Dim TextCol_3 As New DataGridTextBoxColumn

TextCol_3.MappingName = "Salesman_Name"

TextCol_3.HeaderText = "Salesman Name"

TextCol_3.Width = 110

TextCol_3.TextBox.MaxLength = 30

ts1.GridColumnStyles.Add(TextCol_3)

Dim TextCol_4 As New DataGridBoolColumn

TextCol_4.MappingName = "Manager"

TextCol_4.HeaderText = "Manager"

TextCol_4.Width = 50

TextCol_4.AllowNull = False

TextCol_4.FalseValue = "N"

TextCol_4.TrueValue = "Y"

ts1.GridColumnStyles.Add(TextCol_4)

Me.grdSalesmen.TableStyles.Add(ts1)

 

[Andy O'Neill]

Updating.
The grid will write data back to the dataset.
You still need to write the changes back to the database.

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click

Update_Data(Me, SqlDa, dsSalesmen)

Fix_Scroll(Me.grdSalesmen)

End Sub

------------------------



Try

da.Update(ds)

Catch ex As DBConcurrencyException

MsgBox("There was a problem updating." & vbCrLf & _

"See marked rows" & vbCrLf & _

"This is probably due to someone else working on the data at the same time")

Catch ex As SqlException

MsgBox(ex.Errors)

End Try

If ds.HasChanges() Then

' not everything has successfully updated

' This is likely due to concurrency issues

Dim dt As DataTable = ds.Tables(0)

Dim dr As DataRow

For Each dr In dt.Rows

If dr.RowState = DataRowState.Added Then

dr.RowError = "Failed to add this row"

ElseIf dr.RowState = DataRowState.Modified Then

dr.RowError = "Failed to update this row"

ElseIf dr.RowState = DataRowState.Deleted Then

dr.RowError = "Failed to delete this row"

dr.RejectChanges()

End If

Next dr

GoTo reset_cursor

Else

ds.Clear()

da.Fill(ds)

MsgBox("Updated")

End If

 

[Kali]

Thank you Andy. I really appreciate your replies.

I have a datagridview on the form now with 2 dropdownlist columns defined.
I need to bind the datagridviewcontrol to an existing set of data (few
joined tables) and display the results. Only thing is, I need the first
combobox to contain a list of items from an existing table and have the
value selected already from the original data (by id). Here is the code I'd
use in an asp.net page to bind the grid initially... 2 sql commands, 1 to
fill the combobox and the other to get the existing data and preselect the
value from the combobox. Just cant seem to accomplish this in a windows
form app.

protected void dgBooks_ItemDataBound(object sender,
DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.EditItem)
{
//bind drop down list of books
SqlConnection cn = new
SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("cn"));
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from Books order by book_title";
cn.Open();

DropDownList ddlBook;
ddlBook = (DropDownList)e.Item.FindControl("ddlBook");
ddlBook.DataSource =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
ddlBook.DataBind();
ddlBook.Items.Insert(0, "");
cn.Close();
//end of binding drop down list of books

//set values
SqlConnection cn2 = new
SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("cn"));
SqlDataReader dtr;
SqlCommand objCmd;
string strSql;
strSql = "select * from ordered_books where record_id='" +
dgBooks.DataKeys[e.Item.ItemIndex] + "'";
objCmd = new SqlCommand(strSql, cn2);
cn2.Open();
dtr = objCmd.ExecuteReader();
dtr.Read();

ddlBook.Items.FindByValue(dtr["book_id"].ToString()).Selected =
true;

DropDownList ddlQuantityOfBook =
(DropDownList)e.Item.FindControl("ddlQuantityOfBook");
ddlQuantityOfBook.Items.FindByValue(dtr["quantity_of_book"].ToString()).Selected
= true;

cn2.Close();
}
}

 

[Andy O'Neill]

Thank you Andy. I really appreciate your replies.

I have a datagridview on the form now with 2 dropdownlist columns defined.
I need to bind the datagridviewcontrol to an existing set of data (few
joined tables) and display the results. Only thing is, I need the first
combobox to contain a list of items from an existing table and have the
value selected already from the original data (by id). Here is the code
I'd use in an asp.net page to bind the grid initially... 2 sql commands,
1 to fill the combobox and the other to get the existing data and
preselect the value from the combobox. Just cant seem to accomplish this
in a windows form app.

I don't follow that code or your problem.
If you bind a column to the id of the selected book then it'll be the one
shown when you present either an asp.net datagridview or a windows
datagridview.
So I don't get why you need some of that code.

Does the list of data in each combo vary depending on a value in another
column?

 

[Kali]

No, the list remains the same, just that the selected value would be
different based on what the user originally entered.

Do you have a basic example of a datagridview with 1 combobox column? The
combobox column would contain a list of books as a result of a simple query
(select book_id, book_title from books)...

Books table...
Book_id, Book_Title
1, Whatever book
2, This is a book
3, Another book
4, Some book

There is an Order_Books table that looks like this...

Customer_Number, Book_ID
45, 2
45, 4

The datagridview would be bound by running the quesy "select * from
order_books where customer_number=45"

The grid then gets bound with 2 rows and the comboboxes are auto selected
at...

This is a book
Some book


The user can then add, change or delete a row.

 

[Andy O'Neill]

No, the list remains the same, just that the selected value would be
different based on what the user originally entered.

Do you have a basic example of a datagridview with 1 combobox column? The

That's the simplest bit of code I could find mate.
It does what you want.

combobox column would contain a list of books as a result of a simple
query (select book_id, book_title from books)...

So that query goes in your data template.
Setting the id as the datavalue sets the selected item.
( As it would have with your asp.net screen, I don't get why you have all
that findcontrol stuff ).

Books table...
Book_id, Book_Title
1, Whatever book
2, This is a book
3, Another book
4, Some book

There is an Order_Books table that looks like this...

Customer_Number, Book_ID
45, 2
45, 4

The datagridview would be bound by running the quesy "select * from
order_books where customer_number=45"

So you put that in the select for the dataadapter or however that works with
the new binder syntax.
Obviously that there customer number will be a parameter but you can have it
hard coded whilst developing.
On the read data button click you can substitute the entire select statement
if you like.

The grid then gets bound with 2 rows and the comboboxes are auto selected
at...

This is a book
Some book


The user can then add, change or delete a row.

That's how that salesmen screen works..
The only odd thing is that you have 2 tables.
If you can't work out how to generate the dataadapter then do something like
this.
Sorry if I make a istake here or it's different, I haven't written a windows
form since 2004.

Create a table in your database with the same columns as your select query.
Drag and drop and create a strongly typed dataset based on it.
Plus a dataadapter for the table.
You have select, insert, update and delete sql statements for your
dataadapter.
Change the select to the string you have.
Plus the other statements...
Drop the table.

 

[Kali]

Thanks Andy. I see where you're defining the columns and adding the items,
etc. but I don't see where you're selecting existing records and allowing
the user to edit. Am I looking at this the wrong way?

 

[Andy O'Neill]

Thanks Andy. I see where you're defining the columns and adding the
items, etc. but I don't see where you're selecting existing records and
allowing the user to edit. Am I looking at this the wrong way?

You don't see it because you don't need any.
Bear in mind that code is from a working commercial application.

You don't write code to select the existing records except to alter that
select sql.
You need to read up on binding mate.

Datasets are writable.
Bind to one and you need absoltuely zero code to write back to it from a
windows datagrid(view)..
You do, however. have to write the insert, delete and update sql that goes
into the dataadapter.

 

[Kali]

Thank you Andy. I very much appreciate the feedback. I think I'm just
confused between the complexity of an asp.net app and the ease of a windows
app (as it pertains to the datagrid anyways).

So do I define the columns in the properties (colomns collection) first.
Then populate the data from a sql statement?
is the datagrid(view) always aware of the underlying adatper/dataset that
bound it? Will the value of the drop down list auto select based on the
select statement to get the existing data? Eg. will Book 2 auto select in
the combobox if it's bound to a dataset?

 

[Andy O'Neill]

Thank you Andy. I very much appreciate the feedback. I think I'm just
confused between the complexity of an asp.net app and the ease of a
windows app (as it pertains to the datagrid anyways).

The asp.net app was badly written mate.

Somewhere out there on the web will be introductory articles on how to work
with strongly typed datasets, binding and windows forms.
I would advise you to find and read them then go back and look at my code.
There is no way I can write as clear and extensive an explanation as you
need.

So do I define the columns in the properties (colomns collection) first.
Then populate the data from a sql statement?

If you configure it via the collection property then you need to have
already bound it.
I used to write that template stuff manually.

is the datagrid(view) always aware of the underlying adatper/dataset that
bound it?

Read up on binding.

Will the value of the drop down list auto select based on the select
statement to get the existing data? Eg. will Book 2 auto select in the
combobox if it's bound to a dataset?

Read my previous post again,

 

[Kali]

Will do, Andy. Thank you very much for the assistance. I really appreciate
it.

I'd much prefer to add columns programmatically vs. the columns collection.
I will read up on strongly typed datasets and binding to the control. I'll
then review your posts.

Thank you very much.

 

[Kali]

Hi Andy. Line...

Dim cboReg As New DataGridBoundComboColumn

doesn't seem to work in VStudio 2008. VStudio doesn't understand
DataGridBoundComboColumn. Any ideas?