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 insert apostrophe from textbox to database?

Hi experts,

Databases reserve the ' sign to start and end strings. So if user enters a ' (e.g., 'McDonald's') in the company field, the database will give me an error and the insert into is failed. I have lots of textboxes in my asp.net application and how can I handle this exception?

First of all, I retrieved the textbox value from a stored session value:
Dim AppCompany As String = CStr(Session("Company"))

Then I have a SQL commandtext for my Insert Into statement:

Dim dbComm As New SqlClient.SqlCommand
dbComm.Connection = conHRISDb
dbComm.CommandText = "Insert Into Crew_Applicant_Table (First_Name, Last_Name,Main_Phone,App_Street,App_City,App_State,App_Zip,App_Company) Values ( '" & FName & "','" & LName & "','" & AppPhone & "','" & AppStreet & "','" & AppCity & "','" & AppState & "','" & AppZip & "','" & AppCompany & "')
dbComm.CommandType = CommandType.Text

Try usingParametrized queries
Yes, use parameterized queries. But to answer your questionregarding apostrophe, simply append another apostrophe next to yourstring. For example MacDonald's = MacDonald''s.
Insert into company(companyName) value 'MacDonald''s'
will work.
Good Luck

I am trying to insert as a parameterize way, but I got another problem:

On the first page, I stored a textbox value into session value : Session("FromDate1") = txtFromDate1.Text

Then on this page, I retrieve the stored value and take care the null value :

Dim AppFromDate1 As Date
If Session("FromDate1") Is DBNull.Value Then
AppFromDate1 = Nothing
Else
AppFromDate1 = CDate(Session("FromDate1"))
End If

The inserting data part:
dbDetail.Parameters.Add(New SqlParameter("@.AppFromDate1", SqlDbType.SmallDateTime))
sqldatenull = SqlDateTime.Null
If AppFromDate1 = "" Then
dbDetail.Parameters("@.AppFromDate1").Value = sqldatenull
Else
dbDetail.Parameters("@.AppFromDate1").Value = DateTime.Parse(AppFromDate1)
End If

But it throws me an error when AppFromDate1 is null: Cast from string "" to type 'Date' is not valid.
(on Else
AppFromDate1 = CDate(Session("FromDate1"))
End If)

any suggestions?

How is AppFromDate1 declared in code?
It looks as though your Session variable isn't getting populated in thefirst form. For now I would just pass the FromDate value as a URLparameter and extract it via a Request.QueryString. If this worksyou will at least know that your SQL is valid.

I would try the following:
1) Load Session variables into String variables
dim fromDate1Str as String = Session("FromDate1")
2) Cast to Date only if String variable is not empty
if fromDate1Str <> String.Empty then
dim AppFromDate1 = CType(Session("FromDate1"),Date)
end if
Good Luck

I have took care of the seesion variable in the first form:

If txtFromDate1.Text.Trim().Length = 0Then

Session("FromDate1") = DBNull.Value

Else

Session("FromDate1") = txtFromDate1.Text

EndIf

In the 2nd form, I retrieve the value:

If Session("FromDate1")Is DBNull.ValueThen

AppFromDate1 =Nothing

Else

AppFromDate1 =CDate(Session("FromDate1"))

EndIf

HOWEVER, I found the null session date value becomes "#12:00:00AM#" even AppFromDate1=Nothing dosen't set the AppFromDate1 to null but to a string value.

Then I take care the parameter value by:

sqldatenull = SqlDateTime.Null

If AppFromDate1 =NothingThen

dbDetail.Parameters("@.AppFromDate1").Value = sqldatenull

Else

dbDetail.Parameters("@.AppFromDate1").Value = DateTime.Parse(AppFromDate1)

EndIf

But dbDetail.Parameters("@.AppFromDate1").Value = sqldatenull doesn't do the job either, it still try to insert "#12:00:00AM #"

Please someone help me!

If I try to load session variable into String variable: dim fromDate1Str as String = Session("FromDate1")
, I will get an error:

Cast from type 'DBNull' to type 'String' is not valid

you can also do an

If fromDate1Str.Trim.Length = 0 then

'pass the sqldatenull

else

'pass the actual value

end if

In the first form, I stored session variable to:
If txtFromDate1.Text.Trim().Length = 0 Then
Session("FromDate1") = DBNull.Value
Else
Session("FromDate1") = txtFromDate1.Text
End If

Then I retrieve the variable in the 2nd form:

Dim AppFromDate1 As Object
Dim sqldatenull As SqlDateTime
sqldatenull = SqlDateTime.Null
If AppFromDate1.Trim.Length = 0 Then
AppFromDate1 = sqldatenull
Else
AppFromDate1 = (Session("FromDate1"))
End If

I received the error:
Object variable or With block variable not set on

If AppFromDate1.Trim.Length=0

Any Suggestions?

>>Dim AppFromDate1 As Object
what kind of object is it ? a string, an int ?
you might want to checkthis article

Here is what I would do:
1) In form 1, initialize Session("FromDate1") to string.empty and setSession("FromDate1") to txtFromDate1.Text.ToString only iftxtFromDate1.text <> string.empty
2) In form 2 replace your parameterized Insert SQL statement witha parameterized Insert store procedure. This will providebenefits in the area of performance, security and flexibility as youwill see.
3) In your store procedure indicate which parameters are optional in TSQL by initializing them to null
4) In form 2 check whether Session("FromDate1") = string.empty. If it does, do not add the FromDate1 parameter to the parameterlist ofyour Command object. If it is not equal to string.empty then addthe FromDate1 parameter to the parameterlist.
I've done this before and it should work - provided you properlyidentified your optional paramaters in your TSQL store procedure. In a nutshell here's what should happen: If the FromDate1 parameter isnot added to the parameterlist, your store procedure will use thedefault value that you defined otherwise it should read the value fromyour Session variable and process normally.
Good Luck

How to handle DBNull DataType for Ms access ??

Hi,

I have problem in using this function to check if the field is empty or null :

Let say , I have connect to a Database and set up Dataset :

Dim Ds as dataset

Dim StrAddr as string

If IsDBNull( DS.tables("tblName").rows(0).item("Address")) then

LbMsg.text =" No address entered."

else

StrAddr = DS.tables("tblName").rows(0).item("Address"))

end if

I have error msg : Can not cast DBNull to string.

All I wanted is to check the field address to see if it is empty or no data entered.

Please help.

How to check ? How to handle if the field has nothing in it at all. How to use IsDBNull() ?

Thanks in advance.

Try this:

StrAddr = DS.tables("tblName").rows(0).item("Address").ToString

If StrAddr ="" then StrAddr = "No Address Entered"

Tim

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