Get RadioButton value for gender from a MS Access database

Question

How can I retrieve the gender value from my MS Access database?

Here is my code:

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
    myconnection = New OleDbConnection(StrConn)
    myconnection.Open()
    Dim str As String
    str = "SELECT * FROM register WHERE (MatricNum = '" & txtSearch.Text & "')"
    Dim cmd As OleDbCommand = New OleDbCommand(str, myconnection)
    dr = cmd.ExecuteReader
    While dr.Read()
        txtMatricnum.Text = dr("MatricNum").ToString
        txtFname.Text = dr("Fname").ToString
        txtLname.Text = dr("Lname").ToString
        rbtMale .Text or rbtfemale.Text  = dr("Gender").ToString
        DateTimePicker1.Value = dr("DOB").ToString
        txtlevel.Text = dr("Level").ToString
        txtFaculty.Text = dr("Faculty").ToString
        txtDapartment.Text = dr("Department").ToString
        txtContact.Text = dr("Contact").ToString
        txtState.Text = dr("State").ToString
        txtLGA.Text = dr("LGA").ToString
        txtAddress.Text = dr("Address").ToString
        Try
            Dim data As Byte() = DirectCast(dr("Picture"), Byte())
            Dim ms As New IO.MemoryStream(data)
            PictureBox1.Image = Image.FromStream(ms)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End While
    myconnection.Close()
End Sub

OleDbConnection, OleDbCommand and MemoryStream all implement iDisposable, always use using-block with them. Also to avoid sql-injection, always ***use parameterized queries.*** — Esko, Jul 11 '17 at 09:04
Please see [ask] and take the [tour]. It would be easier for us to help you if you let us know what the values of `Gender` are. We can assume _Male_ and _Female_ but we can't be sure on that. Please [edit] your question to provide more information. — Bugs, Jul 11 '17 at 09:56
If you're actually going to use real humans with this database, using a binary gender won't work. A lot of people don't identify as male _nor_ female. Then again they are only able to have this legally registered in certain countries. Still, you should consider adding a third option. — Andras Deak -- Слава Україні, Jul 11 '17 at 11:47

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

This code isn't doing what you would like it to do:

rbtMale .Text or rbtfemale.Text  = dr("Gender").ToString

Assuming that the values you are storing in your database are Male and Female you could consider using a simple Select Case statement:

Select Case dr("Gender").ToString
    Case "Female"
        rbtFemale.Checked = True
    Case "Male"
        rbtMale.Checked = True
End Select

I've gone for a Select Case statement over an If statement as to me it reads better but either would suffice. There is an ISO standard to this I believe which I found from reading this question:

The four codes specified in ISO/IEC 5218 are:

0 = not known,
1 = male,
2 = female,
9 = not applicable.

You may want to consider reading further into this so I have included it for reference.

I would also like to point out that you should strongly use parameters. See Bobby Tables for more information on why.

Here is an example:

cmd.Parameters.Add("@MatricNum", OleDbType.VarChar).Value = txtSearch.Text

You should also consider implementing Using:

Sometimes your code requires an unmanaged resource, such as a file handle, a COM wrapper, or a SQL connection. A Using block guarantees the disposal of one or more such resources when your code is finished with them. This makes them available for other code to use.

Here is an example:

Using con As New OleDbConnection(StrConn),
      cmd As New OleDbCommand("SELECT * FROM [register] WHERE [MatricNum] = ?", con)

End Using

All together your code would look something like this:

Using con As New OleDbConnection(StrConn),
      cmd As New OleDbCommand("SELECT * FROM [register] WHERE [MatricNum] = ?", con)

    con.Open()

    cmd.Parameters.Add("@MatricNum", OleDbType.VarChar).Value = txtSearch.Text

    dr = cmd.ExecuteReader
    While dr.Read()
        txtMatricnum.Text = dr("MatricNum").ToString
        txtFname.Text = dr("Fname").ToString
        txtLname.Text = dr("Lname").ToString

        Select Case dr("Gender").ToString
            Case "Female"
                rbtFemale.Checked = True
            Case "Male"
                rbtMale.Checked = True
        End Select

        DateTimePicker1.Value = dr("DOB").ToString
        txtlevel.Text = dr("Level").ToString
        txtFaculty.Text = dr("Faculty").ToString
        txtDapartment.Text = dr("Department").ToString
        txtContact.Text = dr("Contact").ToString
        txtState.Text = dr("State").ToString
        txtLGA.Text = dr("LGA").ToString
        txtAddress.Text = dr("Address").ToString

        Try
            Dim data As Byte() = DirectCast(dr("Picture"), Byte())
            Dim ms As New IO.MemoryStream(data)
            PictureBox1.Image = Image.FromStream(ms)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End While
End Using

Get RadioButton value for gender from a MS Access database

1 Answers1