Hi,

I have a datagrid that creates check boxes dynamically. That's all working fine. The problem is with the Save sub. The save sub works fine in .NET 1.0 but it errors when I create a new item in the 2.0 app and use the same code as I had in the .NET 1.0 app.

Here is the offending code:

Protected Sub btnSave_Click(ByVal s As Object, ByVal e As EventArgs) Handles btnSave.Click
        Dim Pass As Boolean
        Try
            _oConn.Open()
            Dim SQL As String = "DELETE FROM AccessLevelFeature;"
            Dim CMD As New SqlCommand(SQL, _oConn)
            CMD.ExecuteNonQuery()
            For Each Item As DataGridItem In grdAccessLevelFeature.Items
                Dim i As Integer
                For i = 2 To Item.Cells.Count - 1
It errors on this line.Dim chkAccessLevelFeature As CheckBox = Item.Cells(i).Controls(0)
                    If chkAccessLevelFeature.Checked Then
                        Dim SQL2 As String = "INSERT INTO AccessLevelFeature (AccessLevelID, AccessFeatureID) VALUES (" & Item.Cells(i).Text & ", " & Item.Cells(0).Text & ");"
                        Dim CMD2 As New SqlCommand(SQL2, _oConn)
                        CMD2.ExecuteNonQuery()
                    End If
                Next
            Next
        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            _oConn.Close()
            If Pass Then
                getAccessLevelFeature()
            End If
        End Try
    End Sub

The error is:

"Specified argument was out of the range of valid values. Parameter name: index"

Thank you for your reply.

Richard

How are you creating the checkboxes dynamically? Can you show use that code? It would seem that "Controls(0)" is now illegal.

If you can name the checkbox you create, you could get at it more easily. Instead of trying to get it by an explicit position, you can get it by name off of the item:
This will get the control wherever it exists in the item regardless of which cell it is in.

-Peter
compiledthoughts.com

Hi Peter,

Thank you for your reply.

Here is the code that produces the check boxes:

    Private Sub getAccessLevelFeature()
        Try
            _oConn.Open()
            Dim SQL As String = "SELECT AccessFeatureID, AccessFeatureName AS [Feature]"
            Dim SQL2 As String = "SELECT AccessLevelID, AccessName FROM AccessLevel ORDER BY AccessName;"
            Dim CMD2 As SqlCommand = New SqlCommand(SQL2, _oConn)
            Dim oDA2 As New SqlDataAdapter(CMD2)
            Dim oDS2 As New DataSet
            oDA2.Fill(oDS2)
            For Each Row As DataRow In oDS2.Tables(0).Rows
                SQL += ", " & Row.Item("AccessLevelID") & " AS [" & Row.Item("AccessName") & "] "
            Next
            SQL += "FROM AccessFeature ORDER BY AccessFeatureName;"
            Dim CMD As SqlCommand = New SqlCommand(SQL, _oConn)
            Dim oDA As New SqlDataAdapter(CMD)
            Dim oDS As New DataSet
            oDA.Fill(oDS)
            grdAccessLevelFeatures.DataSource = oDS
            grdAccessLevelFeatures.DataBind()
        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            _oConn.Close()
        End Try
    End Sub

    Protected Sub grdAccessLevelFeature_ItemDataBound(ByVal s As Object, ByVal e As DataGridItemEventArgs) Handles grdAccessLevelFeatures.ItemDataBound
        Select Case e.Item.ItemType
            Case ListItemType.Item, ListItemType.AlternatingItem
                Dim i As Integer
                For i = 2 To e.Item.Cells.Count - 1
                    Dim chkAccessLevelFeature As New CheckBox
                    Dim SQL As String = "SELECT * FROM AccessLevelFeature WHERE AccessLevelID = " & e.Item.Cells(i).Text & " AND AccessFeatureID = " & e.Item.Cells(0).Text & ";"
                    Dim CMD As SqlCommand = New SqlCommand(SQL, _oConn)
                    Dim oDA As New SqlDataAdapter(CMD)
                    Dim oDS As New DataSet
                    oDA.Fill(oDS)
                    chkAccessLevelFeature.Checked = oDS.Tables(0).Rows.Count > 0
                    e.Item.Cells(i).Controls.Add(chkAccessLevelFeature )
                Next
        End Select
        e.Item.Cells(0).Visible = False
    End Sub

I've been messing with your suggestion in the Save sub. Still not sure how to access the check box though in the SQL statement. The SQL statement here errors with "Object reference not set to an instance of an object."

For Each Item As DataGridItem In grdAccessLevelFeatures.Items
   Dim i As Integer
   For i = 2 To Item.Cells.Count - 1
   'Dim chkAccessLevelFeature As CheckBox = Item.Cells(i).Controls(0)
   Dim chkAccessLevelFeature As CheckBox = CType(Item.FindControl("myCheckBox"), CheckBox)
       If chkAccessLevelFeature.Checked Then
       Dim SQL2 As String = "INSERT INTO AccessLevelFeature (AccessLevelID, AccessFeatureID) VALUES (" & Item.Cells(i).Text & ", " & Item.DataItem("myCheckBox") & ");"
       Dim CMD2 As New SqlCommand(SQL2, _oConn)
       CMD2.ExecuteNonQuery()
      End If
    Next
Next

I'm so iffy on the FindControl issues. If I look at the source of the page the check boxes all have names like: name="grdAccessLevelFeatures:_ctl3:_ctl0" so I don't really understand how this works.

Thanks,
Richard

You need to name the checkbox:

Dim chkAccessLevelFeature As New CheckBox
chkAccessLevelFeature.ID = "myCheckBox"

Then you should be able to "find" it by the name:

Dim chkAccessLevelFeature As CheckBox = CType(Item.FindControl("myCheckBox"), CheckBox)

-Peter
compiledthoughts.com

Hi Peter,

Really appreciate your post but I'm not getting it. If you give a monkey a typewriter and an infinite amount of time he will write Shakespeare. Well, OK, I got the monkey part covered.

Still can't find the check box. "Object reference not set to an instance of an object."

    Protected Sub grdAccessLevelFeature_ItemDataBound(ByVal s As Object, ByVal e As DataGridItemEventArgs) Handles grdAccessLevelFeatures.ItemDataBound
        Select Case e.Item.ItemType
            Case ListItemType.Item, ListItemType.AlternatingItem
                Dim i As Integer
                For i = 2 To e.Item.Cells.Count - 1
                    Dim chkAccessLevelFeature As New CheckBox
                    chkAccessLevelFeature.ID = "myCheckBox"
                    Dim SQL As String = "SELECT * FROM AccessLevelFeature WHERE AccessLevelID = " & e.Item.Cells(i).Text & " AND AccessFeatureID = " & e.Item.Cells(0).Text & ";"
                    Dim CMD As SqlCommand = New SqlCommand(SQL, _oConn)
                    Dim oDA As New SqlDataAdapter(CMD)
                    Dim oDS As New DataSet
                    oDA.Fill(oDS)
                    chkAccessLevelFeature.Checked = oDS.Tables(0).Rows.Count > 0
                    e.Item.Cells(i).Controls.Add(chkAccessLevelFeature )
                Next
        End Select
        e.Item.Cells(0).Visible = False
    End Sub

    Protected Sub btnSave_Click(ByVal s As Object, ByVal e As EventArgs) Handles btnSave.Click
        Dim Pass As Boolean
        Try
            _oConn.Open()
            Dim SQL As String = "DELETE FROM AccessLevelFeature;"
            Dim CMD As New SqlCommand(SQL, _oConn)
            CMD.ExecuteNonQuery()
            For Each Item As DataGridItem In grdAccessLevelFeatures.Items
                Dim i As Integer
                For i = 2 To Item.Cells.Count - 1
                    Dim chkAccessLevelFeature As CheckBox = CType(Item.FindControl("myCheckBox"), CheckBox)
                    If chkAccessLevelFeature.Checked Then
                        Dim SQL2 As String = "INSERT INTO AccessLevelFeature (AccessLevelID, AccessFeatureID) VALUES (" & Item.Cells(i).Text & ", " & Item.FindControl("myCheckBox").ID & ");"
                        Dim CMD2 As New SqlCommand(SQL2, _oConn)
                        CMD2.ExecuteNonQuery()
                    End If
                Next
            Next
        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            _oConn.Close()
            If Pass Then
                getAccessLevelFeature()
            End If
        End Try
    End Sub

Not sure if I even implemented your suggestion correctly.

Thanks,
Richard

Hi Richard,

Try setting a break point on this line:

Dim chkAccessLevelFeature As CheckBox = Item.Cells(i).Controls(0)

Then use the watch window to look at stuff like:

Item
Item.Cells(i)
Item.Cells(i).Controls

Change the i variable (add or subtract 1 or more) and see if you can find the control. Maybe it's in a different cell, or maybe the row doesn't contain the CheckBox at all.

I am also not sure why you are executing SQL in the grdAccessLevelFeature_ItemDataBound handler. The data you are binding to is available in the e argument (that is, when you're querying it initially).

Finally, your code is open for SQL injection. It's pretty easy to substitute values in the client grid and then execute arbitrary SQL commands like DROP DATABASE Master.


If all this doesn't help, can you post the full code for the page and its markup?

Imar




---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.

Hi Imar,

Thanks for taking a look at this. I'm still not getting anywhere with it. What's weird is that in the ItemDataBound I need that second SQL query to find out if the checkbox should be checked. It finds the checkboxes fine and marks them correctly. So, in that ItemDataBound it is finding checkbox and marking them correctly.

I tried putting in, "Dim chkAccessLevelFeature As CheckBox = Item.Cells(i).Controls(0)" which is the code I started with. It errors out with:

Specified argument was out of the range of valid values. Parameter name: index

I did some research and found out that that code is now "illegal" in .NET 2.0

The code runs fine in 1.1.

Thanks for pointing out about the SQL injection. We change that once we go to production.

Here are the tables with data and all of the code.

--************************************************** ********************

CREATE TABLE [AccessLevel] (
    [AccessLevelID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [AccessLevelName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AccessLevelOrder] [tinyint] NOT NULL ,
    [Status] [tinyint] NOT NULL CONSTRAINT [DF_AccessLevel_Status] DEFAULT (1),
    CONSTRAINT [PK_AccessLevel] PRIMARY KEY CLUSTERED
    (
        [AccessLevelID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO AccessLevel (AccessLevelName, AccessLevelOrder, Status) VALUES ('Basic', 1, 1)
INSERT INTO AccessLevel (AccessLevelName, AccessLevelOrder, Status) VALUES ('Silver', 2, 1)
INSERT INTO AccessLevel (AccessLevelName, AccessLevelOrder, Status) VALUES ('Gold', 3, 1)
INSERT INTO AccessLevel (AccessLevelName, AccessLevelOrder, Status) VALUES ('Platinum', 4, 1)

CREATE TABLE [AccessFeature] (
    [AccessFeatureID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [AccessFeatureName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AccessFeatureDesc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Status] [bit] NOT NULL ,
    CONSTRAINT [PK_AccessFeature] PRIMARY KEY CLUSTERED
    (
        [AccessFeatureID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO AccessFeature (AccessFeatureName, AccessFeatureDesc, Status) VALUES ('Inventory', 'None', 1)
INSERT INTO AccessFeature (AccessFeatureName, AccessFeatureDesc, Status) VALUES ('Inventory.Add', 'None', 1)
INSERT INTO AccessFeature (AccessFeatureName, AccessFeatureDesc, Status) VALUES ('Inventory.Edit', 'None', 1)
INSERT INTO AccessFeature (AccessFeatureName, AccessFeatureDesc, Status) VALUES ('Inventory.Delete', 'None', 1)

CREATE TABLE [AccessLevelFeature] (
    [AccessLevelID] [int] NOT NULL ,
    [AccessFeatureID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO AccessLevelFeature (AccessLevelID, AccessFeatureID) VALUES (1, 1)
INSERT INTO AccessLevelFeature (AccessLevelID, AccessFeatureID) VALUES (2, 3)

/*

SELECT * FROM AccessLevel
SELECT * FROM AccessFeature
SELECT * FROM AccessLevelFeature

DROP TABLE accesslevel
DROP TABLE accesslevelfeature
DROP TABLE accessfeature
*/


--************************************************** ********************

'********************************VB Code********************************

   Private Sub getAccessLevelFeature()
        Try
            _oConn.Open()
            Dim SQL As String = "SELECT AccessFeatureID, AccessFeatureName AS [Feature]"
            Dim SQL2 As String = "SELECT AccessLevelID, AccessLevelName FROM AccessLevel ORDER BY AccessLevelName;"
            Dim CMD2 As SqlCommand = New SqlCommand(SQL2, _oConn)
            Dim oDA2 As New SqlDataAdapter(CMD2)
            Dim oDS2 As New DataSet
            oDA2.Fill(oDS2)
            For Each Row As DataRow In oDS2.Tables(0).Rows
                SQL += ", " & Row.Item("AccessLevelID") & " AS [" & Row.Item("AccessLevelName") & "] "
            Next
            SQL += "FROM AccessFeature ORDER BY AccessFeatureName;"
            Dim CMD As SqlCommand = New SqlCommand(SQL, _oConn)
            Dim oDA As New SqlDataAdapter(CMD)
            Dim oDS As New DataSet
            oDA.Fill(oDS)
            grdAccessLevelFeature.DataSource = oDS
            grdAccessLevelFeature.DataBind()
        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            _oConn.Close()
        End Try
    End Sub

    Protected Sub grdAccessLevelFeature_ItemDataBound(ByVal s As Object, ByVal e As DataGridItemEventArgs) Handles grdAccessLevelFeature.ItemDataBound
        Select Case e.Item.ItemType
            Case ListItemType.Item, ListItemType.AlternatingItem
                Dim i As Integer
                For i = 2 To e.Item.Cells.Count - 1
                    Dim chkAccessLevelFeature As New CheckBox
                    Dim SQL As String = "SELECT * FROM AccessLevelFeature WHERE AccessLevelID = " & e.Item.Cells(i).Text & " AND AccessFeatureID = " & e.Item.Cells(0).Text & ";"
                    Dim CMD As SqlCommand = New SqlCommand(SQL, _oConn)
                    Dim oDA As New SqlDataAdapter(CMD)
                    Dim oDS As New DataSet
                    oDA.Fill(oDS)
                    chkAccessLevelFeature.ID = "chkAccessLevelFeature"
                    chkAccessLevelFeature.Checked = oDS.Tables(0).Rows.Count > 0
                    e.Item.Cells(i).Controls.Add(chkAccessLevelFeature )
                Next
        End Select
        e.Item.Cells(0).Visible = False
    End Sub

    Protected Sub btnSave_Click(ByVal s As Object, ByVal e As EventArgs) Handles btnSave.Click
        Dim Pass As Boolean
        Try
            _oConn.Open()
            Dim SQL As String = "DELETE FROM AccessLevelFeature;"
            Dim CMD As New SqlCommand(SQL, _oConn)
            CMD.ExecuteNonQuery()
            For Each Item As DataGridItem In grdAccessLevelFeature.Items
                Dim i As Integer
                For i = 2 To Item.Cells.Count - 1
                    'Dim chkAccessLevelFeature As CheckBox = CType(Item.FindControl("chkAccessLevelFeature"), CheckBox)
                    Dim chkAccessLevelFeature As CheckBox = Item.Cells(i).Controls(0)
                    If chkAccessLevelFeature.Checked Then
                        Dim SQL2 As String = "INSERT INTO AccessLevelFeature (AccessLevelID, AccessFeatureID) VALUES (" & Item.Cells(i).Text & ", " & Item.Cells(0).Text & ");"
                        Dim CMD2 As New SqlCommand(SQL2, _oConn)
                        CMD2.ExecuteNonQuery()
                    End If
                Next
            Next
        Pass = True
        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            _oConn.Close()
            If Pass Then
                getAccessLevelFeature()
            End If
        End Try
    End Sub

'************************************************* **************************

aspx front page

<table border="0" width="100%" cellpadding="0" cellspacing="10">
<tr>
    <td width="50%" valign="top">
        <asp:DataGrid ID="grdAccessLevelFeature" Width="100%" CssClass="" runat="server" />
        <br />
        <asp:Button ID="btnSave" Text="Save" runat="server" /><br />
    </td>
    <td width="50%" valign="top">
        <br />
    </td>
</tr>
</table>

Very much appreciated.

Thanks,
Richard