SQL query with parameter returning 0 instead of 475

Question

I do not understand why the following code is returning 0 instead of 475:

Public Function getSectionLength(sectionUID As Integer) As Integer
    Dim sectionLength As Integer = 0
    Using dr As New DataReader(globals.dif)
        Dim SQL As String = "SELECT dbo.SA.SECTION_LENGTH 
FROM dbo.SA WHERE dbo.SA.SECTION_UID = 
@sectionUid"
        Dim paramList As New List(Of SqlClient.SqlParameter)
        paramList.Add(New SqlClient.SqlParameter("@sectionUid", 
sectionUID))
        dr.ExecuteReader(SQL, paramList)
        If dr.Read Then
            sectionLength = dr("SECTION_LENGTH")
        End If
    End Using
    Return sectionLength
End Function

Here are the values of the variables:

sectionUID = 38

When I run the SQL query in SSMS and swap @sectionUid for 38 I get:

SECTION_LENGTH = 475

but

dr.Read = False

How can dr.Read be false?

EDIT: This has been solved. The issue had to do with the globals.dif. It was being initialised first, but then the values were changed before the program hit this function, causing the error. I solved it by re-initialising the dif within the getSectionLength function.

Have you actually debugged your code, i.e. set a breakpoint and stepped through it line by line? It looks as though `dr.Read` is `False` but you should already know that because you should have debugged your code before posting here. — jmcilhinney, Apr 08 '19 at 14:53
Yes I have, but it doesn't change the fact that I don't understand how it could be false if it is within a using datareader statement — NickyLarson, Apr 08 '19 at 15:01
Because it returns false if there are no more rows... Have you debugged it yet? — Jacob H, Apr 08 '19 at 15:03
What happens if you put 38 in place of @sectionUid in your code? Also, I've not seen this method of using a reader — Charles May, Apr 08 '19 at 15:04
The same thing happens if I swap @sectionUid with 38, dr.ExecuteReader is still False. I have debugged it, when I run that sql statement in SSMS it definitely returns SECTION_LENGTH = 475 — NickyLarson, Apr 08 '19 at 15:10
I just don't think this code is correct for reading the information, Of course this may be a method I've just never seen and DataReader could be something you've created in your application. But, that being said, I would look at an example of getting this information using ExecuteScalar and that might help you resolve some of this. — Charles May, Apr 08 '19 at 15:14
Yes, this is not my code I am debugging. DataReader is an in-house data reader from my workplace — NickyLarson, Apr 08 '19 at 15:18
First, use [`Option Strict On`](https://stackoverflow.com/a/5076966/1115360) and correct the problems it points out. You might have working code then. — Andrew Morton, Apr 08 '19 at 15:22
So you rolled your own DataReader and ExecuteReader method? Have you tried this with any other select statements and got it to work? Is there a reason that they are not using the methods supplied by the SQLClient like Mary has suggested below? Otherwise, it may be necessary to see the DataReader class to see what it's doing — Charles May, Apr 08 '19 at 17:07

Mary · Answer 1 · 2019-04-08T20:00:46.620

I don't know where you got the pattern for this function but it is very mixed up. Apparently you are trying to connect to an Sql Server database but I don't see any connection in your code.

First, let's review your code.

'Good name for your function
        Public Function getSectionLength(sectionUID As Integer) As Integer
        Dim sectionLength As Integer = 0
        'The DataReader constructor does take any arguments.
        'You should be using an SqlDataReader
        'Normally you do not need a New DataReader because .ExecuteReader returns a DataReader
        'Good use of Using
        Using dr As New DataReader(Globals.dif)
            Dim SQL As String = "SELECT dbo.SECTION_ATTRIBUTES.SECTION_LENGTH 
    FROM dbo.SECTION_ATTRIBUTES WHERE dbo.SECTION_ATTRIBUTES.SECTION_UID = 
    @sectionUid"
            'Commands provides its own collection called Parameters
            Dim paramList As New List(Of SqlClient.SqlParameter)
            paramList.Add(New SqlClient.SqlParameter("@sectionUid",sectionUID))
            'The only argument that .ExecuteReader takes is a CommandBehavior enumeration
            '.ExecutleReader won't do anything
            dr.Execut1eReader(SQL, paramList)
            If dr.Read Then
                sectionLength = dr("SECTION_LENGTH")
            End If
        End Using
        Return sectionLength
    End Function

This is a possible replacement for your code. You need to add Imports System.Data.SqlClient to the top of your file.

    Private Function GetSectionLength(SectionUID As Integer) As Integer
        Dim sectionLength As Integer = 0
        'Pass your connection string to the constructor of the connection
        Using cn As New SqlConnection("Your connecion string")
            'pass your sql statement and the connection directly to the constructor of the command
            Using cmd As New SqlCommand("SELECT dbo.SECTION_ATTRIBUTES.SECTION_LENGTH 
                                         FROM dbo.SECTION_ATTRIBUTES 
                                         WHERE dbo.SECTION_ATTRIBUTES.SECTION_UID = @sectionUid", cn)
                'Use the .Add method of the commands Parameters collection
                cmd.Parameters.Add("@sectionUid", SqlDbType.Int).Value = SectionUID
                'Open the connection at the last possible moment
                cn.Open()
                '.ExecuteScalar returns a single value, the first column of the first row of your query result
                sectionLength = CInt(cmd.ExecuteScalar)
            End Using 'Closes and disposes the command
        End Using 'closes and disposes the connection
        Return sectionLength
    End Function

SQL query with parameter returning 0 instead of 475

1 Answers1