Please HELP!

Hey there....

Please help. I've typed in a program from a book on VB.Net. I've received an error stating, "ExecuteReader requires an open and available Connection. The connection's current state is closed."

What should I do to correct this? The program that follows generates this error on line, " dr = SQLcmd.ExecuteReader(CommandBehavior.CloseConnection)
".

-----------------

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

Partial Public Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim sqlConnString As String = "Initial Catalog=pubs:Integrated Sceurity=SSPI"

If Not Page.IsPostBack Then
Dim conn As New SqlConnection(sqlConnString)
Dim sql As String = "SELECT * FROM Authors"
Dim SQLcmd As New SqlCommand(sql, conn)
Dim dr As SqlDataReader

Try
conn.Open()
Catch ex As Exception
Response.Write(ex.Message)
End Try

' Read data into the DataReader
dr = SQLcmd.ExecuteReader(CommandBehavior.CloseConnection)

' Show all the records in the ListBox
Do While dr.Read
Dim Total As Integer = dr.FieldCount - 1

' Get the entire set of data at once.
Dim AllData(Total) As Object
dr.GetValues(AllData)

Dim i As Integer
Dim n As String

For i = 0 To Total ' Show one reconrd
n = dr.GetName(i)
ListBox1.Items.Add(n & ": " & AllData(i).ToString)
Next i

ListBox1.Items.Add("______________") ' line to divide records
Loop ' More to next record
End If
End Sub
End Class

----------

Any help you can give that MAY assist would be greatly appreciated! Thanks for your time.

May you have a blessed day as the Lord wills.

 

DataReader needs to have open connection while looping..
Don't close the connection when calling the ExecuteReader.
After the While Read, close the reader and than close the connection if you want...

in .NET you don't have to be worried about open connection since you have the garbage collector.

Isaac,
http://www.7Live7.com

 

Thank you for your reply.

I have another question. Allow me to illustrate the process of the website that I plan to design....

!) A third party app' collects data from an online source and stores the basic data on a dedicated server.

2) A VB.Net 2008 app' analyzes the content of this data and transforms it into a SQL DB.

3) An ASP.Net 2008 app' displays the content of this SQL DB online.

Can ASP.Net display the DB while the VB.Net app' is updating this same DB?

May you hae a blessed day as the Lord wills.

JEP_Dude

 

What do you mean? If you mean using SELECT and UPDATE at one time, it's certainly possible.

 

I do plan to read in the data from an SQL DB into a large array. Then I'd like to display this information via ASP.Net online.

I'm new to this. If it were to read in the data via a SELECT statement, would ASP.Net need to repeat that same SQL SELECT statement each time a visitor comes to the website? Because I would like to minimize the overall DB needs.

May you have a blessed day as the Lord wills.

JEP_Dude

 

you need an active connection while reading by SqlDataReader.

 

You could either add a refresh button to the form or have the page auto-refresh every n minutes