Solved

Can I move three select statements into a single stored procedure?

Posted on 2013-05-31
14
362 Views
Last Modified: 2013-06-03
Sorry for the noob question... Is it possible to combine these three select statements into one Stored Procedure?  There is only one table involved.

It's in a GridView1.RowDataBound event and takes several seconds to update.  I'm looking to speed it up a bit by moving the statements into a stored procedure(s).

            Try
                conn.Open()

                Dim DBCmdMon As New SqlCommand("", conn)
                DBCmdMon.CommandText = "SELECT keyMonitorID FROM [Worksites] WHERE keyWorksiteID = @WorksiteID;"
                DBCmdMon.Parameters.Add("@WorksiteID", SqlDbType.VarChar).Value = WorksiteID
                MonitorID = DBCmdMon.ExecuteScalar.ToString
                txtMonitorID.Text = MonitorID 'row.Cells(9).Text = MonitorID

                Dim cmd As New SqlCommand("", conn)
                cmd.CommandText = "SELECT COUNT(1) FROM [Worksites] WHERE keyMonitorID = @MonitorID ; "
                cmd.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
                cmd.Parameters.Add("@WorksiteID", SqlDbType.VarChar).Value = WorksiteID

                'txtTEST.Text = MonitorID ' WorksiteID.ToString
                ddlMonitors.SelectedValue = MonitorID
                Dim count As Integer
                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 IsNULL(City,'') FROM [Monitors] WHERE keyMonitorID = @MonitorID"
                DBCmd2.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
                mCity = DBCmd2.ExecuteScalar
                If IsDBNull(mCity) Then
                    txtCity.Text = ""
                Else
                    txtCity.Text = mCity 'row.Cells(12).Text = mCity
                End If

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

                conn.Close()

            Catch exp As Exception

Open in new window

Thanks!
0
Comment
Question by:megnin
  • 7
  • 4
  • 3
14 Comments
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
Comment Utility
Yes it is possible.

create procedure <name>
@MonitorID varchar(100)
as

(SELECT COUNT(1) FROM [Applicants] WHERE keyWorksiteID IN (SELECT keyWorksiteID FROM [Worksites] WHERE keyMonitorID = @MonitorID) Counter,
IsNULL(City,'') ,
IsNull(Zip,'')
 FROM [Monitors] WHERE keyMonitorID = @MonitorID
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
Thank you!  I'll give that a try.  Stored Procedures still seem like "magic" to me.  ;)
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
I'm getting "Incorrect syntax near; Counter, City and Zip"  There is a closing parentheses missing and I can't figure out where it should go.

Also, I misstated that there was only one table involved.  I see that all three, Applicants, Worksites and Monitors are being queried.

With all three select statements in a single SP I'm not sure how I would get the three results into the three variables; Caseload, mCity and mZip in order to be sent to the .Text of their respective TextBoxes.

Would it be much slower to just turn each of the three DBCmd's into their own stored procedure?  Should I just do that instead of trying go put all three into a single stored procedure?
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
Corrected for that paren:
create procedure <name>
@MonitorID varchar(100)
as


(SELECT COUNT(1) FROM [Applicants] WHERE keyWorksiteID IN (SELECT keyWorksiteID FROM [Worksites] WHERE keyMonitorID = @MonitorID)) Counter,
IsNULL(City,'') City ,
IsNull(Zip,'') Zip
 FROM [Monitors] WHERE keyMonitorID = @MonitorID


This will return a single dataset with 3 columns.

Use a DataReader to get the results:


eg:  dr = cmd.ExecuteReader()

then use dr("COLUMN_NAME") to access the variable.

Going to the DB 3 times is very expensive in terms of time . . . and you're doing this for every row that's databound to your grid.

Ideally you would want to move the results into the main query (where you the rest of the data from) and then bind the grid once.  One query, one bind.

I normally use the rowdata bound for formatting, and sometimes it's not possible to avoid going back the DB, but it should be used sparingly and only when nothing else will do.
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
Ahhhh.  I didn't think to use a DataReader to get the multiple results.  That will help me on many other projects as well where I'm doing something similar.  

Could you give me or point me to an example of what you mean, "ideally you would want to move the results into the main query...", please?  That's another technique that will help me in many more places than just this problem, so I'd really like to learn exactly how to do it.

Thank you so much!
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
Somewhere you're setting

GridView1.DataSource = ?

You're using some sort of query to populate the data source.

If you could modify THAT query to include the data you need there wouldn't be a need to go back to the database at all.


Think of it as having a conversation with the DB:

You go up and say Hey I need information.

Current:
On the row data bound you're goign back to the DB:
Can you get me data for this guy
Oh and I also this other data for the same guy
Oh and this other piece I forgot to ask for.

Great you did it for that guy, now I need it for the next guy.  (one piece at a time)

VS:
Proposed solution:
Get me this information
I also need three pieces of information for this guy
now I need three pieces of information for the next guy.

VS:
(Best)
Can you get me all the information I need?
Thanks, have a nice day.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
You don't really need to build a stored procedure for that but I agree there are advantages. You can get all the values with 1 query:

SELECT 
	(SELECT COUNT(1) FROM [Worksites] WHERE keyMonitorID = keyMonitorID) as Count
	(SELECT COUNT(1) FROM [Applicants] WHERE keyWorksiteID IN (SELECT keyWorksiteID FROM [Worksites] WHERE keyMonitorID = keyMonitorID)) as Caseload
	IsNULL(City,'') as City, 
	IsNull(Zip,'') as Zip 
FROM 
	[Monitors] 
WHERE 
	keyMonitorID = (SELECT keyMonitorID FROM [Worksites] WHERE keyWorksiteID = @WorksiteID)

Open in new window

But you need to get it in a dataset, as it will come back as a result with 4 columns.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
The first suggestion is not quite OK because the parameter that actually controls everything is @WorksiteID and not @MonitorID so the procedure will be excuted in 2 steps:

1. Get the @MonitorID from @WorksiteID
2. Execute the procedure with the @MonitorID parameter.

In my solution you only have to pass @WorksiteID and you will get all 4 values with one trip to the database.

Of course you could create a procedure that will return the same query result but with @WorksiteID as parameter:
create procedure <name> 
	@WorksiteID varchar(100)
as

SELECT 
	(SELECT COUNT(1) FROM [Worksites] WHERE keyMonitorID = keyMonitorID) as Count
	(SELECT COUNT(1) FROM [Applicants] WHERE keyWorksiteID IN (SELECT keyWorksiteID FROM [Worksites] WHERE keyMonitorID = keyMonitorID)) as Caseload
	IsNULL(City,'') as City, 
	IsNull(Zip,'') as Zip 
FROM 
	[Monitors] 
WHERE 
	keyMonitorID = (SELECT keyMonitorID FROM [Worksites] WHERE keyWorksiteID = @WorksiteID)

Open in new window

Another thing is that probably @WorksiteID is an integer value so you can actually give the parameter a type of integer instead of varchar(100)
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
Thank you both for the explanations.  They both look to help me a great deal.

Let me give a brief description of what the application does since it's not a straightforward GridView.

I load the GridView from SqlDataSource1 simply enough:
SelectCommand="SELECT  [WorksiteZip], [WorksiteCity], [EmployerName], [WorksiteName], [keyWorksiteID], [Program], [PositionsAvailable], [PositionsTaken]
FROM [JobTitles]

But then the work is done in a DripDownList in a TemplateField. Selecting a Monitor to assign to a Worksite, count the number of Worksites that Monitor has been assigned to and display that in the next column over and also count the number of Applicants at all the Worksites that Monitor is assigned and display that total in the next column over from the other count:
<asp:TemplateField HeaderText="Monitor DropDownList">
                <ItemTemplate>
                        <asp:DropDownList ID="ddlMonitors" runat="server" AutoPostBack="True" DataSourceID="SqlDataSourceMonitors" DataTextField="Name" DataValueField="keyMonitorID" 
                        				  OnSelectedIndexChanged="ddlMonitors_SelectedIndexChanged" AppendDataBoundItems="false">
                        </asp:DropDownList>
                    <asp:SqlDataSource ID="SqlDataSourceMonitors" runat="server" ConnectionString="<%$ ConnectionStrings:SYEP2007ConnectionString %>"
                        SelectCommand="SELECT [LastName] + ', ' + [FirstName] + ' - ' + ISNULL([City], 'No City') + ' - ' + ISNULL([Zip],'No Zip') + ' - ' + [HireStatus] AS [Name], [keyMonitorID] FROM [Monitors] ORDER BY [HireStatus], [City], [LastName], [FirstName]">
                    </asp:SqlDataSource>
                </ItemTemplate>
            </asp:TemplateField>
<

Open in new window

    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("SYEP2007ConnectionString").ConnectionString)
        Dim cmd As New SqlCommand("spUpdate_Worksites_keyMonitorID_from_DropDownList", conn)
        'cmd.CommandText = "UPDATE [Worksites] SET keyMonitorID = @MonitorID WHERE keyWorksiteID = @WorksiteID;" '  SELECT COUNT(1) FROM [Worksites] WHERE keyMonitorID = @MonitorID ; "
        cmd.CommandType = Data.CommandType.StoredProcedure
        Select Case MonitorID
            Case "201340033"
                cmd.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = DBNull.Value
            Case Else
                cmd.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
        End Select
        cmd.Parameters.Add("@WorksiteID", SqlDbType.VarChar).Value = WorksiteID

        Try
            conn.Open()
            cmd.ExecuteScalar()
            conn.Close()
        Catch exp As Exception
            Response.Write(exp)
            'Server.Transfer("Error.htm")
        End Try

        GridView1.DataBind()

    End Sub

Open in new window


The code above is the majority of the application, but I'll post the full markup and code behind below just for clarity:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Monitors.aspx.vb" Inherits="Monitors" MaintainScrollPositionOnPostback="true" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Worksite Monitors</title>
    <script type="text/javascript" src="scripts\custom-form-elements.js"></script>
    <style type="text/css">
        h2
        {
            color: #8C004C;
        }
        
        
        /*CSS for custom-form-elements.js */
        /*
span.checkbox { 
  width: 19px; 
  height: 25px; 
  padding: 0 5px 0 0; 
  background: url(images\checkbox.gif) no-repeat; 
  display: block; 
  clear: left; 
  float: left; 
} 
span.radio { 
  width: 19px; 
  height: 25px; 
  padding: 0 5px 0 0; 
  background: url(images\radio.gif) no-repeat; 
  display: block; 
  clear: left; 
  float: left; 
} 
span.select { 
  position: absolute; 
  width: 158px; /* With the padding included, the width is 190 pixels: the actual width of the image.  
  height: 21px; 
  padding: 0 24px 0 8px; 
  color: #fff; 
  font: 12px/21px arial,sans-serif; 
  background: url(select.gif) no-repeat; 
  overflow: hidden; 
} 
*/
        
        span.checkbox
        {
            width: 19px;
            height: 25px;
            padding: 0 5px 0 0;
            background: url(../images/checkbox.png) no-repeat;
            display: block;
            clear: left;
            float: left;
        }
        span.radio
        {
            width: 19px;
            height: 25px;
            padding: 0 5px 0 0;
            background: url(../images/radio.png) no-repeat;
            display: block;
            clear: left;
            float: left;
        }
        span.select
        {
            position: absolute;
            width: 158px;
            height: 21px;
            padding: 0 24px 0 8px;
            color: #fff;
            font: 12px/21px arial,sans-serif;
            background: url(../images/select.gif) no-repeat;
            overflow: hidden;
        }
        span.table.td.radio
        {
            width: 19px;
            height: 25px;
            padding: 0 5px 0 0;
            background: url(../images/radio.png) no-repeat;
            display: block;
            clear: left;
            float: left;
        }
    </style>
</head>
<body onkeydown="if (window.event.keyCode == 13) 
{
    event.returnValue=false; 
    event.cancel = true;
}">
    <div>
        <table border="0" width="100%">
            <tr>
                <td>
                    <img src="images/WF1_Logo009.gif" alt="WorkForce One Logo" class="left" width="118"
                        height="40" />
                </td>
                <td>
                    <div>
                        <h2>Summer Youth 2013 Monitors</h2>
                    </div>
                </td>
                <td>
                    <img src="images/CSC_Logo_150x90.gif" alt="Children's Services Council Logo" class="right"
                        width="62" height="40" />
                </td>
            </tr>
        </table>
    </div>
    <%--<hr style="margin: 0px 0px 5px 0px" />--%>
    <form id="form1" runat="server">
    <asp:Button ID="btnJumpToVerification" runat="server" Style="text-align: center; margin-bottom:5px;" Text="Return to Admin Page" />

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
        DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" 
        PageSize="20" AllowSorting="True">
        <AlternatingRowStyle BackColor="White" />
        <Columns>
            <asp:BoundField DataField="WorksiteZip" HeaderText="Zip" SortExpression="WorksiteZip" />
            <asp:BoundField DataField="WorksiteCity" HeaderText="City" 
                SortExpression="WorksiteCity" >
            <ItemStyle Font-Size="Small" Width="125px" Wrap="False" />
            </asp:BoundField>
            <asp:BoundField DataField="EmployerName" HeaderText="Employer" SortExpression="EmployerName" />
            <asp:BoundField DataField="WorksiteName" HeaderText="Worksite" SortExpression="WorksiteName" />
            <asp:BoundField DataField="keyWorksiteID" HeaderText="WS ID" SortExpression="keyWorksiteID" />
            <asp:BoundField DataField="Program" HeaderText="Prog" SortExpression="Program" >
            <ItemStyle HorizontalAlign="Center" />
            </asp:BoundField>
            <asp:BoundField DataField="PositionsAvailable" HeaderText="Pos" 
                SortExpression="PositionsAvailable" >
            <ItemStyle HorizontalAlign="Center" />
            </asp:BoundField>
            <asp:BoundField DataField="PositionsTaken" HeaderText="Taken" 
                SortExpression="PositionsTaken" >
            <ItemStyle HorizontalAlign="Center" />
            </asp:BoundField>
            <asp:TemplateField HeaderText="Monitor DropDownList">
                <ItemTemplate>
                        <asp:DropDownList ID="ddlMonitors" runat="server" AutoPostBack="True" DataSourceID="SqlDataSourceMonitors" DataTextField="Name" DataValueField="keyMonitorID" 
                        				  OnSelectedIndexChanged="ddlMonitors_SelectedIndexChanged" AppendDataBoundItems="false">
                        </asp:DropDownList>
                    <asp:SqlDataSource ID="SqlDataSourceMonitors" runat="server" ConnectionString="<%$ ConnectionStrings:SYEP2007ConnectionString %>"
                        SelectCommand="SELECT [LastName] + ', ' + [FirstName] + ' - ' + ISNULL([City], 'No City') + ' - ' + ISNULL([Zip],'No Zip') + ' - ' + [HireStatus] AS [Name], [keyMonitorID] FROM [Monitors] ORDER BY [HireStatus], [City], [LastName], [FirstName]">
                    </asp:SqlDataSource>
                </ItemTemplate>
            </asp:TemplateField>
<%--
            <asp:TemplateField HeaderText="TEST">
                <ItemTemplate>
                    <asp:TextBox ID="txtTEST" runat="server" Width="80px"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
--%>
            <asp:TemplateField HeaderText="ID">
                <ItemTemplate>
                    <asp:TextBox ID="txtMonitorID" 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" Visible="False">
                <ItemTemplate>
                    <asp:TextBox ID="txtCity" runat="server" Width="150px" Wrap="false"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Zip" Visible="False">
                <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:SYEP2007ConnectionString %>"
        SelectCommand="SELECT  [WorksiteZip], [WorksiteCity], [EmployerName], [WorksiteName], [keyWorksiteID], 
					   [Program], [PositionsAvailable], [PositionsTaken] 
					   FROM [JobTitles]
					   WHERE WorksiteZip NOT IN ('00000', '11111', '22222', '33333', '44444', '55555', '66666') AND [PositionsAvailable] &gt; 0
					   ORDER BY WorksiteZip "></asp:SqlDataSource>
    </form>
</body>
</html>

Open in new window

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI.Control

Partial Class Monitors
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        '    RadioButton1.InputAttributes.Add("onmouseover", _
        '"this.style.backgroundColor = 'red'")
        '    RadioButton1.InputAttributes.Add("onmouseout", _
        '        "this.style.backgroundColor = 'white'")
        '    'RadioButton1.InputAttributes.Add("onmouseover", "this.style.class = 'styled'")
    End Sub

    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("SYEP2007ConnectionString").ConnectionString)
        Dim cmd As New SqlCommand("spUpdate_Worksites_keyMonitorID_from_DropDownList", conn)
        'cmd.CommandText = "UPDATE [Worksites] SET keyMonitorID = @MonitorID WHERE keyWorksiteID = @WorksiteID;" '  SELECT COUNT(1) FROM [Worksites] WHERE keyMonitorID = @MonitorID ; "
        cmd.CommandType = Data.CommandType.StoredProcedure
        Select Case MonitorID
            Case "201340033"
                cmd.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = DBNull.Value
            Case Else
                cmd.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
        End Select
        cmd.Parameters.Add("@WorksiteID", SqlDbType.VarChar).Value = WorksiteID

        Try
            conn.Open()
            cmd.ExecuteScalar()
            conn.Close()
        Catch exp As Exception
            Response.Write(exp)
            'Server.Transfer("Error.htm")
        End Try

        GridView1.DataBind()

    End Sub

    Protected Sub GridView1_RowDataBound(sender As Object, e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then

            Dim ddlMonitors As DropDownList = CType(e.Row.FindControl("ddlMonitors"), 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 txtTEST As TextBox = DirectCast(ddlMonitors.Parent.FindControl("txtTEST"), TextBox)

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

            Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SYEP2007ConnectionString").ConnectionString)

            Try
                conn.Open()

                Dim DBCmdMon As New SqlCommand("", conn)
                DBCmdMon.CommandText = "SELECT keyMonitorID FROM [Worksites] WHERE keyWorksiteID = @WorksiteID;"
                DBCmdMon.Parameters.Add("@WorksiteID", SqlDbType.VarChar).Value = WorksiteID
                MonitorID = DBCmdMon.ExecuteScalar.ToString
                txtMonitorID.Text = MonitorID 'row.Cells(9).Text = MonitorID

                Dim cmd As New SqlCommand("", conn)
                cmd.CommandText = "SELECT COUNT(1) FROM [Worksites] WHERE keyMonitorID = @MonitorID ; "
                cmd.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
                cmd.Parameters.Add("@WorksiteID", SqlDbType.VarChar).Value = WorksiteID

                'txtTEST.Text = MonitorID ' WorksiteID.ToString
                ddlMonitors.SelectedValue = MonitorID
                Dim count As Integer
                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 IsNULL(City,'') FROM [Monitors] WHERE keyMonitorID = @MonitorID"
                DBCmd2.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = MonitorID
                mCity = DBCmd2.ExecuteScalar
                If IsDBNull(mCity) Then
                    txtCity.Text = ""
                Else
                    txtCity.Text = mCity 'row.Cells(12).Text = mCity
                End If

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

                conn.Close()

            Catch exp As Exception
                Response.Write(exp)
                'Server.Transfer("Error.htm")
            End Try
            If DataBinder.Eval(e.Row.DataItem, "PositionsAvailable").ToString() <> DataBinder.Eval(e.Row.DataItem, "PositionsTaken").ToString() Then
                e.Row.BackColor = System.Drawing.Color.LightYellow
            End If


        End If
    End Sub

    Protected Sub btnJumpToVerification_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnJumpToVerification.Click
        Server.Transfer("admin.aspx?target=Administration")
    End Sub

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

    'Protected Sub ddlMonitors_SelectedIndexChanged(sender As Object, e As EventArgs)
    '    'Casting sender to Dropdown
    '    Dim ddl As DropDownList = TryCast(sender, DropDownList)
    '    'Looping through each Gridview row to find exact Row 
    '    'of the Grid from where the SelectedIndex change event is fired.
    '    For Each row As GridViewRow In GridView1.Rows
    '        'Finding Dropdown control  
    '        Dim ctrl As Control = TryCast(row.FindControl("ddlMonitors"), DropDownList)
    '        If ctrl IsNot Nothing Then
    '            Dim ddl1 As DropDownList = DirectCast(ctrl, DropDownList)
    '            'Comparing ClientID of the dropdown with sender
    '            If ddl.ClientID = ddl1.ClientID Then
    '                'ClientID is match so find the Textbox 
    '                'control bind it with some dropdown data.
    '                Dim txt As TextBox = TryCast(row.FindControl("MonitorID"), TextBox)
    '                txt.Text = ddl1.SelectedValue
    '                Exit For
    '                'Else
    '                '    Dim txt As TextBox = TryCast(row.FindControl("txtCity"), TextBox)
    '                '    txt.Text = "no good"
    '            End If
    '        End If

    '    Next

    'End Sub

End Class

Open in new window


I appreciate your suggestions.  This is a learning experience for me and anything I learn on this application will help me with 90% of my job.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
I would make the item template for any of the montiors a text box (unless your specs say otherwise).  When they go to edit the row, then make it a drop down list via the edit item template.

You're query for the grid could be come:

SELECT  t.[WorksiteZip], t.[WorksiteCity], t.[EmployerName], [WorksiteName], [keyWorksiteID], [Program], [PositionsAvailable], [PositionsTaken] ,
IsNULL(m.City,'') City, Isnull(m.Zip, '') zip,
(SELECT COUNT(1) FROM [Applicants] WHERE keyWorksiteID IN (SELECT keyWorksiteID FROM [Worksites] WHERE keyMonitorID = isnull(t.KeyMonitorID, -1)) AppCount
FROM [JobTitles] t
left join [Monitors] M on t.keyMonitorID  = M.keyMonitorID

and thereby you let SQL do the heavy lifting.
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
I did think of doing it that way at first, but because there are close to 400 Worksites and a pool of about 35 Monitors to be assigned to them all, I wanted staff to be able to do it with as few mouse clicks as possible.  That's why the data updates happen in the SelectedIndexChanged of the DropDownList in the Item template.  Staff doesn't have to select "Edit" then make a change then select "Update" to save the change.  They can just select a Monitor on any Worksite row and see the result of the Count(s) to see if it puts them over any maximum quota of worksites or applicants any one Monitor would be able to handle.

You have a valid point, though.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Megin, your question is very specific and the answer to that is yes and you already got 2 answers. It seems that now we are dwelling in different realms not related to the question. You can post more questions about those issues if you need.
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
Oh, yes.  I'm very sorry.  I'll conclude this.
0
 
LVL 1

Author Closing Comment

by:megnin
Comment Utility
Thank you for that correct answer.  I'm urged to conclude this question, though I was still learning from our dialog.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now