Wrox Programmer Forums
|
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 6th, 2007, 09:47 AM
Authorized User
 
Join Date: Jan 2007
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default Concatenate Rows

Right the problem is that i have an sql query that returns multiple rows and i want to be able to join all these rows into one so that i can use it. so for example

Rows returned
Row1
Row2
Row3

Rolled up rows
Row1 Row2 Row3

The problem that i am having is the main table i am using is fine but when i am referencing a second table i need to combine all the rows so that i can use it in my gridview easily. I have tried to do the above programatically which is possible but there is no way that i can get the jobNumber which is my Primary key. Any Ideas???

 
Old February 6th, 2007, 12:15 PM
Authorized User
 
Join Date: Feb 2007
Posts: 37
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Just need more understanding - when you are binding to GridView why you need to concatenate all rows in one ,you can directly bind to grid ?
when you referencing second table , is that you creating in code or its in dataset coming from db and is that the table in which you trying to hold all rows in one row?

N.B. There is property called DataKeyNames in gridview to which you can assign your primarykey.
 
Old February 6th, 2007, 02:01 PM
Authorized User
 
Join Date: Jan 2007
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well i am creating a report so you have your main Job and then i have a second table that i use to store all the individual jobs. So this could return multiple Rows and i can't get a sql query that will concatenant these rows.This way i wll have something like this in my datagrid

JobNumber---Title---ListofJobs

 
Old February 6th, 2007, 04:16 PM
Authorized User
 
Join Date: Feb 2007
Posts: 37
Thanks: 1
Thanked 0 Times in 0 Posts
Default

so you have two table - Table 1 contains Job Number(PK) and Table 2 contains rest of the fields and you want to display like this below if i am right

  Job#1 MyFirstJob1 5
  Job#1 MyFirstJob2 5
  Job#2 MysecondJob 10
  Job#3 MyThirdJob 15
  <Table 1> < Table 2 >

   there could be more than one rows for Job#.

You may create DataRelation between Two tables using Job Number(if relation can be created)

Dataset.Relations.Add("key_GroupProgramReln", Table1PKColumn,Table2FKColumn);

        then you can use this dataset to fetch the rows using select clause(dataset.select(filter condition)) and create datatable for all rows associated with your PK(you already have in first table so for each pk use select query on dataset). if you want to perform grouping of rows with similar job number than use GridView RowDataBound event.




 
Old February 6th, 2007, 05:36 PM
Authorized User
 
Join Date: Jan 2007
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Right here is my sql statement that i have for the gridview.

SELECT JOBNO, Title, MAILDATE, ActMgr, PRTY FROM dbo.tblTaskJobdtl ORDER BY PRTY

How do i have in my gridview do i attache the select statement for the rows. This will populate with all the rows for that particualar jobno

Select (Column1 + Column2) As Joblist
From Table1
Where Jobno='The current row in gridview'

How i do this on the fly in otherwords how do i pull the primary key when i use gridview. I have set DataKeyNames="JOBNO" which i my primary key

 
Old February 6th, 2007, 06:12 PM
Authorized User
 
Join Date: Feb 2007
Posts: 37
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Dataset 'ds' has Two Table (1,2) , both table will have common column in it Job#.

create Datatable( Table 3) with columns which you need to have in gridview including JobNumber(PK), this may be third table
which you actually bind to gridview at last

Code
// this is High level view but steps are covered

DataRow[] drFilteredRows;
string filterByJobNumber= "";

string JobNumber =string.Empty;

foreach(Datarow dr in Table1.Rows)
{

 JobNumber = dr["JobNO"].ToString();
  // this is the pk from first table which you need to query in second table

 filterByJobNumber= = string.Format("Jobno = '{0}'",JobNumber ) ; // created filter condion i.e selecte * from Table2 where JobNo = ''
 drFilteredRows = ds.Table2.Select(filterByJobNumber); // at this point you should have all rows associated with particular
                            Job Number in datarow collection object


 // add all rows from 'drFilteredRows' into Table 3. Make sure you do it column by column as some columns you may not
 // be needed and also add jobnumber (pk) value too.
}

// After foreach loop you should have table exactly you want with all rows associated with jobnumbers.

//you may directly bind to gridview and also perform grouping for similar job number if you want on RowDataBound Event.









Similar Threads
Thread Thread Starter Forum Replies Last Post
Argh! Why does this concatenate? interrupt Javascript How-To 1 August 13th, 2005 08:44 AM
string concatenate phelkuizon Classic ASP Databases 1 September 13th, 2004 04:08 AM
concatenate with characters lily611 ADO.NET 1 July 21st, 2004 08:10 AM
Concatenate numbers fs22 Javascript 1 April 25th, 2004 06:51 AM
Concatenate 2 variables into one? cpopham Access VBA 2 December 9th, 2003 10:27 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.