how to allow null in database code.

see code for further details

[johnhamman]

Hi all. I have a new question here.
how would i allow this to return null if there is null fields in the table its pulling from

 cA.subcat_posttotal = Convert.ToInt32(Ds.Tables("Subs").Rows(i)("Posttotal").ToString()) ]

I get errors everytime that field is null.

heres the full db code

 Dim sql, Query As String
  Dim sb As New StringBuilder()
  sb.Append("SELECT Akolade_Forum_SubCatList.SubCatID, Akolade_Forum_SubCatList.Title, 
Akolade_Forum_SubCatList.description, Akolade_Forum_SubCatList.Moderator, 
Akolade_Forum_SubCatList.Posttotal, Akolade_Forum_SubCatList.Lastpost, Akolade_Forum_SubCatList.LPby, 
Akolade_Forum_SubCatList.LPThreadID, Akolade_Forum_SubCatList.CatID, Akolade_Members.MemberID, 
Akolade_Members.Username, Akolade_Members.Password")
  sb.Append(" FROM Akolade_Forum_SubCatList LEFT JOIN Akolade_Members ON 
Akolade_Forum_SubCatList.LPby = Akolade_Members.MemberID")
  sb.Append(" WHERE (((Akolade_Forum_SubCatList.CatID)= {0}))")
  Dim InsertString As String = sb.ToString()
  Query = [String].Format(InsertString, catID)
  Dim m_Connection As New SqlConnection(cnStr)
  Dim m_SqlDataAdapter As New SqlDataAdapter(Query, m_Connection)
  Try
  Dim Ds As New DataSet()
  m_SqlDataAdapter.Fill(Ds, "Subs")
  Dim cA As CatStuff
  Sm_NuAnswers = Ds.Tables("Subs").Rows.Count
  ' Place each answer in an array
  Dim i As Integer
  For i = 0 To Sm_NuAnswers - 1
   cA = New CatStuff()
   cA.subCat_ID = Convert.ToInt32(Ds.Tables("Subs").Rows(i)("SubCatID").ToString())
   cA.subcat_title = Ds.Tables("Subs").Rows(i)("Title").ToString()
   cA.subcat_Desc = Ds.Tables("subs").Rows(i)("Description").ToString()
   cA.subcat_mod = Convert.ToInt32(Ds.Tables("Subs").Rows(i)("Moderator").ToString())
   cA.subcat_posttotal = Convert.ToInt32(Ds.Tables("Subs").Rows(i)("Posttotal").ToString())
   cA.subcat_thredmem = Ds.Tables("subs").Rows(i)("Username").ToString()
   cA.subcat_lastpost = Ds.Tables("subs").Rows(i)("Lastpost").ToString()
   cA.subcat_memID = Convert.ToInt32(Ds.Tables("subs").Rows(i)("LPby").ToString())
   cA.subcat_threadid = Convert.ToInt32(Ds.Tables("Subs").Rows(i)("LPThreadID").ToString())
   sCatList.Add(cA)
  Next i
  m_Connection.Close()
  Catch e As Exception
  Throw e
  End Try

[edited by: Xoc at 1:11 am (utc) on June 16, 2002]

[tomasz]

the best way is to use isnull sql function
i.e " isnull(field1,'') as field1 " will return string 0 value not null value and your code will not cause exeption,
or you can use isdbnull function to check for value
i.e
if isbdnull(..reader value ) the
myval=null
else
myval=..reader val..
end if

I prefer to use isnull function or store proc with isnull functions
i hope this will help

[tomasz]

isnull(Akolade_Forum_SubCatList.Posttotal,0) as Posttotal

[Xoc]

When you are trying to work with database Nulls, .NET provides a set of data types that support Null (or actually DBNull) as well as the typical values. You can find these data types in the System.Data.SqlTypes namespace within the .NET framework.

Here are the list of SQL Server data type names and the equivalent .NET data type names from the SqlTypes namespace:

binary SqlBinary
Bigint SqlInt64
Char SqlString
datetime SqlDateTime
decimal SqlDecimal
Float SqlDouble
image SqlBinary
Int SqlInt32
Money SqlMoney
nchar SqlString
Ntext SqlString
nvarchar SqlString
Numeric SqlDecimal
Real SqlSingle
smalldatetime SqlDateTime
smallint SqlInt16
smallmoney SqlMoney
sql_variant Object
sysname SqlString
text SqlString
timestamp SqlBinary
tinyint SqlByte
varbinary SqlBinary
varchar SqlString
uniqueidentifier SqlGuid

[RossWal]

Some databases have a coallesce function. For example,
Coallesce(spareTime, 0) as spareTime

would always return 0 for me

and Coallesce(TodaysProductivity, 'Nada') as productivity

would return Nada. If param1 is null param2 is substituted.