Pull value from SQL Server in VB.NET

Question

I am attempting to pull values from an SQL Server table from VB.NET.

On VB Form 1, the number from NoTable, Row 1, is pulled successfully, and Label1 is updated with the value.

    Dim command As SqlCommand
    Dim query As String = "SELECT Number FROM NoTable"
    command = New SqlCommand(query, con)
    con.Open()
    Dim datareader As SqlDataReader = cmd.ExecuteReader()
    If datareader.Read() Then
        Label1.Text = datareader.GetValue(0)
    End If
    datareader.Close()

On VB Form 2 I am attempting to pull the value from the second row, using:

    Dim query As String = "SELECT Number FROM NoTable"
    command = New SqlCommand(query, con)
    con.Open()
    Dim datareader As SqlDataReader = cmd.ExecuteReader()
    If datareader.Read() Then
        Label1.Text = datareader.GetValue(1)
    End If
    datareader.Close()

However, this does not work, and the label is not updated with the value from the second row.

An unhandled exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll

Additional information: Index was outside the bounds of the array."

How would I go about fixing this, so that on Form 2, the value from Row 2 is pulled, and so forth?

Thank you.

No. [SqlDataReader.GetValue(Int32)](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getvalue(v=vs.110).aspx), the number passed is used to index the columns. If you only return a single column, you should always use `SqlDataReader.GetValue(0)`. You just need to make it iterate the records n times before returning the value, where n is your form #. — djv, Dec 06 '16 at 20:15
In SQL, there is no ordering unless you use an `ORDER BY`, so the intended concept of first and second numbers in your code does not make sense, apart from the issue pointed out by others. — Andrew Morton, Dec 06 '16 at 20:28
It would be good to know if there is a primary key (ID) in your table. This key does not need to be equal to the form #, but it should be in the same order. If there is, I have a better answer. Let me know. — djv, Dec 06 '16 at 20:30

score 0 · Answer 1 · answered Dec 06 '16 at 20:16

0

GetValue(1) does not exist, as this would refer to a second column in the select statement. You are only asking for [Number] which would be datareader.GetValue(0)

answered Dec 06 '16 at 20:16

Jay Hamilton

36
6

score 0 · Accepted Answer · edited May 23 '17 at 11:45

Firstly, you only get one column back from the reader, but you are indexing the columns with that 0 or 1. So you should always pass 0 to GetValue.

To index the row instead, try this. Assign a form number to each form (first line in my example) and use that to determine which record to assign to the Label. There is probably a more efficient way to do this (not returning all the records before it) but this solution should fit in your environment.

' in form # 1
Dim formNumber = 1
Dim command As SqlCommand
Dim query As String = "SELECT Number FROM NoTable"
command = New SqlCommand(query, con)
con.Open()
Dim datareader As SqlDataReader = cmd.ExecuteReader()
Dim index = 0
While index < formNumber    
    If datareader.Read() AndAlso index = formNumber Then
        Label1.Text = datareader.GetValue(0)
    End If
    index += 1
End While
datareader.Close()

See https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getvalue(v=vs.110).aspx

And another similar question in c# Access a specific row in DataReader

Another way is to just return the row you need in the first place, without iterating over the records on the client side. Assuming there is another column with an index which is in the same order as the row you want to return, called "ID"

' in form # 1
Dim formNumber = 1
Dim command As SqlCommand
Dim query As String =
    "SELECT Number FROM " & _
    " (SELECT Number, Row_Number() OVER (ORDER BY ID) AS RowNumber " & _
    " FROM NoTable) AS Results " & _
    " WHERE Results.RowNumber = " & formNumber.ToString()
command = New SqlCommand(query, con)
con.Open()
Dim datareader As SqlDataReader = cmd.ExecuteReader()
Label1.Text = datareader.GetValue(0)
datareader.Close()

See https://msdn.microsoft.com/en-us/library/ms186734.aspx

Pull value from SQL Server in VB.NET

2 Answers2