.Net with AS400 connection help

[cjany]

I'm trying to create what should be a simple web application to pull data from an AS400 database.

I could 2 routes:

I currently have 2 SQLDataSource controls on my form. Each has their own select queries. I would like link those two select queries together using the casenumber field which is in both datasets. If I create another data source control to do this, I cannot see the other two queries to grab the fields from.

Here are my datasource select statements:
Datasource1

SELECT TXPRDDTA.CAMAST.MLNUMBER2, TXPRDDTA.CAMAST.LNAME, TXPRDDTA.CAMAST.FNAME, TXPRDDTA.CAMAST.DOB, TXPRDDTA.CADISP.CASENO, TXPRDDTA.CADISP.RESPOFF, TXPRDDTA.CANAME.FNAME AS OffFirst, TXPRDDTA.CANAME.NAME FROM TXPRDDTA.CAMAST, TXPRDDTA.CADISP, TXPRDDTA.CANAME WHERE TXPRDDTA.CAMAST.MLNUMBER1 = TXPRDDTA.CADISP.MLNUMBER1 AND TXPRDDTA.CAMAST.MLNUMBER2 = TXPRDDTA.CADISP.MLNUMBER2 AND TXPRDDTA.CADISP.RESPOFF = TXPRDDTA.CANAME.CODENUMBER AND (TXPRDDTA.CADISP.CASENO = '616932')

Datasource2

SELECT CASENO, ACCTNO, MAX(DATEPD) AS MaxDatePD FROM TXPRDDTA.CASLED GROUP BY CASENO, ACCTNO

My next query should be this:
SELECT CASENO, ACCTNO, MAXDATEPD FROM DATASOURCE2 INNER JOIN DATASOURCE1 WHERE CASENO=CASENO


My other option is a stored procedure, but I don't know how to make the connection to the database. I know about connection strings but I don't know where to add it.

Does anybody have any suggestions or a better way of doing this??

Thank you,
Cheryl

 

[dvannoy]

are you looking for a connection string to an as400?

http://www.connectionstrings.com

then put the connection string in your web config file..

also, if your tables are on seperate servers, and you are using a sql server look into the linked server section on EM.

 

[rwrobb58]

I see it's been awhile since you posted your question regarding linking two SQL query resultsets. Hope you've gotten an answer, but if not, here goes.

This link

http://www.onlamp.com/pub/a/onlamp/2001/07/26/aboutSQL.html

has information on SQL subquery. Basically, you're telling SQL to get the first result set then filter it against the results of a second query.

The author explains how the queries are processed from the "inside out", so that:
SELECT EmployeeName AS Employee FROM Employees
WHERE EmployeeID IN (Select DISTINCT ManagerID from Employees)

processes the Select inside the parens first, then modifies the "outer" SQL statement so that it becomes:
SELECT EmployeeName AS Employee FROM Employees
WHERE EmployeeID IN (61, 63)


Good luck.