CODEWITHSUNDEEP
asp.netsql-servervb.netvisual-studio
user2860150
user2860150

Reputation: 65

"Invalid attempt to read when no data is present"

Invalid attempt to read when no data is present ASP.NET

Error is on line 46: Dim requestid = dbreader.GetInt32(1)

There's data in that column in my database so I'm stuck.

Can post more code if needed. 

Imports System.Data.SqlClient

Public Class WebForm1
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    writemystuff()
End Sub

Dim conn As New SqlConnection

Sub CloseDatabase()
    conn.Close()
End Sub

Sub ConnectToDatabase()
    conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbSQL").ConnectionString
    conn.Open()
End Sub


Sub writemystuff()

    'Response.Write(SQL1)

    Dim XMLOutput = "<?xml version=""1.0"" encoding=""utf-8"" ?>"
    'XMLOutput = XMLOutput + "<?xml-stylesheet type=""text/xsl"" href=""XMLSchema1.xslt""?>"
    XMLOutput = XMLOutput + "<requests "
    XMLOutput = XMLOutput + " xmlns = ""http://www.w3schools.com"""
    XMLOutput = XMLOutput + " xmlns:xsi = ""http://www.w3.org/2001/XMLSchema-instance"""
    XMLOutput = XMLOutput + " xsi:schemaLocation=""http://www.w3schools.com XMLSchema1.xsd"">"
    ConnectToDatabase()

    Dim choice = Request("requestchoice")
    Dim SQL1 = "select * from request where r_id = 314"
    If choice = "Request 112" Then
        SQL1 = "select * from request where r_id = 112"
    End If

    Dim cmd = New SqlCommand(SQL1, conn)
    Dim dbreader = cmd.ExecuteReader()
    For Each record In dbreader

    Next

    Dim requestid = dbreader.GetInt32(1)
    XMLOutput = XMLOutput + "<vendor"
    XMLOutput = XMLOutput + " v_name=""" & dbreader.GetString(2) & """"
    XMLOutput = XMLOutput + " v_id=""" & dbreader.GetInt32(0) & """"
    XMLOutput = XMLOutput + " r_id=""" & dbreader.GetInt32(1) & """"
    XMLOutput = XMLOutput + " >"
    Dim SQL2 = "select * from product where r_id = " & requestid

    Dim cmd2 = New SqlCommand(SQL2, conn)

    Dim dbreader2 = cmd2.ExecuteReader()
    For Each record2 In dbreader2
        XMLOutput = XMLOutput + "<product"
        XMLOutput = XMLOutput + " p_name=""" & dbreader2.GetString(3) & """"
        XMLOutput = XMLOutput + " p_id=""" & dbreader2.GetInt32(0) & """"
        XMLOutput = XMLOutput + " cost=""" & dbreader2.GetDecimal(2) & """"
        XMLOutput = XMLOutput + " >"

    Next
    dbreader2.Close()
    XMLOutput = XMLOutput + "</request>"

    CloseDatabase()
    XMLOutput = XMLOutput + "</requests>"
    Response.Write(XMLOutput)
End Sub
End Class

Updated code that still has issues:

Imports System.Data.SqlClient

Public Class WebForm1
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    writemystuff()
End Sub

Dim conn As New SqlConnection

Sub CloseDatabase()
    conn.Close()
End Sub

Sub ConnectToDatabase()
    conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbSQL").ConnectionString
    conn.Open()
End Sub


Sub writemystuff()

    'Response.Write(SQL1)

    Dim XMLOutput = "<?xml version=""1.0"" encoding=""utf-8"" ?>"
    'XMLOutput = XMLOutput + "<?xml-stylesheet type=""text/xsl"" href=""XMLSchema1.xslt""?>"
    XMLOutput = XMLOutput + "<requests "
    XMLOutput = XMLOutput + " xmlns = ""http://www.w3schools.com"""
    XMLOutput = XMLOutput + " xmlns:xsi = ""http://www.w3.org/2001/XMLSchema-instance"""
    XMLOutput = XMLOutput + " xsi:schemaLocation=""http://www.w3schools.com XMLSchema1.xsd"">"
    ConnectToDatabase()

    Dim choice = Request("requestchoice")
    Dim SQL1 = "select * from request where r_id = 314"
    If choice = "Request 112" Then
        SQL1 = "select * from request where r_id = 112"
    End If

    Dim cmd = New SqlCommand(SQL1, conn)
    Dim dbreader = cmd.ExecuteReader()
    For Each record In dbreader


        While dbreader.Read()
            Dim requestid = dbreader.GetInt32(1)
            XMLOutput = XMLOutput + "<vendor"
            XMLOutput = XMLOutput + " v_name=""" & dbreader.GetString(2) & """"
            XMLOutput = XMLOutput + " v_id=""" & dbreader.GetInt32(0) & """"
            XMLOutput = XMLOutput + " r_id=""" & dbreader.GetInt32(1) & """"
            XMLOutput = XMLOutput + " >"
            Dim SQL2 = "select * from product where r_id = " & requestid
        End While

        Dim cmd2 = New SqlCommand(SQL2, conn)

        Dim dbreader2 = cmd2.ExecuteReader()
        While dbreader2.Read()
            XMLOutput = XMLOutput + "<product"
            XMLOutput = XMLOutput + " p_name=""" & dbreader2.GetString(3) & """"
            XMLOutput = XMLOutput + " p_id=""" & dbreader2.GetInt32(0) & """"
            XMLOutput = XMLOutput + " cost=""" & dbreader2.GetDecimal(2) & """"
            XMLOutput = XMLOutput + " >"
        End While

    Next

    XMLOutput = XMLOutput + "</request>"


    CloseDatabase()
    XMLOutput = XMLOutput + "</requests>"
    Response.Write(XMLOutput)
End Sub
End Class

Upvotes: 2

Views: 226

Answers (3)

Karl Anderson
Karl Anderson

Reputation: 34846

You need to call .Read() on the SqlDataReader object to get the data for the row, like this:

While dbreader2.Read()
    ' Do logic here to get individual values from each row
End While

UPDATE:

Instead of this:

Dim dbreader2 = cmd2.ExecuteReader()
For Each record2 In dbreader2
    XMLOutput = XMLOutput + "<product"
    XMLOutput = XMLOutput + " p_name=""" & dbreader2.GetString(3) & """"
    XMLOutput = XMLOutput + " p_id=""" & dbreader2.GetInt32(0) & """"
    XMLOutput = XMLOutput + " cost=""" & dbreader2.GetDecimal(2) & """"
    XMLOutput = XMLOutput + " >"

Next

Do this:

Dim dbreader2 = cmd2.ExecuteReader()
While dbreader2.Read()
    XMLOutput = XMLOutput + "<product"
    XMLOutput = XMLOutput + " p_name=""" & dbreader2.GetString(3) & """"
    XMLOutput = XMLOutput + " p_id=""" & dbreader2.GetInt32(0) & """"
    XMLOutput = XMLOutput + " cost=""" & dbreader2.GetDecimal(2) & """"
    XMLOutput = XMLOutput + " >"
End While

Note: If you are interested in knowing if the SqlDataReader has any rows or not, then you can check that before looping through the reading of rows, like this:

Dim dbreader2 = cmd2.ExecuteReader()
If dbreader2.HasRows Then
    While dbreader2.Read()
        XMLOutput = XMLOutput + "<product"
        XMLOutput = XMLOutput + " p_name=""" & dbreader2.GetString(3) & """"
        XMLOutput = XMLOutput + " p_id=""" & dbreader2.GetInt32(0) & """"
        XMLOutput = XMLOutput + " cost=""" & dbreader2.GetDecimal(2) & """"
        XMLOutput = XMLOutput + " >"
    End While
Else
    ' Potentially generate a "no data found" message here, etc.

End If

UPDATE 2:

Okay, here is what your full method code should look like:

Sub writemystuff()
    'Response.Write(SQL1)

    Dim XMLOutput = "<?xml version=""1.0"" encoding=""utf-8"" ?>"
    'XMLOutput = XMLOutput + "<?xml-stylesheet type=""text/xsl"" href=""XMLSchema1.xslt""?>"
    XMLOutput = XMLOutput + "<requests "
    XMLOutput = XMLOutput + " xmlns = ""http://www.w3schools.com"""
    XMLOutput = XMLOutput + " xmlns:xsi = ""http://www.w3.org/2001/XMLSchema-instance"""
    XMLOutput = XMLOutput + " xsi:schemaLocation=""http://www.w3schools.com XMLSchema1.xsd"">"
    ConnectToDatabase()

    Dim choice = Request("requestchoice")
    Dim SQL1 = "select * from request where r_id = 314"
    If choice = "Request 112" Then
        SQL1 = "select * from request where r_id = 112"
    End If

    Dim cmd = New SqlCommand(SQL1, conn)
    Dim dbreader = cmd.ExecuteReader()
    Dim requestid As Integer
    While dbreader.Read()
        requestid = dbreader.GetInt32(1)
        XMLOutput = XMLOutput + "<vendor"
        XMLOutput = XMLOutput + " v_name=""" & dbreader.GetString(2) & """"
        XMLOutput = XMLOutput + " v_id=""" & dbreader.GetInt32(0) & """"
        XMLOutput = XMLOutput + " r_id=""" & dbreader.GetInt32(1) & """"
        XMLOutput = XMLOutput + " >"
    End While

    Dim SQL2 = "select * from product where r_id = " & requestid
    Dim cmd2 = New SqlCommand(SQL2, conn)
    Dim dbreader2 = cmd2.ExecuteReader()
    While dbreader2.Read()
        XMLOutput = XMLOutput + "<product"
        XMLOutput = XMLOutput + " p_name=""" & dbreader2.GetString(3) & """"
        XMLOutput = XMLOutput + " p_id=""" & dbreader2.GetInt32(0) & """"
        XMLOutput = XMLOutput + " cost=""" & dbreader2.GetDecimal(2) & """"
        XMLOutput = XMLOutput + " >"
    End While
    dbreader2.Close()
    XMLOutput = XMLOutput + "</request>"

    CloseDatabase()
    XMLOutput = XMLOutput + "</requests>"
    Response.Write(XMLOutput)
End Sub

Upvotes: 2

Bruno Costa
Bruno Costa

Reputation: 2720

DataReader is used to for reading a forward-only stream of rows from a SQL Server database. You need to call Read the next record.

On your code, you need to change this (e.g., you need to change it as it is supposed to work, don't copy paste, please)

 Dim dbreader2 = cmd2.ExecuteReader()
    For Each record2 In dbreader2
        XMLOutput = XMLOutput + "<product"
        XMLOutput = XMLOutput + " p_name=""" & dbreader2.GetString(3) & """"
        XMLOutput = XMLOutput + " p_id=""" & dbreader2.GetInt32(0) & """"
        XMLOutput = XMLOutput + " cost=""" & dbreader2.GetDecimal(2) & """"
        XMLOutput = XMLOutput + " >"

 Next

for something like

 Dim dbreader2 = cmd2.ExecuteReader()
 While  dbreader2.Read()
        XMLOutput = XMLOutput + "<product"
        XMLOutput = XMLOutput + " p_name=""" & dbreader2.GetString(3) & """"
        XMLOutput = XMLOutput + " p_id=""" & dbreader2.GetInt32(0) & """"
        XMLOutput = XMLOutput + " cost=""" & dbreader2.GetDecimal(2) & """"
        XMLOutput = XMLOutput + " >"

 End While

As you might imagine, your connection will still busy as long you are using the data reader. You should always be sure that you can close your data reader to close the connection and send it to pool again. This is one of the primary reasons of connection leak in .NET.

You can do it using the statement "using" you doing a try{} finally{} block (it's the same thing).

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

You are calling Next of the first DataReader too soon:

For Each record In dbreader
   ' Do Nothing
Next

' Reader is now at the end - can't read any more!
Dim requestid = dbreader.GetInt32(1)

But in reality you should use Do While dbReader.Read():

Do While dbReader.Read()

    Dim requestid = dbreader.GetInt32(1)
    XMLOutput = XMLOutput + "<vendor"
    XMLOutput = XMLOutput + " v_name=""" & dbreader.GetString(2) & """"
    XMLOutput = XMLOutput + " v_id=""" & dbreader.GetInt32(0) & """"
    XMLOutput = XMLOutput + " r_id=""" & dbreader.GetInt32(1) & """"
    XMLOutput = XMLOutput + " >"

    Dim SQL2 = "select * from product where r_id = " & requestid

    Dim cmd2 = New SqlCommand(SQL2, conn)

    Dim dbreader2 = cmd2.ExecuteReader()
    Do While dbReader2.Read()
        XMLOutput = XMLOutput + "<product"
        XMLOutput = XMLOutput + " p_name=""" & dbreader2.GetString(3) & """"
        XMLOutput = XMLOutput + " p_id=""" & dbreader2.GetInt32(0) & """"
        XMLOutput = XMLOutput + " cost=""" & dbreader2.GetDecimal(2) & """"
        XMLOutput = XMLOutput + " >"

    Loop
Loop

Upvotes: 1

Related Questions