how to solve Fill: SelectCommand.Connection property has not been initialized

Question

I have a class which I have written all my methods there. then I have my firstpage of web application which I have some codes there. I face this

• $exception {"Fill: SelectCommand.Connection property has not been initialized."} System.Exception {System.InvalidOperationException}

in the line :

sda.Fill(dsUsers.tblMembers);

here is my code so I hope u can help me:

namespace MosquesNetwork
{
public class cUsers2
{
    SqlConnection scn;
    SqlDataAdapter sda;
    SqlCommandBuilder scb;
    SqlCommand SqlStr;


    public cUsers2()
    {
        SqlConnection scn = new SqlConnection (ConfigurationManager.ConnectionStrings["MosquesDBConnectionString"].ConnectionString);

        sda = new SqlDataAdapter();
        scb = new SqlCommandBuilder(sda);


    }

public bool CheckUserName(string UserName)
    {
        DsUsers2 dsUsers=new DsUsers2();
         bool Success;
        string SqlStr="select * from tblUsers where Username='"+UserName+"' ";
        sda.SelectCommand=new SqlCommand(SqlStr, scn);
        sda.Fill(dsUsers.tblMembers);
        sda.Fill(dsUsers.tblMembers);
        Success=dsUsers.tblMembers.Rows.Count>0;
        return Success;
    }

then I have this code while the login button is pressed in webform:

 protected void Button1_Click(object sender, EventArgs e)
    {

        if (txtuser.Text.Trim().Length>0 && txtpass.Value.Length>0 )
        {  
            cUsers2 cUsers=new cUsers2();
            DsUsers2 dsUser=new DsUsers2();
            bool Success;
            if (txtuser.Text.Trim()=="admin")
            {
                Success=cUsers.CheckAdminPass(txtuser.Text.Trim(),txtpass.Value.Trim());  
                if (Success)
                {
                    Response.Redirect("WebForm2.aspx");
                }
            }

            dsUser=cUsers.Checkpassword(txtuser.Text.Trim(), txtpass.Value.Trim(),out Success);
            if(Success)
            {
                Session["ID"]=dsUser.tblMembers.Rows[0][dsUser.tblMembers.IDUserColumn].ToString();
                System.Web.HttpContext.Current.Response.Redirect("frmProfile.aspx");

            }
            else lblerror.Text="invalid username & password";

        }
    }

Answer 1

Look at your constructor:

SqlConnection scn = new SqlConnection(...);

That's declaring a separate scn local variable, so the scn instance variable is staying as null. If you just change it to:

scn = new SqlConnection(...);

that may well fix the immediate problem. It's not a nice design in my view - I'd prefer to create the SqlConnection in a using block where you actually need it - but that's what's going wrong for now...