Incorrect Syntax

.NET
1

Hey,

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";
        dbConnVac.Open();

        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");

        dbConnVac.Close();

        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>" +
                    "</div>";
                }
            }
            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:

http://kidsunlimited.co.uk/vacancies.aspx

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?

Thanks

2

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 ?

3

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?

4

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?

5

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')
GROUP 
    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

6

the syntax error is caused by a missing comma

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

:slight_smile: