Hi,i'm a new member here.
I'm working on the database which use ODBC as the connection object.
Somehow the datasource that i need to read have the Year column that have data type smallint (equals to int16 in ADO.NET). Unfortunately the smallint data type is not returning the right value , in my case instead of returning the value 1998,1999 or 2000 it return the value 48 for all year. If i'm to use the standard Fill method of ODBCDataAdapter object, i'm getting stuck because there are no way we can do the casting/converting when the Fill method is invoke.
For this case i have simulate the Fill method of ODBCDataAdapter by creating the wrapper which actually use the ODBCDataReader inside to read the content of the table(s). Every time the reader advance to next column i instruct the method to do the conversion. The code looks like below:
     Public Shared Sub FillDataSet(ByRef daName As OdbcDataAdapter, ByVal tableToFill As String, ByRef dsName As DataSet)
        Dim rdr As OdbcDataReader
        Dim shortCol As Short
        Dim schema, table, tbl As DataTable
        Dim tblName As ArrayList
        Dim drSchema, drData As DataRow
        Dim i As Integer
        Dim firstRow As Boolean = True
        rdr = daName.SelectCommand.ExecuteReader()
        While rdr.Read()
            If firstRow Then
                If Not dsName.Tables.Contains(tableToFill) Then
                    table = New DataTable(tableToFill)
                    schema = rdr.GetSchemaTable()
                    For Each drSchema In schema.Rows
                        table.Columns.Add(drSchema("ColumnName").ToString, drSchema("DataType"))
                    Next drSchema
                    dsName.Tables.Add(table)
                End If
                tbl = dsName.Tables(tableToFill)
                firstRow = False
            End If
            drData = tbl.NewRow()
            Dim resultStr As String
            For i = 0 To tbl.Columns.Count - 1
                If tbl.Columns(i).DataType.Name = "Int16" Then
                    Try
                        drData.Item(i) = Convert.ToInt16(rdr.GetInt32(i))
                    Catch ex As Exception
                        ' do nothing
                    End Try
                Else
                    Try
                        drData.Item(i) = rdr.Item(i)
                    Catch ex As Exception
                        ' do nothing
                    End Try
                End If
            Next i
            tbl.Rows.Add(drData)
        End While
        rdr.Close()
    End Sub

Above code are working fine for the int16 column which is not empty/null. But if it is null it'll throw an exception, so to prevent this i use the IsDBNULL to check against the content of the column. But the result is worst, because if i put the checking for null value all the conversion like below won't work even the content of column is not null.
 ...
 drData.Item(i) = Convert.ToInt16(rdr.GetInt32(i))
 ...
Could someone tell me if i'm doing wrong or is there any way to solve my problem other than to have simulate the Fill method like i do with above code.
Please i need your help.
Thanks
Rusli

I am new too!
How do you check against Null value?
One way to go around checking for null is to cast it into string like so:
if ("" & SomeVariantVar) = "" then

I don't know if this will work for your case.

Cheers.

The way i do the checking against the null value is like below:

' if the column have a content then convert it
if Not rdr.IsDBNull(i) then ...
     Try
      drData.Item(i) = Convert.ToInt16(rdr.GetInt32(i))
     Catch ex As Exception
          ' do nothing
     End Try
end if
above code will always throw an exception regardless Null or not null.
By now i take out the checking for null value and it works fine for the not null column, because when the column content is null it will throw an exception that's why i didn't put any message when it throw an exception. But it delay the process a little bit. What i want is to make the conversion smooth.

i've try your code but still it throw the same exception as
"Specified cast is not valid." But thanks anyway pal : )

Thanks in advance,
rusli

Check this link

http://msdn.microsoft.com/library/de...snulltopic.asp