VB.NET - Null value + Datareader question

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

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.

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

does support IsDbNull. What is the exact error message?
Does it print <NULL>?

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

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.

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:

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

This way is good?

Regards 

Mpires

tks it work ok

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

Many different ways.  You could just pass the create table sql statements as well as use COM Interop and use the SQL DMO object.