Error Problem

[danz321]

ok so im getting this error on my asp page, not quite sure why..

it only started coming up after i put "jobs.TypeId FROM jobs," in... i dont know if it's clashin with the other FROM (jobs.JobSectorId FROM jobs;) or wat.

as uve probly guessed im pretty new to this asp stuff. could do with a little help please!!

here's the error:


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

/add_to_jobs.asp, line 11


And here's the code:

<%
Dim adoCOn
Dim rsAddComments
Dim strSQL
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("jobs.mdb")
Set rsAddComments = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT jobs.EmailAddress, jobs.TypeId FROM jobs, jobs.JobTitle, jobs.Salary1, jobs.Salary2, jobs.Per, jobs.SalaryRateOTE, jobs.SalaryRateIncBen, jobs.SalaryRateProRata, jobs.SalaryRateNegShow, jobs.SalaryRateNegHide, jobs.Town, jobs.Region, jobs.JobSectorId FROM jobs;"
rsAddComments.CursorType = 2
rsAddComments.LockType = 3
rsAddComments.Open strSQL, adoCon <-- Line 11
rsAddComments.AddNew
rsAddComments.Fields("EmailAddress") = Request.Form("EmailAddress")
rsAddComments.Fields("TypeId") = Request.Form("Type")
rsAddComments.Fields("JobTitle") = Request.Form("JobTitle")
rsAddComments.Fields("Salary1") = Request.Form("Salary1")
rsAddComments.Fields("Salary2") = Request.Form("Salary2")
rsAddComments.Fields("Per") = Request.Form("Per")
rsAddComments.Fields("Town") = Request.Form("Town")
rsAddComments.Fields("Region") = Request.Form("Region")
rsAddComments.Fields("JobSectorId") = Request.Form("JobSector")
rsAddComments.Update
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing
Response.Redirect "jobadded.html"
%>

can't work out whats wrong, i'm a bit of a newb :p

please help.

dan

[buntine]

Your SQL query should look like this:

strSQL = "SELECT jobs.EmailAddress, jobs.TypeId, jobs.JobTitle, jobs.Salary1, jobs.Salary2, jobs.Per, jobs.SalaryRateOTE, jobs.SalaryRateIncBen, jobs.SalaryRateProRata, jobs.SalaryRateNegShow, jobs.SalaryRateNegHide, jobs.Town, jobs.Region, jobs.JobSectorId FROM jobs;"

Each SQL query can only contain one FROM clause.

Note, you do not need to prefix the columns with the name of the table. SQL is smart enough to work that part out. ;) You should only use table and column aliases when your dealing with an SQL query that grabs data from several tables.

Regards.

[wmif]

props on naming out the columns instead of * though. :)