SELECT Using INNER JOIN Not Working

[ph74]

Can anyone help with the following that I think should be working but just comes back with error'80004005'

SQLQuery = "SELECT Users.DBID, Position.Position FROM Users INNER JOIN Position ON Users.Position = Position.PositionID"

Any ideas appreciated

Paul.

 

[FesterSXS]

are users.position and position.positionID of the same data type?

Is your DB connectivity OK - have you tried doing a simple SELECT PositionID FROM Position?


Tony

 

[ph74]

Thanks for the quick reply.
I have checked the data type and they are the same, and i have just done a quick [bold]SELECT PositionID FROM Position[/bold] and i get the same error? What does that mean?? Surely a Select * from any table should work??

Paul.

 

[gpzcrasher]

Try lookin at the following

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q306518#XSLTH4120121123120121120120

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q306269

Might push you in the right direction

--------------------------------
Rome did not create a great empire by having meetings, they did it by killing all those who opposed them.

 

[DotNetGnat]

try this:

SQLQuery = "SELECT [Users].DBID, [Position].Position FROM [Users] INNER JOIN [Position] ON [Users].Position = [Position].PositionID"

-DNG

 

[Sheco]

It often helps to try to debug SQL separate from your ASP.

So if your database is SQL Server then first make sure that your SQL statement will work in Query Analyzer... if it is MS Access then make a new query in design mode and then click the little button in the top left to switch to SQL view.

 

[onpnt]

Sheco's advice is the best and the more educational one for future development.

there is even a section in the FAQ's just for this topic
Development practices when using SQL/Interfaces faq333-4896
How can other tools help me develop ASP faq333-4770

 

[ph74]

Thanks for all the advice, I tried the code supplied and still comes up with the same error?
I have also taken the SQL code and run this from within Access on SQL view, and the query runs as expected??
The only difference in FAQ333-4770 is i connect to the database with this:

Set DBConnection = Server.Createobject("ADODB.Connection")
DBConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\tyne\db-data$\User Details\User Details Data.mdb"

Not sure if that will make any diference?

Paul.

 

[Chopstik]

The problem may not necessarily be with your query, per se. Can you post the relevant code where it indicates you're having the problem? Point out the line (in bold) within your code where the error indicates and, hopefully, we can help to identify where your problem may be occurring.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[ph74]

This is the code i currently have at the beginning of the asp page,

<%
Set DBConnection = Server.Createobject("ADODB.Connection")
DBConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\tyne\db-data$\User Details\User Details Data.mdb"

'SQLQuery = "SELECT * FROM Users WHERE "
'SQLQuery = SQLQuery & "StartDate Between DateAdd('m',-3,Now()) And DateAdd('m',2,Now()) "
'SQLQuery = SQLQuery & "ORDER BY StartDate DESC, Surname ASC"

SQLQuery = "SELECT [Users].DBID, [Position].Position FROM [Users] INNER JOIN [Position] ON [Users].Position = [Position].PositionID"

'SQLQuery = "SELECT * FROM data_pos"

SET DBOutput = DBConnection.Execute(SQLQuery)
%>

When running the page i get the following :
error '80004005'
/hrintranet/hr-listusers_copy.asp, line 15

Line 15 is SET DBOutput = DBConnection.Execute(SQLQuery)

Thanks.

 

[Chopstik]

I admit that I'm a little rusty as it's been a while, but don't you need to first set DBOutput as a recordset and then try to populate the recordset? Something like this:

Set DBOutput = Server.CreateObject("adodb.recordset")
Set DBOutput = DBConnection.Execute(SQLQuery)

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[tsuji]

>don't you need to first set DBOutput as a recordset and then try to populate the recordset?
I guess no...

To debug, start here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q306518