System.Data ExecuteReader requires an open and available Connection

  • Thread starter Thread starter J Breckbill
  • Start date Start date
J

J Breckbill

Hello everyone,

I'm new to .net and I'm trying to figure out the ado.net. I'm creating a
login page for a new asp.net app. But I'm getting an error:

"System.Data ExecuteReader requires an open and available Connection. The
connection's current state is Closed." with the "If
DA.SelectCommand.ExecuteReader.HasRows = False Then" line of code if there
is no data returned.

If I don't check for no data, I get an error: "System.Data There is no row
at position 0."

Can you give me any hints with this?

thanks,
Jason

Try

Dim DS As New DataSet
Dim DR As DataRow
Dim strUsername As String
Dim strpassword As String
DA.SelectCommand.CommandText = "Select Username, Password from
tblFarmers where username='" & txtusername.Text.ToString & "'"

If DA.SelectCommand.ExecuteReader.HasRows = False Then
lblLoginFailed.Text = "--Login Failed. Username or Password
is invalid"
Exit Sub
End If

DA.Fill(DS)
DR = DS.Tables("tblFarmers").Rows(Session("Row1"))
strUsername = DR("username")
strpassword = DR("password")
If txtusername.Text.ToString = strUsername And
txtpassword.Text.ToString = strpassword Then
txtusername.Text = "Passed"
'Response.Redirect(strPassed)
Else
lblLoginFailed.Text = "Login Failed. Username or Password
is invalid"
End If

Catch

lblLoginFailed.Text = Err.Source & " " & Err.Description & " " &
Err.Erl
Err.Clear()

End Try
 
The message is very specific: System.Data ExecuteReader requires an open and
available Connection. The connection's current state is Closed." .

You never open your connection before executing the datareader. Since
ExecuteReader requires an open connection, you must open the connection
before trying to execute this method.
 
For what you're doing here - you don't even need a reader. Dataadpater.Fill
will do it for you. DataReaders and DataTables are totally different
beasts. DataReaders are provider specific (ie SqlDataReader,
OleDbDataReader) although they all come from IDataReader. DataSets and
datatable all belong to system.Data.
You can get riid of all of this

If DA.SelectCommand.ExecuteReader.HasRows = False Then
lblLoginFailed.Text = "--Login Failed. Username or Password
is invalid"
Exit Sub
End If
Click to expand...

and you'll still be fine. You also may want to use a Parameterzied query
since this is a web app (and in all other cases IMHO). "Select Username,
Password from
tblFarmers where username=@UserName"
daCommand.Parameters.Add("@username", SqlDbType.VarChar).Value =
txtusername.text

You may also want to put in a password, you are only checking for username.

If you don't use Parameters you are wide open for an injection attack which
is bad news ie(put in this in txtusername "' or 1=1; Delete tblFarmers"
without of course the beginning and ending quotes) If you use params you
don't have to deal with it to the same degree (but you should still check
for reserved words and limit the size of the text they can input and all
that other good stuff to be safe).

DataReaders are connnected objects - so they are fundamentally different
from datasets. You can store dataset/datatables, serialize them, store them
in session state etc. You wouldn't want to do the same with a datareader as
Phil mentions here
http://caustictech.typepad.com/caustictech/2004/06/i_know_everythi.html



Let me know if you have any questions.
HTH,

Bill

W.G. Ryan, eMVP

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
 
Yes, sorry. I opened it before and it gave me an error saying it needed to
be closed but I didn't realize that the error came on the "DA.Fill(DS)"
line of code.

Thanks

Jason
 
Thanks for the post Bill.

I'm not sure if I'm totally following your recommendation. The reason I was
using the DA.SelectCommand.ExecuteReader.HasRows command was to find if
there was data or not. I would get a "System.Data There is no row at
position 0" on the Dataadpater.Fill command if I didn't check for data
first. Do you have another recommendation to address that issue?
Also, I couldn't follow the link
http://caustictech.typepad.com/caustictech/2004/06/i_know_everythi.html that
you posted.

Thanks again,
Jason
 
You can use DataTable.Rows.Count to get the number and determine if there
are rows. IF you call Fill and there aren't any rows, you should be getting
any exceptions just b/c of that.

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
 
.... should NOT be getting an exception if there aren't any rows?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
If you just call .Fill and it returns nothing, that shouldn't throw an
exception. If you try to reference Row x for instance, and there aren't any
rows than obviously you will. But just calling .Fill won't throw one - I
probably should have been more clear about what I was speaking too.

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
 
Thanks for your help!

--
Jason D. Breckbill - Microsoft Certified Professional (MCP)
-------------------------------------------------------
Kingdom Computer Services, Inc.
Senior Systems Consultant

telephone: 717-279-8595
mobile: 717-468-9829
email: (e-mail address removed)
 
Back
Top