Solved

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

Posted on 2013-02-05
23
566 Views
Last Modified: 2013-05-01
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.
0
Comment
Question by:megnin
  • 13
  • 9
23 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 38855125
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.
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 100 total points
ID: 38855126
0
 
LVL 1

Author Comment

by:megnin
ID: 38855421
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:  http://www.experts-exchange.com/Programming/Languages/.NET/Q_28013302.html
0
 
LVL 1

Author Comment

by:megnin
ID: 38855482
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.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 38855926
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.
0
 
LVL 1

Author Comment

by:megnin
ID: 38855952
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!
0
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 400 total points
ID: 38856199
Just a couple of things to start:

1)   Instead of doing row.Cells(10) it might be easier to work with the controls themselves:

'Put this after you cast the monitors
dim txtSites as Textbox = DirectCast(ddlMonitors.Parent.FindControl("txtSites"), TextBox)


this way you can use
txtSites.Text = sites.ToString()
 instead of
row.Cells(10).Text

(Now you can freely changing the order of your columns without breaking anything.)

From here to do the update:
http://forums.asp.net/t/1800158.aspx/1

Dim arg As New DataSourceSelectArguments()
 Dim dv As System.Data.DataView = DirectCast(SqlDataSource1.[Select](arg), System.Data.DataView)

Dim drv DataRowView() = dv.FindRows(<YOUR PRIMARY KEY FOR THE ROW HERE>)
if drv.Rows.Count > 0
   Dim dr as DataRow = drv(0).Row
   dr("City") = City
   ' add other fields
else
  ' row not found, this should not be
end if

        gv.DataSource = dv.Table
        gv.DataBind()
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 38856205
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.
0
 
LVL 1

Author Comment

by:megnin
ID: 38856238
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.
0
 
LVL 1

Author Comment

by:megnin
ID: 38856246
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

0
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 400 total points
ID: 38856679
So I guess at this point it's probably worth it to discuss the flow:

You have the database where you're retrieving the information from.
That is downloaded via the SqlDataSource to a DataView which is then used to populate the data.

So let's say you're looking at a page, and then I go and updated the database.  You won't see that change until the next time your DataSource populates the dataview.

The Dataview is what I call the front-end.   The Database would be the backend.

So when I said that it's only updating the front end . . . it's only going to write to the dataview . . . NOT to the database (eg: only the user who is using that page will see the changes).

When I mentioned implementing a save, to me that represents taking the front-end changes and pushing them to the database so that others may see them.

So there's a couple of different ways you can write the code in general:

You always start by getting the data from the database.
From there modifications are made.  
When you make the modifications you can update your database right there and then reload the information (pulling along any changes other users have made), or you can let the changes pile up in the front end and push them all at once later on.  (There are pros and cons to each approach, and it's best to look at business needs and what not)

I realize you're not putting them in edit mode which is fine.  If you wanted to implement the save:

http://blogs.msdn.com/b/mattdotson/archive/2005/11/09/real-world-gridview-bulk-editing.aspx

This article is in C# . . . if you can't read it or want to know how to write it in VB use this site (copy / paste) to convert:
http://www.developerfusion.com/tools/convert/csharp-to-vb/
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 1

Author Comment

by:megnin
ID: 38856751
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.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 38857029
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>
0
 
LVL 1

Author Comment

by:megnin
ID: 38857242
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).
0
 
LVL 1

Author Comment

by:megnin
ID: 38857539
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..."
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 38859522
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
0
 
LVL 1

Author Comment

by:megnin
ID: 38859568
(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.
0
 
LVL 1

Author Comment

by:megnin
ID: 38865241
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

0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 400 total points
ID: 38868074
You can't reference a bound field by name . . . think of it as a read only column.

In the designer it's easy enough to change it to a template field.  

So now updating to the database.

First I would create an "isDirty" hidden checkbox field.

Essentially when you ddl is changed, Check the checkbox.

Then,

add a new save button:

Protected Sub btnSave_Click (sender As Object, e As EventArgs)
   
        Dim con as SqlConnection = new SqlConnection(<conStr>)
        Dim strSql as StringBuilder = new StringBuilder(string.Empty) 'imports System.Text
        Dim cc as CheckBox
        Dim s as string = ""


 For i As Byte = 0 To GridView1.Rows.Count - 1
       cc = DirectCast(GridView1.Rows(i).FindControl("c1"), CheckBox)
                 'other control declarations here using same format

 If cc.Checked Then  
   s = String.Format("update <table> set <col1> = {0}  where myPrimaryKey= {1};  Update <table2> set <col2> = '{2}' where <col2Key> ={3} ",
    txtZip.Text, txtMonitorID.Text, EmployeeName.Text, EmployeeId.Text )
'NOTE THE ABOVE IS JUST A SAMPLE.   Use the '{N}' format for varchars and {I} format for integer/numbers.  Look up string.format to see how this is used if you're not familiar.
 
strSql.Append(s)  
End If

next

'use try catch for any errors.
Dim cmd as SqlCommand(strSql.ToString(), Conn)
Conn.Oen
cmd.ExecuteNonQuery
Conn.Close

end sub
0
 
LVL 1

Author Comment

by:megnin
ID: 38868886
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
0
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 400 total points
ID: 38869000
I want to confirm that you can see the values in the database after you do your selected index change:

(eg: just run a manual query against the DB).

If that's the case.

Add a rowDataBound event to the grid.

e.Row is your new data row.

Use the same logic you use in the DDL selected index changed.
After doing the update to the db, rebind your grid and it'll force the refresh.


http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.rowdatabound.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-3
0
 
LVL 1

Author Comment

by:megnin
ID: 38869040
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.
0
 
LVL 1

Author Closing Comment

by:megnin
ID: 39129527
Thank you so much for all the help. I'm sorry I forgot to close this for so long.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
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.

708 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

17 Experts available now in Live!

Get 1:1 Help Now