Database Query Problems

[Mighty]

I am trying to pull data from two tables by joining the tables and I am getting errors. I can pull the required columns from the tables individually but when I join them the command always fails no matter if I use a JOIN command or just use a where clause. I don't specify any locks, cursortypes or cursorlocations in my connection string and I am wondering if it is anything to do with this. I have enclosed my code and the resultant output below:

Code:

<%
' Connect to the database to get a list of the products for the customer
Dim Connect, rs, objdbconn, sql1, sql2, sql3

Connect = &quot;Provider=BtrvProv;Data Source=E:\Users\elliottn\maxdat;Provider String = &quot; & _
&quot;'INTERNAL_SQL_SUPPORT=3'&quot;
Set objdbconn = Server.CreateObject( &quot;ADODB.Connection&quot; )
objdbconn.Open Connect

' Create a recordset object
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.ActiveConnection = objdbconn

sql1 = &quot;SELECT PRTNUM_01, PMDES1_01 from Prtmst&quot;
sql2 = &quot;SELECT QTYOH_06 from PRTSTK WHERE NETFLG_06 = 'Y'&quot;
sql3 = &quot;SELECT Prtmst.PRTNUM_01, Prtmst.PMDES1_01, PRTSTK.QTYOH_06 FROM Prtmst,&quot;
sql3 = sql3 + &quot;PRTSTK WHERE Prtmst.PRTNUM_01 = PRTSTK.PRTNUM_06 AND PRTSTK.NETFLG_06 = 'Y'&quot;

response.write &quot;SQL1: &quot; & sql1 & vbcrlf
rs.Open sql1
response.write &quot;sql1 was successful&quot; & vbcrlf & vbcrlf
rs.close

response.write &quot;SQL2: &quot; & sql2 & vbcrlf
rs.Open sql2
response.write &quot;sql2 was successful&quot; & vbcrlf
rs.close

response.write &quot;SQL3: &quot; & sql3 & vbcrlf
rs.Open sql3
response.write &quot;sql3 was successful&quot; & vbcrlf & vbcrlf
rs.close

set rs = nothing : objdbconn.close : set objdbconn = nothing
%>

OUTPUT:

SQL1: SELECT PRTNUM_01, PMDES1_01 from Prtmst
sql1 was successful
SQL2: SELECT QTYOH_06 from PRTSTK WHERE NETFLG_06 = 'Y'
sql2 was successful
SQL3: SELECT Prtmst.PRTNUM_01, Prtmst.PMDES1_01, PRTSTK.QTYOH_06 FROM Prtmst,PRTSTK WHERE Prtmst.PRTNUM_01 = PRTSTK.PRTNUM_06 AND PRTSTK.NETFLG_06 = 'Y'

Provider error '80004004'

Operation aborted

/clientarea/stckquery.asp, line 39
Mise Le Meas,

Mighty

 

[link9]

There is no JOIN statement there....

If you are using Access or SQL Server, I would suggest you go in to their respective query builders and visually build up the query that you want.

Then, just view the SQL code to get your desired results. It's a great way to learn SQL if you aren't a &quot;book type&quot; (or even if you are!)...

good luck!
Paul Prewett

 

[Mighty]

I know that there is no JOIN statement but I am selecting fields from two different tables (prtmst and prtstk) where partnum in one table is equal to part number in another. That should work. I have also tried to do an INNER JOIN on the two tables using the same common fields and it fails:

SELECT Prtmst.PRTNUM_01, Prtmst.PMDES1_01, PRTSTK.QTYOH_06 FROM Prtmst
INNER JOIN PRTSTK ON Prtmst.Prtnum_01 = PRTSTK.PRTNUM_06 WHERE
PRTSTK.NETFLG_06 = ‘Y’
Mise Le Meas,

Mighty

 

[FlatHead]

try something like:

select PRTNUM_01, PMDES1_01 from Prtmst as C
Join PRTSTK as P ON C.PRTNUM_01 = P.PRTNUM_06
where P.NETFLG_06 = 'Y'


I hope this helps

Regards
h@rrI$

 

[Mighty]

The problem is not the SQL as I have tried it outside of ASP and it works fine. Mise Le Meas,

Mighty

 

[FlatHead]

LINK9 THIS IS FOR YOU

You made a sudgestion about respective query builders and visually build up the query that you want.

I know sql but your way may be helpfull when building up complex queries and joins )))

Im working on MS SQL server 7.0 (entrerprise manager)
Can you please tell me how can i purform(your tip) and where about is the right options ?