mike A

mike A

  • NA
  • 19
  • 0

VB 2008 Express: How do I add row and/or colum to data set or unbind datagrid view from ds while showing results

Feb 22 2010 1:33 PM

I have a query which displays a "cross tab" report using cases and sums.  The report needs to include rows with zero sums for items that have no entry in the database.  I am looking for how to display the query results in a datagrid view and add rows for items with no results in the database.
I imagine this can be done either by adding rows to the dataset without altering the underlying data (which I can't figure out how to do) or
displaying the SQL query results in the datagrid view without binding the view to the data set. (also can't imagine how to do this and extensive searches havent pointed me the right direction yet)
alternately, if there are any serious sql geeks on this forum I imagine that if I could force the sql query to return the data results and add blank rows for items that don't exist in the database, this would solve the problem too (I have failed at this multiple times as well)
Here is the query:
SQL select "cross tab" query below:


SELECT CASE concat(SUBSTR(tbl_JC0.JC0_Field1, 1, 3), '0')
WHEN '0'
THEN    CASE concat(SUBSTR(tbl_JC0.JC0_Field1, 1, 2), '0')
        WHEN '0'
        THEN 'Total'
        ELSE concat(SUBSTR(tbl_JC0.JC0_Field1, 1, 2), '0') END
ELSE NULL END as "Group",
       tbl_JC0.JC0_Field1 as "ACCT",
       SUM(CASE tbl_JC0.TASK_1 WHEN '000449' then tbl_GL10.AMOUNT
                              else 0 end) as "CI 2007-0449",
       SUM(CASE tbl_JC0.TASK_1 WHEN '000550' then tbl_GL10.AMOUNT
                              else 0 end) as "CI 2007-0550",
       SUM(CASE tbl_JC0.TASK_1 WHEN '000551' then tbl_GL10.AMOUNT
                              else 0 end) as "CI 2007-0551",
       SUM(CASE tbl_JC0.TASK_1 WHEN '000552' then tbl_GL10.AMOUNT
                              else 0 end) as "CI 2007-0552",
       SUM(CASE tbl_JC0.TASK_1 WHEN '000553' then tbl_GL10.AMOUNT
                              else 0 end) as "CI 2007-0553",
       SUM(CASE tbl_JC0.TASK_1 WHEN '000554' then tbl_GL10.AMOUNT
                              else 0 end) as "CI 2007-0554",
       SUM(CASE tbl_JC0.TASK_1 WHEN '000555' then tbl_GL10.AMOUNT
                              else 0 end) as "CI 2007-0555",
       SUM(CASE tbl_JC0.TASK_1 WHEN '000556' then tbl_GL10.AMOUNT
                              else 0 end) as "CI 2007-0556",
       SUM(tbl_GL10.AMOUNT) as "Group Totals"
  FROM tbl_GL10,
       tbl_JC0
 WHERE tbl_JC0.SUB_SYSTEM = tbl_GL10.SUBSYSTEM
   AND tbl_JC0.SS_BATCH = tbl_GL10.BATCH
   AND tbl_JC0.SHEET = tbl_GL10.SHEET
   AND tbl_JC0.SEQ = tbl_GL10.SEQUENCE
   AND 'CI' = tbl_JC0.JOB_AREA
   AND '000000002007' = tbl_JC0.JOB_NO_1
   AND (184 < tbl_GL10.S_TRANS_TYPE
         OR 184 > tbl_GL10.S_TRANS_TYPE)
   AND NVL('PO', UID) <> tbl_GL10.SUBSYSTEM
   AND tbl_JC0.JC0_Field1 in ('8111', '8141', '8142', '8176', '8299', '8319', '8333', '8336', '8359', '8369', '8411', '8415', '8459', '8489', '8499', '8519', '8539', '8549', '8557', '8559', '8568', '8571', '8573', '8579', '8589', '8659', '8765', '8766', '8768', '8779', '8833', '8839')
   AND '31-DEC-2009' >= tbl_GL10.TRANSACTION_DATE
   AND (tbl_JC0.SUB_SYSTEM < 'PO'
         OR tbl_JC0.SUB_SYSTEM > 'PO')
 GROUP BY ROLLUP (SUBSTR(tbl_JC0.JC0_Field1, 1, 2), tbl_JC0.JC0_Field1)
 ORDER BY SUBSTR(tbl_JC0.JC0_Field1, 1, 2)

the results of this query are placed in a dataset "ds" and then the datagrid view datasource is set to equal "ds"
Problem is that if I want to add a row, I receive an error that I cant programatically add a row to the datagridview because it has a bound datasource.  Is it possible to unbind the datasource while maintaining the data in the datagrid view?  Or is there a way to add rows to the datasource without chaning the underlying data (for reporting purposes only) or does anyone know how to force the sql result to include categories in the list of job_obj list: '8111','8141','8142','8176','8299'... which have no record in the any of the database tables?
I hope this question is clear and that someone can point me in the right direction.
--thanks, M