Link to home
Start Free TrialLog in
Avatar of mokingham
mokingham

asked on

Updating a Guid column - ASP.NET 2.0

Summary:
When trying to update certain uniqueidentifier columns in a table from a DropDownList, I get the following error: Object cannot be cast from DBNull to other types.

Details (forgive me if I'm too verbose):
We have a screen that displays a large amount of information from a db table, and allows the user to update a few of the fields. Three of the updatable columns come from a single Users Table (different users associated with a particular order). The primary key on that table (UserID) is a SQL uniqueidentifier.

On the form, we have three dropdown lists with some of the users from the Users table displayed (filtered based on usertype). So, for example, the Broker field on an order is chosen from a DropDownList containing Users with UserType = 2. ANY OF THESE THREE FIELDS CAN BE NULL - a broker is not required on an order.

This all works fine when you are viewing an order: If there is a BrokerID for the Order, then it is displayed properly in the dropdown list. If there is no BrokerID, then the field defaults to this: <asp:ListItem Value="">No Broker Chosen</asp:ListItem>.

The problem comes when attempting to update the Order. I've spent quite a bit of time trying to debug this, but with no success. Every time I get the following error:  Object cannot be cast from DBNull to other types.

Code:

[SqlDataSource]:Tied to a FormView

<asp:SqlDataSource ID="OrderDS" runat="server" ConnectionString="<%$ ConnectionStrings:MainConnection %>"
                    SelectCommand="......"
                    UpdateCommand="UPDATE SalesOrders SET ScheduleShipDate = @ScheduleShipDate, ScheduleArrivalDate = @ScheduleArrivalDate, WarehousePickupDate = @WarehousePickupDate, CustomerDeliveryDate = @CustomerDeliveryDate, ActualDeliveryDate = @ActualDeliveryDate, WarehouseID = @WarehouseID, TruckingCompanyID = @TruckingCompanyID, BrokerID = @BrokerID, DateLastModified = @DateLastModified, LastModifiedBy = @LastModifiedBy WHERE (SalesOrderID = @SalesOrderID)">
                    <SelectParameters>
                        <asp:QueryStringParameter Name="SalesOrderID" QueryStringField="OrderID" />
                    </SelectParameters>
                    <UpdateParameters>
                        <asp:Parameter Name="ScheduleShipDate" Type="DateTime" />
                        <asp:Parameter Name="ScheduleArrivalDate" Type="DateTime" />
                        <asp:Parameter Name="WarehousePickupDate" Type="DateTime" />
                        <asp:Parameter Name="CustomerDeliveryDate" Type="DateTime" />
                        <asp:Parameter Name="ActualDeliveryDate" Type="DateTime" />
                        <asp:Parameter Name="WarehouseID" Type="Object" />
                        <asp:Parameter Name="TruckingCompanyID" Type="Object" />
                        <asp:Parameter Name="BrokerID" Type="Object" />
                        <asp:Parameter Name="DateLastModified" Type="DateTime" />
                        <asp:Parameter Name="LastModifiedBy" Type="Object" />
                        <asp:QueryStringParameter Name="SalesOrderID" QueryStringField="OrderID" />
                    </UpdateParameters>
                </asp:SqlDataSource>

[BrokeID DropDownList]

<asp:DropDownList ID="ddlBrokerID" runat="server" CssClass="StandardTextbox" DataSourceID="BrokerDS"
                                        DataTextField="FullName" SelectedValue='<%# Bind("BrokerID") %>' AppendDataBoundItems="True" DataValueField="UserID">
                                        <asp:ListItem Value="">No Broker Chosen</asp:ListItem>
                                    </asp:DropDownList><asp:SqlDataSource ID="BrokerDS" runat="server" ConnectionString="<%$ ConnectionStrings:MainConnection %>"
                                        SelectCommand="SELECT [UserID], [FullName], [CompanyName] FROM [Users] WHERE ([UserType] = @UserType) ORDER BY [FullName], [CompanyName]">
                                        <SelectParameters>
                                            <asp:Parameter DefaultValue="1" Name="UserType" Type="Int32" />
                                        </SelectParameters>
                                    </asp:SqlDataSource>

[Code behind] : I also tried the following in the OrderFormView_ItemUpdating event.

        Dim WarehouseID As String = e.NewValues.Item("WarehouseID")
        If WarehouseID = "" Then WarehouseID = Guid.Empty.ToString
        Dim TruckingCompanyID As String = e.NewValues.Item("TruckingCompanyID")
        If TruckingCompanyID = "" Then TruckingCompanyID = Guid.Empty.ToString
        Dim BrokerID As String = e.NewValues.Item("BrokerID")
        If BrokerID = "" Then BrokerID = Guid.Empty.ToString

        If New Guid(WarehouseID) = Guid.Empty Then e.NewValues.Item("WarehouseID") = DBNull.Value
        If New Guid(TruckingCompanyID) = Guid.Empty Then e.NewValues.Item("TruckingCompanyID") = DBNull.Value
        If New Guid(BrokerID) = Guid.Empty Then e.NewValues.Item("BrokerID") = DBNull.Value
       


Avatar of GavinMannion
GavinMannion

When creating the following [<asp:ListItem Value="">No Broker Chosen</asp:ListItem>]

All you should need to do is give it a value ie.
[<asp:ListItem Value="00000000-0000-0000-0000-000000000000">No Broker Chosen</asp:ListItem>]

This should solve the problem.

Cheers,
Gavin
Avatar of mokingham

ASKER

When trying that, if the BrokerID is null in the DB, the page errors out and says:

'ddlBrokerID' has a SelectedValue which is invalid because it does not exist in the list of items.
Parameter name: value

I'd like to keep the BrokerID = NULL in the database, but if there is no other way around it, I'll make default it to an empty guid.

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of GavinMannion
GavinMannion

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial