How to insert a new row to a gridview that is already databound to a Sqldatasource programmatically ?

Question

User31862606 posted

.aspx

<asp:Panel ID="PanelGrid" runat="server" Visible="false">
<asp:GridView ID="gvFundingFile" runat="server" AutoGenerateColumns="False"
DataSourceID="SqlselFundingFileTimesheets"
EmptyDataText="No Funding File records for this Funder" ShowFooter="True" FooterStyle-ForeColor="White">
<Columns>
<asp:BoundField DataField="Client" HeaderText="Employer" SortExpression="Client" />
<asp:BoundField DataField="Worker" HeaderText="Employee" SortExpression="Worker" />
<asp:BoundField DataField="CaseMgr" HeaderText="Case Mgr" SortExpression="CaseMgr" />
<asp:BoundField DataField="AuthNumber" HeaderText="Auth Num" SortExpression="AuthNumber" />
<asp:BoundField DataField="MoYr" DataFormatString="{0:d}" HeaderText="Pay Prd" SortExpression="MoYr" />
<asp:TemplateField HeaderText="Pay<br />Rate">
<ItemTemplate>
<asp:Label ID="LblPayRate" runat="server" Text='<%# Bind("PayRate", "{0:n2}") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
</asp:TemplateField>
<asp:BoundField DataField="Funding" HeaderText="Funding" SortExpression="Funding" />
<asp:BoundField DataField="Hipaa" HeaderText="Code" SortExpression="Hipaa" />
<asp:BoundField DataField="ServiceName" HeaderText="Service" SortExpression="ServiceName" />
<asp:TemplateField HeaderText="Hours" SortExpression="Hours">
<ItemTemplate>
<asp:Label ID="LblHours" runat="server" Text='<%# Bind("Hours", "{0:n2}") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterTemplate>
<asp:Label ID="LblTotalHours" runat="server" ></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="O/T" SortExpression="HoursOT">
<ItemTemplate>
<asp:Label ID="LblHoursOT" runat="server" Text='<%# Bind("HoursOT", "{0:n2}") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterTemplate>
<asp:Label ID="LblTotalHoursOT" runat="server" ></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Factor" HeaderText="Factor" SortExpression="Factor"
ItemStyle-HorizontalAlign="Center" >
<ItemStyle HorizontalAlign="Center"></ItemStyle>
</asp:BoundField>
<asp:TemplateField HeaderText="Units" SortExpression="CalcUnits">
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("CalcUnits", "{0:n2}") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterTemplate>
<asp:Label ID="LblTotalUnits" runat="server" ></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Gross" SortExpression="Gross" ItemStyle-HorizontalAlign="Right">
<ItemTemplate>
<asp:Label ID="LblGross" runat="server" Text='<%# Bind("Gross", "{0:c}") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="LblTotalGross" runat="server" ></asp:Label>
</FooterTemplate>
<ItemStyle HorizontalAlign="Right" />
</asp:TemplateField>
<asp:TemplateField HeaderText="FICA_ER" SortExpression="FICA_ER">
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("FICA_ER", "{0:c}") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterTemplate>
<asp:Label ID="LblTotalFICA" runat="server" ></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Medicare" SortExpression="Medicare">
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("Medicare", "{0:c}") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterTemplate>
<asp:Label ID="LblTotalMed" runat="server" ></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Shared<br />Cost" SortExpression="SharedCost">
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("SharedCost", "{0:c}") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterTemplate>
<asp:Label ID="LblTotalShared" runat="server" ></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Mileage" SortExpression="NonTaxCost">
<ItemTemplate>
<asp:Label ID="LblNonTaxCost" runat="server" Text='<%# Bind("NonTaxCost", "{0:c}") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterTemplate>
<asp:Label ID="LblTotalNonTax" runat="server" ></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Subtotal" SortExpression="Subtotal">
<ItemTemplate>
<asp:Label ID="LblSubtotal" runat="server" Text="$0.00"></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterTemplate>
<asp:Label ID="LblTotalSubtotal" runat="server" ></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="MgmtFee">
<ItemTemplate>
<asp:Label ID="LblBillingFee" runat="server" Text="$0.00"></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterTemplate>
<asp:Label ID="LblTotalBillingFee" runat="server" ></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Total<br />Cost">
<ItemTemplate>
<asp:Label ID="LblTotalCost" runat="server" Text="$0.00"></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterTemplate>
<asp:Label ID="LblTotalCostAll" runat="server" ></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:BoundField DataField="MA Agreement" HeaderText="MA Agree" SortExpression="MA Agreement"
ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="CaseNumber" HeaderText="MembID" SortExpression="CaseNumber"
ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="EmployeeNumber" HeaderText="Emp No" SortExpression="EmployeeNumber"
ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="CheckDate" HeaderText="Ck Date" SortExpression="CheckDate" DataFormatString="{0:d}" />
</Columns>
<FooterStyle ForeColor="White"></FooterStyle>
</asp:GridView>

<asp:SqlDataSource ID="SqlselFundingFileTimesheets" runat="server" CancelSelectOnNullParameter="false"
ConnectionString="<%$ ConnectionStrings:MCFICoreConnectionString %>"
SelectCommand="mc_selFundingFileTimesheets" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="Branch" QueryStringField="brn" Type="Int16" />
<asp:ControlParameter ControlID="txtCheckDateStart" DbType="Date" Name="CheckDateStart" PropertyName="Text" />
<asp:ControlParameter ControlID="txtCheckDateEnd" DbType="Date" Name="CheckDateEnd" PropertyName="Text" />
<asp:ControlParameter ControlID="ckIncludeBkgnd" Type="Boolean" Name="BackgroundChecks" PropertyName="Checked" DefaultValue="false" />
<asp:ControlParameter ControlID="ckUpdateBilling" Type="Boolean" Name="BackgroundBilled" PropertyName="Checked" DefaultValue="false" />
</SelectParameters>
</asp:SqlDataSource>
</asp:Panel>

.aspx.vb

Protected Sub gvFundingFile_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvFundingFile.RowDataBound
Dim lbl As Label
If e.Row.RowType = DataControlRowType.DataRow Then
If e.Row.RowState = DataControlRowState.Normal _
Or e.Row.RowState = DataControlRowState.Alternate Then

intRowCount += 1
Dim intClientLinkID As Int32 = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "ClientLinkID"))
Dim intWorkerLinkID As Int32 = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "WorkerLinkID"))
Dim intCheckID As Int32 = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "WorkerLinkID"))
Dim strFunding As String = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Funding"))

dblTotalHours += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Hours"))
dblTotalHoursOT += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "HoursOT"))
dblTotalUnits += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "CalcUnits"))
dblTotalGross += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Gross"))
dblTotalFICA += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "FICA_ER"))
dblTotalMed += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Medicare"))
dblTotalSharedCost += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "SharedCost"))
dblTotalMileage += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "NonTaxCost"))
dblTotalMgmtFee += Convert.ToDouble(txtBillingRate.Text)

Dim dblSubtotal As Double = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Gross"))
dblSubtotal += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "FICA_ER"))
dblSubtotal += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Medicare"))
dblSubtotal -= Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "SharedCost"))
dblSubtotal += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "NonTaxCost"))

lbl = e.Row.FindControl("LblSubtotal")
lbl.Text = dblSubtotal.ToString("c")
Dim dtPeriod As Date = Convert.ToDateTime(DataBinder.Eval(e.Row.DataItem, "MoYr"))

lbl = e.Row.FindControl("LblBillingFee")
If strFunding <> "BkgCk" Then
'not a background check record so figure billing fee
Select Case txtBillingFeeTypeID.Text
Case "1" 'per check
If Request.QueryString("brn") = "20" Then
'Waukesha Aut
If Trim(Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Hipaa"))) = "99499 TS" Then
lbl.Text = Convert.ToDouble(txtBillingRate.Text) * Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "CalcUnits"))
dblSubtotal += Convert.ToDouble(txtBillingRate.Text) * Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "CalcUnits"))
Else
lbl.Text = "$0.00"
End If
Else
If intClientLinkID = intLastClientLinkID And dtPeriod = dtLastPeriod And intCheckID = intLastCheckID Then
'Same employer and period so set bill fee to zero
lbl.Text = "$0.00"
Else
lbl.Text = txtBillingRate.Text
dblSubtotal += Convert.ToDouble(txtBillingRate.Text)

End If
End If

Case "2" 'per month
If intClientLinkID = intLastClientLinkID And Month(dtPeriod) = intLastMonth Then
'Same employer and month so set bill fee to zero
lbl.Text = "$0.00"
Else
Dim intIncludeBilling As Int16 = Convert.ToInt16(DataBinder.Eval(e.Row.DataItem, "IncludeBilling"))
If intIncludeBilling = 1 Then
lbl.Text = Format(txtBillingRate.Text, "Currency")
dblSubtotal += Convert.ToDouble(txtBillingRate.Text)
End If
End If

Case Else 'per payroll
lbl.Text = "$0.00"

End Select
Else
lbl.Text = "$0.00"
lbl = e.Row.FindControl("LblSubtotal")
Dim dblPayRate As Double = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "PayRate"))
lbl.Text = dblPayRate.ToString("c")
dblSubtotal += dblPayRate
End If

lbl = e.Row.FindControl("LblTotalCost")
lbl.Text = dblSubtotal.ToString("c")

intLastClientLinkID = intClientLinkID
intLastMonth = Month(dtPeriod)
dtLastPeriod = dtPeriod
intLastCheckID = intCheckID

End If
ElseIf e.Row.RowType = DataControlRowType.Footer Then
'increase last row # by 2 to allow for 2 line heading
intRowCount += 2
lbl = e.Row.FindControl("LblTotalGross")
lbl.Text = "=SUM(N3:N" & intRowCount.ToString & ")"
lbl = e.Row.FindControl("LblTotalFICA")
lbl.Text = "=SUM(O3:O" & intRowCount.ToString & ")"
lbl = e.Row.FindControl("LblTotalMed")
lbl.Text = "=SUM(P3:P" & intRowCount.ToString & ")"
lbl = e.Row.FindControl("LblTotalShared")
lbl.Text = "=SUM(Q3:Q" & intRowCount.ToString & ")"
lbl = e.Row.FindControl("LblTotalNonTax")
lbl.Text = "=SUM(R3:R" & intRowCount.ToString & ")"
lbl = e.Row.FindControl("LblTotalSubtotal")
lbl.Text = "=SUM(S3:S" & intRowCount.ToString & ")"
lbl = e.Row.FindControl("LblTotalBillingFee")
lbl.Text = "=SUM(T3:T" & intRowCount.ToString & ")"

lbl = e.Row.FindControl("LblTotalCostAll")
lbl.Text = "=SUM(U3:U" & intRowCount.ToString & ")"

LblTotalGross.Text = dblTotalGross.ToString("c")
LblCount.Text = intRowCount.ToString
End If

'Show/Hide MA Agreement column
'e.Row.Cells(20).Visible = ckMAAgreement.Checked
'Show/hide CaseNumber (MembID)
'e.Row.Cells(21).Visible = ckMemberID.Checked

End Sub

Answer 1

User622904130 posted

my post here dessscribes how to add a row to a databound gridview. 

Answer 2

User-1336211562 posted

Hi jeeva,

According to your code and description.I think you can use SqlDataSource.Insert Method.

The following code example demonstrates how to insert data into a database using the SqlDataSource control and a simple Web Forms page. The current data in the Data table is displayed in the GridView control. You can add new records by entering values in the TextBox controls, and then clicking the Insert button. When the Insert button is clicked, the specified values are inserted into the database, and then the GridView is refreshed.

    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" />
                <asp:BoundField DataField="ImageName" HeaderText="ImageName" SortExpression="ImageName" />
                <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
            </Columns>
        </asp:GridView>
        <br />
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="btnAddNewRow" runat="server" Text="Add NewRow" />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DB_3_25ConnectionString %>" SelectCommand="SELECT * FROM [SlideShowTable]"></asp:SqlDataSource>
    </div>
    </form>

code behind:

    Protected Sub btnAddNewRow_Click(sender As Object, e As EventArgs) Handles btnAddNewRow.Click
        SqlDataSource1.InsertCommandType = SqlDataSourceCommandType.Text
        SqlDataSource1.InsertCommand = "INSERT INTO [SlideShowTable](ImageName,[Description]) VALUES(@ImageName,@Description)"
        SqlDataSource1.InsertParameters.Add("ImageName", TextBox1.Text)
        SqlDataSource1.InsertParameters.Add("Description", TextBox2.Text)
        SqlDataSource1.Insert()

        GridView1.DataBind()
    End Sub

More information please refer to:

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert(v=vs.110).aspx

http://www.codeproject.com/Tips/331181/How-to-insert-Data-in-SQL-server-via-SqlDataSource 

Hope it helps.