Hiding a Gridview row based on the result of a stored procedure in SQL Server VB.NET

Question

I have a stored procedure in SQL Server that checks the permission of the user who wants to see my gridview.

The procedure returns 1 if they have permission and 0 if they do not.

I want to show the column 'Salary' only to those who have this special permission. this is my aspx page:

 'Hide Salary Column 
 Dim IsPermitted As Integer
 Using cmd As New SqlCommand("stored_procedure_IsPermitted")
     cmd.CommandType = CommandType.StoredProcedure
     cmd.Parameters.AddWithValue("@IDnumber", "4257673")
     cmd.Connection = con
     con.Open()
     IsPermitted = Convert.ToInt32(cmd.ExecuteScalar())
     con.Close()
 End Using
 If IsPermitted = 0 Then
     GridView.Columns("Salary").Visible = False -> this line gets the error
 Else
     GridView. Columns("Salary").Visible = True
 End If

This is my code and I don't understand why I get this error:

Conversion from string "Salary" to type 'Integer' is not valid.'

Can you please help.

Thanks a lot in advance.

Answer 1

The columns for a asp.net (webforms) GridView can't be indexed or referenced by a name, but you have to use an integer value (index) for the GridView. So, you’re passing a name, and vb.net is attempting to convert that string into a number.

So, if you used "2", or better yet 2, then that would be a valid integer "index" into the column.

So, you can look at the GridView, and count the columns, (starting at 0), and enter the column number. Say, like this:

GridView.Columns(3).Visible = False

Or, you can use the posted code in the other answer here. However, keep in mind that any templated columns for a GridView don't require a heading name.

Hence, it is best to simply use the index, since those columns may well not have a heading text anyway.

It would help if you posted the markup of the GridView you are currently using.

For the time being, I suggest using an integer index for the column, and not a name.

However, you can adopt a column search by name routine (such as suggested in the other post) that will return a column based on heading text passed. This will allow use of a column name in place of an integer index number for referencing the columns in the GridView. So, in your global helper code module, you can use this function:

Public Function GetColumnByName(MyColumns As DataControlFieldCollection,
                                sColumnName As String) As DataControlField

    Dim sResult As DataControlField = Nothing

    For Each dColumn As DataControlField In MyColumns
        If dColumn.HeaderText = sColumnName Then
            sResult = dColumn
            Exit For
        End If
    Next

    Return sResult

End Function

And then your code becomes this:

    If IsPermitted = 0 Then
        GetColumnByName(GridView1.Columns, "Salary").Visible = False
    Else
        GetColumnByName(GridView1.Columns, "Salary").Visible = True
    End If

Which could be shortened to:

GetColumnByName(GridView1.Columns, "Salary").Visible = (IsPermitted = 1)