OdbcDataReader and Null Values

OdbcDataReader and Null Values

• Computer-Geek

  
  From ADO.NET, you'll need to perform a null check before retrieving data into your strings if that column could contain null values.  This holds true whether you're using a DataReader or a DataTable to retrieve the results of your query.  There may be options at the query level, like Paul suggested, that could work with your back-end or ODBC driver.
  
  If you provide more information about what you want to do if the column contains null, someone may be able to offer other potential solutions.
  
  

• Michael Walker

  Paul,
  
  Thanks for the tip, and I think it would be excellent it I could get it to work but I am afraid my ODBC driver for COBOL doesn't like it.  My statement is as follows:
  
  

  "SELECT VEHFILE.VEHICLE, VEHFILE.YEAR, VEHFILE.VEHICLE_MAKE, " +
   
  //  refer to the item VEHFILE.VEHICLE_CLASS from this line below
  "VEHFILE.VEHICLE_MODEL, VEHFILE.CLASS_CODE, VEHFILE.VEHICLE_CLASS, " +
  
  "FLTYPEFL.FUEL_TYP_DESC, VEHFILE.DIVISION, DEPTFILE.DEPT_NAME, " + "VEHFILE.PURCHASE_DATE, VEHFILE.INSERVICE_DATE, VEHFILE.GROSS_VOL_WEIGHT " +
  "FROM (VEHFILE LEFT OUTER JOIN FLTYPEFL ON VEHFILE.FUEL_TYPE = FLTYPEFL.FUEL_TYP) " +
  "LEFT OUTER JOIN DEPTFILE ON VEHFILE.DIVISION = DEPTFILE.DEPT_NUMBER " +"WHERE VEHFILE.VEHICLE = AND VEHFILE.FACILITY = ";
  
  I've tried it like this:
  
  ..."VEHFILE.VEHICLE_MODEL, VEHFILE.CLASS_CODE, ISNULL[VEHFILE.VEHICLE_CLASS, someValue], " +
  
  Returns exception:
  ERROR [S0000] [AcuCorp, Inc.][AcuODBC Driver]Column not found: VEHICLE_CLASS
  
  The code works fine if I remove the statement.  FYI, I also tried enclosing the parameters in parentheses (as the docs state) but that returned a lexical element error as follows:
  
  

  ERROR [42000] [AcuCorp, Inc.][AcuODBC Driver]Expected lexical element not found: FROM expected, ( found, near line 1, column 111
  
  Any other ideas  
  

  
  
  
  

  
  

• Partha Datta

  Hi,
  
  If you dont want to have null values in your datareader, then its best to use the ISNULL function in your sql statement. The ISNULL function takes in a field and a replacement for a field if its null. ex:
  
  SELECT ISNULL(name, ' ') FROM myTable
  
  
  
  cheers,
  
  Paul June A. Domag
  
  
  

• Sameer C T

  David,
  
  Well it really depends on the type of value.  If it's an int I am happy with a 0.  if it's a string, I am happy with an empty string ("").  What I've done as a temporary (possibly permanent) solution is built some methods to filter the values through depending on the data type (see below).  I was just hoping to avoid the extra process time and coding but so far it's not slowed it down to where I can notice and the extra coding was minimal.  Now all I have to do is figure out how to create the equivalent of a class library in VWD Express and I'll have it wrapped, but that is another posting all together!  =)  
  
  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=135338&SiteID=1
  
  If you have a better solution I would certainly love to hear it.
  
  Thanks!
  
  P.S.  What's the difference between null and DBNull.Value   Null seemed to work fine with my string objects but when I checked it against an int it flopped.  I have since changed all the if statements to check against DBNull.Value instead and so far, so good. 
  
  
  

  // Converts null string to empty string.

  private string ConvertStr(object strValue)

  {

  string str;

  if (strValue != DBNull.Value)

  {

  str = strValue.ToString();

  }

  else

  {

  str = "";

  }

  return str;

  }

  //  Converts a String to Int and checks for nulls in the process.
  private int ConvertStrToInt(object intValue)

  {

  int numValue;

  if (intValue != DBNull.Value)

  {

  numValue = Convert.ToInt32(intValue);

  }

  else

  {

  numValue = 0;

  }

  return numValue;

  }

  
  
  
  
• OdbcDataReader and Null Values