Question Getting query results from datareader

ulynen

Member
Joined
Feb 18, 2015
Messages
22
Programming Experience
5-10
I am new to VB.NET and some of my questions are probably kind of dumb. I wrote the app originally in VB 6 and had very little trouble getting it to work.
I am running a select query with 1 parameter which returns data from an access database. I know from running the query in access that it returns 2 rows. Depending on the parameter it may return from 0 to maybe 5 rows. How can I find out in code how many rows the query returns and how is the best way to retrieve the rows of data.
Uwe
 
I got it to work with the help of the help of the internet.
Here is the code:
    Private Sub Button_Search_Click(sender As Object, e As EventArgs) Handles Button_Search.Click


        Dim i As Integer
        Dim iFields As Integer
        Dim strName As String = ""
        Dim blCheck As Boolean
        Dim oReader As OleDbDataReader


        Try
            With cmd
                .CommandText = "qselphone"
                .Parameters.AddWithValue("parPhone", "4162652035")
                .Parameters.AddWithValue("parPhone", " ")
            End With
            oReader = cmd.ExecuteReader()
            blCheck = oReader.HasRows


            ' test to see if the query actually returned data
            If Not blCheck Then
                gstrData = ""
                giRowcount = 0
                Exit Sub
            End If
            iFields = oReader.FieldCount
            'giRowcount = 0   'set as public in Global because it is used elsewhere


            i = 0
            giRowcount = 1
            While (oReader.Read)
                Do While i < iFields
                    If i = 0 Then
                        ' label the data with row number at the beginning of the data for each row
                        gstrData = gstrData & "Row " & Str(giRowcount) & " "
                    End If
                    If giRowcount = 1 Then
                        ' only gather the field names once
                        strName = strName & vbCrLf & " " & oReader.GetName(i)
                    End If
                    gstrData = gstrData & vbCrLf & " " & Convert.ToString(oReader.GetValue(i))
                    i = i + 1
                Loop
                giRowcount = giRowcount + 1
                i = 0
            End While
        Catch ex As Exception
            MessageBox.Show(ex.Message, _
            "The program will terminate", _
            MessageBoxButtons.OK, _
            MessageBoxIcon.Exclamation, _
            MessageBoxDefaultButton.Button1)
        Finally
            oReader.Close()
            ' girowcount is 1 larger than actual from the loop
            giRowcount = giRowcount - 1
        End Try
    End Sub
 
Last edited by a moderator:
That code could be improved significantly, e.g.
Using connection As New OleDbConnection("connection strinbg here"),
      command As New OleDbCommand("SQL query here", connection)
    'Add parameters to command here.

    Try
        connection.Open()

        Using reader = command.ExecuteReader()
            'Start building a string with the first column name.
            Dim builder As New StringBuilder(reader.GetName(0))

            'Add the rest of the column names with a comma delimiter.
            For i = 1 To reader.FieldCount - 1
                builder.Append("," & reader.GetName(i))
            Next

            If reader.HasRows Then
                'Create an array to receive the field values for a row.
                Dim values(reader.FieldCount - 1) As Object

                Do While reader.Read()
                    'Get the values for the next row.
                    reader.GetValues(values)

                    'Add the values to the next line of the string with a comma delimiter.
                    builder.AppendLine()
                    builder.Append(String.Join(",", values))
                Loop
            End If

            'Display the output.
            Console.WriteLine(builder.ToString())
        End Using
    Catch ex As Exception
        'Notify user of error here.
    End Try
End Using
 
The code worked then all of a sudden I get an exception when the command.ExecuteReader().
I made the changes suggested in the post and it still does not work.
The exception is: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
There have been no changes to the query and it runs perfectly in access. If I execute it with the command.execute.nonquery() I don't get any errors.
 
If you're trying to execute a stored procedure then you must set the CommandType property of the command to StoredProcedure rather than the default Text, which indicates inline SQL code.
 
OMG sometimes this old brain misfires. Thanks. Somehow I must have removed the following: cmd.CommandType = CommandType.StoredProcedure. No wonder it stopped working.
 
I'm trying to get the result of the reader into a textbox on the form. It is named SEARCHRESULT. The strings that I'm trying to add actually contain data. Here is the code snippet:
Dim strArr() As String
Me.SearchResult.Text = "TESTING"
Me.SearchResult.Text = strName
strArr = gstrData.Split(vbCrLf)
For i = 0 To strArr.Length - 1
Me.SearchResult.Text = strArr(i)
Next
No errors, bur nothing shows up in the textbox. It is configured as multiline and readonly set to false.
 
I'm trying to get the result of the reader into a textbox on the form. It is named SEARCHRESULT. The strings that I'm trying to add actually contain data. Here is the code snippet:
Dim strArr() As String
Me.SearchResult.Text = "TESTING"
Me.SearchResult.Text = strName
strArr = gstrData.Split(vbCrLf)
For i = 0 To strArr.Length - 1
Me.SearchResult.Text = strArr(i)
Next
No errors, bur nothing shows up in the textbox. It is configured as multiline and readonly set to false.

Why are you splitting the String in the first place? The whole point of making a TextBox multi-line is that it can display a multi-line String, so why split your multi-line String in the first place? Just assign your original String to the Text of TextBox and you're done.

By the way, the code you have is only going to display the last line of the original String so, if that last line is empty, your TextBox will be empty.
 
Me.SearchResult.Text = "TESTING" I did that as a test and nothing showed in the textbox.

I have another issue. I have been trying to use substring. the following snippet shows what I'm trying to do. I'm experimenting because I have to retrieve data by query for telephone number from the caller id and display some of the fields in an excel spreadsheet.
' the original value of strWork1
This is the actual comma delimited data in strWork1
strWork1 = "File #, Family #, Name First, Name Initial, Name Last, Address, Home City, Home Region, Home Postal, Notes, Present, Birth Date, MaxOfDay" strWork = strWork1.Substring(iPos, (0 - 6) strWork = "File #" ' here the substring works, but it is starting at the beginning of the buffer
strWork = strWork1.Substring(7, 130)
notice that it shows the entire string not the part starting with position 7
strWork = "File #, Family #, Name First, Name Initial, Name Last, Address, Home City, Home Region, Home Postal, Notes, Present, Birth Date, MaxOfDay"
The values displayed showed up by hovering over the variable during debug after the line execution and then I copied the displayed value.
 
Back
Top