locked
List View and Dataset ProbleM with DBNULL

    Question

  • Dear Experts,

     

    i am having problems with filling a list view with a dataset which gets the data from the SQL server. the problem occurs when the datarowpicker encounters DBNULL values. Its gives the error " Invalid typecast from DBNULL to string" the code is given below

     

     

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    populate_cmbPersonalDetails()

    End Sub

     

    Private Sub populate_cmbPersonalDetails()

    Dim dsDataSet As DataSet

    Dim drRowPicker As DataRow

    Dim strQuery As String

    Dim sqlConnection1 As New SqlConnection

    Dim sqlDataAdapter1 As New SqlDataAdapter

    Dim sqlSelectCommand1 As New SqlCommand

    sqlConnection1.ConnectionString = strConnectionString

    strQuery = "SELECT DISTINCT StudentSlNo, FirstName, MiddleName, LastName, DateOfBirthEn, GenderEn, Address FROM Tbl_Student"

    dsDataSet = New DataSet

    sqlDataAdapter1.SelectCommand = sqlSelectCommand1

    sqlDataAdapter1.SelectCommand.CommandText = strQuery

    sqlDataAdapter1.SelectCommand.Connection = sqlConnection1

    sqlDataAdapter1.Fill(dsDataSet, "Students")

    lsvStudent.Items.Clear()

    Dim intRowCount As Integer

    intRowCount = 0

    For Each drRowPicker In dsDataSet.Tables("Students").Rows

    Dim strVoterRow As String() = {drRowPicker(0), drRowPicker(1), drRowPicker(2), drRowPicker(3), drRowPicker(4), drRowPicker(5), drRowPicker(6)}

    lsvStudent.Items.Add(New ListViewItem(strVoterRow))

    intRowCount += 1

    Next

    End Sub

     

    Private Sub lsvStudent_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lsvStudent.SelectedIndexChanged

    selectedFlag = True

    Dim selectedStudent As New ListView.SelectedListViewItemCollection(lsvStudent)

    Try

    Dim SlNo As Integer = selectedStudent.Item(0).SubItems.Item(0).Text

    loadDetails(SlNo)

    Catch ex As Exception

    '=MessageBox.Show(ex.Message.ToString, "Error")            ''''' if i dont comment this it gives error  Line1 incorrect syntax near '='

    End Try

    End Sub

     

    Public Sub loadDetails(ByVal SlNo As Integer)

    Dim sqlConnection1 As New SqlConnection

    Dim strQueryString As String

    Dim sqlCommand1 As SqlCommand

    Dim sqlDataReader1 As SqlDataReader

    strQuery = "SELECT DISTINCT StudentSlNo, FirstName, MiddleName, LastName, DateOfBirthEn, GenderEn, Address FROM Tbl_Student WHERE VoterSlNo = " & voterSlNo

    '

    sqlConnection1.ConnectionString = strConnectionString

    sqlCommand1 = New SqlCommand(strQueryString, sqlConnection1)

    sqlCommand1.CommandType = CommandType.Text

    Try

    sqlConnection1.Open()

    sqlDataReader1 = sqlCommand1.ExecuteReader

    If sqlDataReader1.HasRows Then

    Do While sqlDataReader1.Read

    txtSl.Text = sqlDataReader1("SlNo")

    txtFname.Text = sqlDataReader1("FirstName")

    txtMname.Text = sqlDataReader1("MiddleName")

    txtLname.Text = sqlDataReader1("LastName")

    DTPicker1.Value = sqlDataReader1("DateOfBirthEn")

    Loop

    End If

    sqlConnection1.Close()

    Catch MyException As Exception

    MessageBox.Show(MyException.Message.ToString, "Error Loading Details")

    End Try

    End Sub

     

    please help me get this right

     

    Wednesday, June 06, 2007 10:21 AM

Answers

  • When you request a columns value from a datareader it might return DBNull.Value and you should check for that value and handle it.

     

    txtFname.Text = sqlDataReader1("FirstName")

     

    The code above requires that the returned value of sqlDataReader1("FirstName") returns an object that is implicitly convertable to string as txtFname.Text expects a string value. However the value returned is of the generic type of object and may contain any type and if the database column is set to null it will return DBNull.Value that can not be converted to string.

     

    A somewhat quick fix to this would be to get the ToString() value.

     

    txtFname.Text = sqlDataReader1("FirstName") .ToString()

    Thursday, June 07, 2007 10:09 PM

All replies

  • When you request a columns value from a datareader it might return DBNull.Value and you should check for that value and handle it.

     

    txtFname.Text = sqlDataReader1("FirstName")

     

    The code above requires that the returned value of sqlDataReader1("FirstName") returns an object that is implicitly convertable to string as txtFname.Text expects a string value. However the value returned is of the generic type of object and may contain any type and if the database column is set to null it will return DBNull.Value that can not be converted to string.

     

    A somewhat quick fix to this would be to get the ToString() value.

     

    txtFname.Text = sqlDataReader1("FirstName") .ToString()

    Thursday, June 07, 2007 10:09 PM
  • thanks a lot. ..... i got it.......

    Monday, June 11, 2007 9:33 AM