Query problem with multiple tables...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query problem with multiple tables...

Author

Topic

Dorffius
Starting Member

36 Posts

Posted - 2002-01-30 : 10:39:54

Hey folks,

I know this might not be right up your alley but I figure you might be able to help me solve this. I am running Dreamweaver UltraDev and have to generate a report from an SQL database. I can get the database connection to work fine but my problem comes when generating the report.

I need to take all the records from one table and insert them into my HTML table. That part I can do. The part that is getting me stuck is when I have to fill one of the HTML columns with information from another table in my database. Ultradev allows you to declare recordsets so my first one is just all the information from the first table. Easy enough. The second one has to be based on the first one. I have to take every field in a column and match it up to a field in another table and output the results. Let me try to explain this a different way.

Table 1 (Recordset1) Table 2 (Recordset2)
Product Codes Product Codes
... Product Descriptions
...
...
...

So as you can probably guess I need to get the description of the product from table 2 by matching product codes. Here is my query which always returns the error:

"ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

Query:
"SELECT ENAME
FROM SPROD
WHERE PCODE = '{Recordset1.Prod_Code}'"

Any ideas? Thanks in advance.

Dorffius
Starting Member

36 Posts

Posted - 2002-01-30 : 10:41:44

That didn't come out as I had intended it to.

The one part should be...

Table 1 (Recordset1)
Product Codes
...
...
...

Table 2 (Recordset2)
Product Codes
Product Descriptions

Nazim
A custom title

1408 Posts

Posted - 2002-01-30 : 10:50:22

Try using a join and open a recordset

select a.[Product Codes],b.[Product Description] from table1 a
inner join table2 b
on a.[Product Code]=b.[Product Code]

HTH

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources