Error using SqldataReader

Question

1.00/5 (1 vote)

See more:

Hi im trying to use SqldataReader to read all the columns from my table and show them in the textbox of the form, but it gives me an error saying that there no object reference
the dr (SqldataReader) is below Public Class frmRegCoop

VB

selectCommand.Connection = conn
  selectCommand.CommandText = "SELECT * FROM [tblEjemplo] WHERE COOP_ID= @COOP_ID"

  selectCommand.CommandType = CommandType.Text
  selectCommand.Parameters.Add("@COOP_ID", SqlDbType.Int).Value = txtIdCoop.Text

  While dr.Read ()
      Try
          conn.Open()

          selectCommand.ExecuteReader()
          txtNombreCoop.Text = dr("@COOP_NOMBRE").ToString()
          EstatusRecordRCoop.Text = "Record Encontrado"

      Catch ex As SqlException
          EstatusRecordRCoop.Text = "Error"
          MessageBox.Show(ex.Message)
      Finally
          conn.Close()
      End Try

  End While

Thanks

Posted 21-Mar-13 5:17am

TesterJSR

Updated 21-Mar-13 5:27am

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 21-Mar-13 11:20am

What exactly do you mean by "VB"?
—SA

[no name] 21-Mar-13 11:22am

Without more information it is difficult to say. This is one of the easiest problem to find and fix, you just have to debug it. You do not show where you instantiate dr in your code though.

TesterJSR 21-Mar-13 11:39am

the dr (SqldataReader) is below Public Class frmRegCoop

[no name] 21-Mar-13 11:44am

That means nothing to me. You are actually getting the reader, selectCommand.ExecuteReader(), after you are trying to use it, dr.Read().

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-03-21T05:40:00

You haven't executed the command, os the chancesa re that the reader is not initialized. Try:

VB

selectCommand.Parameters.Add("@COOP_ID", SqlDbType.Int).Value = txtIdCoop.Text
dr = selectCommand.ExecuteReader()
While dr.Read ()

"I fix it but they are now without the @ am I vulnerable to sqlInjection? also the new error I get is after searchin,inserting or updating one time after I do it for second time it tells me The variable name '@COOP_ID' has already been declared. Variable names must be unique within a query batch or stored procedure."

Change your code to like this:

VB

selectCommand.CommandType = CommandType.Text
selectCommand.Parameters.Add("@COOP_ID", SqlDbType.Int).Value = txtIdCoop.Text
conn.Open()

selectCommand.ExecuteReader()

    Try
        While dr.Read ()
            txtNombreCoop.Text = dr("@COOP_NOMBRE").ToString()
            EstatusRecordRCoop.Text = "Record Encontrado"
        End While
    Catch ex As SqlException
        EstatusRecordRCoop.Text = "Error"
        MessageBox.Show(ex.Message)
    Finally
        conn.Close()
    End Try

You may also want to make the the textbox.Text set add the text rather than overwrite it each time.
You may find you don't want the "@" on the dr access - unless you have a column named @COOP_NOMBRE you shouldn't need it. (It's also good practice to explicitly nam eteh columns you want returned in teh SELECT statement rather than using "SELECT * FROM..." as the latter wastes bandwidth and time).