I have a standard Gridview which is populated from a SQLDataSource. The Gridview will always have 17 rows. Can anyone please give me an example of how to manually insert rows into it at designated row spots? For example, insert a new row into rows 3 and 5.
Thanks
CodePudding user response：
Well, it not at all clear why say inserting at row 5, or at row 8 is important here?
Remember, when we used desktop PC - single user, or say used punch cards?
Then order of data entered DID matter. But, we now don't use punched cards, and thus order of such data does not really matter from a database point of view. I mean, if you have multiple users entering data, and I insert at row 5, then what happens if 3 other uses also did the same. Now that record is in the 8th position. So, introduction of a "order" or some "position" NEEDS some further context here - in other words what is the goal here?
when working with a database, the FIRST rule is that data does NOT have order. And if YOU need some kind of order, say like date of entry, or even say order of new records added, then you the developer needs to design that into your software.
Now, having stated the above? Sure, there are class problems or say UI interface types of issues in which you might have say a order of 5 boxes, and you need to offer the user the ability to re-order those 5 items in ANY order you wish.
Ok, so having stated above?
Well, it not clear how the records were entered in the first place. and it not clear HOW the record that exists in the 5th position is supposed to be the 5th record.
Ok, now regardless of the above?
The trick to adding rows, or deleting rows, or inserting data? Do this at the data level, and NOT at the gridview level. This not only separates out the UI layer, and the data layer? It also saves world poverty and boatloads of complex code.
So, lets assume we have a column called "myorder". And if you don't have this column in the table, then you really quite much need to add it, since as I stated, some data order does not exist by magic, but in fact has to be "designed" and "managed" by YOU THE developer!!!
So, lets take a simple gv like this:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="ID" CssClass="table borderhide" >
<Columns>
<asp:TemplateField HeaderText="First" ><ItemTemplate>
<asp:TextBox ID="txtFirst" runat="server" Text='<%# Eval("FirstName") %>' Width="80px">
</asp:TextBox>
</ItemTemplate></asp:TemplateField>
<asp:TemplateField HeaderText="Last"><ItemTemplate>
<asp:TextBox ID="txtLast" runat="server" Text='<%# Eval("LastName") %>' Width="80px">
</asp:TextBox>
</ItemTemplate></asp:TemplateField>
<asp:TemplateField HeaderText="Hotel"><ItemTemplate>
<asp:TextBox ID="txtHotel" runat="server" Text='<%# Eval("HotelName") %>'></asp:TextBox>
</ItemTemplate></asp:TemplateField>
<asp:TemplateField HeaderText="Description" ><ItemTemplate>
<asp:TextBox ID="txtDescription" runat="server" Text='<%# Eval("Description") %>'
TextMode="MultiLine" Rows="3" Columns="45"
></asp:TextBox>
</ItemTemplate></asp:TemplateField>
<asp:TemplateField HeaderText="Active" ItemStyle-HorizontalAlign="Center" ><ItemTemplate>
<asp:CheckBox ID="chkActive" runat="server" Checked='<%# Eval("Active") %>' />
</ItemTemplate></asp:TemplateField>
</Columns>
</asp:GridView>
Ok, and our code to load the grid. and as noted, we will persit the data source for the grid, since we need to do those inserts.
So, our code to load:
Dim rstData As New DataTable
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
LoadData()
LoadGrid()
Else
rstData = ViewState("rstData")
End If
End Sub
Sub LoadData()
Using conn As New SqlConnection(My.Settings.TEST4)
Dim strSQL = "SELECT * FROM tblHotelsA ORDER BY MyOrder"
Using cmdSQL As New SqlCommand(strSQL, conn)
conn.Open()
rstData.Load(cmdSQL.ExecuteReader)
End Using
End Using
End Sub
Sub LoadGrid()
rstData.DefaultView.Sort = "MyOrder"
GridView1.DataSource = rstData
GridView1.DataBind()
RowCount.Value = rstData.Rows.Count
ViewState("rstData") = rstData
End Sub
and we now have this:
So, now we have that add button. That button will add the row to the grid, and prompt us for the location (what row to insert at).
so, the button looks like this:
<asp:Button ID="cmdSave" runat="server" Text="Save Changes" style="float:left" CssClass="btn"/>
<asp:Button ID="cmdAddRow" runat="server" Text=" Add New" style="float:right" CssClass="btn"
OnClientClick="return askwhatrow(this);"
/>
<asp:HiddenField ID="WhatRow" runat="server" ClientIDMode="Static"/>
<asp:HiddenField ID="RowCount" runat="server" ClientIDMode="Static"/>
<script>
function askwhatrow(btn) {
MyRowCount = $('#RowCount').val()
strMsg = "There are row 1 to " MyRowCount "\n"
"What row to insert new record?"
strAns = prompt(strMsg)
if (strAns === null) {
return false
}
if ((strAns < 1) || (strAns > MyRowCount) ) {
alert("only 1 to " MyRowCount " is allowed")
return false
}
// ok, set the row insert location, and run our server side buttion
$('#WhatRow').val(strAns)
return true
}
</script>
So, when we click on add row, we get this prompt:
I of course entered say 2 (or your 5 for your example).
the js code will prompt the user, but of couse if the user hits cancel, then the button code (code behind server) will not run. The code to add the row of data now looks like this:
Protected Sub cmdAddRow_Click(sender As Object, e As EventArgs) Handles cmdAddRow.Click
' add new row to grid at location choosen by user:
GridToTable() ' save any possbile edits by user
Dim InsertLocation As Integer = WhatRow.Value
For Each OneRow In rstData.Rows
If Int(OneRow("MyOrder")) >= InsertLocation Then
OneRow("MyOrder") = 1
End If
Next
' setup new row - some defaults
Dim NewRow As DataRow = rstData.NewRow
NewRow("MyOrder") = InsertLocation
NewRow("Active") = False
rstData.Rows.Add(NewRow)
LoadGrid()
End Sub
Again, note how VERY easy it is to add that row of data. That's because we add the row into the table, and then simple re-bind the grid to display that new record. In other words, don't try to manipulate the gv, but ONLY manipulate the data!!! (that way we save world poverty and you starving from having to write too much code).
So, after we do the above, we see this:
So, with above grid? You can tab around (almost like excel). You can edit any row, make any changes you want.
We then have that ONE save button. All that does is send gv data back to the table, AND THEN IN ONE operation sends the data back to the database.
The code looks like:
Protected Sub cmdSave_Click(sender As Object, e As EventArgs) Handles cmdSave.Click
GridToTable()
Using conn As New SqlConnection(My.Settings.TEST4)
Dim strSQL = "SELECT * FROM tblHotelsA"
Using cmdSQL As New SqlCommand(strSQL, conn)
conn.Open()
Dim da As New SqlDataAdapter(cmdSQL)
Dim daU As New SqlCommandBuilder(da)
da.Update(rstData)
End Using
End Using
End Sub
Sub GridToTable()
' send all data from gv to local table
For Each gvRow As GridViewRow In GridView1.Rows
Dim pkID As Integer = GridView1.DataKeys(gvRow.RowIndex).Item("ID")
Dim OneRow As DataRow = rstData.Select("ID = " & pkID).FirstOrDefault
OneRow("FirstName") = CType(gvRow.FindControl("txtFirst"), TextBox).Text
OneRow("LastName") = CType(gvRow.FindControl("txtLast"), TextBox).Text
OneRow("HotelName") = CType(gvRow.FindControl("txtHotel"), TextBox).Text
OneRow("Description") = CType(gvRow.FindControl("txtDescription"), TextBox).Text
OneRow("Active") = CType(gvRow.FindControl("chkActive"), CheckBox).Checked
Next
End Sub
So in above, we send all edits, all additions (and if you have or add a delete button to each row, then even deletions are ALL SEND back to the database with the above simple few lines of code. As noted, this was and is possible since we persisted the rstData that drives the GV.
and really nice? Well, for 30 years, every user on the planet who used Excel, or used any accounting system, or any other computer software? They can bounce around in that grid, make changes. And then with one simple save button send the whole she-bang back to the database.