New to VB.NET...HELP REQUIRED IMMEDIATELY

[santosh singh via DotNetMonster.com]

Hi,
I'm new to VB.NET..I'm developing a login page...im getting this error..

System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is Open, Fetching. at System.Data.OleDb.OleDbCommand.ValidateConnectionAndTransaction(String method, Int32& localState) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at bt.index.Page_Load(Object sender, EventArgs e) in d:\inetpub\wwwroot\bt\index.aspx.vb:line 56


The code in .aspx.vb page is


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
Try



Dim MyConn As OleDbConnection = New OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
Dim strVisitor As String
Dim visitorCmd As OleDbCommand
Dim vRdr As OleDbDataReader
strVisitor = "select max(v_id) from visitor"
visitorCmd = New OleDbCommand(strVisitor, MyConn)
MyConn.Open()
Dim old_visitor As Integer
Dim new_visitor As Integer

vRdr = visitorCmd.ExecuteReader()
While vRdr.Read()
If Not IsDBNull(visitorCmd.ExecuteReader()) Then
old_visitor = vRdr.Item(0)
End If
End While

new_visitor = old_visitor + 1
lblvisitor.Text = new_visitor
MyConn.Close()

Catch ex As Exception
Response.Write(ex)
End Try

End If
End Sub



Your help will be much appreciated

 

[Scott M.]

You are calling ExectueReader on your command object twice. Also, you
didn't show us your connection string in Web.Config, so make sure that it is
correct and then try this:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
Try
Dim MyConn As New
OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
Dim strVisitor As String = "select max(v_id) from visitor"
Dim visitorCmd As New OleDbCommand(strVisitor, MyConn)

MyConn.Open()
Dim old_visitor As Integer
Dim new_visitor As Integer

Dim vRdr As OleDbDataReader = visitorCmd.ExecuteReader()
If vRdr.HasRows Then
Do While vRdr.Read()
If Not IsDBNull(vRdr(0)) Then
old_visitor = vRdr(0)
End If
Loop
End If

new_visitor = old_visitor + 1
lblvisitor.Text = new_visitor.
vRdr.Close()
MyConn.Close()
Catch ex As Exception
Response.Write(ex)
End Try
End If
End Sub

 

[W.G. Ryan eMVP]

Scott already answered the crux of your problem - you are using the same
connection for two different commands which you can't do until ADO.NET 2.0

But since your first execution is MAX() - then you should probably opt for
ExecuteScalar instead of Reader. If nothing else it will save you a few
lines of code and make your intent a little clearer.

Also, you should consider using a Finally Block and closing your connection
in the finally - that's the only way youy can make sure it will get closed.

And as always - I have to include my obligatory rant against catching
system.Exception here. Since your not doing anything with your exception
other than writing it out - you probably ought to catch a OleDbException for
the connection.Open and the Execute statements - if you got a StackOverflow
or something else there - well you may want to respond differently. But
that's another story.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

Hi,
I'm new to VB.NET..I'm developing a login page...im getting this error..

System.InvalidOperationException: ExecuteReader requires an open and

available Connection. The connection's current state is Open, Fetching. at
System.Data.OleDb.OleDbCommand.ValidateConnectionAndTransaction(String
method, Int32& localState) at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at
System.Data.OleDb.OleDbCommand.ExecuteReader() at bt.index.Page_Load(Object
sender, EventArgs e) in d:\inetpub\wwwroot\bt\index.aspx.vb:line 56

The code in .aspx.vb page is


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles MyBase.Load

 

[Scott M.]

I don't see him using one connection for 2 different commands. I see him
calling ExecuteReader on the same command twice. There is only one command
object in his code.

 

[W.G. Ryan eMVP]

Scott - a DataReader requires an open and available connection and is only
valid in a 'live' context regarding the connection. You can't pass more
than one command over the same connection at one time - even if the command
has the exact same commandtext. The execution of the first command ins't
even finished yet when it's being called again on the same wire. So
technically, there aren't Two different command objects instantiated here,
but there are two commands attempting to be sent with the same connection -
which isn't going to work. You would certainly agree that through Query
Analyzer for instance, it's possible to exectue 2 commands right? Even
though in QA you aren't ever even explicity creating even one SqlCommand
object. Moreover, since the query is using an aggregate function to return
one single value - excecutescalar makes more sense in every regard 1) You
don't need the datareader object in the first place 2) you don't need to
extra code associated with iterating through it.

I do acknowledge that there aren't 2 SqlCommand objects - but my statement
that you're trying to fire two commands over the same connection isn't
incorrect.

 

[Cor Ligthert]

Bill,

Sorry

I do acknowledge that there aren't 2 SqlCommand objects - but my statement
that you're trying to fire two commands over the same connection isn't
incorrect.

However not the problem

)))))

Cor