By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,016 Members | 2,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,016 IT Pros & Developers. It's quick & easy.

VB/ASP.NET Selecting Null Values

P: 3
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
Share this Question
Share on Google+
5 Replies


balabaster
Expert 100+
P: 797
Try looking up System.DBNull.Value...

If Not MyValue Is System.DBNull.Value Then do something...
Nov 18 '08 #2

P: 3
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
Expert 100+
P: 797
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
Expert 100+
P: 569
How about

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

Steven
Nov 19 '08 #5

P: 3
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.