CODEWITHSUNDEEP
sqlsql-servervb.net
Silentbob
Silentbob

Reputation: 3065

Error reading data from SQL Server database using SqlDataReader

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?

Upvotes: 2

Views: 1219

Answers (1)

Silentbob
Silentbob

Reputation: 3065

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

Upvotes: 2

Related Questions