Operand type clash error

Hello!

I am trying to update a DetailsView but I keep getting the error of Operand type clash: int is incompatible with text. I have double checked the stored procedure but I don't see any obvious offending parameters. ID is the only parameter that is an interger. I am not trying to update the ID at all, just for the filter purposes to update the records.

ALTER PROCEDURE [dbo].[proc_update]
/*
(
@.parameter1 int = 5,
@.parameter2 datatype OUTPUT
)
*/
@.BUILDING_SV float,
@.BUILDING_ADDS float,
@.BUILDING_DEL float,
@.BUILDING_TOTAL float,
@.ME_SV float,
@.ME_ADDS float,
@.ME_DEL float,
@.ME_TOTAL float,
@.PATTERNS_SV float,
@.PATTERNS_ADDS float,
@.PATTERNS_DEL float,
@.PATTERNS_TOTAL float,
@.SS_VALUE float,
@.SS_TOT float,
@.COMMENTS text,
@.OCCUPANCY_TYPE varchar(255),
@.REVISED_BY varchar(255),
@.DRAWINGS float,
@.INVENTORY float,
@.TOTAL float,
@.DIVISION varchar(255),
@.LOCATION varchar(255),
@.LOCATIONCODE varchar(255),
@.ADDRESS1 varchar(255),
@.ADDRESS2 varchar(255),
@.ADDRESS3 varchar(255),
@.ADDRESS4 varchar(255),
@.PROP_ID float,
@.POST_DATE smalldatetime,
@.ID int
AS
/* SET NOCOUNT ON */
UPDATE dbo.PROPERTY_VALUES
SET BUILDING_SV = @.BUILDING_SV, BUILDING_ADDS = @.BUILDING_ADDS, BUILDING_DEL = @.BUILDING_DEL, BUILDING_TOTAL = @.BUILDING_TOTAL, ME_SV = @.ME_SV, ME_ADDS = @.ME_ADDS, ME_DEL = @.ME_DEL, ME_TOTAL = @.ME_TOTAL, PATTERNS_SV = @.PATTERNS_SV, PATTERNS_ADDS = @.PATTERNS_ADDS, PATTERNS_DEL = @.PATTERNS_DEL, PATTERNS_TOTAL = @.PATTERNS_TOTAL, SS_VALUE = @.SS_VALUE, SS_TOT = @.SS_TOT, COMMENTS = @.COMMENTS,OCCUPANCY_TYPE = @.OCCUPANCY_TYPE, REVISED_BY = @.REVISED_BY, DRAWINGS = @.DRAWINGS, INVENTORY = @.INVENTORY, TOTAL = @.TOTAL, DIVISION = @.DIVISION, LOCATION = @.LOCATION, LOCATIONCODE = @.LOCATIONCODE, ADDRESS1 = @.ADDRESS1, ADDRESS2 = @.ADDRESS2, ADDRESS3 = @.ADDRESS3, ADDRESS4 = @.ADDRESS4, PROP_ID = @.PROP_ID, POST_DATE = @.POST_DATE
WHERE [ID] = @.ID
RETURN

Help!

Thanks.

I did more debugging. I discovered that the null values in the DetailsView are passing as "1" to the database. Anyone know how to debug this? Thanks.

|||

You probably have some default values set to 1, and "ConvertNullToDefaultValue" set to true (I forget the exact property name, but it's close).

|||

Thanks.

I don't recall setting the default values to 1. I am stumped why it is doing this. Is CONVERTNULLTODEFAULTVALUE a part of the codebehind or in the source page?

Thanks!

|||Please post the sqldatasource source (Everything between <asp:SqlDataSource and </asp:SqlDataSource>).|||

Here is it.

<asp:SqlDataSourceID="Update"runat="server"

SelectCommand="proc_update_select"UpdateCommand="proc_update"SelectCommandType="StoredProcedure"UpdateCommandType="StoredProcedure">

<UpdateParameters>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="SS_VALUE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="SS_TOT"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="COMMENTS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="OCCUPANCY_TYPE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="POST_DATE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="REVISED_BY"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="DRAWINGS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="INVENTORY"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="DIVISION"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="LOCATION"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="LOCATIONCODE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS1"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS2"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS4"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS3"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PROP_ID"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ID"PropertyName="SelectedValue"/>

</UpdateParameters>

</asp:SqlDataSource>

|||

Why is every update parameter a ControlParameter? The parameters that are getting their values from being databound should be a plain "Parameter". Also what is the selectedvalue of detailsview1 in your test? 1?

Please change the parameters from type ControlParameter to type Parameter. If the problem still persists, then launch your app, go to the detailsview, enter into edit mode (So the dropdowns show), then post the HTML that one of the dropdowns that is bound to a field that is submitting a 1 by clicksing Tools->View Source in IE, and copying everything between the <SELECT and </SELECT> tags.

|||

I did what you said. It now no longer passing the null values as 1! Thank you!

However, it is not updating to the database even though I see the parameters updating. I am using the trace feature and the code below to trace the updating/updated parameters.

ProtectedSub Update_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles Update.Updating

For xAsInteger = 0To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

Any Ideas?

EndSub

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

Conversion from type 'DBNull' to type 'String' is not valid.

Here is the code for the updated parameters

ProtectedSub Update_Updated(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles Update.Updated

For xAsInteger = 0To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

EndSub

|||

oops sorry for the last post...let me repost again.

I did what you said. It now no longer passing the null values as 1! Thank you!

However, it is not updating to the database even though I see the parameters updating. I am using the trace feature and the code below to trace the updating/updated parameters.

Here's the code for updating parameters

Protected Sub Update_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles Update.Updating

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

Here is the code for the updated parameters

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

I got an error for the updated parameters code

Conversion from type 'DBNull' to type 'String' is not valid.

Any Ideas?

|||

Help?

It won't update the fields that currently have null values...

Help again? :)

|||Are you getting an error? Is it updating the fields that don't have null values?|||

Yes to both.

I am still having the error that I posted above and the update is also updating one of the columns that didn't have a null value with a null value.

Strangely, enough the update works if there are null values but it will still catch the exception of this error Conversion from type 'DBNull' to type 'String' is not valid when I step into the updated parameters. I would like to fix that so there won't be any problems in the future even though it still works. The another problem is that the update function will update this column named BLDG_SV with a null value even though it had a value, previously.

Thanks!!

|||Is the column named BLDG_SV or BUILDING_SV?|||yes sorry it is BUILDING_SV, forgive me.