David Megnin
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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.
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.
ASKER
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.:
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!
[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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
I'm not sure I know what you mean here:
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.
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.
ASKER
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>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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:Text Box>
(added: Text='<%# Eval("City")%>')
Full text box:
<asp:TextBox ID="txtCity" runat="server" Width="150px" Wrap="false" Text='<%# Eval("City")%>'></asp:Text
ASKER
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).
ASKER
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:
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.
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.
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..."
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
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
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)
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
Did adding the text property help?
Also note you should still change the code so you're not using row.Cells(12).Text = mCity
ASKER
(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
Thank you so much for you help. I really appreciate it.
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
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.
ASKER
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_SelectedIndexC hanged after the modification:
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_SelectedIndexC
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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. 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.
ASKER
Thank you so much for all the help. I'm sorry I forgot to close this for so long.
You don't have to, but it's usually better to:
Dim conn as SqlConnection("<MyConnecti
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("<MyConnecti
Dim cmd as SqlCommand("", conn)
Dim count as integer
cmd.CommandText = "StoredProcName"
cmd.CommandType = CommandType.StoredProcedur
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.