SqlDataReader Convert byte to integer vb.net

Question

I am trying to retrieve the information from a database table using SqlDataReader. I have two columns one string and the other is bit. The string will be filled in a textbox. But the problem with the bit when I wanted to fill it on a radiobuttonlist it doesn't happen. It keep giving me this error Specified cast is not valid. This is my vb server side code:

Dim dt As DataTable = New DataTable()
        Dim command As New SqlCommand(query2, conn)
        Dim param As New SqlParameter()
        param.ParameterName = "@cUserName"
        param.Value = Session("Edit")
        command.Parameters.Add(param)

        Dim dr As SqlDataReader = command.ExecuteReader()
        If dr.HasRows Then
            dr.Read()
            tbUsername.Text = dr.GetString(0)
            rblDept.SelectedIndex = dr.GetByte(1)
        End If

I tried dr.GetByte(1) but it doesn't work. Help me please.

Have you tried `Convert.ToInt32(dr.GetByte(1))` or possible `CInt()`? — yu_ominae, Mar 19 '13 at 04:56
Come to think of it, as far as I know a column with bit value is binary, storing either 0 or 1 (or null in which case you might have a problem). You should just be able to do `CInt(dr(1))` and it should work. — yu_ominae, Mar 19 '13 at 05:00
@yu_ominae i tried `CInt(dr(1))` it retrieves only when then value is 0. Doesn't make sense — 7alhashmi, Mar 19 '13 at 05:10
@yu_ominae i just tried to put it in a label and i am surprised why it gives me -1 when the value is false. what shell i do? — 7alhashmi, Mar 19 '13 at 05:15
@7alhasmi when you say it retrieves only when the value is 0, you mean it retrieves only `dr(0)` or `dr(1) = 0`? According to the doc, SQLServer's bit datatype can be 0, 1 or null, so it is surprising that it should give you -1... What happends when you do `dr(1).ToString()` does that give you the -1? — yu_ominae, Mar 19 '13 at 05:27
Yeah i tried it using dr(1).ToString() and it gives me 0 when its true and -1 when its false. What about if statement `if CInt(dr(1)) = -1 Then rbl.selectedindex = 1` ? — 7alhashmi, Mar 19 '13 at 05:30
That's probably what you have to do then. I know that with MSAccess True = 0, False = -1. I thought you were using SQLServer, but maybe you are not? You could use something like `rblDept.SelectedIndex = If(CInt(dr(1))=0, 0, 1)` — yu_ominae, Mar 19 '13 at 05:32
Is it a MSAccess database? If no, what is the database? Which .NET framework are you using? — J..., Mar 19 '13 at 05:42

7alhashmi · Accepted Answer · 2013-03-20T04:23:58.623

0

I did something silly and it works. The code is:

If dr.HasRows Then
            dr.Read()
            tbUsername.Text = dr.GetString(0)
            Dim deptid As Integer = CInt(dr(1))
            If deptid = -1 Then
                rblDept.SelectedIndex = 1
            Else
                rblDept.SelectedIndex = 0
            End If
        End If

It works perfect.

Thanks all.

edited Mar 20 '13 at 04:23

answered Mar 19 '13 at 06:06

7alhashmi

924
7
24

SqlDataReader Convert byte to integer vb.net

1 Answers1