how to convert a NULL smalldatetime in sql query?

[Lisana]

I have a data type is smalldatetime in the sql table, when I bind to my DataTable in VB.NET, when there is value in this column, the output is fine, but when it's NULL, then it gets error of the data type. In the sql, if it's string, I do ISULL(firstname, '') AS firstName inside sql query, then the output is fine, but I don't know how to convert the null smalldatetime. Is there any idea? Lisa

[Colin Angus Mackay]

In your .NET application check the value of that column before using it. If it is a NULL in the database it will be System.DBNull.Value in the .NET application - You can then choose what to do about that as required by your application.

---

My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

[Lisana]

I'm doing the OleDbDataReader in the vb appl. THis is my code: Dim OrderDate as date Dim DataAdapter As New OleDbDataAdapter Dim DataReader As OleDbDataReader **connecting*** **sql query** Select dateOrder from orderTable *** DataReader = DataAdapter.SelectCommand.ExecuteReader() Do While DataReader.Read() OrderDate = DataReader.GetDateTime(0) Loop lblOrderDate.text = OrderDate Where should I check the value of the column before using it? If the dateOrder is Null, then this OrderDate = DataReader.GetDateTime(0) will get error. THanks Lisa

[Colin Angus Mackay]

Okay - You are using a SqlDataReader, I assumed you had a dataset. By the way, if you are extracting data through a data reader then using a data adapter is redundant. Just build the SqlCommand object and use that. Anyway, I've modified your code:

DataReader = DataAdapter.SelectCommand.ExecuteReader()
Do While DataReader.Read()
If Not DataReader.IsDBNull(0) Then
OrderDate = DataReader.GetDateTime(0)
Else
' Do something else because the data time is null
End If
Loop

Does this help? Disclaimer - I typed this directly. There may be typos, syntax errors, etc.

---

My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

[Lisana]

Thanks! it works much better.. but there is one more thing. how can I covert a Date type to a String type in vb? The OrderDate is Date type, and I want it to be OrderDate= "" when it's NULL. Lisa

[Colin Angus Mackay]

For example:

myDateTimeObject.ToString("dd/MM/yyyy")

You can find more information about the format options on MSDN[^]

---

My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

[Lisana]

it won't work when I do this... Dim OrderDate as String inside DataRead If Not DR.IsDBNull(0) Then orderDate = DR.GetDateTime(0).ToString("MM/dd/yyyy") Else orderDate = "" ***here is not working*** End If any idea? Lisa

[mtone]

Try orderDate = IIf(IsDBNull(DR.GetDateTime(0)),"",DR.GetDateTime(0)) IIF(expression,Truepart,falsePart)

[Lisana]

I tried your code, but the output is like this: when the GetDateTime(0) has value, then it output empty, run time is fine, but if GetDateTime(0) is Null, then it has error "Specified cast is not valid" do you know why? thanks. Lisa

[mtone]

I noticed that you are trying to put the datetime into a string variable. Since your variable is not a date do not use GetDateTime. orderDate = IIf(IsDBNull(Convert.ToString(DR(0))),"",Convert.ToString(DR(0))) DR(0) should match the column or use the Column Name DR("COL_NAME") Or try to use a non Date datatype orderDate = IIf(IsDBNull(Convert.ToString(DR.GetValue(0))),"",Convert.ToString(DR.GetValue(0))) play around with this.