retrieving data from sql database using C# with stored procedure

Question

Whenever I try to retrieve data from my database i keep getting null. The code i'm using is below:

protected void Button2_Click(object sender, EventArgs e)
 {
    SqlConnection myConnection = new SqlConnection(GetConnectionString());
    SqlCommand cmd = new SqlCommand("spSelectCustomer", myConnection);
    cmd.CommandType = CommandType.StoredProcedure;
    myConnection.Open();

    SqlParameter custId = cmd.Parameters.Add("@CustomerId", SqlDbType.Int);
    custId.Direction = ParameterDirection.Input;
    custId.Value = 10;

    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

    Label1.Text = dr["FirstName"].ToString();
    Label2.Text = dr["LastName"].ToString();
    Label3.Text = dr[3].ToString();
    Label4.Text = dr["Email"].ToString();
}
private static string GetConnectionString()
{
    return ConfigurationManager.ConnectionStrings["Lab3ConnectionString"].ConnectionString;
}

rs. · Accepted Answer · 2013-02-01 20:03:29Z

7

You need to call Read before you can access data, Your code should be

While (dr.Read())
{

    Label1.Text = dr["FirstName"].ToString();
    Label2.Text = dr["LastName"].ToString();
    Label3.Text = dr[3].ToString();
    Label4.Text = dr["Email"].ToString();
}

//close DataReader
dr.Close();

answered Feb 1, 2013 at 20:03

rs.

27.6k13 gold badges71 silver badges93 bronze badges

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

Comments

Sławomir Rosiek · Accepted Answer · 2013-02-01 20:01:51Z

2

Before you read column values from DataReader you must invoke Read() method from data reader.

if (dr.Read())
{
    Label1.Text = dr["FirstName"].ToString();
    Label2.Text = dr["LastName"].ToString();
    Label3.Text = dr[3].ToString();
    Label4.Text = dr["Email"].ToString();
}

You can also try:

SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);

answered Feb 1, 2013 at 20:01

Sławomir Rosiek

4,0733 gold badges28 silver badges44 bronze badges

Comments

Daniel Kelley · Accepted Answer · 2013-02-01 20:12:53Z

You are missing a call to Read() on your reader. I also suggest you wrap your IDisposable objects in using statements as below.

You also seem to be using a strange combination of column names and ordinal positions when retrieving your values from the SqlDataReader.

protected void Button2_Click(object sender, EventArgs e)
{
    using (SqlConnection myConnection = new SqlConnection(GetConnectionString()))
    {
        using (SqlCommand cmd = new SqlCommand("spSelectCustomer", myConnection))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            myConnection.Open();

            SqlParameter custId = cmd.Parameters.AddWithValue("@CustomerId", 10);

            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                if (dr.Read())
                {
                    Label1.Text = dr["FirstName"].ToString();
                    Label2.Text = dr["LastName"].ToString();
                    Label3.Text = dr[3].ToString();
                    Label4.Text = dr["Email"].ToString();
                }
            }
        }
    }
}

TylerH · Accepted Answer · 2020-06-17 21:12:58Z

0

SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while(myReader.Read()) 
{
Console.WriteLine(myReader.GetString(0));
}
myReader.Close();
//Implicitly closes the connection because CommandBehavior.CloseConnection was specified.

Documentation: SqlCommand.ExecuteReader Method

edited Jun 17, 2020 at 21:12

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Feb 1, 2013 at 20:04

Adrian10 BEN

1,2832 gold badges11 silver badges15 bronze badges

Collectives™ on Stack Overflow

retrieving data from sql database using C# with stored procedure

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related