469,126 Members | 1,288 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

VB/ASP.NET Selecting Null Values

Hi I am working with a web application where I am selecting values from a SQL Server 2005 database and then loading the values into different controls on my page. Most of the values load with no problem, except for those columns where the value of the selected row in the column could be a null. I get the following error message:

The value for column 'COLUMN NAME' in table 'TABLE NAME' is DBNull.

I've tried in my Sql select statement to use the following: IsNull(Column_Name, ' '), but I still get the error message. Can anyone tell me how to get around this error message?

Here is my web service with my ADO.NET code:

Expand|Select|Wrap|Line Numbers
  1.  <WebMethod()> _
  2.     Public Function WebService2(ByVal SessionID As Integer, ByVal SessionMonth As String, ByVal SessionType As String, ByVal Program As String, ByVal PilotEvaltrID As Integer, ByVal SOEvaltrID As Integer, ByVal MinLevel As Integer, ByVal IssuedCA As Boolean, ByVal IssuedFO As Boolean, ByVal IssuedSO As Boolean, ByVal FltNbr As Integer, ByVal FltZDate As String, ByVal Path As String) As DataSet1
  3.  
  4.         ' Creates a new DataSet, a connection to data source named sqlConn, and a data adapter named sqlDA
  5.         Dim DS2 As DataSet1
  6.         Dim sqlConn As SqlConnection
  7.         Dim sqlDA As SqlDataAdapter
  8.         Dim value As Boolean
  9.  
  10.  
  11.         ' creates a new connection to DB
  12.         sqlConn = New SqlConnection("Data Source=S11694;Initial Catalog=svtppdbSQL;Integrated Security=True")
  13.  
  14.         sqlConn.Open()
  15.  
  16.         ' New instance of sqlDA used to fill the DataSet
  17.         sqlDA = New SqlDataAdapter("select SessionMonth, SessionType, Program, PilotEvaltrID, SOEvaltrID, MinLevel, IssuedCA, IssuedFO, IssuedSO, FltNbr, FltZDate from tblSessionID where SessionID = '" & SessionID & "'", sqlConn)
  18.  
  19.         ' New instance of DataSet
  20.         DS2 = New DataSet1
  21.  
  22.         'Disables EnforceConstraints
  23.         value = DS2.EnforceConstraints
  24.         DS2.EnforceConstraints = False
  25.  
  26.  
  27.         ' fills the DataAdapter object with information from tblSessionID
  28.         sqlDA.Fill(DS2.tblSessionID)
  29.  
  30.         ' Returns the DataSet for the function
  31.         Return DS2
  32.  
  33.         sqlConn.Close()
  34.  
  35.     End Function
  36.  
And then this is the code where I call the web service and populate the controls:

Expand|Select|Wrap|Line Numbers
  1. Try
  2.  
  3.             ' Declares variable TempPath and creates new instance of clsWebServices serviceObj
  4.  
  5.             Dim TempPath As String
  6.  
  7.             Dim serviceObj As New clsWebServices
  8.  
  9.             ' creates new instance of DataSet 
  10.  
  11.             Dim dsFind2 As New DataSet1
  12.  
  13.  
  14.             TempPath = Server.MapPath("svtppdbSQL.mdf") 'Creates specifies MapPath for variable TempPath to svtppdbSQL
  15.  
  16.             ' Specifies search criteria 
  17.  
  18.             dsFind2 = serviceObj.WebService2(Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, TempPath)
  19.  
  20.  
  21.             'Returns rows > than 0 in tblSessionID 
  22.  
  23.             If dsFind2.tblSessionID.Rows.Count > 0 Then
  24.  
  25.                 ' Displays information in specified text boxes
  26.  
  27.                 Me.txtSessionMonth.Text = dsFind2.tblSessionID(0).SessionMonth.ToShortDateString()
  28.  
  29.                 Me.txtSessionType.Text = dsFind2.tblSessionID(0).SessionType.ToString
  30.  
  31.                 Me.txtSessionType.Text = dsFind2.tblSessionID(0).SessionType.ToString
  32.  
  33.                 Me.txtProgram.Text = dsFind2.tblSessionID(0).Program.ToString
  34.  
  35.                 Me.lblPilotEvaltrID.Text = dsFind2.tblSessionID(0).PilotEvaltrID.ToString
  36.  
  37.                 Me.lblSOEvaltrID.Text = dsFind2.tblSessionID(0).SOEvaltrID.ToString
  38.  
  39.                 Me.MinLevel.Text = dsFind2.tblSessionID(0).MinLevel.ToString
  40.  
  41.                 Me.IssCA.Checked = dsFind2.tblSessionID(0).IssuedCA.ToString
  42.  
  43.                 Me.IssFO.Checked = dsFind2.tblSessionID(0).IssuedFO.ToString
  44.  
  45.                 Me.IssSO.Checked = dsFind2.tblSessionID(0).IssuedSO.ToString
  46.  
  47.                 Me.txtFltNbr.Text = dsFind2.tblSessionID(0).FLTNbr.ToString()
  48.  
  49.                 Me.txtFltZDate.Text = dsFind2.tblSessionID(0).FltZDate.ToString
  50.  
  51.                 lblMessage.Text = ""
  52.  
  53.             Else
  54.  
  55.                 lblMessage.Text = "No records were found!!"
  56.  
  57.             End If
  58.  
  59.  
  60.         Catch ex As Exception
  61.  
  62.             ' Displays any error messages that may occur at runtime
  63.  
  64.             lblMessage.Text = ex.Message
  65.  
  66.         End Try
  67.  
There are 4 columns where the value can be null: PilotEvaltrID, SOEvaltrID, FltNbr, FltZDate.

Can anyone offer me a solution?
Nov 18 '08 #1
5 1472
balabaster
797 Expert 512MB
Try looking up System.DBNull.Value...

If Not MyValue Is System.DBNull.Value Then do something...
Nov 18 '08 #2
Could you be a little more specific? Like maybe show me an example? When I try the following in my click event I still get the error message:

If Not dsFind2.tblSessionID(0).FLTNbr.ToString Is System.DBNull.Value Then


Me.txtFltNbr.Text = dsFind2.tblSessionID(0).FLTNbr.ToString


End If
Nov 18 '08 #3
balabaster
797 Expert 512MB
Could you be a little more specific? Like maybe show me an example? When I try the following in my click event I still get the error message:

If Not dsFind2.tblSessionID(0).FLTNbr.ToString Is System.DBNull.Value Then


Me.txtFltNbr.Text = dsFind2.tblSessionID(0).FLTNbr.ToString


End If
Are you trying to run the ToString() method on a value that doesn't exist?

Try removing the ToString in your IF...

If Not dsFind2.tblSessionID(0).FLTNbr Is System.DBNull.Value Then...

and seeing if your code still crashes...
Nov 18 '08 #4
MrMancunian
569 Expert 512MB
How about

[code]
If Not IsDBNull(dsFind2.tblSessionID(0).FLTNbr) Then
...
End If
[code]

Steven
Nov 19 '08 #5
I have tried both of those, and neither work. But, I did figure out the answer for anyone else who might read this and is having the same problem. I needed to use a DataReader like this:

Expand|Select|Wrap|Line Numbers
  1. Dim cn As SqlConnection
  2.  
  3.         Dim cmd As SqlCommand
  4.  
  5.         Dim rdr As SqlDataReader
  6.  
  7.  
  8.         Try
  9.  
  10.             cn = New SqlConnection("Data Source=(local);Initial Catalog=db1;Integrated Security=True")
  11.  
  12.             cmd = New SqlCommand("select SessionMonth, SessionType, Program, PilotEvaltrID, SOEvaltrID, MinLevel, IssuedCA, IssuedFO, IssuedSO, FltNbr, FltZDate from tblSessionID where SessionID = '" & ddlSessionID.SelectedValue & "'", cn)
  13.  
  14.             cn.Open()
  15.  
  16.             rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
  17.  
  18.             rdr.Read()
  19.  
  20.             txtSessionMonth.Text = rdr("SessionMonth").ToString()
  21.  
  22.             txtSessionType.Text = rdr("SessionType").ToString()
  23.  
  24.             txtProgram.Text = rdr("Program").ToString()
  25.  
  26.             lblPilotEvaltrID.Text = rdr("PilotEvaltrID").ToString()
  27.  
  28.             lblSOEvaltrID.Text = rdr("SOEvaltrID").ToString()
  29.  
  30.             MinLevel.Text = rdr("MinLevel").ToString()
  31.  
  32.             IssCA.Checked = rdr("IssuedCA").ToString()
  33.  
  34.             IssFO.Checked = rdr("IssuedFO").ToString()
  35.  
  36.             IssSO.Checked = rdr("IssuedSO").ToString()
  37.  
  38.             txtFltNbr.Text = rdr("FltNbr").ToString()
  39.  
  40.             txtFltZDate.Text = rdr("FltZDate").ToString()
  41.  
  42.             rdr.Close()
  43.  
  44.             cn.Close()
  45.  
  46.         Catch ex As Exception
  47.  
  48.             lblMessage.Text = ex.Message
  49.  
  50.  
  51.         End Try
  52.  
Once I did this I no longer the error message. I think it was a very sloppy way I was trying to select my data. Thanks for everyones help.
Nov 19 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by karthik | last post: by
1 post views Thread by Sunny K | last post: by
7 posts views Thread by raj | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.