July 8, 2009 at 9:14 am
Hello all.
I have a VB6 program that I need to call SQL2000 server and return a recordset.
That in its self is not a problem.
What I need help with is looping thru records in one table and then select count on another then return results back to VB6 program.
Example to follow.
Table 1
Fields: = RecordID DeptNumber DepartmentDescription
Table 2
Fields: = This table has many fields but I only need to return the count of records from one status field.
What I need to do is return a recordset to my software that has Table1.DeptNumber Table1.DepartmentDescription and then select count from table2 where table1.DeptNumber = NonApproved from table 2.
Any help would be much appreciated.
Robert Wallace
Let me explain more.
I am already returning the first recordset back to my VB app.
Then I am looping thru the first recordset and returning the count for each row in the first recordset.
That is not a problem.
I was hoping to be able to have this count lookup at the server because having this travel to the VB app is not very efficient.
Let me give an example....
Table 1
DeptNumber DeptDescription
1 Charlotte Field Service
2 Hickory Main Shop
5 Monroe Main Shop
7 Ashville Field Service
Now I need to take DeptNumber and select count from another table.
I need something like this returned
DeptNumber DeptDescription RecordsCounted
1 Charlotte Field Service 12
2 Hickory Main Shop 154
5 Monroe Main Shop 201
7 Ashville Field Service 0
I dont need multiple recordsets returned.
Bob Wallace
July 8, 2009 at 9:37 am
Your query can return two different recordsets, and then both can be extracted within the VB.
NextRecordset is the method that you'll need:
http://www.freevbcode.com/ShowCode.asp?ID=3058
http://msdn.microsoft.com/en-us/library/ms677569(VS.85).aspx
BrainDonor
July 8, 2009 at 3:40 pm
Hi Bob,
why don't you set up a view on your SQL Server containing all information you need (department info and record count)?
Therewith you wouldn't have to worry about doing two queries per dept...
Maybe you should also consider to get all rows at once with a single statement instead doing it row by row (depending on thy application you're working on.)
July 9, 2009 at 3:26 am
So does your VB even need the first record set, or is the ultimate aim to have the second record set within the VB?
1 Charlotte Field Service 12
2 Hickory Main Shop 154
5 Monroe Main Shop 201
7 Ashville Field Service 0
So I assume that this output means there are 12 records for Dept Code 1 within the other table, 154 for Dept Code 2 etc.?
In which case instead of outputing the first, original record set why don't you put that data into a temporary table within the Stored Procedure and then use that table to retrieve the counts? Append the counts to the records within the temporary table and then output that table.
BrainDonor
July 10, 2009 at 12:05 pm
Sorry for not getting back with you all sooner.
I guess what I really need is a view that I call and have the join in that view.
I am even having problems creating this join at this point.
If anybody can help me with this I would appreciate it very much.
What I need to do is have all departments,descriptions from table 1 and include a field with the count of records with say 'NonApproved' status from table 2.
If anybody can help me please chime in.:w00t:
Bob
July 10, 2009 at 1:44 pm
Hi Bob,
following please find two options on how to define a view depending on how you'd need to get the results.
Since we don't have any more information regarding table definition and sample data I had to make some assumptions...
I hope this will give you something to start with.
If you have further questions please provide sample data as described in my signature.
--option 1: one column for each status
--CREATE VIEW [DptmtStatusCnt] AS
SELECT
t1.DeptNumber AS DeptNumber,
t1.DeptDescription AS DeptDescription,
ISNULL (SUM(CASE WHEN t2.Status ='NonApproved' THEN 1 ELSE 0 END),0) AS NonApprovedRecords,
ISNULL (SUM(CASE WHEN t2.Status ='Approved' THEN 1 ELSE 0 END),0) AS ApprovedRecords
FROM @Table1 t1
LEFT OUTER JOIN @anothertable t2
ON t1.DeptNumber = t2.DeptNumber
GROUP BY t1.DeptNumber,t1.DeptDescription
/* result set
DeptNumberDeptDescriptionNonApprovedRecordsApprovedRecords
7Ashville Field Service00
1Charlotte Field Service510
2Hickory Main Shop57
5Monroe Main Shop11
*/
--option 2: one row for each status
--CREATE VIEW [DptmtStatusCnt] AS
SELECT
t1.DeptNumber AS DeptNumber,
t1.DeptDescription AS DeptDescription,
ISNULL(t2.Status,'UNKNOWN') AS ApprovalStatus,
ISNULL (COUNT(t2.DeptNumber),0) AS RecordCount
FROM @Table1 t1
LEFT OUTER JOIN @anothertable t2
ON t1.DeptNumber = t2.DeptNumber
GROUP BY t1.DeptNumber,t1.DeptDescription, t2.Status
/* result set
DeptNumberDeptDescriptionApprovalStatusRecordCount
1Charlotte Field ServiceApproved10
1Charlotte Field ServiceNonApproved5
2Hickory Main ShopApproved7
2Hickory Main ShopNonApproved5
5Monroe Main ShopApproved1
5Monroe Main ShopNonApproved1
7Ashville Field ServiceUNKNOWN0*/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply