?
Solved

ASP.Net Datagrid with Dropdownlists

Posted on 2006-04-05
5
Medium Priority
?
433 Views
Last Modified: 2010-08-05
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
0
Comment
Question by:Mikerhonda
  • 3
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
Torrwin earned 375 total points
ID: 16381718
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
 

Author Comment

by:Mikerhonda
ID: 16384073
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
 
LVL 13

Expert Comment

by:Torrwin
ID: 16386267
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
 
LVL 13

Expert Comment

by:Torrwin
ID: 16391828
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
 

Author Comment

by:Mikerhonda
ID: 16401023
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Loops Section Overview
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month16 days, 14 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question