Link to home
Start Free TrialLog in
Avatar of David Megnin
David MegninFlag for United States of America

asked on

How to fill an ASP.NET 4.0 GridView from a Stored Procedure

I have a GridView on a web form with DropDownList in one column.  I need to populate the TextBoxes in the next column over with a COUNT query using the value from the DropDownList as a parameter.  There are two tables involved in the query, so I can't just bind to a SqlDataSource.

How can I populate a field in the GridView using a multi-table query.  I expect I'll have to use a StoredProcedure?  Are there any examples of this?

Thanks.
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

To Call to get the value use this:

You don't have to, but it's usually better to:

Dim conn as SqlConnection("<MyConnectionString">)
Dim cmd as SqlCommand("", conn)
Dim count as integer

cmd.CommandText = " Select count(col1) from tblA a join tblB b on a.B_ID = b.ID where ..."
conn.Open()
count = cmd.ExecuteScalar() 'return one value, else use a data reader.
conn.Close()




for a stored proc:


Dim conn as SqlConnection("<MyConnectionString">)
Dim cmd as SqlCommand("", conn)
Dim count as integer


cmd.CommandText = "StoredProcName"
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
count = cmd.ExecuteScalar() 'return one value, else use a data reader.
conn.Close()





When would you be doing the databinding?

Is it possible to include the logic in your first query so you're not making so many round trips to the database?  Then you would only have to worry about doing this on the new row.
SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David Megnin

ASKER

ged325, since the same value may be selected in the DDL on more than one row, I need for all of the "same values" to be updated when the SelectedValue changes.  Also a result of that is, a previously selected value may be changed which will mean that two values need to be updated.

Let me explain by example.  I'll use common objects for simplicity's sake...  Let's say the purpose of the GridView tool is assign Teachers to Classrooms.  The classrooms have varying numbers of students.
One column contains the names of the Classrooms.
The DDL displays the Teacher Names with their ID as the value.
Each Teacher will be selected on more than one row. Between 5 and 10, normally.
The next column will display the COUNT of the number of Classrooms the selected Teacher has been assigned to in total.
The next column will display the TOTAL number of students in all classes that teacher has been assigned to.

The reason for the counts and totals is so that no one teacher is assigned more classes than they can handle or more total students than they are allowed.

The assignment process is complicated so a few teachers may be assigned to a few classes and then some of them may be changed as the numbers start filling up.  Say, class # 2 and 3 are assigned to Susan and # 4 and 5 are assigned to William.  # 2 may have to be changed to William or maybe Edward.  The counts and totals need to keep track of the changes.
On every row where Susan has been selected her count and totals need to be reflected, with each change, as well as the count and totals of a teacher that is UN-selected, to remove that class and total of students from her values.

There are more details, including a screen capture video and my markup and code behind here in my question where I requested attention and got no responces:  https://www.experts-exchange.com/questions/28013302/GridView-aggregate-column.html
CodeCruiser, thank you, those links look helpful, especially the first one, for getting the selcted index of the DDL, but my bigger problem is how to populate other controls in the GV from a multi-table joined query or SP using that index as a parameter.

I have some of the code done (see link in comment above), but the problem I'm having is the values are only displayed on the current row I'm selecting.  When I go make a selection on a different row all to other values are blank except for the new current row.  I can't populate all the rows in the GridView, only the current one.  (see video from link in comment above).

The video really makes it easier to understand what's going on.
See this for how to edit your gridview:
http://www.codeproject.com/Articles/23471/Editable-GridView-in-ASP-NET-2-0

There are most likely 2 things going on:

1) You're calling a databind in the page_load when you shouldn't (only on a Not PostBack condition to initially load the table)

2) You're not updating your datasource when moving from on DDL to the next.

If you attach your primary key to the keys of the gridView, you can use:

dropdown.Parent.Keys(0) to get the primary key of the row.  Call an update to the database, and then it should persist.
1) Ahhh, that may be the main problem. I'll work on fixing that.
   [edit] No, my Page_Load has nothing in it whatsoever.
This is pretty much the complete code behind other than "Imports System.Data", etc.:
Protected Sub ddlMonitors_SelectedIndexChanged(sender As Object, e As EventArgs)
    Dim ddlMonitors As DropDownList = DirectCast(sender, DropDownList)
    Dim row As GridViewRow = DirectCast(ddlMonitors.NamingContainer, GridViewRow)

    Dim Worksite As String = row.Cells(4).Text
    Dim Monitor As String = ddlMonitors.SelectedValue

    Dim query As String = "UPDATE Worksites SET keyMonitorID = @MonitorID WHERE keyWorksiteID = @WorksiteID; SELECT COUNT(1) FROM Worksites WHERE keyMonitorID = @MonitorID ; "

    Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("SYEP2012ConnectionString").ConnectionString)
    Dim com As New SqlCommand(query, con)

    com.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = Monitor
    com.Parameters.Add("@WorksiteID", SqlDbType.VarChar).Value = Worksite

    Try
        con.Open()
        Dim sites As String = com.ExecuteScalar
        row.Cells(10).Text = sites  'Count of Worksites where Selected Monitor is assigned.


        Dim DBCmd1 As New SqlCommand 'Load
        Dim DBCmd2 As New SqlCommand 'City

        DBCmd1 = New SqlCommand("SELECT COUNT(1) FROM [Applicants] WHERE keyWorksiteID IN (SELECT keyWorksiteID FROM Worksites WHERE keyMonitorID = @MonitorID)", con)
        DBCmd1.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = Monitor
        Dim Load As Integer = DBCmd1.ExecuteScalar
        row.Cells(11).Text = Load

        DBCmd2 = New SqlCommand("SELECT City FROM Monitors WHERE keyMonitorID = @MonitorID", con)
        DBCmd2.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = Monitor
        Dim City As String = DBCmd2.ExecuteScalar
        row.Cells(12).Text = City

        con.Close()

    Catch exp As Exception
        Response.Write(exp)
        'Server.Transfer("Error.htm")
    End Try

    row.Cells(9).Text = Monitor

End Sub

Open in new window


2) Can you show me how to do the updating when moving from one DDL to the next and the "Call an update".  (I'm a novice trying to accomplish things way over my head.)

Thank you!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That's one way to do the update . . . but I normally follow the layout I put in my previous post:
http://www.codeproject.com/Articles/23471/Editable-GridView-in-ASP-NET-2-0


This shows you how to edit the gridview and apply changes to the database.  Note that the code I provided will only do it on the front end.  If you want to implement a save later on you're better of following the template in the link above.
I'm not sure I know what you mean here:
Note that the code I provided will only do it on the front end.  If you want to implement a save later on you're better of following the template in the link above.

BTW: The way I have it now, there are no "Edit" buttons.  The GV is never put in "Edit Mode".  When a DDL selection is made, the _SelectedIndexChanged sub fires and updates the database.
On the other page (see link a few comments back) I only posted the markup for the template field where the DDL is and one of the TextBoxes.  Here is the entire GridView and DataSource.
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
        DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" AllowPaging="True">
        <AlternatingRowStyle BackColor="White" />
        <Columns>
            <asp:BoundField DataField="WorksiteZip" HeaderText="Zip" SortExpression="WorksiteZip" />
            <asp:BoundField DataField="WorksiteCity" HeaderText="City" SortExpression="WorksiteCity" />
            <asp:BoundField DataField="EmployerName" HeaderText="Employer" SortExpression="EmployerName" />
            <asp:BoundField DataField="WorksiteName" HeaderText="Worksite" SortExpression="WorksiteName" />
            <asp:BoundField DataField="keyWorksiteID" HeaderText="Worksite ID" SortExpression="keyWorksiteID" />
            <asp:BoundField DataField="Program" HeaderText="Prog" SortExpression="Program" />
            <asp:BoundField DataField="PositionsAvailable" HeaderText="Avail" SortExpression="PositionsAvailable" />
            <asp:BoundField DataField="PositionsTaken" HeaderText="Taken" SortExpression="PositionsTaken" />
            <asp:TemplateField HeaderText="Monitor DropDownList">
                <ItemTemplate>
                    <asp:DropDownList ID="ddlMonitors" runat="server" AutoPostBack="True" DataSourceID="SqlDataSourceMonitors" 
                        DataTextField="Name" DataValueField="keyMonitorID" OnSelectedIndexChanged="ddlMonitors_SelectedIndexChanged" AppendDataBoundItems="true">
                        <asp:ListItem Text="" Value=""></asp:ListItem>
                    </asp:DropDownList>
                    <asp:SqlDataSource ID="SqlDataSourceMonitors" runat="server" ConnectionString="<%$ ConnectionStrings:SYEP2012ConnectionString %>"
                        SelectCommand="SELECT [LastName] + ', ' + [FirstName] + ' - ' + [City] + ' - ' + ISNULL([Zip],'00000') + ' - ' + [HireStatus] AS [Name], [keyMonitorID] FROM [Monitors] ORDER BY [HireStatus], [City], [LastName], [FirstName]">
                    </asp:SqlDataSource>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="ID">
                <ItemTemplate>
                    <asp:TextBox ID="MonitorID" runat="server" Width="80px"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Sites">
                <ItemTemplate>
                    <asp:TextBox ID="txtSites" runat="server" Width="40px" Wrap="False"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Load">
                <ItemTemplate>
                    <asp:TextBox ID="txtLoad" runat="server" Width="40px" Wrap="False"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="City">
                <ItemTemplate>
                    <asp:TextBox ID="txtCity" runat="server" Width="150px" Wrap="false"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Zip">
                <ItemTemplate>
                    <asp:TextBox ID="txtZip" runat="server" Width="80px" Wrap="False"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <EditRowStyle BackColor="#7C6F57" />
        <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#E3EAEB" />
        <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
        <SortedAscendingCellStyle BackColor="#F8FAFA" />
        <SortedAscendingHeaderStyle BackColor="#246B61" />
        <SortedDescendingCellStyle BackColor="#D4DFE1" />
        <SortedDescendingHeaderStyle BackColor="#15524A" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SYEP2012ConnectionString %>"
        SelectCommand="SELECT [WorksiteZip], [WorksiteCity], [EmployerName], [WorksiteName], [keyWorksiteID], 
					   [Program], [PositionsAvailable], [PositionsTaken] 
					   FROM [JobTitles]
					   WHERE WorksiteZip NOT IN ('00000', '11111', '22222', '33333', '44444', '55555', '66666')
					   ORDER BY WorksiteZip "></asp:SqlDataSource>

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ged325, thank you for all that info.  It will take me a little while to get through it all and find out what I understand and if the solution is in there.

Here is a link to that screen capture video:
Capture video of DDL selections.

It currently writes all the correct values to the database as soon as the DDL is selected, it just doesn't pull the values back from the database to populate all the rows.
Try adding a text property:

(added:  Text='<%# Eval("City")%>')

Full text box:

<asp:TextBox ID="txtCity" runat="server" Width="150px" Wrap="false" Text='<%# Eval("City")%>'></asp:TextBox>
I think that will give me the City from the Worksite table (SqlDataSource1).  The value of that TextBox should be the City from the Monitors table (SqlDataSourceMonitors).
ged325, I took your suggestion from your first comment and modified my connection statements.  I do like that format better.  It makes it simple to convert one to use a StoredProcedure.

There are no operational changes just formatted like your example now:
    Protected Sub ddlMonitors_SelectedIndexChanged(sender As Object, e As EventArgs)

        Dim ddlMonitors As DropDownList = DirectCast(sender, DropDownList)
        Dim row As GridViewRow = DirectCast(ddlMonitors.NamingContainer, GridViewRow)

        Dim WorksiteID As String = row.Cells(4).Text
        Dim MonitorID As String = ddlMonitors.SelectedValue

        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SYEP2012ConnectionString").ConnectionString)
        Dim cmd As New SqlCommand("", conn)
        Dim count As Integer

        cmd.CommandText = "UPDATE [Worksites] SET keyMonitorID = @MonitorID WHERE keyWorksiteID = @WorksiteID; SELECT COUNT(1) FROM [Worksites] WHERE keyMonitorID = @MonitorID ; "


        cmd.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
        cmd.Parameters.Add("@WorksiteID", SqlDbType.VarChar).Value = WorksiteID

        Try
            conn.Open()
            count = cmd.ExecuteScalar
            row.Cells(10).Text = count  'Count of Worksites where Selected Monitor is assigned.


            Dim DBCmd1 As New SqlCommand("", conn) 'Caseload (Number of students at all assigned sites)
            Dim DBCmd2 As New SqlCommand("", conn) 'City where Monitor lives.
            Dim Caseload As Integer
            Dim mCity As String

            DBCmd1.CommandText = "SELECT COUNT(1) FROM [Applicants] WHERE keyWorksiteID IN (SELECT keyWorksiteID FROM [Worksites] WHERE keyMonitorID = @MonitorID) ;"
            DBCmd1.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
            Caseload = DBCmd1.ExecuteScalar
            row.Cells(11).Text = Caseload

            DBCmd2.CommandText = "SELECT City FROM [Monitors] WHERE keyMonitorID = @MonitorID"
            DBCmd2.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
            mCity = DBCmd2.ExecuteScalar
            row.Cells(12).Text = mCity

            conn.Close()

        Catch exp As Exception
            Response.Write(exp)
            'Server.Transfer("Error.htm")
        End Try

        row.Cells(9).Text = MonitorID

        '' Notes: Above SQL commands easily converted to StoredProcedures by using format below:
        '' cmd.CommandText = "StoredProcName"
        '' cmd.CommandType = CommandType.StoredProcedure

    End Sub

Open in new window

When would you be doing the databinding?
Should be every time any DDL.SelectedIndex changes so all the counts and Caseloads are updated.

Is it possible to include the logic in your first query so you're not making so many round trips to the database?  Then you would only have to worry about doing this on the new row.
Hmmm, I don't see how.
The first query Updates the [Worksites] table putting the keyMonitorID of the selected Monitor into the foreign key field, basically "assigning" the selected Monitor to that Worksite.
UPDATE [Worksites] SET keyMonitorID = @MonitorID WHERE keyWorksiteID = @WorksiteID; SELECT COUNT(1) FROM [Worksites] WHERE keyMonitorID = @MonitorID

Open in new window


The second query gets a list of Worksites where the selected Monitor has been assigned and uses that list to get a count of all the Applicants in those Worksites.
SELECT COUNT(1) FROM [Applicants] WHERE keyWorksiteID IN (SELECT keyWorksiteID FROM [Worksites] WHERE keyMonitorID = @MonitorID)

Open in new window


The third query just gets the City name from the selected Monitor's record.

Because the queries all access different tables or combination of tables, I wouldn't know where to begin combining them.  I may just not understand what you meant by "...include the logic in your first query so you're not making so many round trips..."
Can you layout your table structure and explain the relationships between the tables?

Did adding the text property help?

Also note you should still change the code so you're not using  row.Cells(12).Text = mCity
(added:  Text='<%# Eval("City")%>') would pull the City name from the Worksites table which the GridView is bound to.  That field is for the Monitor's home city which I get from
   DBCmd2.CommandText = "SELECT City FROM [Monitors] WHERE keyMonitorID = @MonitorID"
            DBCmd2.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
            mCity = DBCmd2.ExecuteScalar
            row.Cells(12).Text = mCity

Open in new window

I'll change the Cells reference to the control reference as you suggested.  That makes sense.  It just got late yesterday and I had to go home.  I'll be in a staff meeting today from 9:30 until 11:00 A.M. EST but I'll work on this more when the meeting is over.

Thank you so much for you help.  I really appreciate it.
One of the benefits of referencing the cells by name instead of by number was that the values remain in the field after a Post Back.

The do not, however, remain after a full page refresh, though.  The values are not pulled from the database to re-populate the GridView.  That's what I need now.

Here is my ddlMonitors_SelectedIndexChanged after the modification:
    Protected Sub ddlMonitors_SelectedIndexChanged(sender As Object, e As EventArgs)

        Dim ddlMonitors As DropDownList = DirectCast(sender, DropDownList)
        Dim row As GridViewRow = DirectCast(ddlMonitors.NamingContainer, GridViewRow)
        Dim txtSites As TextBox = DirectCast(ddlMonitors.Parent.FindControl("txtSites"), TextBox)
        Dim txtLoad As TextBox = DirectCast(ddlMonitors.Parent.FindControl("txtLoad"), TextBox)
        Dim txtCity As TextBox = DirectCast(ddlMonitors.Parent.FindControl("txtCity"), TextBox)
        Dim txtZip As TextBox = DirectCast(ddlMonitors.Parent.FindControl("txtZip"), TextBox)
        Dim txtMonitorID As TextBox = DirectCast(ddlMonitors.Parent.FindControl("txtMonitorID"), TextBox)

        Dim WorksiteID As String = row.Cells(4).Text  'I'm not sure how to reference this boundfield by name.
        Dim MonitorID As String = ddlMonitors.SelectedValue

        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SYEP2012ConnectionString").ConnectionString)
        Dim cmd As New SqlCommand("", conn)
        Dim count As Integer

        cmd.CommandText = "UPDATE [Worksites] SET keyMonitorID = @MonitorID WHERE keyWorksiteID = @WorksiteID; SELECT COUNT(1) FROM [Worksites] WHERE keyMonitorID = @MonitorID ; "


        cmd.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
        cmd.Parameters.Add("@WorksiteID", SqlDbType.VarChar).Value = WorksiteID

        Try
            conn.Open()
            count = cmd.ExecuteScalar
            txtSites.Text = count.ToString()  'row.Cells(10).Text = count  'Count of Worksites where Selected Monitor is assigned.


            Dim DBCmd1 As New SqlCommand("", conn) 'Caseload (Number of students at all assigned sites)
            Dim DBCmd2 As New SqlCommand("", conn) 'City where Monitor lives.
            Dim DBCmd3 As New SqlCommand("", conn) 'Zip where Monitor lives.
            Dim Caseload As Integer
            Dim mCity As String
            Dim mZip As String

            DBCmd1.CommandText = "SELECT COUNT(1) FROM [Applicants] WHERE keyWorksiteID IN (SELECT keyWorksiteID FROM [Worksites] WHERE keyMonitorID = @MonitorID) ;"
            DBCmd1.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
            Caseload = DBCmd1.ExecuteScalar
            txtLoad.Text = Caseload.ToString() ' row.Cells(11).Text = Caseload

            DBCmd2.CommandText = "SELECT City FROM [Monitors] WHERE keyMonitorID = @MonitorID"
            DBCmd2.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
            mCity = DBCmd2.ExecuteScalar
            txtCity.Text = mCity.ToString() 'row.Cells(12).Text = mCity

            DBCmd3.CommandText = "SELECT Zip FROM [Monitors] WHERE keyMonitorID = @MonitorID"
            DBCmd3.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
            mZip = DBCmd3.ExecuteScalar
            txtZip.Text = mZip.ToString() 'row.Cells(13).Text = mZip

            conn.Close()

        Catch exp As Exception
            Response.Write(exp)
            'Server.Transfer("Error.htm")
        End Try

        txtMonitorID.Text = MonitorID.ToString() 'row.Cells(9).Text = MonitorID

        '' Notes: Above SQL commands easily converted to StoredProcedures by using format below:
        '' cmd.CommandText = "StoredProcName"
        '' cmd.CommandType = CommandType.StoredProcedure

    End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ged325, I want to make sure we are both on the same page because I don't really understand what the code above is doing.

My database updating is all good now.  This application could be used for it's intended purpose as is.  It works.  It's not convenient because the information doesn't stay in the grid after the page is refreshed.

This is the sole purpose of the application:
UPDATE [Worksites] SET keyMonitorID = @MonitorID WHERE keyWorksiteID = @WorksiteID
It doesn't do anything else at all.

Grabbing the counts and totals out of the database and filling the GridView is to help staff see how many Sites (Worksites) the Monitors have been assigned to so far so they don't overdo it.  Same with the Load or count of Applicants.   I just need to fill the GridView when the page loads and re-fill it when a Monitor selection changes.  Please look at the attached .flv
2-8-2013-12-44-18-PM.flv
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, I check the database every time I make a change that may affect it.  I see the correct MonitorIDs in the Worksites table after every DDL SelectedIndexChanged.

Thank you.  That just might do it.  I may need some help tweaking it, but I'll study the links you included first.  Adding events to grids and that e.Row thing are just outside my scope of experience, but I'll get there.  ;)

I'm off today but I'm try to remote in and look at this this weekend if I get a chance.
Thank you so much for all the help. I'm sorry I forgot to close this for so long.