Generating predicates(''String literal") for a where clause of query dynamically

Question

I have two queries.

1st query: id = ("select PreferredTimeSlot1 from RoutineInfo where TeacherInitials = 'NM'");

2nd query: string query = ("select ordinal_position from information_schema.columns c where table_name = 'FinalRoutine' and table_schema = 'dbo' and column_name ='" + id + "'");

I want to use the id variable to dynamically extract ordinal_position from 2nd query.

Theoretically these query should work. But I am getting syntax error near =.

Any help is appreciated. The sample code is rather large to share here. But if needed I can supply that too.

Here is the code. I hope this piece of code clarifies my question a bit more.

public void ColumnIDPulling()
    {
       int Column;
       try
        {

            string id,selection="NM";
            conn = new SqlConnection(@"Data Source=MIRAZ-PC\SQLEXPRESS;Initial Catalog=ClassRoutine_1;Integrated Security=True");
            conn.Open();
            id = "select PreferredTimeSlot1 from RoutineInfo where TeacherInitials = 'NM'";
            reader = new SqlCommand(id, conn).ExecuteReader();
            textBox1.Text = Convert.ToString(id);
            //id = textBox2.Text;
            reader.Close();
            string query = ("select ordinal_position from information_schema.columns c where table_name = 'FinalRoutine' and table_schema = 'dbo' and column_name ='" + id + "'");
            //string query = ("select ordinal_position from information_schema.columns c where table_name = 'FinalRoutine' and table_schema = 'dbo' and column_name =[9:15-10:30]");
            reader = new SqlCommand(query, conn).ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Column = reader.GetInt32(0);
                    textBox1.Text = Convert.ToString(Column);
                   // textBox1.Text = Convert.ToString(id);

                }
            }
            else
            {
                textBox1.Text = "NF";
            }
            reader.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
       conn.Close();
    }

Answer 1

You need to use ExecuteScalar when you want to get a single value from the database which is you case you need only to return "PreferredTimeSlot1". We use ExecuteReader when you need to return a complete row. The issue that you have is you use ExecuteReader and then you try to convert the row to string instead of the value

public void ColumnIDPulling()
    {
        int Column;
        try
        {

            string query, id, selection = "NM";
            conn = new SqlConnection(@"Data Source=MIRAZ-PC\SQLEXPRESS;Initial Catalog=ClassRoutine_1;Integrated Security=True");
            conn.Open();
            query = "select PreferredTimeSlot1 from RoutineInfo where TeacherInitials = 'NM'";
            id = new SqlCommand(query, conn).ExecuteScalar().ToString();
            if (id != null)
            {
                textBox1.Text = id;

                query = string.Format("select ordinal_position from information_schema.columns c where table_name = 'FinalRoutine' and table_schema = 'dbo' and column_name ='{0}'",id);
                //string query = ("select ordinal_position from information_schema.columns c where table_name = 'FinalRoutine' and table_schema = 'dbo' and column_name =[9:15-10:30]");
                reader = new SqlCommand(query, conn).ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Column = reader.GetInt32(0);
                        textBox1.Text = Convert.ToString(Column);
                        // textBox1.Text = Convert.ToString(id);

                    }
                }
                else
                {
                    textBox1.Text = "NF";
                }
            }

            reader.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        if (conn != null) conn.Close();
    }