testing for Null in dataReader Solved/Closed

Question

Hello, 
please when i execute my query i also get error for those null column how can i test for this please find my code below i have 'commented' the code i tried using to check for null that didn't work.
 With CMD
                .Connection = con
                .CommandType = CommandType.Text
                CMD.CommandText = ("select * from student_info Where Surname = '" & txt_serach.Text & "' ")
            End With
            DataR = CMD.ExecuteReader 'new
            DataR.Read()

            'If 
            'If Not DataR Is Nothing Then
            If DataR.HasRows = True Then
                
                    'If DataR!surname = False Then
                    Student_Info.txtSurname.Text = DataR!Surname
                    'Else
                    '    Student_Info.txtSurname.Text = ""
                    'End If
                    'If DataR!first_name = False Then
                    Student_Info.TxtfirstName.Text = DataR!first_name
                    'Else
                    '    Student_Info.TxtfirstName.Text = ""
                    'End If
                    'If DataR!matric_number = False Then
                    Student_Info.txtMatricNum.Text = DataR!matric_number
                    'Else
                    '    Student_Info.txtMatricNum.Text = ""
                    'End If
                    'If DataR!level = False Then
                    Student_Info.TxtLevel.Text = DataR!level
                    'Else
                    '    Student_Info.TxtLevel.Text = ""
                    'End If
                    'If DataR!birth_day = False Then
                    Student_Info.txtBirthday.Text = DataR!birth_day
                    'Else
                    '    Student_Info.txtBirthday.Text = ""
                    'End If
                    'If DataR!sex = False Then
                    Student_Info.TxtSex.Text = DataR!sex
                    'Else
                    '    Student_Info.TxtSex.Text = ""
                    'End If
                    'If DataR!height = False Then
                    Student_Info.txtheight.Text = DataR!height
                    'Else
                    '    Student_Info.txtheight.Text = ""
                    'End If
                    'If DataR!state = False Then
                    Student_Info.TxtState.Text = DataR!state
                    'Else
                    '    Student_Info.TxtState.Text = ""
                    'End If
                    'If DataR!room_no = False Then
                    '    Student_Info.txtRoomNo.Text = DataR!room_no
                    'Else
                    '    Student_Info.txtRoomNo.Text = ""
                    'End If
                    'If DataR!picture = False Then
                    Student_Info.TxtPicturePath.Text = DataR!picture
                    'Else
                    '    Student_Info.TxtPicturePath.Text = ""
                    'End If
                    'If DataR!Home_Address = False Then
                    Student_Info.TxtHomeadd.Text = DataR!Home_Address
                    'Else
                    '    Student_Info.TxtHomeadd.Text = ""
                    'End If
                    'If DataR!finger_print = False Then
                    Student_Info.txtfingerPrint.Text = DataR!finger_print
                    'Else
                    '    Student_Info.txtfingerPrint.Text = ""
                    'End If
                    DataR.Close()

                   
                    img = Student_Info.TxtPicturePath.Text
                    Student_Info.PictureBoxPic.Image = System.Drawing.Bitmap.FromFile(img)
                    Student_Info.ShowDialog()
            Else
                    MessageBox.Show("Friend's First Name doesn't exist")

                    ClearAll()
                    DataR.Close()
                End If



Configuration: Windows 7 / Firefox 16.0

Zohaib R · Answer

Hi mikeoe2003,

As I have worked with you earlier, I will assist you further; a little clarification will help here. As you have commented multiple lines in the code, the code is hardly legible. Request you to update the original code starting from beginning (including your declarations such as "Dim CMD As New OleDbCommand" in your post). If you want to send the code as attachment, you can use the below mentioned site:

https://authentification.site

Note: Before you upload any files please make sure there is no personal or confidential information contained in it.

Please do write back to us.

mikeoe2003 · Answer

dear Zohaib sorry for the late response, internet is so bad in my country Nigeria, i have worked on the code a little bit and my problem is that retrieving image from the database is giving me problems.

Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Drawing

Public Class Select_Form
    


    Private Sub btn_search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_search.Click
        Try
            Dim frm As New Student_Info
            Dim img As String
            Dim img2 As String
            Dim DataR As OleDb.OleDbDataReader  
            Dim CMD As New OleDb.OleDbCommand   
            Dim con As New OleDbConnection
            
            con.ConnectionString = _
               "provider=Microsoft.JET.OLEDB.4.0; " & _
               "data source = '" & Application.StartupPath & "\school_record.mdb'"

            
            con.Open()
            Dim ds As New DataSet
            Dim dt As New DataTable
            ds.Tables.Add(dt)
            Dim da As New OleDbDataAdapter
            If txt_serach.Text = vbNullString Then 
                MessageBox.Show("please enter surname") 
            End If
                        
With CMD
                .Connection = con
                .CommandType = CommandType.Text
   CMD.CommandText = ("select * from student_info Where Surname = '" & txt_serach.Text & "' ")
            End With
            DataR = CMD.ExecuteReader 
            DataR.Read()

            
            If DataR.HasRows = True Then


                If Not IsDBNull(DataR("Surname")) Then
                    frm.txtSurname.Text = DataR!Surname
                Else
                    frm.txtSurname.Text = ""
                End If

                If Not IsDBNull(DataR("first_name")) Then
                    frm.TxtfirstName.Text = DataR!first_name
                Else
                    frm.TxtfirstName.Text = ""
                End If
                If Not IsDBNull(DataR("matric_number")) Then
                    frm.txtMatricNum.Text = DataR!matric_number
                Else
                    frm.txtMatricNum.Text = ""
                End If
                If Not IsDBNull(DataR("level")) Then
                    frm.TxtLevel.Text = DataR!level
                Else
                    frm.TxtLevel.Text = ""
                End If
                If Not IsDBNull(DataR("birth_day")) Then
                    frm.txtBirthday.Text = DataR!birth_day
                Else
                    frm.txtBirthday.Text = ""
                End If
                If Not IsDBNull(DataR("sex")) Then
                    frm.TxtSex.Text = DataR!sex
                Else
                    frm.TxtSex.Text = ""
                End If
                If Not IsDBNull(DataR("height")) Then
                    frm.txtheight.Text = DataR!height
                Else
                    frm.txtheight.Text = ""
                End If
                If Not IsDBNull(DataR("state")) Then
                    frm.TxtState.Text = DataR!state
                Else
                    frm.TxtState.Text = ""
                End If
                If Not IsDBNull(DataR("room_no")) Then
                    frm.txtRoomNo.Text = DataR!room_no
                Else
                    frm.txtRoomNo.Text = ""
                End If
                If Not IsDBNull(DataR("id_no")) Then
                    frm.txtIdNo.Text = DataR!id_no
                Else
                    frm.txtIdNo.Text = ""
                End If
			'IMAGE (PICTURE), THIS IS WHERE THE PROBLEM IS

                If Not IsDBNull(DataR("picture")) Then
                    frm.TxtPicturePath.Text = DataR!picture
                    img = frm.TxtPicturePath.Text
                    frm.PictureBoxPic.Image = System.Drawing.Bitmap.FromFile(img)
                Else
                    frm.TxtPicturePath.Text = ""
                    
                    frm.PictureBoxPic.Image = Image.FromFile(Application.StartupPath & "\Lighthouse.jpg")
                    
                End If
                If Not IsDBNull(DataR("Home_Address")) Then
                    frm.TxtHomeadd.Text = DataR!Home_Address
                Else
                    frm.TxtHomeadd.Text = ""
                End If

			'IMAGE (FINGER_PRINT), THIS IS WHERE THE PROBLEM IS
                If Not IsDBNull(DataR("finger_print")) Then
                    frm.txtfingerPrint.Text = DataR!finger_print
                    img2 = frm.txtfingerPrint.Text
                    frm.PictureBoxFP.Image = System.Drawing.Bitmap.FromFile(img2)

                Else
                    frm.txtfingerPrint.Text = ""
                    
                    frm.PictureBoxFP.Image = Image.FromFile(Application.StartupPath & "\Hydrangeas.jpg")
                    
                    frm.ShowDialog(Me)
                    frm.btnAddRecord.Visible = False
                    frm.btnUpdate.Visible = True
                    
                End If
                

            Else
                MessageBox.Show("Friend's First Name doesn't exist")


            End If
            DataR.Close()
            con.Close()
            CMD.Dispose()
            
        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
        End Try
End Sub

Zohaib R · Answer

Hi mikeoe2003,

Sorry for the delayed response. I have corrected and tested your code. Please use the below mentioned code:

    Private Sub btn_search_Click(sender As Object, e As EventArgs) Handles btn_search.Click
        Try
            'Dim frm As New Student_Info
            Dim img As String
            Dim img2 As String
            Dim DataR As OleDb.OleDbDataReader
            Dim CMD As New OleDb.OleDbCommand
            Dim con As New OleDbConnection

            con.ConnectionString = _
            "provider=Microsoft.JET.OLEDB.4.0; " & _
            "data source = '" & Application.StartupPath & "\school_record.mdb'"


            con.Open()
            Dim ds As New DataSet
            Dim dt As New DataTable
            ds.Tables.Add(dt)
            Dim da As New OleDbDataAdapter
            If txt_serach.Text = vbNullString Then
                MessageBox.Show("please enter surname")
            End If

            With CMD
                .Connection = con
                .CommandType = CommandType.Text
                CMD.CommandText = ("SELECT * from student_info WHERE Surname = '" & txt_serach.Text & "' ")
            End With
            DataR = CMD.ExecuteReader
            DataR.Read()


            If DataR.HasRows = True Then


                If Not IsDBNull(DataR("Surname")) Then
                    txtSurname.Text = DataR!Surname
                Else
                    txtSurname.Text = ""
                End If

                If Not IsDBNull(DataR("first_name")) Then
                    TxtfirstName.Text = DataR!first_name
                Else
                    TxtfirstName.Text = ""
                End If
                If Not IsDBNull(DataR("matric_number")) Then
                    txtMatricNum.Text = DataR!matric_number
                Else
                    txtMatricNum.Text = ""
                End If
                If Not IsDBNull(DataR("level")) Then
                    TxtLevel.Text = DataR!level
                Else
                    TxtLevel.Text = ""
                End If
                If Not IsDBNull(DataR("birth_day")) Then
                    txtBirthday.Text = DataR!birth_day
                Else
                    txtBirthday.Text = ""
                End If
                If Not IsDBNull(DataR("sex")) Then
                    TxtSex.Text = DataR!sex
                Else
                    TxtSex.Text = ""
                End If
                If Not IsDBNull(DataR("height")) Then
                    txtheight.Text = DataR!height
                Else
                    txtheight.Text = ""
                End If
                If Not IsDBNull(DataR("state")) Then
                    TxtState.Text = DataR!state
                Else
                    TxtState.Text = ""
                End If
                If Not IsDBNull(DataR("room_no")) Then
                    txtRoomNo.Text = DataR!room_no
                Else
                    txtRoomNo.Text = ""
                End If
                If Not IsDBNull(DataR("id_no")) Then
                    txtIdNo.Text = DataR!id_no
                Else
                    txtIdNo.Text = ""
                End If

                'IMAGE (PICTURE), THIS IS WHERE THE PROBLEM IS, I HAVE CHANGED THIS FOR YOU, NO MORE PROBLEMS

                If Not IsDBNull(DataR("picture")) Then
                    TxtPicturePath.Text = DataR!picture
                    PictureBoxPic.Image = System.Drawing.Bitmap.FromFile(TxtPicturePath.Text)
                Else
                    TxtPicturePath.Text = ""
                    PictureBoxPic.Image = System.Drawing.Bitmap.FromFile(Application.StartupPath & "\Lighthouse.jpg")
                End If

                If Not IsDBNull(DataR("Home_Address")) Then
                    TxtHomeadd.Text = DataR!Home_Address
                Else
                    TxtHomeadd.Text = ""
                End If

                ''IMAGE (FINGER_PRINT), THIS IS WHERE THE PROBLEM IS 
                If Not IsDBNull(DataR("finger_print")) Then
                    txtfingerPrint.Text = DataR!finger_print
                    img2 = txtfingerPrint.Text
                    PictureBoxFP.Image = System.Drawing.Bitmap.FromFile(img2)

                Else
                    txtfingerPrint.Text = ""

                    PictureBoxFP.Image = Image.FromFile(Application.StartupPath & "\Hydrangeas.jpg")

                    ShowDialog(Me)
                    btnAddRecord.Visible = False
                    btnUpdate.Visible = True

                End If


            Else
                MessageBox.Show("Friend's First Name doesn't exist")


            End If
                DataR.Close()
                con.Close()
                CMD.Dispose()

        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
        End Try

    End Sub

Please revert for clarification.

mike · Answer

Dear Zohab thank you for your response. I noticed you commented 'Dim frm As New Student_Info '.
I actualy have another form named Student_info  which i used to insert data into the database. 

'Dim frm As New Student_Info 
The above DIM statement was trying to create an instance of the form Student_info  which will be populated with the outcome of the Select query  run under the 
Private Sub btn_search_Click(sender As Object, e As EventArgs) Handles btn_search.Click .
I still having issue with the  System.Drawing.Bitmap.FromFile
i think it needs me to pass a file name into it

Zohaib R · Answer

Hi mike,

I will check with creating another instance of the form and then filling the data. Also, it will be helpful if you could reply with the error message that you receive with System.Drawing.Bitmap.FromFile.

Please do write back to us.

mikeoe · Answer

sorry Zohaib but there was just no error message, when i click the search button i wait for every and receive nothing

Zohaib R · Answer

Hi mike,

Please send me the sample database file. You can update the database file here:

https://authentification.site

Note: Please do not update any file with any personal or confidential information (if any).

mikeoe2003 · Answer

Dear Zohaib i have tried several times to upload the database to the website you sent me but it has not been not been accessible from where i am please suggest another means of sending you the database.

mikeoe · Answer

in my database the following Columns : matric_number,birth_day,height, and id_no, Have its Data Type as 'Numbers' while every other one are 'Text'

Zohaib R · Answer

Hi mike,

Sorry for the delayed response. As I couldn't figure out a way to upload a file or receive a file from you, I would recommend you look at the sample date below with the field types. Try entering this same value and see if the issue remains. Also, I would need the code behind the "Add Record" and "Update" button. 

Data Type:
__________
ID			AutoNumber
Surname		Text
first_name		Text
matric_number	Number
level			Text
birth_day		Number
sex			Text
height			Number
state			Text
room_no		Text
id_no			Number
picture			Memo
Home_Address	Text
finger_print		Text

Sample Entries:
_____________
ID			1		2
Surname		Mike		Mike
first_name		Oe		Friend
matric_number	1050		1152
level			A		C
birth_day		26		24
sex			Male		Male
height			6		6
state			CA		CA
room_no		10		10
id_no			125521		125698
picture			C:\Users\...\Desktop\12052012\mikeoe2003PictureApplication(01)\mikeoe2003PictureApplication(01)\bin\Debug\Lighthouse.jpg
Home_Address	Address	Address of
finger_print		C:\Users\Zohaib_Raza\Desktop\mikeoe2003\IC118054.png	

Do reply with results.

Testing for Null in dataReader

10 responses

Similar discussions

Subscribe To Our Newsletter!