Incorrect Syntax


I am trying to use this code:

        string connVac = "Database connection...";
        SqlConnection dbConnVac = new SqlConnection(connVac);

        DataSet dsInfo = new DataSet();

        string sqlVac1 = "Select distinct job_name from tbl_vacancies Where (availability = 'Live')";
        string sqlVac2 = "SELECT job_id, job_name,location, (location) AS region, " +
                                  "(SELECT TOP (1) job_id FROM tbl_vacancies WHERE (availability = 'Live') AND (job_name = tbl_vacancies_1.job_name)) AS job_id " + 
                                  "(SELECT TOP (1) details FROM tbl_vacancies WHERE (availability = 'Live') AND (job_name = tbl_vacancies_1.job_name)) AS theDetails " +
                                    "FROM tbl_vacancies AS tbl_vacancies_1 " +
                                    "WHERE (availability = 'Live') " +
                                    "GROUP BY job_id, job_name, region, location";

        SqlCommand dbVac = new SqlCommand(sqlVac1, dbConnVac);
        SqlDataAdapter adapter = new SqlDataAdapter();

        adapter.SelectCommand = dbVac;
        adapter.Fill(dsInfo, "Jobs");

        dbVac = new SqlCommand(sqlVac2, dbConnVac);

        adapter = new SqlDataAdapter();
        adapter.SelectCommand = dbVac;
        adapter.Fill(dsInfo, "JobDetails");


        int ocount = 0;
        int icount = 0;

        string jobname = "";

        for (ocount = 0; ocount < dsInfo.Tables["Jobs"].Rows.Count; ocount++)
            Label2.Text += "<div id='vacancies'>" +
                    "<p><strong>" + dsInfo.Tables["Jobs"].Rows[ocount]["job_name"].ToString() + "</strong></p>";
            jobname = dsInfo.Tables["Jobs"].Rows[ocount]["job_name"].ToString().ToLower();

            for (icount = 0; icount < dsInfo.Tables["JobDetails"].Rows.Count; icount++)
                if (jobname == dsInfo.Tables["JobDetails"].Rows[icount]["job_name"].ToString().ToLower())
                    Label2.Text += "<div class='vacancy-info'><p>" + dsInfo.Tables["JobDetails"].Rows[icount]["theDetails"].ToString() + "</p><br/>" +
                "<span>" + dsInfo.Tables["JobDetails"].Rows[icount]["region"].ToString() + "</span>" +
            Label2.Text += "</div>";

And i get this error:

Incorrect syntax near the keyword ‘AS’.

What i am trying to do is SELECT details of a job and also SELECT which region the job is in.

I currently have this:

But i need to change it so it shows the Job name, details and then the regions…

What am i doing wrong? Can anyone help?


Sorry i am not explaining properly :confused:

First of all, i didn’t find the syntax error? What is the error? Can you assist?

Once i find the syntax error hopefully i can see what is going wrong…

Any ideas ?

i don’t understand what this means

there appears to be only one table!

also, i still don’t see what you’re trying to do with the TOP

by the way, did you see the syntax error?

Ok what this is doing currently is selecting job applications and grouping them by the job name.

But i wanted to list them by the job name as you can see in the link i have provided…

However, the client now wants to show the job name and the details. I could have just SELECTED job_name, details, but the problem is thatup till now the vacancies have been added separately with the same details.

SO if i select just the details it will SELECT all the details for ALL the different vacancies.

SO i need to SELECT just the one…

Am i doing something wrong?

perhaps you can spot the error a little easier now …

SELECT job_id
     , job_name
     , location
     , (location) AS region
     , ( SELECT TOP (1) job_id 
           FROM tbl_vacancies 
          WHERE (availability = 'Live') 
            AND (job_name = tbl_vacancies_1.job_name) ) AS job_id 
       ( SELECT TOP (1) details 
           FROM tbl_vacancies 
          WHERE (availability = 'Live') 
            AND (job_name = tbl_vacancies_1.job_name)) AS theDetails 
  FROM tbl_vacancies AS tbl_vacancies_1 
 WHERE (availability = 'Live')
    BY job_id
     , job_name
     , region
     , location

notice i have used the leading comma convention, which separates items in a list, e.g. expressions in the SELECT clause

could you take a moment to describe what those subqueries are supposed to be doing for you? what’s the TOP 1 used for?

also, you have a penchant for enclosing stuff in parentheses that doesn require parentheses – this makes your query soggy and hard to light more difficult to read because it adds noise to the signal

the syntax error is caused by a missing comma

i thought my hint (about the leading comma convention) was enough, but apparently not