Error reading data from SQL Server database using SqlDataReader

Question

I have the following vb.net code

Protected Sub submit_date_Click(sender As Object, e As System.EventArgs) Handles submit_date.Click
    Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("GasNominationsConnectionString").ConnectionString)
        conn.Open()
        Using cmd As SqlCommand = conn.CreateCommand
            cmd.CommandText = "select * from MorningReport where readdate = " & "'" & datedisplay.text & "'"
            Dim sqlrd As SqlDataReader = cmd.ExecuteReader()

            If sqlrd.HasRows Then sqlrd.Read()
            Me.Label6.Text = sqlrd.Item("NteesHR")

        End Using
    End Using
End Sub

The problem I have is it doesn't read any data. The datedisplay.text is a text box populated using an ajax calendar extender and when I debug the data I can see it gets the correct date which is 22/08/2013 for this example. I then get the error

Invalid attempt to read when no data is present.

The problem I have is if I copy the SQL statement into SQL Server Management Studio and run it against the table it works fine and I get the result I want.

The SQL is

select * from MorningReport where readdate = '22/08/2013'

The format of the date in the sql table is 2013-08-22 with type date.

What am I missing?

When you debug your code what is in cmd.CommandText after that line executes? — Karl Anderson
– Karl Anderson, Commented Aug 23, 2013 at 13:55
And if you take that exact value, minus the double quotes at the beginning and end, does it execute correctly in SQL Server? — Karl Anderson
– Karl Anderson, Commented Aug 23, 2013 at 14:00
Just should absolutely use a parametrized query instead of concatenating together your SQL! First of all, doing what you're doing now is in open invitation to SQL injection attacks, secondly it's slower, and thirdly, you get into issue like this with what format your dates - mishandled as strings - are...... don't do this! Use a parameter of type DateTime and all your problems go away immediately! — marc_s
– marc_s, Commented Aug 23, 2013 at 14:19

Community · Accepted Answer · 2017-05-23 10:32:15Z

2

using marc_s recommendation I used a parametrized query. Please see below. Cheers

Protected Sub submit_date_Click(sender As Object, e As System.EventArgs) Handles submit_date.Click

    Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("GasNominationsConnectionString").ConnectionString)
        conn.Open()
        Using cmd As SqlCommand = conn.CreateCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "getMorningReportData"
            cmd.Parameters.Add("datedisplay", SqlDbType.Date).Value = DateDisplay.Text
            Dim sqlrd As SqlDataReader = cmd.ExecuteReader()

            If sqlrd.HasRows Then
                sqlrd.Read()

                Me.Label6.Text = sqlrd.Item("NteesHR")
            End If


        End Using
    End Using

End Sub

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Aug 23, 2013 at 14:38

Silentbob

3,0757 gold badges42 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error reading data from SQL Server database using SqlDataReader

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related