syntax of query

[de Source]

hi
i am new to sql i want to get some values from different tables and i have made my own logic like that

"SELECT course_name, course_id 
FROM tbl_course 
WHERE course_id = (SELECT course_id 
                    FROM tbl_enrollment 
                    WHERE student_id = '" + lbl_StudentID.Text + "')"

what could the correct sytax for this
the subquery returns more than one value that is
there are some course ids corresponding to student id in enrollment table
now i want to select course name and course id from course table which matches those course ids

[de Source]

may be some kind of join would b needed but its not working
string sqlCourse = "SELECT tbl_course.course_name, tbl_course.course_id FROM tbl_course INNER JOIN tbl_enrollment ON tbl_course.course_id=tbl_enrollment.course_id ORDER BY Customers.CustomerName";

[Reverend Jim]

Try

SELECT tbl_course.course_id, tbl_course.course_name
  FROM tbl_enrollment INNER JOIN tbl_course ON tbl_enrollment.course_id = tbl_course.course_id
 WHERE student_id = '" + lbl_StudentID.Text + "')"

When you get the query working I suggest you change it to use parameters. Examples of how to do this with OleDB or SqlClient can be found here. If student_id is numeric then you don't need the single quotes around it.

[de Source]

thanks budy u did this thumbs up..

i have got another issue its rather c# but related to this thread i am discussing here

private void BindCourseDropDownList()
    {

        DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection(GetConnectionString());
        try
        {
            connection.Open();                

            string sqlCourse = "SELECT tbl_course.course_id, tbl_course.course_name 
            FROM tbl_enrollment 
            INNER JOIN tbl_course 
            ON tbl_enrollment.course_id = tbl_course.course_id 
            WHERE student_id = '" + lbl_StudentID.Text + "'";

            SqlCommand sqlCmdCourse = new SqlCommand(sqlCourse, connection);
            SqlDataAdapter sqlDaCourse = new SqlDataAdapter(sqlCmdCourse);

            sqlDaCourse.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                ddl_Course.DataSource = dt;
                ddl_Course.DataTextField = "course_name";
                ddl_Course.DataValueField = "course_id";
                ddl_Course.DataBind();
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
    }

this code bind my drop down list though i am getting courses name but
on ddl_course.selectedvalue i always get 1 as the value whats wrong in this code

[Reverend Jim]

Sorry. I don't do C#. Perhaps someone else can step in.

[M.Waqas Aslam]

use this

 ddl_Course.DisplayField = "course_name";
ddl_Course.ValueField = "course_id";

[de Source]

thanks waqas :)