Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sql DataReader System.IndexOutOfRangeException

Posted on 2006-04-30
6
Medium Priority
?
2,910 Views
Last Modified: 2012-05-07
Hi,

I’m using ASP.NET 2.0 with SQL Server 2005.  The following code snippet gives me an error:

Dim Rdr As SqlDataReader = cmd.ExecuteReader()

        Dim iID As Integer
        Dim sName As Char

        While Rdr.Read
            iID = Rdr("pkcGenerator")
            sName = Rdr("sName")
        End While


The error message is as follows:
System.IndexOutOfRangeException was unhandled by user code
  Message="pkcGenerator"
  Source="System.Data"

My table fr_GeneratorInfo contains the following data:
pkcGenerator      sName      
1                        Harrison
2                        Starr
3                        Lennon
4                       McCartney

pkcGenerator = numeric(18, 0)
sName= char(55)

My stored procedure is as follows:
Create Procedure [dbo].[spXML]
as
Select pkcGenerator,sName from fr_GeneratorInfo
for XML PATH

My entire code is as follows:

    Private Sub ExecStoredProcXML()
         Dim sSQL As String

        'Set up sql string and connection
        sSQL = "spXML"

        'Use clsDatabase class for connection string
        sCon = oCon.SqlConString
        Dim Con As SqlConnection = New SqlConnection(sCon)

        Dim cmd As New SqlCommand
        With cmd
            'Create connection object
            .Connection = New SqlConnection(sCon)
            'Set the SQL
            .CommandText = sSQL
            .CommandType = Data.CommandType.StoredProcedure
        End With

        cmd.Connection.Open()

        Dim Rdr As SqlDataReader = cmd.ExecuteReader()

        Dim iID As Integer
        Dim sName As Char

        While Rdr.Read
            iID = Rdr("pkcGenerator")
            sName = Rdr("sName")
        End While

        cmd.Connection.Close()

    End Sub      

Thanks,
Denise      


 


0
Comment
Question by:DeniseGoodheart
6 Comments
 
LVL 37

Assisted Solution

by:samtran0331
samtran0331 earned 480 total points
ID: 16575110
try using a string instead of char...

Dim sName As String = String.Empty



0
 

Author Comment

by:DeniseGoodheart
ID: 16575184
Hi samtran0331 :

Thanks for your suggestion, but I already tried that with no success.

Denise
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 540 total points
ID: 16575256
your procedure has the FOR XML clause, but in your ASP you seem to want to read the records "as usual".
you should remove the FOR XML clause in the procedure, or, run the procedure once in the SQL Server Management studio to see what column names you get and use those instead
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Assisted Solution

by:sukumar_diya
sukumar_diya earned 480 total points
ID: 16575292
hi,
Try to check the field count.. If it is greater than 0 then try to get the value by index...

 Dim iID As Integer
 Dim sName As String

 While Rdr.Read
         if Rdr.FieldCount > 0 then
            iID = Rdr.GetValue(0)
            sName = Rdr.GetValue(1)
        else
         '' throw message
        end if
        End While

Tell me if this gives u any result...

Suk
0
 

Author Comment

by:DeniseGoodheart
ID: 16575371
Hi All,

I removed the XML clause in my stored procedure and it ran fine, but I was curious as to why it would not work with the XML clause.  I'm experiementing with different ways to create an XML file without using the XML class.  I suppose writing an XML file using the SQL Server XML clause is not going to work.

I tried the following code with no success:
While Rdr.Read
         if Rdr.FieldCount > 0 then
            iID = Rdr.GetValue(0)
            sName = Rdr.GetValue(1)
        else
         '' throw message
        end if
        End While


Thanks All,
Denise


0
 
LVL 7

Expert Comment

by:sukumar_diya
ID: 16575379
hi Denise,
Can u tell us the fieldcount it returns.....

Suk
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question