ASP.net Update multiple GridView Checkbox

Bakedgoods

Bakedgoods

Bakedgoods

Capodecina
Permabanned
Joined
31 Dec 2003
Posts
5,172
Location
Barrow-In-Furness
Looking for some help or a point in the right direction with what i'm trying to achieve.

I basically need up update multiple checkbox values at once. The only examples I can find are adding extra columns to GridViews so rows can be deleted. I actually have a field within the table that needs updating so it's a fair bit different.

It's for a timesheet system and the page will be used for approving hours (the checkbox value needs to be set to approve the hours)

The SQL Query i'm using is:

Code: 
SELECT ID , TimeCode , Date , Hours , Username , Comments , Approved  , 

( SELECT CodeOwner FROM lward.tblCodes WHERE tblCodes.CodeID = tblHours.TimeCode AND CodeOwner = @UserName) as CodeOwner

FROM lward.tblHours WHERE Approved is NULL and tblHours.TimeCode in (SELECT distinct CodeID from lward.tblCodes WHERE tblCodes.CodeID = tblHours.TimeCode AND CodeOwner = @UserName)

Can anyone help me with this?
 
Last edited:
Do you mean checking/unchecking multiple checkboxes of the same field in the grid and then updating to the database, or a checkbox external to the grid which will update the database and show the changes on the grid?
 
Multiple checkboxes of the same field in the GridView.

I want to UPDATE several rows of the GridView at once. The only field that will be updated is the CheckBox field and nothing else.

It's basically so a Manager and Approve peoples hours by using the checkbox.
 
Well, it depends how you implement it, you just need to call a stored procedure to update the field passing in the Id.

UPDATE lward.tblHours SET Approved WHERE ID = @ID

now you can call this multiple times iterating through the grid and hit the database multiple times, or you could use a dynamic stored procedure, iterate through the grid take the id's into a string which can then be passed into it and call it only once so something like this is generated from within the stored procedure.

UPDATE lward.tblHours SET Approved WHERE ID IN (1, 2, 3)
 
When I try create a new stored procedure, the default query is....

Code: 
UPDATE [lward].[tblHours] SET [TimeCode] = @TimeCode, [Date] = @Date, [Hours] = @Hours, [Username] = @Username, [Comments] = @Comments, [Approved] = @Approved WHERE (([ID] = @Original_ID))

I changed this to..

Code: 
UPDATE lward.tblHours SET Approved WHERE ID = @ID

It says generated UPDATE statement is fine, but gives me an error which just says "There was a problem with the UPDATE stored procedure, The stored procedure was not created."

Hardly descriptive :[
 
Try using a script like this.

Code: 
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sup_HoursApprovalUpdate')
	BEGIN
		PRINT 'Dropping Procedure sup_HoursApprovalUpdate'
		DROP  Procedure  sup_HoursApprovalUpdate
	END
GO

PRINT 'Creating Procedure sup_HoursApprovalUpdate'
GO

CREATE Procedure dbo.sup_HoursApprovalUpdate
			@ID int
AS
SET NOCOUNT ON

UPDATE lward.tblHours SET Approved WHERE ID = @ID
GO

GRANT EXEC ON sup_HoursApprovalUpdate TO PUBLIC
GO
 
Not only do I have no idea how to add a script like that to my DAL, i've got no idea about what's going no so it's abit pointless to use it (i'll struggle with creating the actual VB code to do what I need as well).
 
I've never had to do that is all, there's no need to be funny about it because i'm just learning... :)

I can manage with the rest of the code but i'm just unsure about how to do the actual updating and setting up the query/stored procedure
 
Last edited:
Using Visual Studio 2005, MS SQL Server (2000) and MS SQL Management Studio Express.

I'm fairly certain i'm fine with the rest of the coding, it's just the ADO.net part and actually performing the update. I'm pretty sure I can code the rest of it (checking which checkboxes have been selected).

Thanks :]
 
An alternative would be to use a BLL.
You could set up your BLL with two update methods, one for updating everything in the table and a seperate one for updating just the Approved field.

In your code behind when you iterate through the GridView, check if the checkbox is checked and if so, reference to your BLL to use the update method to update the Approved field.
 
So if i'm just wanting up UPDATE one field at a time, the UPDATE query work fine as:

UPDATE lward.tblHours SET Approved WHERE ID = @ID

The approved field is a bit type. I guess i'll do some reading on BLLs and try get my head round it again.
 
Well you don't use a query as such in a BLL you point to the queries in your DAL.
Have a look at the Data Access Tutotorial on BLLs.

The update method populates a datatable using a query in the DAL to select all by ID, using your requirements it would be:

Code: 
SELECT * FROM tblHours WHERE ID = @ID

Then it populates a datarow with the row in the datatable.

After this you can set the values for as many fields as you like and update the row.
 
I would try it if I was you, they are ok, as long you don't want performance or actually try to do anything to adventurous in the database (for which you don't need to).
 
Ok i've added a new query in my DAL which looks like this...

Code: 
UPDATE [tblHours] SET  [Approved] = 1 WHERE (([ID] = @Original_ID))

I'm struggling to workout how to complete my code though and I could do with it checking to make sure i'm not going about this the wrong way. Here's the Code..

Code: 
Protected Sub UpdateApproved_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateApproved.Click

        Dim intUpdateValue As Integer
        Dim num As Integer = GridView1.Rows.Count

        Dim hoursAdapter As New ManHoursTableAdapters.tblHoursTableAdapter()


        For i As Integer = 0 To num - 1

            Dim cbox As CheckBox = GridView1.Rows(i).FindControl("CheckBox1")

            If cbox.Checked = True Then
                intUpdateValue = "1"
            Else
                intUpdateValue = "0"
            End If
        Next

        If intUpdateValue = "1" Then
           [B][COLOR="Red"]hoursAdapter.UpdateApproveCheckBoxQuery()[/COLOR][/B]

        End If

    End Sub

On the line i've highlighted i'm getting the message:

"Argument not specified for parameter 'Original_ID' of 'Public overridable function UpdateApprovedCheckBoxQuery(Original_ID as Integer) As Integer"

I know that I need to get the CodeID/Original_ID into the code somewhere but i'm not sure how to do it.

Should I scrap this and go about it a different way?
 
Last edited:
Change the method

UpdateApproveCheckBoxQuery() to UpdateApproveCheckBoxQuery(int originalId)

If you post your DAL method then I can have a look at that, but you need to pass the integer parameter (assuming the id is an integer) into the stored procedure.

In the event method, like how you get the checkbox data you need to get the id from the grid. I'm assuming it might be a textbox, use the watch to query to find out what the cell actually has as a control and to get it's name I've substitued TextBox1 for the moment. If it's hidden it may not be stored but there is an easy work around.

Code: 
Protected Sub UpdateApproved_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateApproved.Click

        Dim intUpdateValue As Integer
        Dim num As Integer = GridView1.Rows.Count

        Dim hoursAdapter As New ManHoursTableAdapters.tblHoursTableAdapter()


        For i As Integer = 0 To num - 1

            [COLOR="Red"]Dim idText as TextBox = GridView1.Rows(i).FindControl("Textbox1")[/COLOR]
            Dim cbox As CheckBox = GridView1.Rows(i).FindControl("CheckBox1")

            [COLOR="Red"]Dim originalId as integer = -1
            If idText.Text.Trim().Length > 0 Then
                originalId  = Convert.ToInt32(idText.Text)
            End If
[/COLOR]
            If cbox.Checked = True Then
                intUpdateValue = "1"
            Else
                intUpdateValue = "0"
            End If
        Next

        If intUpdateValue = "1" [COLOR="Red"]AndAlso originalId > -1[/COLOR] Then
           hoursAdapter.UpdateApproveCheckBoxQuery([COLOR="Red"]originalId[/COLOR])

        End If

    End Sub
 
I'm not sure what you mean by post my DAL method? I posted the UPDATE query I added to my tblHours Table Adapter.

The page runs fine using the above code but nothing happens when I click the Approve button.

Here's the code, I had to change it slightly to get it to work. Original_ID needed to be declared outside the If statement.

Code: 
Protected Sub UpdateApproved_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateApproved.Click

        Dim intUpdateValue As Integer
        Dim num As Integer = GridView1.Rows.Count
        Dim Original_ID As Integer = -1

        Dim hoursAdapter As New ManHoursTableAdapters.tblHoursTableAdapter()


        For i As Integer = 0 To num - 1

            Dim IDlabel As Label = GridView1.Rows(i).FindControl("Label1")
            Dim cbox As CheckBox = GridView1.Rows(i).FindControl("CheckBox2")


            If IDlabel.Text.Trim().Length > 0 Then
                Original_ID = Convert.ToInt32(IDlabel.Text)
            End If

            If cbox.Checked = True Then
                intUpdateValue = "1"
            Else
                intUpdateValue = "0"
            End If
     [B][COLOR="Red"]   Next[/COLOR][/B]

        If intUpdateValue = "1" AndAlso Original_ID > -1 Then
            hoursAdapter.UpdateApproveCheckBoxQuery(Original_ID)

        End If

I put a break in where the red highlighted line is and the value of Original_ID was "-1"

Thanks for the help, i'm struggling with this one
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom