How to handle null value mapping in SqlDataSource

Hi, guys

If I pass null value to a parameter of SQL automatically generated by SqlDataSource (as WHERE field1=@dotnet.itags.org.field1, where field1 is of type ntext or nvarchar) at design-time, it seems SqlDataSource can not update appricately, throwing an exception: [SqlException (0x80131904): The data types ntext and nvarchar are incompatible in the equal to operator.],

even though the field1 in the db table is null-allowable.

How do I handle the situation where I must pass null value to update any field permitting null value.

Thanks,

Ricky.

What's the type of @.field1, and what are you setting it to? Post the relevant section of your code.

How to Handle Null Value from <%# Bind(...

I am trying to retrieve the values for a selected row in a FormView to allow editing. One of the fields ("photo") can be null and when the following statement gets executed it throws an error that it cannot convert a null to an integer.

<gs:PhotopickerID="Photopicker1"runat="server"ImageId='<%# Bind("photo") %>'/>

I recall that in 1.1 I could replace the olde EVAL with a function that could check for a null and return a zero instead? Can that be done here, and if so, what is the syntax?

Hi Wayne,

Bind is a two-way data operation -- is that what you are trying to use with this?

Otherwise, you could just do an <%# Eval( CallSomeFunction("photo")) %> and define the "CallSomeFunction" (or another name) on your page or control to convert it to an appropriate value.

Alternatively, you could define ImageId on the control to accept null and handle the conversion there.

Hope this helps,

Scott

I am actually trying to modify the Club Starter Kit so most of the original code is from that kit. I did figure out that Bind was a 2 way process and I believe that the way that is written, the 2 way binding is necessary. I guess I may have to re-code that whole section?

Could you expand on defining Imageid on the control? I am not familiar with that approach.

Assuming you are talking about the control defined in photos_formpicker.ascx:

The easiest fix is to add a new property to the control for the ImageID, and have it take object rather than string. You could change the type on the existing property, but you'd then also have to change the code throughout the control. This way you just need to change the property name used to bind to the database from ImageID to PhotoImageID:

constint DEFAULTIMAGEID = 0;

privateint ImageId

{

get

{

EnsureChildControls();

if (viewswitch.ActiveViewIndex == 1)

{

returnConvert.ToInt32(selectedimage.Value);

}

else

{

object id = ViewState["ImageID"];

if (id ==null)

{

ViewState["ImageID"] = DEFAULTIMAGEID;

return DEFAULTIMAGEID;

}

else

{

returnConvert.ToInt32(id);

}

}

}

set { ViewState["ImageID"] =value; }

}

publicobject PhotoImageID

{

get {return ImageId; }

set { ViewState["ImageID"] = (value ==DBNull.Value) ? DEFAULTIMAGEID :Convert.ToInt32(value); }

}

Thank you very much. That is all a little beyond my current understanding so I'll have to do a bit of reading to make sure I impliment it correctly.

I appreciate the very complete response.

How to handle null value

I got trouble with System.Null.ReferenceException. Please guide me how to manipulate it.

Below is my error code line, when the result of my query returns null value.

string FATypeName = SelectCommand.ExecuteScalar().ToString().Trim();

Thanks,

string FATypeName = SelectCommand.ExecuteScalar()
if ( FATypeName!=null )
{
FATypeName=FATypeName.ToString().Trim();
}
else
{
FATypeName=String.Empty;
}

How to handle a integer null value in a (.xsd) dataset

Hi.

I want to pass a null value to an integer in a (stored) dataset. The column in the database allows null values, but the dataset throws an error. (VS2005)

Goos van Beek

If column in DataTable allows NULLs, you could assing value using DBNull.Value, not Null

Thanks for responding, Val.

How can I pass a DBNull value to the dataset? The user deletes an integer value in a bound textfield and the result is a frozen application.

Of course I can use unbound fields, which I usually do, but I have chosen for the easy way this time :-)

Goos van Beek.

In a case of binding, I do not think you have much control, but I believe application should not freeze in this case anyway. Do you get any exception? Do you trap any exceptons but do not handle them? In a case of unbound controls, you could assign value using next code, but you have to know index of the row that require the change

MyDataTable.Rows(IndexOfTheRowHere).Items("MyColumn)=DBNull.Value

Actually the application doesn't freeze, but the cursor can't leave the field until there is a integer value typed in the field. And that's the problem when the value should be null (or dbnull)

I know the record index, but i can't update a single value of that row.

this.MyDataSet.Tables[0].Rows[idx].Items["MyFieldName"] = DBNull.Value; doesn't work, because the there is no defenition for 'Items'

I can update the value in the underlaying database table, but not in the dataset.

Goos van Beek.

uhmm... (a little idea...i don't now how it fits your needs)...

you can set in design time a default value for your column...this value is never used by your application (e.g. invoice total value cannot be negative and you can use -1).

In runtime when the user clean the column you can set somthing like this

this.MyDataSet.Tables[0].Rows[idx].Items["MyFieldName"] = myDataset.MyTable.MyColumn.DefaultValue

Thanks for responding, Bob.

Sometimes you don't want a default value, although not in the userform.

I changed the datatype in the dataset from System.Int32 to System.String. This allows me to enter a null value in the field. The value in the table is updated by a SqlCommand. Only this is not what I want...

this.MyDataSet.Tables[0].Rows[idx].Items["MyFieldName"] = myDataset.MyTable.MyColumn.DefaultValue; doesn't work, because the there is no defenition for 'Items'

Isn't there a way to adapt the default behaviour of the dataset to handle null values for an integer?