Drop Down List in a Gridview with option to update

I would like to display a Gridview control with a Drop Down list inside of it, with the ability to update a database table.
I have two tables
TRNS and CAT

TRNS
TRNID Type, Name, Amount, CATID
1 POS Outback -45.00 1
1 POS Petco -67.00 2
1 POS Kash n Karry -131.00 0

CATS
CATID CAT
1 Dinners
2 Pets
3 Groceries

Right now, I have a gridview that just queries the TRNS table. I would like to be able to display the category name in drop downlist and be able to change it.

All the transaction will start off with a CATID of 0 and will need to be updated, and I want to display the CAT NAME instead of the ID.

Here is what I have so far...

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack() Then
            gvTransactionHistory.DataSource = GetTransactionHistory().Tables("dtTransactionHistory")
            gvTransactionHistory.DataBind()
        End If
    End Sub

Public Function GetTransactionHistory() As DataSet

        Try
            connection = New SqlConnection(ConfigurationManager.ConnectionStrings("WachoviaConnectionString1").ConnectionString)
            ds = New DataSet

            da = New SqlDataAdapter("TransactionHistory", connection)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.Fill(ds, "dtTransactionHistory")

            da = New SqlDataAdapter("Categories", connection)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.Fill(ds, "dtCategories")

            Return ds
        Catch ex As Exception
            Response.Write(ex.Message)
            Return Nothing
        End Try

<asp:GridView ID="gvTransactionHistory" runat="server" AllowPaging="True" OnPageIndexChanging ="PageResults" AutoGenerateColumns="false">
                        <Columns>
                            <asp:BoundField HeaderText="Type" DataField="TRNTYPE" />
                            <asp:BoundField HeaderText="Posted" DataField="DTPOSTED" />
                            <asp:BoundField HeaderText="Amount" DataField="TRNAMT" />
                            <asp:BoundField HeaderText="Name" DataField="Name" />
                            <asp:TemplateField>
                                <itemtemplate>
                                    <asp:DropDownList ID="ddlCategory" runat="server">
                                    </asp:DropDownList>
                                </itemtemplate>
                            </asp:TemplateField>
                        </Columns>
                    </asp:GridView>

LVL 8
JRockFLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

noamattdCommented:
Add this to somewhere in your columns

<asp:ButtonColumn Text="Update" CommandName="Update"></asp:ButtonColumn>

Then hook the ItemCommand event handler (this is in C#, sorry)

this.grid.ItemCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.grid_ItemCommand);

finally, the function :
private void userGrid_ItemCommand(object source, DataGridCommandEventArgs e)
{
   if(e.CommandName == "Update")
   { //db access code // }
}

You can have any number of columns that have their own commands.  Access the stuff in the grid's row with e.Item.Cells[index].
0
JRockFLAuthor Commented:
Thank you for your reply.

I'm trying to do it witout the button. After selecting the category, it would then autopost back and do the db update.

                    <asp:GridView ID="gvTransactionHistory" runat="server" AllowPaging="True" OnPageIndexChanging ="PageResults" AutoGenerateColumns="False">
                        <Columns>
                            <asp:BoundField HeaderText="Type" DataField="TRNTYPE" />
                            <asp:BoundField HeaderText="Posted" DataField="DTPOSTED" />
                            <asp:BoundField HeaderText="Amount" DataField="TRNAMT" />
                            <asp:BoundField HeaderText="Name" DataField="NAME" />
                            <asp:TemplateField>
                            <ItemTemplate>
                                 <asp:DropDownList ID="ddlCategories" runat="server" DataSource="<%#GetCategories%>" DataValueField="CATID" DataTextField="CAT" SelectedValue='<%# Eval("CATID") %>'>
                                 </asp:DropDownList>  
                            </ItemTemplate>
                            </asp:TemplateField>
                        </Columns>
                    </asp:GridView>
0
noamattdCommented:
You can do that, too:

<asp:DropDownList ID="ddlCategories" runat="server" DataSource="<%#GetCategories%>" DataValueField="CATID" DataTextField="CAT" SelectedValue='<%# Eval("CATID") %>'  AutoPostBack="True"                    OnSelectedIndexChanged="DropDown_SelectedIndexChanged"></asp:DropDownList>

Then do your db code in DropDown_SelectedIndexChanged; access the other fields of the row with

DataGridItem dgi = (DataGridItem)((DropDownList)sender).Parent.Parent;

param1 = dgi.Cells[0],

and so on.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

JRockFLAuthor Commented:
It doesen't want to "recognize" the drop downlist since it's in the gridview.

This is what I have...
Protected Sub DropDown_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCategories.SelectedIndexChanged

End Sub

Now if ddlCategories is ouside the gridview, then it sees it
0
JRockFLAuthor Commented:
Hmmm it looks like if I leave off the ddlCategories.SelectedIndexChanged i might be able to get it to work

 Protected Sub DropDown_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Response.Write("Hello")
    End Sub
0
noamattdCommented:
Right, you don't want to wire an event handler explicitly in code, that's taken care of with the  OnSelectedIndexChanged="DropDown_SelectedIndexChanged" attribute in the server tag.
0
JRockFLAuthor Commented:
Ok, but I'm still having problems...
Here is a screen print.
http://rockenbach.net/sp.gif

So for the first one, there is a check for -$50.00
I should be able to select a category from the dropdown and it will automatically do they update with the selected category.

So right now, when I click on one of the drop downs, it will display "hello"
Protected Sub DropDown_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Response.Write("Hello")
    End Sub

I need the ID for the row that was just selected and also the selected value of the drop down.
I do have a column called ID in the db, but it's not being displayed.

I need to do something like this....I just need the values.
Protected Sub DropDown_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        UPDATE TRNS
        SET CAT = selected value in drop down
        WHERE ID = the id from the the row
    End Sub
0
noamattdCommented:
To get the selected value, just do ((DropDownList)sender).SelectedValue.

As I said earlier, to get to your ID column you'll first need to get the row containing your ddl, like this:

DataGridItem dgi = (DataGridItem)((DropDownList)sender).Parent.Parent;

ID = dgi.Cells[index of ID column].Text

And that should be all you need.  I may be wrong about how many "Parents" to traverse since I'm going from memory, but traversing them will inevitably get you to a "DataGridItem", which is the class for representing a row in a datagrid.
0
JRockFLAuthor Commented:
Since I have the Drop Down List in the gridview, it doesn't seem to recognize it.
The name is ddlCategories, so after I type it...I dont' get the methods with intellisense. Make sense?

"DataGridItem dgi = (DataGridItem)((DropDownList)sender).Parent.Parent;"
Also, I'm using a gridview and not a datagrid...
0
noamattdCommented:
Oh, sorry, I can't believe I missed that.  I haven't used 2.0 or the gridview at all, but here's the idea behind accessing dynamic controls.

You can't just type in the name and get intellisense for them, because technically they don't exists until some data's pumped into their container to create rows.  You need to access the controls collection of the cell you're working with, and cast it as a dropdownlist.  What I've told you should still apply, you'll just have to adjust it for a gridview.  You've done the hard part of getting the dropdownlists to fire the event, now it's just a matter of accessing the data.  Getting the dropdownlist's value is easy by doing ((DropDownList)sender).SelectedValue.  To get the ID from your row, you have to (as I said) access that row itself, which should be doable by using the "Parent" property of the dropdownlist, then the parent of whatever it's contained in (In a datagrid, it's tablecell).  You can check on that by debugging and plugging in the ddl, and it's parent, and parent's parent, etc, into the Watch panel.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.