Search + Sum + GridView + Edit

[met0555]

Hi,

I would like to have your opinion on how how i should do this.
I would like to run a search and show the result on the grid view from my database.
The search have many criteria , means user can search by first name and last name and amount ....
Then i would like the user to be able to be able to edit and delete the records.
And finally i would like to show a row showing the sum of some fields.

Can you please tell me how i should proceed, and which system i don't have to use?

Thanks

[gep13]

Hey,

From the ground up, you are going to need something like the following:

1) Create a connection to your database
2) Create a command object, and set the CommandText property to the query or stored procedure that you want to use
3) Create parameters for each of the "where" clauses that the user can choose from
4) Execute the command
5) Prepare the results, and set the result to the DataSource Property of the GridView
6) Edit the GridView to include a sum of the necessary fields:
http://msdn.microsoft.com/en-us/library/ms972833.aspx

Gary

[met0555]

Hi,

For the edit and delete button, how should i include that?

thanks

[gep13]

Hey,

Handle the RowDeleting and RowEditing events of the GridView, and do the necessary query on the database to update the underlying table.

Gary

[met0555]

Ok, I have wrote the code "it's a bit big to post" but if required i will, and whenever i turn on the edit mode, and then click on the edit button i get the following error

The GridView 'GridView1' fired event RowEditing which wasn't handled.

thanks

[gep13]

Hey,

That error would suggest that you have not implemented the RowEditing event. Have you?

Gary

[met0555]

Hi,

Ok, I added the the rowediting event, but it's empty, what code should i write in it?

thanks

[met0555]

I tried to use the following code under the rowediting event, but nothing it's happening

GridView1.EditIndex = e.NewEditIndex
        'Bind data to the GridView control.
        GridView1.DataBind()

[gep13]

Hey,

The code necessary to edit the underlying table.

i.e. a query that will take all the changes to the values, and update the database table.

Gary

[gep13]

I tried to use the following code under the rowediting event, but nothing it's happening

GridView1.EditIndex = e.NewEditIndex
        'Bind data to the GridView control.
        GridView1.DataBind()

What exactly are you expecting that to do?

You need to take a step back and understand exactly what you are trying to achieve before writing any code!

Gary

[met0555]

I guess what i need now, is to be in the edit mode when the button is clicked. :S

[mendhak]

Set the GridView's AutoGenerateEditButton property to true. That should give you a button. Click on it, it goes into Edit Mode.

[met0555]

Hi,
It just redirect be to a blank page.

thanks

[met0555]

ok, here is my code

Dim sqlstr As String
        sqlstr = "select * from trackmain where " + pwhereClause + "ORDER BY datee DESC"
        Dim dt As DataTable
        Dim datatable1 As DataTable
 
        Dim command As New DbCommand(sqlstr, CommandType.Text, Nothing)
 
 
        datatable1 = command.ExecuteDataTable()
 
 
        dt = GetCustDT()
        If (datatable1.Rows.Count > 0) Then
 
 
            Dim str As String()
 
            str = (datatable1.Rows.Count / _rowCount).ToString().Split(".")
            If (str.Count > 1) Then
 
                hfPageCount.Value = Convert.ToInt32(str(0)) + 1
                hflastpagecount.Value = datatable1.Rows.Count Mod _rowCount
            Else
                hfPageCount.Value = Convert.ToInt32(str(0))
                hflastpagecount.Value = datatable1.Rows.Count Mod _rowCount
            End If
 
 
 
 
 
 
            If (hfCurrentPage.Value = 1) Then
                hfStartIndex.Value = 0
                If (hfPageCount.Value = hfCurrentPage.Value) Then
                    hfEndIndex.Value = hflastpagecount.Value - 1
                    btnNext.Enabled = False
                    btnPrevious.Enabled = False
                Else
                    hfEndIndex.Value = _rowCount - 1
                    btnNext.Enabled = True
                    btnPrevious.Enabled = False
                End If
 
            Else
                If (Convert.ToInt32(hfPageCount.Value) > Convert.ToInt32(hfCurrentPage.Value)) Then
                    hfStartIndex.Value = (hfCurrentPage.Value - 1) * _rowCount
                    hfEndIndex.Value = hfStartIndex.Value + _rowCount - 1
                    btnNext.Enabled = True
                    btnPrevious.Enabled = True
                ElseIf (Convert.ToInt32(hfPageCount.Value) = Convert.ToInt32(hfCurrentPage.Value)) Then
                    hfStartIndex.Value = (hfCurrentPage.Value - 1) * _rowCount
                    If (Convert.ToInt32(hflastpagecount.Value) = 0) Then
                        hfEndIndex.Value = Convert.ToInt32(hfStartIndex.Value) + _rowCount - 1
                    Else
                        hfEndIndex.Value = Convert.ToInt32(hfStartIndex.Value) + Convert.ToInt32(hflastpagecount.Value - 1)
                    End If
 
                    btnNext.Enabled = False
                    btnPrevious.Enabled = True
                End If
            End If
 
 
 
 
 
            Dim i As Integer
            Dim inhouse As Decimal = 0
            Dim payouts As Decimal = 0
            Dim expenses As Decimal = 0
            Dim net As Decimal = 0
 
 
 
 
            For i = Convert.ToInt32(hfStartIndex.Value) To Convert.ToInt32(hfEndIndex.Value) 'datatable1.Rows.Count - 1
                dt.Rows.Add(New String() {datatable1.Rows(i)("ID").ToString(), datatable1.Rows(i)("dayy").ToString(), datatable1.Rows(i)("datee").ToString(), datatable1.Rows(i)("Client").ToString(), datatable1.Rows(i)("fromto").ToString(), datatable1.Rows(i)("collect").ToString(), datatable1.Rows(i)("via").ToString(), datatable1.Rows(i)("gross").ToString(), datatable1.Rows(i)("inhouse").ToString(), datatable1.Rows(i)("payouts").ToString(), datatable1.Rows(i)("expenses").ToString(), datatable1.Rows(i)("Net").ToString(), datatable1.Rows(i)("reconciliated").ToString(), datatable1.Rows(i)("farm").ToString()})
 
                SubTotal = SubTotal + (Convert.ToDecimal(datatable1.Rows(i)("gross").ToString)) 'gross
                inhouse = inhouse + (Convert.ToDecimal(datatable1.Rows(i)("inhouse").ToString))
                payouts = payouts + (Convert.ToDecimal(datatable1.Rows(i)("payouts").ToString))
                expenses = expenses + (Convert.ToDecimal(datatable1.Rows(i)("expenses").ToString))
                net = net + (Convert.ToDecimal(datatable1.Rows(i)("net").ToString))
 
            Next
 
            If (btnType = "next") Then
                hfTotalGross.Value = Convert.ToDecimal(hfTotalGross.Value) + Convert.ToDecimal(SubTotal)
 
                hfTotalInhouse.Value = Convert.ToDecimal(hfTotalInhouse.Value) + Convert.ToDecimal(inhouse)
                hfTotalPayouts.Value = Convert.ToDecimal(hfTotalPayouts.Value) + Convert.ToDecimal(payouts)
 
                hfTotalexpense.Value = Convert.ToDecimal(hfTotalexpense.Value) + Convert.ToDecimal(expenses)
                hfTotalNet.Value = Convert.ToDecimal(hfTotalNet.Value) + Convert.ToDecimal(net)
            ElseIf (btnType = "pre") Then
 
 
 
                hfTotalGross.Value = Convert.ToDecimal(hfTotalGross.Value) - Convert.ToDecimal(hfSubGross.Value)
 
                hfTotalInhouse.Value = Convert.ToDecimal(hfTotalInhouse.Value) - Convert.ToDecimal(hfSubInhouse.Value)
                hfTotalPayouts.Value = Convert.ToDecimal(hfTotalPayouts.Value) - Convert.ToDecimal(hfSubPayouts.Value)
 
                hfTotalexpense.Value = Convert.ToDecimal(hfTotalexpense.Value) - Convert.ToDecimal(hfSubexpense.Value)
                hfTotalNet.Value = Convert.ToDecimal(hfTotalNet.Value) - Convert.ToDecimal(hfSubNet.Value)
            Else
                hfTotalGross.Value = Convert.ToDecimal(SubTotal)
                hfTotalInhouse.Value = inhouse
                hfTotalPayouts.Value = payouts
 
                hfTotalexpense.Value = expenses
                hfTotalNet.Value = net
                btnPrevious.Enabled = False
            End If
            'hfSubVia.Value = via
            hfSubGross.Value = Convert.ToDecimal(SubTotal)
            hfSubInhouse.Value = inhouse
            hfSubPayouts.Value = payouts
            hfSubexpense.Value = expenses
            hfSubNet.Value = net
 
            dt.Rows.Add(New String() {"", "", "", "", "", "Sub Total", "", Convert.ToString(hfSubGross.Value), Convert.ToString(inhouse), Convert.ToString(payouts), Convert.ToString(expenses), Convert.ToString(net), "", ""})
            dt.Rows.Add(New String() {"", "", "", "", "", "Total", "", Convert.ToString(hfTotalGross.Value), Convert.ToString(hfTotalInhouse.Value), Convert.ToString(hfTotalPayouts.Value), Convert.ToString(hfTotalexpense.Value), Convert.ToString(hfTotalNet.Value)})
            ' GridView1.AutoGenerateEditButton = True
            GridView1.DataSource = dt
            GridView1.DataBind()
        Else
            lblmsg.Text = "no result found"
            btnNext.Enabled = False
            btnPrevious.Enabled = False
            btnPrint.Enabled = False
        End If
 
    End Sub
    Public Function GetCustDT() As DataTable
        Dim dt As New DataTable
        dt.Columns.Add("Id", GetType(String))
        dt.Columns.Add("Day", GetType(String))
        dt.Columns.Add("Date", GetType(String))
        dt.Columns.Add("Client", GetType(String))
        dt.Columns.Add("From-To", GetType(String))
        dt.Columns.Add("Collect", GetType(String))
        dt.Columns.Add("Via", GetType(String))
        dt.Columns.Add("Gross", GetType(String))
        dt.Columns.Add("In-House", GetType(String))
        dt.Columns.Add("Payouts", GetType(String))
        dt.Columns.Add("Expenses", GetType(String))
        dt.Columns.Add("Net", GetType(String))
        dt.Columns.Add("Status", GetType(String))
        dt.Columns.Add("Farm", GetType(String))
        Return dt
    End Function
 
    Protected Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        hfCurrentPage.Value = hfCurrentPage.Value - 1
        btnType = "pre"
        setQueryValue()
        getSearchResult(txtDate, txtclient, txtfarm, txtcollect, txtvia, txtgross, txtgross2, txtNet, txtNet2, txtexpenses, txtexpenses2, txtDay, txtdate1, txtdate2)
 
    End Sub
 
    Protected Sub btnnext_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        hfCurrentPage.Value = hfCurrentPage.Value + 1
        btnType = "next"
        setQueryValue()
        getSearchResult(txtDate, txtclient, txtfarm, txtcollect, txtvia, txtgross, txtgross2, txtNet, txtNet2, txtexpenses, txtexpenses2, txtDay, txtdate1, txtdate2)
 
    End Sub
 
 
   
  
    Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs)
        '  Dim GridView1 As GridView
 
        Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
        GridView1.EditIndex = e.NewEditIndex
      
        BindData()
    End Sub
 
    Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs)
        Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
 
        GridView1.PageIndex = e.NewPageIndex
        'Bind data to the GridView control.
          BindData()
 
    End Sub
    Private Sub BindData()
        Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
 
        GridView1.DataSource = Session("trackmain")
        GridView1.DataBind()
    End Sub
 
    Protected Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs)
        Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
 
        GridView1.EditIndex = -1
 
    End Sub
 
    Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs)
        Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
 
        Dim dt = CType(Session("trackmain"), DataTable)
 
        'Update the values.
        Dim row = GridView1.Rows(e.RowIndex)
        dt.Rows(row.DataItemIndex)("Id") = (CType((row.Cells(0).Controls(0)), TextBox)).Text
       
 
        'Reset the edit index.
        GridView1.EditIndex = -1
 
 
    End Sub
End Class

[mendhak]

Does line 177 get hit when you click on the Edit button?

[met0555]

Hi,

Yes it hits

[met0555]

any help please.

[mendhak]

OK, so you're saying line 177 is hit and therefore the BindData() method is called. But then it sends you to a blank page? Step through the code in its entirety. At what point in the code does it suddenly give you a blank page?

[met0555]

Hi,

I changed some line of code.... and using this code, i'm able to see the edit mode gridview but i have to click twice the button to go to the edit mode and the edit textboxes are blank.
Still even when it's blank i tried to edit rows value i get the following error

Object reference not set to an instance of an object.

at this line

dt.Rows(row.DataItemIndex)("client") = (CType((row.Cells(4).Controls(0)), TextBox)).Text

current code

Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs)
        '  Dim GridView1 As GridView
 
        Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
        GridView1.EditIndex = e.NewEditIndex
 
 
    End Sub
 
    Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs)
        Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
 
        GridView1.PageIndex = e.NewPageIndex
       
    End Sub
    Private Sub BindData()
        Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
 
        GridView1.DataSource = Session("trackmain")
        GridView1.DataBind()
    End Sub
 
    Protected Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs)
        Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
 
        GridView1.EditIndex = -1
 
    End Sub
 
    Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs)
        Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
 
        Dim dt = CType(Session("trackmain"), DataTable)
 
        'Update the values.
        Dim row = GridView1.Rows(e.RowIndex)
        dt.Rows(row.DataItemIndex)("client") = (CType((row.Cells(4).Controls(0)), TextBox)).Text
 
        GridView1.EditIndex = -1
 
 
    End Sub
End Class

thanks

[mendhak]

Set a breakpoint on #37 and do a quickwatch on row.Cells(4). (Highlight it and press Ctrl+Alt+Q). It's likely that it shows you null. If it doesn't, explore the Controls collection. It's also possible that the textbox is Controls(1) or that the textbox is in a child of Controls(0).

[mendhak]

You can also show us your grid markup.

[met0555]

The value is null, if it helps, the gridview is under a login control

<asp:GridView ID="GridView1" runat="server"
                      
                        AutoGenerateEditButton="True" onpageindexchanging="GridView1_PageIndexChanging" 
                          onrowcancelingedit="GridView1_RowCancelingEdit" 
                          onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating" >
                      </asp:GridView>

[met0555]

i fixed the code at this line, but still get error.

Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs)
        Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
 
        Dim dt = GetCustDT()
 
        'Update the values.
        Dim row = GridView1.Rows(e.RowIndex)
        dt.Rows(row.DataItemIndex)("Client") = (CType((row.Cells(3).Controls(0)), TextBox)).Text
 
        GridView1.EditIndex = -1
 
 
    End Sub

The error i get now is

No row at position 0

#
Public Function GetCustDT() As DataTable
#
        Dim dt As New DataTable
#
        dt.Columns.Add("Id", GetType(String))
#
        dt.Columns.Add("Day", GetType(String))
#
        dt.Columns.Add("Date", GetType(String))
#
        dt.Columns.Add("Client", GetType(String))
#
        dt.Columns.Add("From-To", GetType(String))
#
        dt.Columns.Add("Collect", GetType(String))
#
        dt.Columns.Add("Via", GetType(String))
#
        dt.Columns.Add("Gross", GetType(String))
#
        dt.Columns.Add("In-House", GetType(String))
#
        dt.Columns.Add("Payouts", GetType(String))
#
        dt.Columns.Add("Expenses", GetType(String))
#
        dt.Columns.Add("Net", GetType(String))
#
        dt.Columns.Add("Status", GetType(String))
#
        dt.Columns.Add("Farm", GetType(String))
#
        Return dt
#
    End Function

[gep13]

Hey,

Is it possible that you can upload a complete project? It is getting to the point that this is getting hard to follow? If not, can you knock up a sample project, based on what you currently have, and upload that?

Gary

[met0555]

here is the project with the SQL script


thx

[mendhak]

I think I see the problem here.

When you do your RowUpdating, you're attempting to assign a value to a row in a datatable. However, this datatable is empty. If you look at GetCustDT(), all you're doing is defining the datatable, but no rows. You'd normally create a row by using dt.NewRow() and assigning it a value, but I don't think this is what you want. You want to update the database with your new value.

The original datatable you used is in another method, when you bind the grid. It's out of scope now.

Either make it class-level and fill the datatable up each time you load the page, and then update the row and call dataadapter.update

or

Use a SqlCommand, create your UPDATE statement and call ExecuteNonQuery and then rebind your grid.