asked on

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

ASKER CERTIFIED SOLUTION

Torrwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mikerhonda

ASKER

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

Torrwin

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

Torrwin

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>

Mikerhonda

ASKER

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