locked
Recordset always returns a record less RRS feed

  • Question

  • User1877874361 posted

    Hi all,

    I'm breaking my head with this issue. In my code i record a set of records but when I retrieve it, recordset always return total records -1.

    If  I see records in database they are ok, from code I call a Stored Procedure and this stored from SQL works fine, Isay it gives me back the total amount of records, but when I call it from code behid I'm always missing a record.

    this is my sp

    		(
    		
    		@pIdExC integer
    		
    		)
    
    
    as
    
    
    SELECT     TB_comp.id_comp
    FROM         tb_ExC_body INNER JOIN
                          TB_Comp ON tb_ExC_body.IdComp = TB_Comp.Id_Comp
    where		tb_ExC_body.Id = @pIdExC
    ORDER BY	TB_Comp.Comp

    this is my code in Visual ASP

     Dim cmd01 As SqlCommand = New SqlCommand
            cmd01.CommandText = "usp_NuevaExC_Body_Select"
            cmd01.CommandType = CommandType.StoredProcedure
            cmd01.Connection = conn01
            conn01.Open()
            cmd01.Parameters.Add("@pIdExC", SqlDbType.Int).Value = IdExC
            Dim sdr01 As SqlDataReader = cmd01.ExecuteReader
            Try
    
                If sdr01.Read Then
                    'bucle
                    Dim n As Integer = 0
                    Do While sdr01.Read
                        n = n + 1
                        MsgBox(n)
                    Loop 'fin del bucle principal
    
                End If
    
            Catch ex As SqlException
                MsgBox(ex.ToString())
            End Try

    where n is a counter to compare with total records.

    can you help to solve this?

    thanks in advance!!

    Friday, August 11, 2017 9:05 AM

Answers

  • User753101303 posted

    Hi,

    Or use just (remove or comment the "if" test):

         'If sdr01.Read Then (not needed)
                    'bucle
                    Dim n As Integer = 0
                    Do While sdr01.Read
                        n = n + 1
                        MsgBox(n)
                    Loop 'fin del bucle principal
    
          'End If
    
    

    For now you are testing if you can read a first row before entering the loop which is why the count would need to start at 1. Doing that test first doesn't seems to be really needed here.

    As a side note MsgBox is a quick debug convenience but will never work on a real web server (as this code runs on the web server, not on the client side).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 13, 2017 12:17 PM

All replies

  • User475983607 posted

    You are starting the count at zero so the count will always be one less.

    Dim n As Integer = 0

    Start the count at one.

    Dim n As Integer = 1

    Friday, August 11, 2017 10:31 AM
  • User753101303 posted

    Hi,

    Or use just (remove or comment the "if" test):

         'If sdr01.Read Then (not needed)
                    'bucle
                    Dim n As Integer = 0
                    Do While sdr01.Read
                        n = n + 1
                        MsgBox(n)
                    Loop 'fin del bucle principal
    
          'End If
    
    

    For now you are testing if you can read a first row before entering the loop which is why the count would need to start at 1. Doing that test first doesn't seems to be really needed here.

    As a side note MsgBox is a quick debug convenience but will never work on a real web server (as this code runs on the web server, not on the client side).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 13, 2017 12:17 PM
  • User1877874361 posted

    Hi PatriceSC

    You're right, I've commented If and now it's working fine as I can see all records returned by query

    thanks for help

    best

    Monday, August 14, 2017 7:57 AM