Handling NULL values returned to an ODBC DataReader
You can call the IsDBNull method on the ODBCDataReader (http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdatareader.isdbnull.aspx). You
will need to know the position of the column (0 is first column, etc). Or you can find the position by name using GetOrdinal method (http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdatareader.getordinal.aspx).
I would assume that the column name argument is case sensitive
Something like:
Dim cmd As New System.Data.Odbc.OdbcCommand("Select FirstColumn, SecondColumn from table")
Dim rdr As System.Data.Odbc.OdbcDataReader = cmd.ExecuteReader()
Dim iColFirstColumn As Integer = rdr.GetOrdinal("FirstColumn") ' Alternately could use constant 0
Dim iColSecondColumn As Integer = rdr.GetOrdinal("SecondColumn") ' Alternately could use constant 1
rdr.Read()
If rdr.IsDBNull(iColFirstColumn) And rdr.IsDBNull(iColSecondColumn) Then
End If
Russel Loski, MCT
January 15th, 2011 6:57am
Thanks for the reply.
I have below code in my script component. I would like to apply isnull on
rw.Item("dws_value") column. Please let me know what you find.
'player rank order
If found4.Length > 0
Then
For Each rw
In found4
If CType(Row.playerrankmax,
String) = CType(rw.Item("source_value"),
String) Then
rank_order = CType(rw.Item("dws_value"), Int32)
End If
Next
End If
outputBuffer.AddRow()
outputBuffer.PLAYERKEY = Row.PLAYERKEY
outputBuffer.playeradtcategory = adt_cat
outputBuffer.playeradtorder = adt_order
outputBuffer.playerfirstplaydate = Row.firstplaydate
outputBuffer.playerlastplaydate = Row.lastplaydate
outputBuffer.playerlastplaycategory = last_play_cat
outputBuffer.playerlastplaycategoryorder = last_play_cat_order
outputBuffer.playertype = player_type_desc
outputBuffer.playerrankmax = Row.playerrankmax
outputBuffer.playertiermax = Row.playertiermax
outputBuffer.playertiermaxorder = Row.playertiermaxorder
outputBuffer.playerrankmaxorder = rank_order
End Sub
Public Overrides
Sub PreExecute()
MyBase.PreExecute()
Dim conn As
New OleDb.OleDbConnection(Me.Connections.mardw.ConnectionString)
Dim ole1 As
New OleDb.OleDbDataAdapter("select * from dbo.tab_dw_sys_mapping_between_xref (nolock)", conn)
Dim ole2 As
New OleDb.OleDbDataAdapter("select * from dbo.tab_dw_sys_mapping_xref (nolock)", conn)
ole1.Fill(tab1)
ole2.Fill(tab2)
tab1.Select(Nothing,
"dws_column")
tab2.Select(Nothing,
"dws_column_name")
conn.Close()
End Sub
Public Overrides
Sub PostExecute()
MyBase.PostExecute()
tab1.Dispose()
tab2.Dispose()
End Sub
srinivas
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 7:04am
Thanks for the reply.
I have below code in my script component. I would like to apply isnull on
rw.Item("dws_value") column. Please let me know what you find.
Your code is not using an ODBCDataReader, but rather a DataTable.
rw.IsNull ("dws_value") will return whether the value in that column is null or not.
http://msdn.microsoft.com/en-US/library/system.data.datarow.isnull(v=VS.80).aspx. Try the following:
If rw.IsNull("source_value") = false andalso CType(Row.playerrankmax, String) = CType(rw.Item("source_value"), String) Then
rank_order = CType(rw.Item("dws_value"), Int32)
End If
Russel Loski, MCT
January 15th, 2011 7:24am
Hi All,
I have a SSIS package with script component (SQL Server 2008 R2). I am using sql query to read data inside the script component and writting it to output buffer. But in my source few columns had null values. How Can I handle NULL values inside the
script component for OLEDB source query.
Thank you
Srini
srinivas
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 8:09am
we will find "_isNUll" option for the columns which are comming as input columns to script component not for the select query inside the script component.
But I am using select query inside the script component. For the select query columns I want to use isnull. How Can I check ISNULL functionality?
Thank you
Srini
srinivas
January 15th, 2011 8:19am
Each column in your Script Component should have a related column that ends in "_IsNull" It will tell you if the column is a null.Russel Loski, MCT
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 8:31am