I have an application that stores students results from Grade 1 - 9. Each grade has 3 terms so there are 27 tables in total to store the grades of a student. A student can start from any grade, therefore it might be that a student has results for grade 4-9 because he/she started from grade 4. So my tables tables are names as follows, Grade1FT,Grade1ST,Grade1TT,Grade2FT,Grade2ST,Grade3TT etc. i.e it follows such a pattern right to Grade9FT,Grade9ST,Grade9TT. Now each grade table contains 10 columns, so the column names are ID,English,Maths,Science,Social,RME,ICT,Drawing,History and Geography.
I have another table called register where all students register their names and are given their unique ID's.
I want the ability where a student can search through all his results from grade 1 - 9 and display in a single datagrid view using his unique student ID which he/she inputs into a textbox. I put in a sample code trying to run but l got an error saying "error near LEFT"
Here is what l tried but with no luck.
What I have tried:
private void btnsearchall_Click(object sender, EventArgs e)
{
cn.Open();
cmd = new SqlCommand(" SELECT register.StudentId LEFT JOIN grade1FT_results,grade1ST_results,grade1TT_results,grade2FT_results,
grade2ST_results,grade2TT_results,grade3FT_results,grade3ST_results,
grade3TT_results,
grade4FT_results, grade4ST_results,grade4TT_results,grade5FT_results,
grade5ST_results,grade5TT_results,
grade6FT_results,grade6ST_results,grade6TT_results, grade7FT_results,grade7ST_results,grade7TT_results,
grade8FT_results,grade8ST_results,grade8TT_results ,grade9FT_results,
grade9ST_results,
grade9TT_results WHERE StudentId=@Id ", cn);
cmd.Parameters.AddWithValue("@Id", txtid.Text);
cmd.ExecuteNonQuery();
DataTable dtable;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
dtable = new DataTable();
da.Fill(dtable);
BindingSource dsource = new BindingSource();
dsource.DataSource = dtable;
datagrid.DataSource = dsource;
da.Update(dtable);
cn.Close();
}