Creating a DataReader based on a variable

Question

I'm trying to create a DataReader based on a variable. I need to fill a series of TextBoxes based on which node in a TreeView is selected, and the Parent node data is different than the Child node data. So, I've written this code:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["PBRConnectionString"].ConnectionString))
{
    try
    {
        conn.Open();
        if (TreeViewAccts.SelectedNode.Parent == null)
        {
            // At the parent level, we filter by Account Group ID
            SqlCommand cmd = new SqlCommand(@"Select [ACCT_GRP_PK], [ACCT_GRP], 'X' as [DOS], [ACTIVE_FLG], [LOAD_BY], [LOAD_TIMESTAMP], [UPDT_BY], [UPDT_TIMESTAMP], [A_FLG_UPDT_BY], [A_FLG_UPDT_TIMESTAMP] from [ACCT_GRP_LIST] where [ACCT_GRP_PK] = @AcctID ORDER BY [ACCT_GRP] ASC", conn);

            cmd.Parameters.AddWithValue("@AcctID", Convert.ToInt32(TreeViewAccts.SelectedValue.ToString()));
        }
        else
        {
            // At the child level, we filter by Account ID
            SqlCommand cmd = new SqlCommand(@"Select [ACCT_PK], [ACCT_NUM], [DOS], [ACTIVE_FLG], [LOAD_BY], [LOAD_TIMESTAMP], [UPDT_BY], [UPDT_TIMESTAMP], [A_FLG_UPDT_BY], [A_FLG_UPDT_TIMESTAMP] from [ACCT_LIST] where [ACCT_PK] = @AcctID ORDER BY [ACCT_NUM] ASC", conn);

            cmd.Parameters.AddWithValue("@AcctID", Convert.ToInt32(TreeViewAccts.SelectedValue.ToString()));
        }

        //Here is the trouble
        using (SqlDataReader reader = cmd.ExecuteReader())
        while (reader.Read())
        {
            txtID.Text = reader.GetByte(0).ToString();
            txtName.Text = reader.GetByte(1).ToString();
            txtDOS.Text = reader.GetByte(2).ToString();
            txtFlag.Text = reader.GetByte(3).ToString();
            txtLoadedBy.Text = reader.GetByte(4).ToString();
            txtLoadedOn.Text = reader.GetByte(5).ToString();
        }

The problem is, at this line:

using (SqlDataReader reader = cmd.ExecuteReader())

it tells me that

'cmd' doesn't exist in the current context.

I'm assuming it's because it's outside of the if/else block where cmd is defined.

How can I make this work?

Igor · Accepted Answer · 2016-01-06 16:56:43Z

You need to set the command outside of the if statement otherwise that object pointer only has scope in that if block and in the else block. You can then assign it in the block.

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["PBRConnectionString"].ConnectionString))
{
    try
    {
        conn.Open();
        SqlCommand cmd = null; // declare it here
        if (TreeViewAccts.SelectedNode.Parent == null)
        {
            // At the parent level, we filter by Account Group ID
            cmd = new SqlCommand(@"Select [ACCT_GRP_PK], [ACCT_GRP], 'X' as [DOS], [ACTIVE_FLG], [LOAD_BY], [LOAD_TIMESTAMP], [UPDT_BY], [UPDT_TIMESTAMP], [A_FLG_UPDT_BY], [A_FLG_UPDT_TIMESTAMP] from [ACCT_GRP_LIST] where [ACCT_GRP_PK] = @AcctID ORDER BY [ACCT_GRP] ASC", conn);

            cmd.Parameters.AddWithValue("@AcctID", Convert.ToInt32(TreeViewAccts.SelectedValue.ToString()));
        }
        else
        {
            // At the child level, we filter by Account ID
            cmd = new SqlCommand(@"Select [ACCT_PK], [ACCT_NUM], [DOS], [ACTIVE_FLG], [LOAD_BY], [LOAD_TIMESTAMP], [UPDT_BY], [UPDT_TIMESTAMP], [A_FLG_UPDT_BY], [A_FLG_UPDT_TIMESTAMP] from [ACCT_LIST] where [ACCT_PK] = @AcctID ORDER BY [ACCT_NUM] ASC", conn);

            cmd.Parameters.AddWithValue("@AcctID", Convert.ToInt32(TreeViewAccts.SelectedValue.ToString()));
        }

Salah Akbari · Accepted Answer · 2016-01-06 19:23:49Z

From MSDN:

If you declare a variable within a block construct such as an If statement, that variable's scope is only until the end of the block. The lifetime is until the procedure ends.

The problem is that you're defining your cmd variable within the scope of your if-else block, so it's not known outside of those blocks. The solution is declaring the cmd out of the if-else block. Like This:

try
{
    SqlCommand cmd = null;
    ....
    if (TreeViewAccts.SelectedNode.Parent == null)
    { 
        cmd = new SqlCommand(@"Select [ACCT_GRP_PK], [ACCT_GRP], 'X' as [DOS], [ACTIVE_FLG], [LOAD_BY], [LOAD_TIMESTAMP], [UPDT_BY], [UPDT_TIMESTAMP], [A_FLG_UPDT_BY], [A_FLG_UPDT_TIMESTAMP] from [ACCT_GRP_LIST] where [ACCT_GRP_PK] = @AcctID ORDER BY [ACCT_GRP] ASC", conn);
        ....
    }
    else
    {
         // At the child level, we filter by Account ID
         cmd = new SqlCommand(@"Select [ACCT_PK], [ACCT_NUM], [DOS], [ACTIVE_FLG], [LOAD_BY], [LOAD_TIMESTAMP], [UPDT_BY], [UPDT_TIMESTAMP], [A_FLG_UPDT_BY], [A_FLG_UPDT_TIMESTAMP] from [ACCT_LIST] where [ACCT_PK] = @AcctID ORDER BY [ACCT_NUM] ASC", conn);
         ....
    }
}

Collectives™ on Stack Overflow

Creating a DataReader based on a variable

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