ASP.Net Datagrid with Dropdownlists

I have a datagrid that uses templates for data entry and for adding additional rows to the table.  The problem I am having is that if the primary table row contains a null value, I get a "Cast from type 'DBNull' to type 'String' is not valid" error.  I basically have 3 questions:
1.  How can I modify this code to be able to handle nulls in the primary table?
2.  How can I handle situations where the child table has only text values for the dropdownlist and no indexes?
3.  How can I make the dropdownlist show empty until I choose a name when I'm adding a row?  Currently it will show the first item in the list.

'Code behind
'Function that returns the list during add mode
Public Function FillDesignChampion()
        ds = SqlHelper.ExecuteDataset(oConn, CommandType.StoredProcedure, "SP_DBUserList")
        Return ds
End Function

'Function that returns the list and the text value of the index during normal edit mode.
Function GetDesignChampionIndex(ByVal CatID As String) As Integer
        Dim iLoop As Integer
        ds = SqlHelper.ExecuteDataset(oConn, CommandType.StoredProcedure, "SP_DBUserList")
        tblOriginator = ds.Tables(0)
        For iLoop = 0 To tblOriginator.Rows.Count - 1
            If Int32.Parse(CatID) = _
                  Int32.Parse(tblOriginator.Rows(iLoop)("OriginatorID")) Then
                Return iLoop
            End If
        Next iLoop
End Function

'HTML Code
<asp:TemplateColumn HeaderText="Design Champion">
   <ItemTemplate>
     <asp:DropDownList id=editDesignChampion runat="server" Width="115px" SelectedIndex='<%# GetDesignChampionIndex(Container.DataItem("DesignChampion")) %>' DataSource="<%# FillDesignChampion() %>" DataTextField="EmployeeName" DataValueField="OriginatorID">
     </asp:DropDownList>
   </ItemTemplate>
   <FooterTemplate>
      <asp:DropDownList id=addDesignChampion runat="server" Width="115px" DataSource="<%# FillDesignChampion() %>" DataTextField="EmployeeName" DataValueField="OriginatorID">
      </asp:DropDownList>
   </FooterTemplate>
</asp:TemplateColumn>
End Function
MikerhondaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TorrwinCommented:
1)  One way to do this would be to change your stored procedure to include a "WHERE COLUMN <> NULL" statement.  You could also use the .ToString method on the end of your variable, which would change null values to empty string values.

2)  Set both the DataTextField and DataValueField to the text value.

3)  Get rid of the datasource, textfield, and valuefield from your html code and change your FillDesignChampion to the following:

Public Sub FillDesignChampion()
        Dim DDL as dropdownlist = DataGrid.FindControl("editDesignChampion")
        ds = SqlHelper.ExecuteDataset(oConn, CommandType.StoredProcedure, "SP_DBUserList")

        DDL.Datasource = ds
        DDL.DataTextField = "EmployeeName"
        DDL.DataValueField = "OriginatorID"
        DDL.DataBind
        DDL.Items.Insert(0, New ListItem("", ""))
End Sub

You'll probably need to call this Sub from your page load sub:
        If Not IsPostBack then
             FillDesignChampion()
        End If

Let me know how it goes,
-Torrwin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikerhondaAuthor Commented:
Torrwin:
Thanks for the quick reply.  Lemme take these one at a time:
1.  I need to retrieve all of the data in the parent table-even rows that have a null value for this field.  I just need for the dropdownlist box to be able to handle it.
2.  That was my thought too but if you look at the html code, it uses 'SelectedINDEX' and if I don't use an integer, I get a whole different error-sorry don't remember what the error says.
3.  Am doing that now, and you're right, this should work.

-Mike
0
TorrwinCommented:
For number 1 you could also try editing your stored procedure like:
     ISNULL(COLUMN_NAME, '')

I haven't tried that myself yet, but I think it will work.
-----------------------------------------------------------
The way that I handle number 2 myself is this:

I also add a label to the template column and set its text equal to the value you want selected, but set visible = false.  Something like:

<asp:Label ID="lblEditParent" Runat="server" Width="0%" Visible="False" Text='<%# DataBinder.Eval(Container, "DataItem.EmployeeName") %>'>
</asp:Label>

Then, at the end of your subroutine where you bind the dropdown list add the following:
            Dim lbl As Label = e.Item.FindControl("lblEditDesignChampion")
            Dim sSelected = CStr(lbl.Text)
            If sSelected <> "" Then
                DDL.Items.FindByText(sSelected).Selected = True
            End If
0
TorrwinCommented:
For number 1 you could also try something like:

<asp:DropDownList id=editDesignChampion runat="server" Width="115px" SelectedIndex='<%# GetDesignChampionIndex(Container.DataItem("DesignChampion")) %>' DataSource="<%# FillDesignChampion() %>"
DataTextField='<%# iif(Databinder.Eval(Container, "DataItem.EmployeeName") = NULL, "", Databinder.Eval(Container, "DataItem.EmployeeName"))%>' DataValueField="OriginatorID">
     </asp:DropDownList>
0
MikerhondaAuthor Commented:
Thanks for the suggestions Torrwin.  The final solution was a combination of lots of things.  I decided to take care of everything is the VB code behind.  All of this is handled in the ItemDataBound event of the datagrid.  Here's the corrected code:

'Code behind
'Function that returns the DropDownList
FillDropDown("SP_Operation", editOperationPFMEA, "OperationNumber", "OperationNumber", "@CPID", CPID)

Public Function FillDropDown(ByVal strProcedure As String, ByVal ddlDropdown As DropDownList, ByVal strTextField As String, ByVal strValueField As String, Optional ByVal strParameter As String = "", Optional ByVal strParameterValue As String = "")
        Try
            If strParameter = "" Then
                ds = SqlHelper.ExecuteDataset(oConn, CommandType.StoredProcedure, strProcedure)
            Else
                ds = SqlHelper.ExecuteDataset(oConn, CommandType.StoredProcedure, strProcedure, New SqlClient.SqlParameter(strParameter, strParameterValue))
            End If
            With ddlDropdown
                .DataSource = ds
                .DataTextField = strTextField
                .DataValueField = strValueField
                .DataBind()
                .Items.Insert(0, "")
            End With

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
End Function

'Function that returns the text value of the DropDownList during normal edit mode.
GetSelectedValue("SP_ControlPlanPFMEARowSelect", editOperationPFMEA, "Operation#", "@PFMEAID", DataGridPFMEA.DataKeys(e.Item.ItemIndex))

Public Function GetSelectedValue(ByVal strProcedure As String, ByVal ddlDropdown As DropDownList, ByVal strTextField As String, Optional ByVal strParameter As String = "", Optional ByVal strParameterValue As String = "")
        Try
            dr = SqlHelper.ExecuteReader(oConn, CommandType.StoredProcedure, strProcedure, New SqlClient.SqlParameter(strParameter, strParameterValue))
            If dr.HasRows Then
                dr.Read()
                With ddlDropdown
                    .SelectedValue = CheckNull(dr(strTextField))
                End With
                dr.Close()
            End If

        Catch ex As Exception
            dr.Close()
            MsgBox(ex.Message)
        End Try
        dr.Close()
End Function

Public Function CheckNull(ByVal Value)
        If IsDBNull(Value) Then
            Return ""
        Else
            Return Value
        End If
End Function
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.