Testing for Null in dataReader

[Solved/Closed]
Report
Posts
25
Registration date
Thursday November 1, 2012
Status
Member
Last seen
March 27, 2014
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
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



10 replies

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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.
Posts
25
Registration date
Thursday November 1, 2012
Status
Member
Last seen
March 27, 2014
2
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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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.
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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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.
sorry Zohaib but there was just no error message, when i click the search button i wait for every and receive nothing
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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).
Posts
25
Registration date
Thursday November 1, 2012
Status
Member
Last seen
March 27, 2014
2
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.
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'
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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.