Writing a WebPage in VB 4.8. A GridView has parameters, which outlet to new Data Fields in a DetailsView

Question

The WebPage is set up for Sales and I need to reorganise the ID index i am given (In the GridView) I have chosen a procedure that's using scripted DataTable to fill a DetailsView.

Sub SortOrder_Item(ByVal Sender As Object, ByVal e As GridViewCommandEventArgs) Handles SortOrder.RowCommand
    Dim workTable As New System.Data.DataTable
            
    Dim DTabRow As System.Data.DataRow = workTable.NewRow

    Dim index As Integer = Convert.ToInt32(e.CommandArgument)           

    Dim Din As GridViewRow = SortOrder.Rows(index)
    if e.CommandName = "Add" Then
    Dim workCol As System.Data.DataColumn = workTable.Columns.Add("ID", Type.GetType("System.Int32"))
        workCol.AllowDBNull = false
        workCol.Unique = false
        Dim _Item_Name As String = Server.HTMLDecode(Din.Cells(1).Text)
        Dim _Curr_Price As Int32 = Server.HTMLDecode(Din.Cells(4).Text)
        Dim _REFNo As String = Server.HTMLDecode(Din.Cells(5).Text)
        Dim Dout As Int32 = index +1

        workCol = New System.Data.DataColumn()
        workCol.DataType = System.Type.GetType("System.String") 
        workCol.AllowDBNull = True
        workCol.Caption = ""
        workCol.ColumnName = "ItemName" & Dout 
        workCol.DefaultValue = ""
        workTable.Columns.Add(workCol)

        workCol = New System.Data.DataColumn()
        workCol.DataType = System.Type.GetType("System.Double") 
        workCol.AllowDBNull = True 
        workCol.Caption = ""
        workCol.ColumnName = "Curr_Price" & Dout
        workCol.DefaultValue = 0
        workTable.Columns.Add(workCol)
                        
        workCol = New System.Data.DataColumn()
            workCol.DataType = System.Type.GetType("System.String") 
            workCol.AllowDBNull = True 
            workCol.Caption = ""
            workCol.ColumnName = "REFNo" & Dout
            workCol.DefaultValue = ""
            workTable.Columns.Add(workCol)
        
        workCol = New System.Data.DataColumn()
            DTabRow("ID") = Dout
            DTabRow("ItemName" & Dout) = _Item_Name
            DTabRow("Curr_Price" & Dout) = _Curr_Price
            DTabRow("REFNo" & Dout) = _REFNo
        
            workTable.Rows.Add(DTabRow)
            DetailInsert.DataSource = workTable
            DetailInsert.DataBind()
            SortOrder.Enabled = False
        Go_NET.Visible = True
        else
            Go_NET.Visible = False
        end if
End Sub

            
  Sub CustomerDetailsView_ItemUpdating(ByVal sender As Object, ByVal e As DetailsViewUpdateEventArgs)


  End Sub

  Sub CustomerDetailsView_ModeChanging(ByVal sender As Object, ByVal e As DetailsViewModeEventArgs)
  
    If e.CancelingEdit Then
      
      ' The user canceled the update operation.
      ' Clear the error message label.
      MesX1.Text = ""
    End If
    
  End Sub

And so, the Edit Command is not working and will not show (Update/Cancel) Control. If I tried to directly assign a DataSourceID to my DetailsView Submission form I'm sure it works but the whole Database needs its index's organized.

Answer 1

Gee, you filled out the GV, so why not just grab the pk ID of that row, and re-pull that one row from the database?

It certainly not going to really for all practical purposes increase the server load to re-pull that one record. But, while it not a performance issue, it MOST certainly is going to save you a BOATLOAD of hand coding.

So, I suggest you don't try and "hand code" the creating of the one row from the GV. You only going to write all that code, but then if you add, or change the columns, you right back to hand coding the one row data. Worse yet, such code really can't be re-used for other pages.

I suggest that you simple re-pull the data and get a nice working row.

Over time, I suggest you build a "helper" routine that "automatic" puts the columns from the one row into the controls on the form. That way, then you don't have to hand code the loading up of controls each time.

And then write ONE routine to do the reverse (take values from the controls back to the data row). Then one more routine to write that row out to the database.

With these 3 simple routines, then you can now use that code for all forms and all of your work (and not re-write what amounts to the same code over and over).

And you "could" use the details' view, but you still have to type in eval("some column name") anyway. So, might as well type in that code in vb - its about the same efforts.

So, I suggest this approach:

our grid view, and drop in a plane jane regular edit button (don't' bother with the GridView event model - it not all that great for editing the VERY instant you start to use custom code for this).

As noted, a details view will not help much either. (Since we not trying to use the built-in wizards, and they are not that flexible).

So, so our grid view:

<div id="MyGridArea" runat="server">
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        DataKeyNames="ID"
        CssClass="table table-hover" Width="60em" GridLines="None"
        ShowHeaderWhenEmpty="true">
        <Columns>
            <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
            <asp:BoundField DataField="LastName" HeaderText="LastName" />
            <asp:BoundField DataField="City" HeaderText="City" />
            <asp:BoundField DataField="HotelName" HeaderText="HotelName" />
            <asp:BoundField DataField="Description" HeaderText="Description" />
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Button ID="cmdEdit" runat="server" Text="Edit" CssClass="btn myshadow"
                        OnClick="cmdEdit_Click" />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
</div>

Note how we do NOT expose nor include the pk "ID" in that grid. We set/use the Datakeys setting (a VERY nice feature, since then we don't have to try and "hide" the pk row, and better yet its secure - and 100% server side managed for us).

Ok, so now our code to load the grid can be this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadGrid()
    End If

End Sub

Sub LoadGrid()
    Dim strSQL = "SELECT * FROM tblHotelsA ORDER BY HotelName"

    GridView1.DataSource = Myrst(strSQL)
    GridView1.DataBind()

End Sub

And we now have/see this:

Ok, so now our edit button.

That markup is this:

            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Button ID="cmdEdit" runat="server" Text="Edit" CssClass="btn myshadow"
                        OnClick="cmdEdit_Click" />
                </ItemTemplate>
            </asp:TemplateField>

But note VERY close in above. Note the click event!!!

But, we can't just double click on that button to create a click event, since it is nested inside of the GV. So this BIG HUGE NICE tip?

You add the click event while in markup, and type in

OnClick=

The instant you hit "=", then note close, you get a popup to add the click event like this:

So, we choose to create new event. Don't seem like much occurred, and our button markup become this:

<asp:Button ID="cmdEdit" runat="server" Text="Edit" CssClass="btn myshadow"
   OnClick="cmdEdit_Click" />

Now, if we flip to code behind, we have a button click for that button - but one inside of the GV.

So, our button needs to:

Get the current row id, and the row PK for the database row

We then transfer that data to our "div area", and then hide the GV,
display the "edit div".

The above is "nice" since now we again writing plane jane simple markup, and our buttons to cancel, save, or even delete are ALSO plane jane code behind.

So, only part now we need is our "edit div" area. As noted, I don't see much in the way of using details view here. You can use a details view, but I don't think it helps much at all.

Ok, so our edit div looks like this:

(just plane jane markup in that "div" with controls, check box etc. to edit the one row).

    <div id="EditRecord" runat="server" style="float: left; display: none" >

        <br />
        <div style="float: left" class="iForm">

            <label>HotelName</label>
            <asp:TextBox ID="txtHotel" runat="server" Width="280">
            </asp:TextBox>
            <br />
            <label>First Name</label>
            <asp:TextBox ID="tFN" runat="server" Width="140"></asp:TextBox>
            <br />
            <label>Last Name</label>
            <asp:TextBox ID="tLN" runat="server" Width="140"></asp:TextBox>
            <br />

            <label>City</label>
            <asp:TextBox ID="tCity" runat="server" Width="140"></asp:TextBox>

            <br />
            <label>Province</label><asp:TextBox ID="tProvince" runat="server" Width="75"></asp:TextBox>
        </div>

        <div style="float: left; margin-left: 20px" class="iForm">
            <div style="float: left">
                <label>Description</label>
                <br />
                <asp:TextBox ID="txtNotes" runat="server" Width="260px" TextMode="MultiLine"
                    Height="100px" ></asp:TextBox>
            </div>

            <div style="float: left; margin-left: 14px">
                <label>Hotel Options</label>
                <br />
                <asp:CheckBox ID="chkSmoking" runat="server" Text="Smoking" />
                <br />
                <asp:CheckBox ID="chkBalcony" runat="server" Text="Balcony" />
                <br />
                <asp:CheckBox ID="chkBags" runat="server" Text="Has Baggage" />
                <br />
            </div>

            <div style="clear: both">
            </div>

            <asp:CheckBox ID="chkActive" Text=" Active"
                runat="server" TextAlign="Right" Width="171px" />

        </div>
        <div style="clear: both"></div>
        <hr />

        <button id="cmdSave" runat="server" class="btn myshadow" type="button"
            onserverclick="cmdSave_ServerClick" >
            <span aria-hidden="true" class="glyphicon glyphicon-floppy-saved">Save</span>
        </button>

        <button id="cmdCancel" runat="server" class="btn myshadow" style="margin-left: 15px"
            type="button"
            onserverclick="cmdCancel_ServerClick" >
            <span aria-hidden="true" class="glyphicon glyphicon-arrow-left">Back/Cancel</span>
        </button>

        <button id="cmdDelete" runat="server" class="btn myshadow" style="margin-left: 15px"
            type="button"
            onserverclick="cmdDelete_ServerClick"
            onclick="if (!confirm('Delete this record?')) {return false};">
            <span aria-hidden="true" class="glyphicon glyphicon-trash">Delete</span>
        </button>
        <br />
        <br />
    </div>

</div>

as a FYI:

I used html "buttons" in above. I COULD have used plane jane asp.net buttons, but I wanted the cute looking bootstrap icons. I should point out that after bootstrap 4, they dropped the including of the bootstap icons, but if you wish, use plane jane asp.net buttons for those 3 buttons if you have any trouble with using html "buttons". I did use plane jane vb code behind for those buttons, and note how I have used onserverclick - just a wee bit different syntax in the markup, but code behind is really the same as any button click code on a typical page.

Ok, so now we need our edit button code.

So, this is where we now have to type in those controls. (as noted, for future, we can write a routine that does this over and over for any markup, but for now, baby steps, just some baby steps).

So, we have this code on the edit button.

Protected Sub cmdEdit_Click(sender As Object, e As EventArgs)

    Dim btn As Button = sender
    Dim gRow As GridViewRow = btn.NamingContainer

    Debug.Print("Row index click = " & gRow.RowIndex)

    ' get data base key
    Dim intPK As Integer = GridView1.DataKeys(gRow.RowIndex).Item("ID")
    Dim strSQL As String = "SELECT * FROM tblHotelsA WHERE ID = " & intPK

    Dim rstData As DataTable = Myrst(strSQL)
    ' load up our edit div area

    With rstData.Rows(0)

        txtHotel.Text = .Item("HotelName")
        tFN.Text = .Item("FirstName")
        tLN.Text = .Item("LastName")
        tCity.Text = .Item("City")
        tProvince.Text = .Item("Province")
        txtNotes.Text = .Item("Description")
        chkActive.Checked = .Item("Active")
        chkBalcony.Checked = .Item("Balcony")
        chkSmoking.Checked = .Item("Smoking")
    End With

    ViewState("PK") = intPK     ' same our PK for later
    ShowGrid(False)         ' hide GV, show edit area

End Sub

So, really, shoving a bunch of eval("column name") in markup, or typing in the text box as per above = about the same amount of effort on your part.

So, really, now all we need is a save button to write the data back to the table.

That code is really quite much the reverse of the edit button load code.

So, this code does the trick:

Protected Sub cmdSave_ServerClick(sender As Object, e As EventArgs)

    Dim intPK As Integer = ViewState("PK")
    Dim strSQL As String = "SELECT * FROM tblHotelsA WHERE ID = " & intPK

    Using conn = New SqlConnection(My.Settings.TEST4)
        Using cmdSQL = New SqlCommand(strSQL, conn)
            conn.Open()
            Dim da As New SqlDataAdapter(cmdSQL)
            Dim daU As New SqlCommandBuilder(da)

            Dim rstData As New DataTable
            rstData.Load(cmdSQL.ExecuteReader)

            With rstData.Rows(0)

                .Item("HotelName") = txtHotel.Text
                .Item("FirstName") = tFN.Text
                .Item("LastName") = tLN.Text
                .Item("City") = tCity.Text
                .Item("Province") = tProvince.Text
                .Item("Description") = txtNotes.Text
                .Item("Active") = chkActive.Checked
                .Item("Balcony") = chkBalcony.Checked
                .Item("Smoking") = chkSmoking.Checked
            End With

            da.Update(rstData)

        End Using
    End Using

    LoadGrid()   ' refrsh grid to show any new edits

    ShowGrid(True)

End Sub

So, that quite much gives this effect:

So, it not a lot of code, but I think more imporant is all the code is quite plane jane - nothing fancy.

The other "helper routine" I used was this one, and it simple returns a table for a given SQL query:

Public Function Myrst(strSQL As String) As DataTable

    Dim rstData As New DataTable
    Using mycon As New SqlConnection(My.Settings.TEST4)

        Using cmdSQL As New SqlCommand(strSQL, mycon)
            mycon.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using

    End Using

    Return rstData

End Function

Make GOOD close note of how I used the simple button click to get the current gv row index and PK. That code works for a repeater, listview, gridview etc, and it allows you to dispense with the built in grid/listview/repeater events, and use a simple button click.