Cannot find the error with my query in asp.net

Question

I have a gridview and linkbuttons at every row of this gridview. When i click one of the buttons, i get the values of the courseName,courseCode and term column values of this row. Here is my code:

 int row = -1;
        int.TryParse(e.CommandArgument as string, out row);

        GridViewRow gdrow = instCourses.Rows[row];

        // *** Get the underlying data item - in this case a DataRow
        DataRow dr = ((DataTable)this.instCourses.DataSource).Rows[gdrow.DataItemIndex];

        // *** Retrieve our context
        string courseCode = dr["CourseCode"].ToString();
        string courseNumber = dr["CourseNumber"].ToString();
        string term = dr["Term"].ToString();
        SqlConnection con = new SqlConnection();
        con.ConnectionString = Userfunctions.GetConnectionString();
        con.Open();
        SqlCommand cmd=new SqlCommand("select RegisterTable.StudentID,StudentTable.Name,StudentTable.Surname from RegisterTable INNER JOIN StudentTable where RegisterTable.StudentID=StudentTable.StudentID and RegisterTable.CourseCode=@courseCode and RegisterTable.Term=@term and RegisterTable.CourseNumber=@courseNumber",con);
        cmd.Parameters.AddWithValue("@courseCode", courseCode);
        cmd.Parameters.AddWithValue("@courseNumber", courseNumber);
        cmd.Parameters.AddWithValue("@term", term);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        studList.DataSource = dt;
        studList.DataBind();

The values return as i expect, but i think there is something wrong with my query, it says Incorrect syntax near the keyword 'where'. Can anyone see the problem?

Thanks

Tim Schmelter · Accepted Answer · 2013-06-05 13:31:13Z

Format your sql query and you'll see the problem:

select RegisterTable.StudentID,StudentTable.Name,StudentTable.Surname 
from RegisterTable INNER JOIN StudentTable 
where RegisterTable.StudentID=StudentTable.StudentID 
and RegisterTable.CourseCode=@courseCode 
and RegisterTable.Term=@term 
and RegisterTable.CourseNumber=@courseNumber

There is no ON clause in the join.

from RegisterTable INNER JOIN StudentTable 
    ON RegisterTable.StudentID=StudentTable.StudentID 
where ....

By the way, in C# you can use a verbatim string literal to format your sql:

string sql = @"
    SELECT registertable.studentid, 
           studenttable.name, 
           studenttable.surname 
    FROM   registertable 
           INNER JOIN studenttable 
                   ON registertable.studentid = studenttable.studentid 
    WHERE  registertable.coursecode = @courseCode 
           AND registertable.term = @term 
           AND registertable.coursenumber = @courseNumber";

Then it's easier to locate errors in your sql-queries.

rsbarro · Accepted Answer · 2013-06-05 13:37:25Z

1

The query syntax is not correct. Try:

SELECT 
    RegisterTable.StudentID,
    StudentTable.Name,
    StudentTable.Surname 
FROM RegisterTable 
INNER JOIN StudentTable ON RegisterTable.StudentID = StudentTable.StudentID
WHERE 
    RegisterTable.CourseCode = @courseCode 
    AND RegisterTable.Term = @term 
    AND RegisterTable.CourseNumber = @courseNumber

You need to change the WHERE to ON to complete the JOIN, and then you should move the rest of the criteria to the WHERE clause.

edited Jun 5, 2013 at 13:37

answered Jun 5, 2013 at 13:27

rsbarro

27.4k9 gold badges73 silver badges75 bronze badges

1 Comment

yrazlik Over a year ago

Thanks, that worked the only thing is the name of the column in student table is ID, not StudentID when i fixed it that worked, thank you!

kostas ch. · Accepted Answer · 2013-06-05 13:24:49Z

1

You have an inner join with out ON

select RegisterTable.StudentID,StudentTable.Name,StudentTable.Surname from RegisterTable INNER JOIN StudentTable ON RegisterTable.studentid = StudentTable .id  where RegisterTable.StudentID=StudentTable.StudentID and RegisterTable.CourseCode=@courseCode and RegisterTable.Term=@term and RegisterTable.CourseNumber=@courseNumber

answered Jun 5, 2013 at 13:24

kostas ch.

2,0451 gold badge18 silver badges32 bronze badges

Comments

Sean Airey · Accepted Answer · 2013-06-05 13:27:57Z

1

You need to specify which column from each table to join StudentTable on using ON instead of WHERE:

SELECT RegisterTable.StudentID, StudentTable.Name, StudentTable.Surname
FROM RegisterTable
INNER JOIN StudentTable ON RegisterTable.StudentID = StudentTable.StudentID
WHERE RegisterTable.CourseCode = @courseCode
    AND RegisterTable.Term = @term
    AND RegisterTable.CourseNumber = @courseNumber

answered Jun 5, 2013 at 13:27

Sean Airey

6,3922 gold badges23 silver badges38 bronze badges

Comments

Claudio Redi · Accepted Answer · 2013-06-05 13:30:05Z

1

You're missing the join condition here

FROM RegisterTable INNER JOIN StudentTable

Your inner join should looks like this

from RegisterTable INNER JOIN StudentTable 
   ON RegisterTable.StudentID = StudentTable.StudentId

According your where clause, your query could be written without using inner join, simply using a cartesian product.

select 
        RegisterTable.StudentID,
        StudentTable.Name,
        StudentTable.Surname 
from    RegisterTable, StudentTable 
where   RegisterTable.StudentID=StudentTable.StudentID and 
        RegisterTable.CourseCode=@courseCode and 
        RegisterTable.Term=@term and 
        RegisterTable.CourseNumber=@courseNumber

edited Jun 5, 2013 at 13:30

answered Jun 5, 2013 at 13:24

Claudio Redi

68.6k15 gold badges138 silver badges161 bronze badges

1 Comment

Katie Kilian Over a year ago

You only need one = in that SQL statement.

Collectives™ on Stack Overflow

Cannot find the error with my query in asp.net

5 Answers 5

Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related