CODEWITHSUNDEEP
asp.netsql
user2110714
user2110714

Reputation:

What is wrong with my query in asp.net?

I am trying to fill a gridview by taking data from multiple tables. Here is my code:

 protected void Page_Load(object sender, EventArgs e)
{

        SqlConnection con = new SqlConnection();
        con.ConnectionString = Userfunctions.GetConnectionString();
        con.Open();

            string query = "SELECT RegisterTable.CourseCode,
RegisterTable.courseNumber,
RegisterTable.Term,RegisterTable.Grade,
CourseTable.CourseName,
CourseTable.Level,
CourseTable.Credit 
FROM RegisterTable,CourseTable 
WHERE StudentID='" + MyGlobals.currentID + "' and 
RegisterTable.CourseCode=CourseTable.CourseCode and 
RegisterTable.CourseNumber=CourseTable.CourseNumber and 
RegisterTable.Term=CourseTable.Term";




        SqlDataAdapter adap = new SqlDataAdapter(query, con);

        DataTable tab = new DataTable();

        adap.Fill(tab);

        GridView1.DataSource = tab;
        GridView1.DataBind();


}

This gives an error saying that "Incorrect syntax near the keyword 'where'." Can anyone help me with this? Thanks

EDIT:

Ignore the string's lack of concatenation. It was all on one line and you had to scroll for a mile to see it all. They just made it easier to see.

Upvotes: 1

Views: 82

Answers (1)

pete
pete

Reputation: 25081

Have you tried re-writing your query (so it's not using a cross-join)?

protected void Page_Load(object sender, EventArgs e)
{
    SqlConnection con = null;
    SqlCommand cmd = null;
    SqlDataAdapter adap = null;
    string query = string.Empty();
    DataSet ds = null;
    DataTable tab = null;

    con = new SqlConnection();
    con.ConnectionString = Userfunctions.GetConnectionString();
    query = "SELECT RegisterTable.CourseCode, RegisterTable.CourseNumber, RegisterTable.Term, RegisterTable.Grade, CourseTable.CourseName, CourseTable.Level, CourseTable.Credit FROM RegisterTable INNER JOIN CourseTable ON RegisterTable.CourseCode = CourseTable.CourseCode AND RegisterTable.CourseNumber = CourseTable.CourseNumber AND RegisterTable.Term = CourseTable.Term WHERE StudentID = @StudentID;";

    cmd = new SqlCommand(query, con);
    cmd.Parameters.Add("StudentID", SqlDbType.VarChar, 50).Value = MyGlobals.currentID;

    ds = new DataSet();
    adap = new SqlDataAdapter(cmd);
    adap.Fill(ds);

    if (ds.Tables.Count > 0) {
        tab = ds.Tables(0);
    }

    GridView1.DataSource = tab;
    GridView1.DataBind();
}

Upvotes: 2

Related Questions