[2005] while reader.Read not working

[rlk75]

I'm using a sqldatareader to execute a stored procedure. The stored procedure executes successfully, but I want to loop through the results and display them in a multiline textbox. For some reason the while loop goes immediately to the end while. Please let me know if you see what could be wrong with my code. Thanks.

Private Sub FillTableButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillTableButton.Click

        Try

            Dim results As New System.Text.StringBuilder
            Dim ExecuteSprocCommand As New SqlCommand

            'server name, stored procedure, stored procedure name
            ExecuteSprocCommand.Connection = DevDBConnection
            ExecuteSprocCommand.CommandType = CommandType.StoredProcedure
            ExecuteSprocCommand.CommandText = "PopulateAssociateTable_proc"

            ' open the database connection
            ExecuteSprocCommand.Connection.Open()

            ' Create an instance of SqlDataReader and execute the stored procedure
            Dim reader As SqlDataReader = ExecuteSprocCommand.ExecuteReader

            ' Create a string containing the results to populate the textbox with
            While reader.Read
                For i As Integer = 0 To reader.FieldCount - 1
                    ' separate each field with tab for readability
                    results.Append(reader(i).ToString & vbTab)
                Next
                ' new line after each entry so each row is on its own line
                results.Append(Environment.NewLine)
            End While

            ' close the reader and connection since we're in a connected environment
            reader.Close()
            ExecuteSprocCommand.Connection.Close()

            ' write the results to the textbox
            ResultsTextBox.Text = results.ToString

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

[Tom Sawyer]

Are you sure there results to display? Run the sp in Query Analyser and see how many rows are returned.

[rlk75]

That's what I'm not understanding. If I run the stored procedure, it inserts 190 rows. Even when I run the vb app it still inserts 190 rows, but the while keeps failing for some reason.

[Tom Sawyer]

You said it inserts 190 rows. The reader.Read is based on how many it selects.

If it just inserts them, then there's nothing to pass out to the reader.

[rlk75]

OH!!! Now I see what's going on. I was actually basing this on an example that uses the Northwind database. I never thought to look at the stored procedure in that database, but now that I looked at it, I see that it does a select, not an insert. Thanks for the help.


I was gonna give you some reputation, but I guess I must have given you some too many times:

You must spread some Reputation around before giving it to Tom Sawyer again.