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
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