How do I handle SQL null in VB.NET?

My dataset contains NULL - how do I detect that?

Page 1 of 1

6 Replies - 27222 Views - Last Post: 21 January 2009 - 07:41 AM Rate Topic: -----

#1 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

How do I handle SQL null in VB.NET?

Post icon  Posted 19 January 2009 - 01:30 PM

Hi!

I read stuff from a SQL DB (database) using a data set. Some of the columns in the DB may contain NULL. I want to go through the table in the dataset and print, or use in some other way, the data. But when I try to use a certain data that corresponds to a NULL in the DB my program crashes.

How can I detect - in the dataset - that it has no value and that I shouldn't try to use it?

	Private Sub GetÄrendenLista(ByVal e As System.EventArgs)
				'----------------------------------------------------------------------
		' This function fetches a list of Ärende from the table Ärende
		'----------------------------------------------------------------------
		Dim cno As New SqlConnection
		Dim cmd As New SqlCommand
		Dim sda As New SqlDataAdapter
		Dim dset As New DataSet

		cno.ConnectionString = connString
		cmd.Connection = cno
		cmd.CommandText = "SELECT * FROM Ärende ORDER BY id DESC"

		sda.SelectCommand = New SqlClient.SqlCommand(cmd.CommandText)
		sda.SelectCommand.Connection = cno
		sda.Fill(dset)
		For ro As Integer = 0 To dset.Tables(0).Rows.Count
			For co As Integer = 0 To dset.Tables(0).Columns.Count
			   ' If dset.Tables(0).Rows(ro).Item(co)  Then	 <-- What to put here?
					MsgBox(dset.Tables(0).Rows(ro).Item(co))
				End If
			Next
		Next
	End Sub


:)
Regards
/Jens

This post has been edited by jens: 19 January 2009 - 01:37 PM


Is This A Good Question/Topic? 0
  • +

Replies To: How do I handle SQL null in VB.NET?

#2 kasbaba  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 133
  • Joined: 03-November 08

Re: How do I handle SQL null in VB.NET?

Posted 19 January 2009 - 02:01 PM

hi,

try using ISDBNULL ( your Recordset item )

ISDBNULL is a boolean variable and can be used like:

	If NOT ISDBNULL ( dset.Tables(0).Rows(ro).Item(co) ) Then
		MsgBox(dset.Tables(0).Rows(ro).Item(co))
	End If



hope this helps
kasbaba
Was This Post Helpful? 0
  • +
  • -

#3 djjeavons  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 44
  • Joined: 09-January 09

Re: How do I handle SQL null in VB.NET?

Posted 20 January 2009 - 03:45 AM

Hi

While the IsDBNull method will work, it is worth noting that this method resides in the Microsoft.VisualBasic namespace which means that if you were to switch languages then this method would be unavailable to you. However, if you use the classes provided by the .NET Framework, specifically the DBNull class then this can be used in all .NET languages. So with that said, an alternative would be:

If YourDatabaseColumn Is System.DbNull.Value Then
	MessageBox.Show("Database column contains null value")
End If




HTH
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5846
  • View blog
  • Posts: 12,705
  • Joined: 16-October 07

Re: How do I handle SQL null in VB.NET?

Posted 20 January 2009 - 06:03 AM

Lots of ways to check, huh? :P

Personally, I'd use the method provided at the DataRow level, called IsNull. I'd also probably use a few "For Each" rather than "For", but I'm just lazy.

Something like If dset.Tables(0).Rows(ro).IsNull(co) should work.

For the foreach:
Dim dt As New DataTable
Dim row As DataRow
Dim col As DataColumn
'....
For Each row In dt.Rows
	For Each col In dt.Columns
		If Not row.IsNull(col) Then
			MsgBox(row.Item(col))
		End If
	Next
Next


This post has been edited by baavgai: 20 January 2009 - 06:04 AM

Was This Post Helpful? 0
  • +
  • -

#5 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

Re: How do I handle SQL null in VB.NET?

Posted 21 January 2009 - 06:43 AM

Thank you guys for your input! I'll try it when I get home and a few hours to spare... In the meantime I wonder if you could also give me some ideas on how to solve the same (almost) problem when working streambased.

In the code below suppose that in the line ettÄrende.InkommetTid = CDate(rdr.Item("InkommetTid"))
the DB contained NULL in the InkommetTid, since this is forward-only how can I test in this case before trying to cast into a date?

	Private Sub GetÄrendenLista(ByVal e As System.EventArgs)
		'----------------------------------------------------------------------
		'' This function fetches a list of Ärende from the table Ärende
		''----------------------------------------------------------------------
		Dim cno As New SqlConnection
		Dim cmd As New SqlCommand
		Dim rdr As New SqlDataReader
		Dim ant As Integer
		Dim ettÄrende As Ärende

		cno.ConnectionString = connString
		cmd.Connection = cno
		cmd.CommandText = "SELECT * FROM Ärende ORDER BY id DESC"

		Try
			ant = 0
			cno.Open()
			rdr = cmd.ExecuteReader
			While rdr.Read
				ant += 1
				ettÄrende = New Ärende
				ettÄrende.id = CInt(rdr.Item("ID"))
				ettÄrende.HurKritiskt = CShort(rdr.Item("HurKritiskt"))
				ettÄrende.InkommetTid = CDate(rdr.Item("InkommetTid"))
				ettÄrende.ÄrendeStängt = CBool(rdr.Item("ÄrendeStängt"))

				_ärendeLista.Add(ettÄrende)
			End While
		Catch ex1 As Exception
			MsgBox("Fel vid läsning i GetÄrendenLista" + ex1.ToString)
		Finally
			cno.Close()
		End Try

	End Sub



Regards
Jens
Was This Post Helpful? 0
  • +
  • -

#6 djjeavons  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 44
  • Joined: 09-January 09

Re: How do I handle SQL null in VB.NET?

Posted 21 January 2009 - 07:18 AM

Hi

If rdr.Item("InkommetTid") IsNot System.DBNull.Value Then ettÄrende.InkommetTid = CDate(rdr.Item("InkommetTid"))




HTH
Was This Post Helpful? 0
  • +
  • -

#7 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

Re: How do I handle SQL null in VB.NET?

Posted 21 January 2009 - 07:41 AM

Hi, thanks. I'm - at the moment - not in position to test the above solution but I have a vague feeling that it won't work. This is only theoretical but...

Since this is a forward-only read from the DB I believe that it isn't possible to access the reader twice (more than once) for the same piece of data. I will try your suggestion though.

Regards
/Jens

This post has been edited by jens: 21 January 2009 - 07:42 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1