object reference set to null on sqldatareader

Question

I have an issue with the following code,

 Protected Sub lnkEdit_Click(sender As Object, e As EventArgs)
    Dim dr As SqlDataReader
    Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("dbconnectionConnectionString").ToString())
    Dim command As New SqlCommand()





    Try

        Dim lnk As LinkButton = TryCast(sender, LinkButton)
        Dim gr As GridViewRow = DirectCast(lnk.NamingContainer, GridViewRow)
        Dim eid As String = GridView1.DataKeys(gr.RowIndex).Value.ToString()
        ViewState("username") = eid
        sqlQry = "select FirstName, Surname, DepartmentName, ExtensionName, jobTitle, Pager, mailaddress, from employees1 where username='" & eid & "'"
        If connection.State <> ConnectionState.Open Then
            connection.Open()
        End If
        command = New SqlCommand(sqlQry, connection)

        dr = command.ExecuteReader()
        If dr.Read() Then

            lblFirstName.Text = Convert.ToString(dr("FirstName"))
            lblSurname.Text = Convert.ToString(dr("Surname"))
            lblDepartmentName.Text = Convert.ToString(dr("DepartmentName"))
            lblExtensionName.Text = Convert.ToString(dr("ExtesionName"))
            lbljobTitle.Text = Convert.ToString(dr("jobTitle"))
            txtPager.Text = Convert.ToString(dr("Pager"))
            txtEmail.Text = Convert.ToString(dr("mailaddress"))
            'lblFirstName.Text = dr("FirstName").ToString()
            'lblSurname.Text = dr("Surname").ToString()
            'lblDepartmentName.Text = dr("DepartmentName").ToString()
            'lblExtensionName.Text = dr("ExtensionName").ToString()
            'lbljobTitle.Text = dr("jobTitle").ToString()
            'txtPager.Text = dr("Pager").ToString()
            'txtEmail.Text = dr("mailaddress").ToString()
            'txtMobile.Text = dr("MobileNumber").ToString()
            'lblUserName.Text = dr("username").ToString()

        End If
        mpe2.Show()
    Catch
        Return
    Finally
        command.Dispose()
        dr.Close()
        connection.Close()
    End Try

End Sub

I understand that the sqldatareader is throwing the null exception.

Ive tried

  dim dr as new sqldatareader

which states that it cant be accessed to it being "friend"

hope someone can help.

Thanks

Your code is susceptible to sql injection attacks. Please use parameters. — crthompson
– crthompson, Commented Mar 19, 2014 at 12:00

D Stanley · Accepted Answer · 2014-03-19 13:37:53Z

1

You have an empty catch block which is swallowing any exception. You assume in Finally that dr is not null and try and close it. I would do three things:

Add better exception handling so you know what the real problem is.
Add an if Not (dr Is Nothing) to your Finally block.
Switch to using parameters instead of concatenating SQL strings - the real problem may be a bad eid input value, and you are vulnerable to SQL injection.

answered Mar 19, 2014 at 13:37

D Stanley

153k12 gold badges189 silver badges257 bronze badges

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

Comments

AlexF · Accepted Answer · 2014-03-19 13:28:14Z

At first look the query has a comma more than needed

"select FirstName, Surname, DepartmentName, ExtensionName, jobTitle, Pager, mailaddress------>,<---- from employees1 where username='" & eid & "'"

must be

"select FirstName, Surname, DepartmentName, ExtensionName, jobTitle, Pager, mailaddress from employees1 where username='" & eid & "'"

In debug mode (or if you can't getting out a log on a file) get the query you're executing and test directly on the db query editor and see if is working, then use it inside you code

Probably commenting the try for a while will give you a SqlException exception on dr = command.ExecuteReader() but instead you are getting the exception in the finally block because all the code if skipped.

Collectives™ on Stack Overflow

object reference set to null on sqldatareader

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related