"Invalid attempt to read when no data is present" -- VB2005 / SQL2005

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

Hi

I am trying to connect to an instance of SQL Server 2005 (Express)
from a project in VB2005.


Forgive my ignorance - I know very little of SQL 2005 security (and
not that much more of VB2005!) but I wonder if anyone can help.


I am getting the message "Invalid attempt to read when no data is
present" every time I try to use a Data Reader object. The code that
accesses the Data reader is as follows:


Sub TestConnection
Using cnn As New
SqlConnection(My.Settings.SQLConnectionString)
Using cmd As New SqlCommand( _
"Select CategoryID, CategoryName FROM Categories " &
_
"ORDER BY CategoryName", cnn)
cnn.Open()
Using dr As SqlDataReader = cmd.ExecuteReader


MsgBox(dr(0)) ' This is just a test for accessing
the data
End Using
End Using
End Using
End Sub


The SQL connection string is listed in app.config as follows:


<connectionStrings>
<add name="AccessingData.My.MySettings.SQLConnectionString"
connectionString="Data Source=.\SQLExpress;Initial
Catalog=Northwind; Integrated Security=SSPI" />
</connectionStrings>


SQL server is set to use SQL Server and Windows Authentication mode.


I know that the table exists, and that the field names etc are spelt
correctly, and that there is data in the table. Yet the error I get
implies a lack of data, rather than an error in the security info, or
connection string etc.


Can anyone help? Please??!


Thanks a lot


Andrew
 
Hi

I am trying to connect to an instance of SQL Server 2005 (Express)
from a project in VB2005.

Forgive my ignorance - I know very little of SQL 2005 security (and
not that much more of VB2005!) but I wonder if anyone can help.

I am getting the message "Invalid attempt to read when no data is
present" every time I try to use a Data Reader object. The code that
accesses the Data reader is as follows:

    Sub TestConnection
        Using cnn As New
SqlConnection(My.Settings.SQLConnectionString)
            Using cmd As New SqlCommand( _
                "Select CategoryID, CategoryName FROM Categories " &
_
                "ORDER BY CategoryName", cnn)
                cnn.Open()
                Using dr As SqlDataReader = cmd.ExecuteReader

                    MsgBox(dr(0)) ' This is just a test for accessing
the data
                End Using
            End Using
        End Using
    End Sub

The SQL connection string is listed in app.config as follows:

    <connectionStrings>
        <add name="AccessingData.My.MySettings.SQLConnectionString"
            connectionString="Data Source=.\SQLExpress;Initial
Catalog=Northwind; Integrated Security=SSPI" />
    </connectionStrings>

SQL server is set to use SQL Server and Windows Authentication mode.

I know that the table exists, and that the field names etc are spelt
correctly, and that there is data in the table. Yet the error I get
implies a lack of data, rather than an error in the security info, or
connection string etc.

Can anyone help? Please??!

Thanks a lot

Andrew
Click to expand...

Okay - thanks anyway - I just spotted it myself.

Forgot to actually call the "read" method of the datareader!

Incidentally, what is the law that states that you never spot these
things until AFTER you've posted a message asking x million other
people to help?!

Thanks
Andrew
 
Ah, I think it's one of Murphy's laws. Incidentally, as you're just getting
started, I'm beginning to start a jihad about Using and the Connection
object. I was just at a customer site where these did not properly close the
Connection objects. I suggest using a belt-and-suspenders approach here to
avoid the problems--put a Cnn.Close in the Finally block.
I would also question use of the DataReader Read method. If I find customers
doing this, I suggest use of the DataTable Load method or using Complex
bound controls that do the reading for me. Make sure to test the rowset for
rows before doing a Read or passing the stream to a loader method using the
HasRows method.

This is all laid out in detail in my 7th edition.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
Hi

I am trying to connect to an instance of SQL Server 2005 (Express)
from a project in VB2005.

Forgive my ignorance - I know very little of SQL 2005 security (and
not that much more of VB2005!) but I wonder if anyone can help.

I am getting the message "Invalid attempt to read when no data is
present" every time I try to use a Data Reader object. The code that
accesses the Data reader is as follows:

Sub TestConnection
Using cnn As New
SqlConnection(My.Settings.SQLConnectionString)
Using cmd As New SqlCommand( _
"Select CategoryID, CategoryName FROM Categories " &
_
"ORDER BY CategoryName", cnn)
cnn.Open()
Using dr As SqlDataReader = cmd.ExecuteReader

MsgBox(dr(0)) ' This is just a test for accessing
the data
End Using
End Using
End Using
End Sub

The SQL connection string is listed in app.config as follows:

<connectionStrings>
<add name="AccessingData.My.MySettings.SQLConnectionString"
connectionString="Data Source=.\SQLExpress;Initial
Catalog=Northwind; Integrated Security=SSPI" />
</connectionStrings>

SQL server is set to use SQL Server and Windows Authentication mode.

I know that the table exists, and that the field names etc are spelt
correctly, and that there is data in the table. Yet the error I get
implies a lack of data, rather than an error in the security info, or
connection string etc.

Can anyone help? Please??!

Thanks a lot

Andrew
Click to expand...

Okay - thanks anyway - I just spotted it myself.

Forgot to actually call the "read" method of the datareader!

Incidentally, what is the law that states that you never spot these
things until AFTER you've posted a message asking x million other
people to help?!

Thanks
Andrew
 
Ah, I think it's one of Murphy's laws. Incidentally, as you're just getting
started, I'm beginning to start a jihad about Using and the Connection
object. I was just at a customer site where these did not properly close the
Connection objects. I suggest using a belt-and-suspenders approach here to
avoid the problems--put a Cnn.Close in the Finally block.
I would also question use of the DataReader Read method. If I find customers
doing this, I suggest use of the DataTable Load method or using Complex
bound controls that do the reading for me. Make sure to test the rowset for
rows before doing a Read or passing the stream to a loader method using the
HasRows method.

This is all laid out in detail in my 7th edition.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205  (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)















Okay - thanks anyway - I just spotted it myself.

Forgot to actually call the "read" method of the datareader!

Incidentally, what is the law that states that you never spot these
things until AFTER you've posted a message asking x million other
people to help?!

Thanks
Andrew- Hide quoted text -

- Show quoted text -
Click to expand...

Ok. THanks for your thoughts, and sorry not to respond earlier - been
unable to type to due a shoulder op!!

Regards
Andrew
 
The error message "Invalid attempt to read when no data is present"
it arises in a While executing datareader when the table Or Rows which
is read is
empty
Since the read property return a Boolean value Either true or false

Just Assigin like this
when i got The Same error i rectified it as follows
rdVacationInfo = cmdVaccation.ExecuteReader
rdVacationInfo.Read()
If rdVacationInfo.Read = True Then
If Not (IsDBNull(rdVacationInfo.Item("leavefrom"))) Then
LeaveFrom = rdVacationInfo.Item("leavefrom")
End If
/////--------------------------------/////
i will show an example

Private Function GetleaveDays(ByVal EmpId As Integer)
Try
EmpOnLeave = False
LeaveFrom = DummyDate.Date
LeaveTo = DummyDate.Date
Dim cnvaccation As New SqlClient.SqlConnection(ConnectionString)
If cnvaccation.State = ConnectionState.Closed Then
cnvaccation.Open()
Dim cmdVaccation As New SqlClient.SqlCommand

With cmdVaccation
.CommandType = CommandType.StoredProcedure
.CommandText = "Pr_GetVaccationDays"
.CommandTimeout = 0
.Connection = cnvaccation
.Parameters.Clear()
.Parameters.Add("@EmpID", EmpId)
End With
rdVacationInfo = cmdVaccation.ExecuteReader
rdVacationInfo.Read()
If rdVacationInfo.Read = True Then
If Not (IsDBNull(rdVacationInfo.Item("leavefrom"))) Then
LeaveFrom = rdVacationInfo.Item("leavefrom")
End If
If Not (IsDBNull(rdVacationInfo.Item("leaveTo"))) Then
LeaveTo = rdVacationInfo.Item("leaveTo")
End If
rdVacationInfo.Close()
Else
LeaveFrom = DummyDate.Date
LeaveTo = DummyDate.Date
End If
End If
Return LeaveFrom & "" & LeaveTo
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
End Function

Regards
Pramod
s/w Engineer
Group Harwal
 
Add
If MyDataReader.HasRows Then...



--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
Back
Top