VB.NET - Null value + Datareader question - Asked By Miguel Pires on 12-Jul-05 12:40 PM

Hi

I’m getting this error when I try to fill text boxes in my form,
from a datareder.

(Cast from type 'DBNULL' to type 'STRING' is not valid)

My code:

  ceConn.Open()
        Dim sql As String = "SELECT * FROM APL01 WHERE CLT_COD=" & cod2.Text
        Dim cmd As New SqlCeCommand(sql, ceConn)
        Dim reader As SqlCeDataReader = cmd.ExecuteReader
        reader.Read()

        PRZ_PGT1.Text = reader.Item("PRZ_PGT")
        PLFD.Text = reader.Item("PLFD")
        SUM_RESP.Text = reader.Item("SUM_RESP")
        SUM_CC.Text = reader.Item("SUM_CC")
        LETRASCH.Text = reader.Item("LETRASCH")
        LETRAS_ATE.Text = reader.Item("LETRAS_ATE")
        BLQ_PED.Text = reader.Item("BLQ_PED")
       
 Error-  BLQ_GUIAS.Text = reader.Item("BLQ_GUIAS")
        BLQ_FACT.Text = reader.Item("BLQ_FACT")

  ceConn.close()

 This error occurs because in my SQL table I have null values.

How can I run round this null values problem?

Regardes.

Miguel pires

Use IsDBNull - Asked By Aarthi Saravanakumar on 12-Jul-05 12:59 PM

reader.IsDbNull(ordinal) returns a boolean value true if the column contains a null value.
You need to do check for IsDBNull and assign an empty string or some other default value that you wish.

No luck - Asked By Miguel Pires on 14-Jul-05 03:02 PM

Hi..

I can put this code to work in a compact framework datareader..

Any idea?

Hi find this code to end no luck I real don’t understand.

' Loop through all the rows in the DataTableReader
   While reader.Read()
      If reader.IsDBNull(2) Then
         Console.Write("<NULL>")
      Else
         Try
            Console.Write(reader.GetString(2))
         Catch ex As InvalidCastException
            Console.Write("Invalid data type.")
         End Try
      End If
      Console.WriteLine()
   End While


Thus dataset support null values?

Regardes 
Mpires

sqldatareader for compact framework - Asked By Aarthi Saravanakumar on 14-Jul-05 03:20 PM

does support IsDbNull. What is the exact error message?
Does it print <NULL>?
exact error - Asked By Miguel Pires on 15-Jul-05 04:46 AM
This is the error that I catch when I use my code, end this error popup when I try to fetch the data from my datareder to one textbox.

the exact error message is:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.SqlServerCe.dll

Additional information: No data exists for the row/column.

Resume the code don’t rite anything:

My code:

        ceConn.Open()
        Dim sql As String = "SELECT * FROM APL01 WHERE CLT_COD=" & cod2.Text
        Dim cmd As New SqlCeCommand(sql, ceConn)
        Dim reader As SqlCeDataReader = cmd.ExecuteReader
        reader.Read()


        ' Loop through all the rows in the DataTableReader 
        While reader.Read()
            If reader.IsDBNull("BLQ_FACT") Then
                Console.Write("<NULL>")
            Else
                Try
                    Console.Write(reader.GetString("BLQ_FACT"))
                Catch ex As InvalidCastException
                    Console.Write("Invalid data type.")
                End Try
            End If
            Console.WriteLine()
        End While


        PRZ_PGT1.Text = reader.Item("PRZ_PGT")
        PLFD.Text = reader2.Item("PLFD")
        SUM_RESP.Text = reader2.Item("SUM_RESP")
        SUM_CC.Text = reader2.Item("SUM_CC")
        LETRASCH.Text = reader2.Item("LETRASCH")
        LETRAS_ATE.Text = reader2.Item("LETRAS_ATE")
        BLQ_PED.Text = reader2.Item("BLQ_PED")
        BLQ_GUIAS.Text = reader2.Item("BLQ_GUIAS")
        BLQ_FACT.Text = reader.Item("BLQ_FACT")


'BLQ_FACT.Text is were I have null values


       ceConn.Close()
    End Sub

Regards
ISDBNULL - Asked By Aarthi Saravanakumar on 15-Jul-05 09:22 AM
takes the column ordinal as paramter Not the column name.
Also is it intentional that you ahve a reader.read() and then a while(reader.read())
you are actually reading the second row in the result set by doing this.
Null error when populating text box w/data reader - Asked By K F on 15-Jul-05 11:22 AM
I ran into the exact same problem earlier this week. Try adding .ToString() to the end of the reader item. For example, here is the code I used:

lblSerialNumber.Text() = dtrMaster("SerialNumber").ToString()

The .ToString() method converts it into an empty string if the item is null.
I resolve the problem this way - Asked By Miguel Pires on 18-Jul-05 01:18 PM
I resolve the problem this way:

txt1.Text = IIf(IsDBNull(reader.Item("BLQ_PED")), "", reader.Item("BLQ_PED"))

This way is good?

Regards 

Mpires
tks it work ok - Asked By Miguel Pires on 21-Jul-05 07:00 AM
tks it work ok
it's possible??? - Asked By Miguel Pires on 09-Sep-05 02:25 PM
Hi ...

It’s possible to create a vb.net application that connect to a SQL server 2000 and then create tables and other thins??? 


Regards

Mpires
sure - Asked By Robbe Morris on 09-Sep-05 02:42 PM
Many different ways.  You could just pass the create table sql statements as well as use COM Interop and use the SQL DMO object.