How to handle sqlcommand parameter that could be null?

I have this sql sqlcommand:

string sql = "INSERT Zones (ZName, TypeID, Description) VALUES (@dotnet.itags.org.ZName, @dotnet.itags.org.typeID, @dotnet.itags.org.description)";

command.Parameters.Add("@dotnet.itags.org.description", SqlDbType.NVarChar, 100).Value = description;

The column Description is nullable. But if the variable description is null, it seems the paramter @dotnet.itags.org.description can't be passed correctly.

How can I pass a null parameter to sql server?

Thanks!

You can send DBNull.Value as a value, that is it.

How to handle NULL values in SQL Server

I'm building an ASP.NET application using VB.NET and SQL Sever 2000.

I'm returning data from a table to my datagrid that contains a datetime field. The date values for some of the records are null in the database table. My strategy (be it good or bad) is to update every field in a record during the datagrid's UpdateCommand event, regardless of whether each field's data has changed.

When I update a record whose date was null and has not been modified, SQL Server places a 1/1/1900 value in the date field. I was expecting the datetime field to remain null. Would someone be able to enlighten me on how best to handle this?

My update code looks like this:

Private Sub dgrdEaTask_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgrdEaTask.UpdateCommand
Dim Success As Boolean
Dim intEaId As Integer
Dim txtEaTask As TextBox
Dim txtDueDate As TextBox

txtEaTask = e.Item.Cells(2).Controls(0)
txtDueDate = e.Item.Cells(11).Controls(0)
intEaId = dgrdEaTask.DataKeys(e.Item.ItemIndex)

'Call the update method in the oEaTasks class
Success = oEaTasks.Update(txtEaId.Text, txtEaTask.Text, txtDueDate.Text)

If Success Then
dgrdEaTask.EditItemIndex = -1
BindGrid()
Else
'There was a problem updating the data
End If

End Sub

Thanks!use dbnull.value incase of null...

hth
Thanks!

I'll research it and give it try.

how to handle null values for Templatefield, hyperlink etc...

Nulldisplaytext work for asp:boundfields
<asp:BoundFieldDataField="fax"HeaderText="fax"SortExpression="fax"/>

how to make it work for

<asp:HyperLinkFieldDataNavigateUrlFields="supplierID"DataNavigateUrlFormatString="editSupplier.aspx?supplierID={0}"DataTextField="supplierID"HeaderText="SupplierID">
<ItemStyleHorizontalAlign="Left"/>
</asp:HyperLinkField>

<asp:TemplateFieldHeaderText="Supplier Name"SortExpression="SupplierName">
<ItemStyleHorizontalAlign="Left"/>
<ItemTemplate>
<%#Eval("SupplierName")%>
</ItemTemplate>
</asp:TemplateField>

In a TemplateField, you can use a databinding statement like this (in C#):

<asp:TemplateField>
<ItemTemplate>
<asp:Label runat=server Text='<%# Eval("CustomerID") == null ? "nullDisplayText" : Eval("CustomerID") %>' />
</ItemTemplate>
</asp:TemplateField>

There's no easy way to do this with a HyperLinkField, so consider converting your HyperLinkField to a TemplateField to do the method above.

This is what I use to do to handle null,

select isnull(field,'')as field from table. If i do this do you think it will be an performance issue as I have to do for each an every field.

and what's the replacement for your code in VB

Text='<%# Eval("CustomerID") == null ? "nullDisplayText" : Eval("CustomerID") %>'

bhavin78 wrote:

and what's the replacement for your code in VB

Text='<%# Eval("CustomerID") == null ? "nullDisplayText" : Eval("CustomerID") %>'

Try,

Text='<%# IIF(Eval("CustomerID") is nothing,"nullDisplayText",Eval("CustomerID")) %>'

which one would be better to use as far as performance goes

isnull("data",'') in sql query

or

Text='<%# IIF(Eval("CustomerID") is nothing,"nullDisplayText",Eval("CustomerID")) %>'

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 boolean values from Access

I use a DataReader to retrieve my database (field)information. Everything works fine when working with strings.

Ex.

string data1 = MyReader["FieldName"].ToString();

But now I want to store a boolean value from the database in a variable, but how? Seems stupid, but I cant get it working. It's an Acces database with a yes/no field in the table. I want something like this:

bool data2 = MyReader["FieldName"];

but then asp.net tells me: cant convert object to bool.

What to do...

Hi,

you need to cast it to boolean

bool data2 = (bool)MyReader["FieldName"];

ok, thanks for your (simple) solution! But I do think this is the ugly way. Isn't there a function like ToString() but then for booleans? Or thould I forget the ugly thing about it and simply use a cast?

You'll see lots of casting when working with .NET so get used to it. :-)

You can useConvert.ToBoolean in .NET (Convert is a helper class in the Framework) if it's the prettier way for you

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?