title: asp.net c# ExecuteReader() error
body: <p>The following code produces an error.
  dbo.getit works fine when I call it directly on the server.  The error occurs at cmd.ExecuteReader() .  What am I doing wrong?</p>
  
  <pre><code>    string user;
      string pw;
      SqlDataReader dr = null;
      SqlConnection conn = new SqlConnection("Data Source=xxx;Initial Catalog=myDB;    Integrated Security=True");
  
      user = Username.Text.Trim();
      pw = Password.Text.Trim();
  
      conn.Open();
  
     try {
          SqlCommand cmd = new SqlCommand("dbo.getit", conn);
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.AddWithValue("@param1", user);
          cmd.Parameters.AddWithValue("@param2", pw);
  
          dr = cmd.ExecuteReader();
  
          while ( dr.Read() )
          {
              Session["username"] = user;
              // Session["admin"] = 
              // Session["completed"] =
              Server.Transfer("all_is_well.aspx");
  
          }
          conn.Close();
          conn.Dispose();
  
      } catch (Exception ex) {
          if (dr != null)
          {
              dr.Close();
              conn.Close();
          }
          Server.Transfer("ERROR.aspx");
      }
  </code></pre>
  
  <p>SOLUTION:
  Replace the two corresponding lines above with these:</p>
  
  <pre><code>SqlCommand cmd = new SqlCommand("select * from dbo.getit(@param1, @param2);", conn);
  cmd.CommandType = CommandType.text;
  </code></pre>