How to edit the data shown in a grid view from a SQL database-CodePudding

I made a search function using Grid View, the program reads the user input and based on that it returns the data that matches from the data base, however it returns the whole line, which includes 2 ID columns which I don't want to show. Sounds like something simple yet I can't seem to find any kind of tutorial on how to do this.

Also, the second column IdCargo (IdProfession, in english), I'd like to translate this data, as in, if a specific ID is supposed to appear I would like to instead show the profession of said employee. I would also like to show the column with "Cargo" name instead of "IdCargo", also instead of "CargaHoraria" I want to show "Carga Horaria".

If anyone knows any kind of guide or tutorial with using GridViews and SQL, that would be extremely helpful for future research as well.

CodePudding user response：

Great. Ok, we don't have to worry to much about the search part- I'll assume you enter some search, with parameters, the result is a data table.

Now, I will STRONG suggest that you consider a listview in place of the grid view.

As for controlling which columns? Well, you can template each column. (and that's why I suggest list view - it is less markup).

However, I don't have too many columns - so a GV is "ok", but as you want more columns, more custom layout - then the LV will be LESS markup.

And another REALLY big advantage of LV, is you can get it to write the markup for you.

Anyway, ok, this is our GV.

VERY important: We have a PK primary key for each row ("ID"). And we of course don't want to show or display that PK ID, but as we all know, a PK is the lifeblood of any data system. So, there is a VERY cool feature in GV - called DataKeys. It allows you to use/have/play the PK row id, but NEVER do you have to expose or display it in the GV. (so not only nice from UI point of view, also VERY nice from a security point of view).

So, say we have this GV layout:

    <div style="width:40%;padding:25px">

              <style> .borderhide input {border:none}</style>


        <asp:GridView ID="GVPeople" runat="server" AutoGenerateColumns="False" 
            DataKeyNames="ID" cssclass="table borderhide">
            <Columns>
                <asp:TemplateField HeaderText="First Name">
                    <ItemTemplate>
                        <asp:TextBox ID="FirstName" runat="server" Text='<%# Eval("FirstName") %>' ></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>

                <asp:TemplateField HeaderText="Last Name">
                    <ItemTemplate>
                        <asp:TextBox ID="LastName" runat="server" Text='<%# Eval("LastName") %>' ></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>

                <asp:TemplateField HeaderText="City">
                    <ItemTemplate>
                        <asp:TextBox ID="City" runat="server" Text='<%# Eval("City") %>' ></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>

                <asp:TemplateField HeaderText="Active" ItemStyle-HorizontalAlign="Center">
                    <ItemTemplate>
                        <asp:CheckBox ID="Active" runat="server"
                            Checked='<%# Eval("Active") %>'/> 
                    </ItemTemplate>
                </asp:TemplateField>

                <asp:TemplateField HeaderText="Hotel ID">
                    <ItemTemplate>
                        <asp:TextBox ID="Hotel_ID" runat="server" Text='<%# Eval("Hotel_ID") %>' ></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>

            </Columns>
        </asp:GridView>

And we will feel this GV with data - MANY more columns exist - but we don't care.

so, my code so far is this:

Dim rstData As New DataTable
Dim rstHotels As New DataTable  ' for combo box
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadGrid()
        ViewState("rstData") = rstData
    Else
        rstData = ViewState("rstData")
    End If

End Sub


Sub LoadGrid()

    rstHotels = MyRst("SELECT ID, HotelName from tblHotels ORDER BY HotelName")
    rstData = MyRst("SELECT * from People Order by FirstName")

    GVPeople.DataSource = rstData
    GVPeople.DataBind()


End Sub

So now we have this:

Ok, so your one part of the question is we obvious don't want to show the Hotel_id, but want to translate it to a description. And of course, if we going to allow edits, then lets convert the Hotel_ID to a combo box (drop down list). And like near most/all combo box, we will store the PK id of the hotel, but of course display the Hotel name for ease of use.

So, in place of hotel_id, we change our markup to this:

<asp:TemplateField HeaderText="Hotel ID">
    <ItemTemplate>
    <asp:DropDownList ID="cboHotel" runat="server"
        DataTextField="HotelName" 
        DataValueField="ID">
    </asp:DropDownList>
    </ItemTemplate>
</asp:TemplateField>

Ok, so now we have to fill setup the combo box. We have TWO tasks:

Fill the combo box with a data source

Set the combo box to the CURRENT selection for each gv row.

For this, we will use the GV row data bound event.

So, our code to fill out the combo will look like this:

So we have this code:

Protected Sub GVPeople_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles GVPeople.RowDataBound

    If e.Row.RowType = DataControlRowType.DataRow Then

        ' get full row of data bind - all columns
        Dim gData As DataRowView = e.Row.DataItem ' NOT A GRID VIEW ROW!!!!!

        ' get combo box
        Dim cboHotels As DropDownList = e.Row.FindControl("cboHotel")
        ' setup cbo data source
        cboHotels.DataSource = rstHotels
        cboHotels.DataBind()
        cboHotels.Items.Insert(0, New ListItem("", ""))   ' add blank (no choice)

        If IsDBNull(gData("Hotel_id")) = False Then
            cboHotels.SelectedValue = gData("Hotel_ID").ToString
        End If

    End If

End Sub

so, now our results are this:

Ok, so that takes care of one of your questions/issues.

Next up is to edit - and this is REALLY cool, and REALLY easy.

Ok, if you look close, I "hide" the borders for the text boxes, but you find now that you can tab around quite much like excel. And a nice free-bee is that when text boxes have focus, they show!!

So lets drop in our button below the grid to save edits. It looks like this when I tab around:

Quite much like magic - you can now tab around - almost like Excel. And you can choose combo box value.

And in above, we dropped in a simple button below the GV like this:

        </asp:GridView>
        <asp:Button ID="cmdSave" runat="server" Text="Save Edits" CssClass="btn" />

Ok, so now the save data button.

We will write one helper routine. There is a BOATLOAD of reasons to split this code into two routines. So the first routine?

It will send the grid values BACK to our table. If you look close, I persisted the GV table data source as rstData.

So this routine sends grid back to table.

Sub GridToTable()

    ' pull GV rows back to table.
    For Each gRow As GridViewRow In GVPeople.Rows

        ' Get database PK value
        Dim PK As Integer = GVPeople.DataKeys(gRow.RowIndex).Item("ID")

        Dim OneDataRow As DataRow = rstData.Select("id = " & PK)(0)

        OneDataRow.Item("FirstName") = CType(gRow.FindControl("FirstName"), TextBox).Text
        OneDataRow.Item("LastName") = CType(gRow.FindControl("LastName"), TextBox).Text
        OneDataRow.Item("City") = CType(gRow.FindControl("City"), TextBox).Text
        OneDataRow.Item("Active") = CType(gRow.FindControl("Active"), CheckBox).Checked

        ' combo box
        Dim cboHotel As DropDownList = gRow.FindControl("cboHotel")
        If cboHotel.Text = "" Then
            OneDataRow("Hotel_ID") = DBNull.Value
        Else
            OneDataRow("Hotel_ID") = cboHotel.SelectedItem.Value
        End If

    Next

End Sub

Ok, so now all we have to do is send the rstData table (and get this: this will handle NEW rows, or edits!!!!).

so, now our save button code looks like this:

Protected Sub cmdSave_Click(sender As Object, e As EventArgs) Handles cmdSave.Click

    GridToTable()

    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand("SELECT * from People where ID = 0", conn)

            Dim da As New SqlDataAdapter(cmdSQL)
            Dim daC As New SqlCommandBuilder(da)

            conn.Open()
            da.Update(rstData)

        End Using
    End Using

End Sub

So note how we send the WHOLE grid back to the database, and all changes in ONE shot.

Last but not least:

I used a helper routine to get a data table (became REAL fast typing that kind of code over and over, so I have this and I made it global to the whole application:

Public Function MyRst(strSQL As String) As DataTable

    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
            rstData.TableName = strSQL
        End Using
    End Using

    Return rstData
End Function

NOTE very carefull, I also STUFF the sql statement into the rst.Table name. Table name not really used, but now since I persisted the SQL for that table?

Then in fact this line

    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand("SELECT * from People where ID = 0", conn)

becomes:

    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand(rstData.TableName, conn)

What this means is that if/when I have say a child master, or multiple tables of data to edit on a page? I use a dataset (a colleciton of tables), adn have ONE routien to send all tables and all edits back to the database in one shot/routine. We don't have more then one table of data to edit, but this explains why I shove in the SQL statement into the data table "table" name, since as you can see, then we don't even have to re-type the sql used.

NOTE FYI: That sql statement I used of:

SELECT * from People WHERE ID = 0

Was NOT a type-o. I used that to allow sqlCommandBuilder to do all the dirty work of wiring up and creating the sql insert and update steaments for me.