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:MainConn ection %>"
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:Sq lDataSourc e ID="BrokerDS" runat="server" ConnectionString="<%$ ConnectionStrings:MainConn ection %>"
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("Warehous eID")
If WarehouseID = "" Then WarehouseID = Guid.Empty.ToString
Dim TruckingCompanyID As String = e.NewValues.Item("Trucking CompanyID" )
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("Warehous eID") = DBNull.Value
If New Guid(TruckingCompanyID) = Guid.Empty Then e.NewValues.Item("Trucking CompanyID" ) = DBNull.Value
If New Guid(BrokerID) = Guid.Empty Then e.NewValues.Item("BrokerID ") = DBNull.Value
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:MainConn
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"
<asp:Parameter Name="WarehousePickupDate"
<asp:Parameter Name="CustomerDeliveryDate
<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"
DataTextField="FullName" SelectedValue='<%# Bind("BrokerID") %>' AppendDataBoundItems="True
<asp:ListItem Value="">No Broker Chosen</asp:ListItem>
</asp:DropDownList><asp:Sq
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
Dim WarehouseID As String = e.NewValues.Item("Warehous
If WarehouseID = "" Then WarehouseID = Guid.Empty.ToString
Dim TruckingCompanyID As String = e.NewValues.Item("Trucking
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("Warehous
If New Guid(TruckingCompanyID) = Guid.Empty Then e.NewValues.Item("Trucking
If New Guid(BrokerID) = Guid.Empty Then e.NewValues.Item("BrokerID
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!
'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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
All you should need to do is give it a value ie.
[<asp:ListItem Value="00000000-0000-0000-
This should solve the problem.
Cheers,
Gavin