Inner join works in Access database but not in ASp

[Beng79]

Good day to all,

I have write a inner query in Access database and it is able to produce results. I have 2 tables, Proj and Functional Features. The query is:

SELECT Proj.ProjID, Proj.ProjName, [Functional Feature].FeatID
FROM Proj INNER JOIN [Functional Feature] ON Proj.ProjID = [Functional Feature].ProjID;

When I copy over the query over to ASP, I got the following error when I try to run it ->
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

The code is below:
SQL = "SELECT ProjID, ProjName, [Functional Feature].FeatID, " _
& "FROM [Proj] INNER JOIN [Functional Feature] ON Proj.ProjID = Functional Feature.ProjID" _
& "WHERE ProjID LIKE '%" & Replace(strSearch, "'", "''") & "%';"_

 

[DotNetGnat]

try this:

SQL = "SELECT ProjID, ProjName, [Functional Feature].FeatID, " _ 
        & "FROM [Proj] INNER JOIN [Functional Feature] ON Proj.ProjID = [Functional Feature].ProjID" _
        & "WHERE ProjID LIKE '%" & Replace(strSearch, "'", "''") & "%';"_

you missed braces around Functional Feature...

-DNG

 

[Sheco]

It also looks like there needs to be a space before the WHERE

 

[Bullschmidt]

And perhaps this may hopefully give you some ideas:

Classic ASP Design Tips - Search Criteria on Multiple Fields

http://www.bullschmidt.com/devtip-searchcriteria.asp

Best regards,
-Paul

http://www.Bullschmidt.com

- Freelance Web and Database Developer

http://www.Bullschmidt.com/DevTip.asp

- Classic ASP Design Tips

 

[Beng79]

I have tried amending the query by adding in the braces around Functional Feature and also tried adding the space before "Where"

I still get the same error. Anyone know of any link that has tutorials or tips on database queries in ASP script?

 

[Sheco]

When debugging SQL statements in ASP, the first thing to try is to use Response.Write to output the SQL statement as it is immediately before execution.

There is often a formatting problem that will be quickly apparent and you will wonder why you didnt try that sooner.

If the SQL looks fine then copy it off your browser and open up MS Access --> New Query in Design Mode --> Add no tables --> Switch into SQL mode w/button in top left --> Paste the SQL copied out of the browser --> Run it and see what happens.

 

[onpnt]

Development practices when using SQL/Interfaces faq333-4896 How can other tools help me develop ASP faq333-4770

 

[Smeglor]

Try this:

SQL = "SELECT ProjID, ProjName, [Functional Feature].FeatID, "_ 
        & "FROM [Proj] INNER JOIN [Functional Feature] ON Proj.ProjID = Functional Feature.ProjID "_
        & "WHERE ProjID LIKE '%" & Replace(strSearch,"'","") & "%'"

I don't know sir...I guess its broke.

 

[Tarwn]

Your also missing some table names in front of repetitive field names and I would double check your speling of the fieldnames and I would remove the semi colon:

SQL = "SELECT [highlight]Proj.[/highlight]ProjID, ProjName, [Functional Feature].FeatID, " _
        & "FROM [Proj] INNER JOIN [Functional Feature] ON Proj.ProjID = [Functional Feature].ProjID" _
        & " WHERE [highlight]Proj.[/highlight]ProjID LIKE '%" & Replace(strSearch, "'", "''") & "%'"_

Oh, and unless this SQL string is supposedto continue to the next line, that underscore at the end ofthe string shoudn't be there.