Grouping Data Problem

Hello all

I am using SQL Server 2000. I have a table of over 1 million accounting transactions. I need to be able to remove all items that have contra items.

e.g

debit �G100 - credit �G100 - debit of �G125 ( I only want to see the debit of �G125)

I can achieve this in MS Access by grouping the key fields, suming the value fields and using the First() or Last() command for columns that I need to display but not group.

How can I achieve this in SQL?

All help appreciated.

hi how does your table look like?

can you please post the schema

|||

I am still a little new to SQL so am not entirely sure what the 'schema' is so here's the code from the create table command: -

Please note that I am importing data from an old DB3 file so don't have a lot of control over the data structure.

(
[KEY] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[POL_IDX] [nvarchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[ITEM] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[AGCY] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BRCH] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[DEPT] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[MONTH] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[AMT] [float] NULL ,
[TBAL] [float] NULL ,
[DESC] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[EDATE] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[INPUTDATE] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[FLAG] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[TRANS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[BILL] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CPAID] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[STATE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BCO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[ICO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[MCO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[COM_P] [float] NULL ,
[PR] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P] [float] NULL ,
[PR2] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P2] [float] NULL ,
[BR_P] [float] NULL ,
[TYPE] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[POL] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[OINT] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[LOANNUM] [nvarchar] (11) COLLATE Latin1_General_CI_AS NULL ,
[PRT] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CLOSED] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[OP_ID] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[CSR] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[JOURNAL] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[DOCTYPE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[REFER] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[PRINTED] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CODE] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[PC] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BIN] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[SPLIT_PLAN] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[PR3] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P3] [float] NULL ,
[CO_AMT] [float] NULL ,
[PR_AMT1] [float] NULL ,
[PR_AMT2] [float] NULL ,
[PR_AMT3] [float] NULL ,
[BR_AMT] [float] NULL ,
[PPAID] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PFLAG] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CO_TYPE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE1] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE2] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE3] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CPAID_MO] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[ADJUST] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[STATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[TYPEGROUP] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[REFER1] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[NOTE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CONGLOM] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[SKIP] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[EXTRA] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL
)

|||I have now managed to achived this by using the same approach as I did with Access, however used the min,max options

Group by/ Having question

Hi all
I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null.
This is what I have so far..
select accounts.name, packages.guid, packages.datecreated,
packages.dateremoved
from accounts inner join packages on packages.account = accounts.guid
where packages.datecreated > '2007-01-12 00:00:00'
and (packages.dateremoved is null)
order by packages.datecreated
This gives me number of accounts and i can see the accounts that have
more than 1 package where the dateremoved field is null but I only want
to get those accounts..
I did try this;
select accounts.guid, packages.guid, packages.datecreated
from accounts inner join packages on packages.account = accounts.guid
where packages.dateremoved is null
and (packages.dateremoved in ( select packages.dateremoved
from packages
GROUP BY (packages.dateremoved)
HAVING count(packages.dateremoved) > 1
but this gives me now results.. not error, just empty results.
Can you guys and girls help me with this?
thanx
SELECT accounts.*
FROM accounts
JOIN (select account
from packages
where datecreated > '2007-01-12 00:00:00'
and dateremoved is null
group by account
HAVING COUNT(*) > 1) as X
ON accounts.guid = X.account
If all you wanted was the list of account numbers, just run the inner
query. If you also need other account information you need the rest
too.
Roy Harvey
Beacon Falls, CT
On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:

>Hi all
>I have these two tables, account and package and i want to get all
>accounts that have more than 1 package where dateremoved is null.
>This is what I have so far..
>select accounts.name, packages.guid, packages.datecreated,
>packages.dateremoved
>from accounts inner join packages on packages.account = accounts.guid
>where packages.datecreated > '2007-01-12 00:00:00'
>and (packages.dateremoved is null)
>order by packages.datecreated
>This gives me number of accounts and i can see the accounts that have
>more than 1 package where the dateremoved field is null but I only want
>to get those accounts..
>I did try this;
>select accounts.guid, packages.guid, packages.datecreated
>from accounts inner join packages on packages.account = accounts.guid
>where packages.dateremoved is null
>and (packages.dateremoved in ( select packages.dateremoved
>from packages
>GROUP BY (packages.dateremoved)
>HAVING count(packages.dateremoved) > 1
>but this gives me now results.. not error, just empty results.
>Can you guys and girls help me with this?
>thanx
|||This works like a charm..
Thanks Roy, regards to you from Iceland.
Roy Harvey wrote:[vbcol=seagreen]
> SELECT accounts.*
> FROM accounts
> JOIN (select account
> from packages
> where datecreated > '2007-01-12 00:00:00'
> and dateremoved is null
> group by account
> HAVING COUNT(*) > 1) as X
> ON accounts.guid = X.account
> If all you wanted was the list of account numbers, just run the inner
> query. If you also need other account information you need the rest
> too.
> Roy Harvey
> Beacon Falls, CT
> On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:
|||You should consider using the plan analyzer, I guess Roys solution will
take longer than just using the EXISTS.
-Jens
|||On 16 Jan 2007 06:29:40 -0800, "Jens" <Jens@.sqlserver2005.de> wrote:

>You should consider using the plan analyzer, I guess Roys solution will
>take longer than just using the EXISTS.
>-Jens
Recall that the requirement was "to get all accounts that have more
than 1 package where dateremoved is null." I believe the EXISTS
version posted does not test for more than one, it tests for at least
one.
Roy Harvey
Beacon Falls, CT
|||You are right, I missed the "more" than one.

Group by/ Having question

Hi all
I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null.
This is what I have so far..
select accounts.name, packages.guid, packages.datecreated,
packages.dateremoved
from accounts inner join packages on packages.account = accounts.guid
where packages.datecreated > '2007-01-12 00:00:00'
and (packages.dateremoved is null)
order by packages.datecreated
This gives me number of accounts and i can see the accounts that have
more than 1 package where the dateremoved field is null but I only want
to get those accounts..
I did try this;
select accounts.guid, packages.guid, packages.datecreated
from accounts inner join packages on packages.account = accounts.guid
where packages.dateremoved is null
and (packages.dateremoved in ( select packages.dateremoved
from packages
GROUP BY (packages.dateremoved)
HAVING count(packages.dateremoved) > 1
but this gives me now results.. not error, just empty results.
Can you guys and girls help me with this'
thanxAccording to yout definition:
"I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null. "
SELECT * FROM Accounts A
WHERE EXISTS
(
SELECT * FROM Package P
WHERE dateremoved IS NULL
AND P.account = A.guid
AND where packages.datecreated > '2007-01-12 00:00:00'
)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||SELECT accounts.*
FROM accounts
JOIN (select account
from packages
where datecreated > '2007-01-12 00:00:00'
and dateremoved is null
group by account
HAVING COUNT(*) > 1) as X
ON accounts.guid = X.account
If all you wanted was the list of account numbers, just run the inner
query. If you also need other account information you need the rest
too.
Roy Harvey
Beacon Falls, CT
On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:

>Hi all
>I have these two tables, account and package and i want to get all
>accounts that have more than 1 package where dateremoved is null.
>This is what I have so far..
>select accounts.name, packages.guid, packages.datecreated,
>packages.dateremoved
>from accounts inner join packages on packages.account = accounts.guid
>where packages.datecreated > '2007-01-12 00:00:00'
>and (packages.dateremoved is null)
>order by packages.datecreated
>This gives me number of accounts and i can see the accounts that have
>more than 1 package where the dateremoved field is null but I only want
>to get those accounts..
>I did try this;
>select accounts.guid, packages.guid, packages.datecreated
>from accounts inner join packages on packages.account = accounts.guid
>where packages.dateremoved is null
>and (packages.dateremoved in ( select packages.dateremoved
>from packages
>GROUP BY (packages.dateremoved)
>HAVING count(packages.dateremoved) > 1
>but this gives me now results.. not error, just empty results.
>Can you guys and girls help me with this'
>thanx|||This works like a charm..
Thanks Roy, regards to you from Iceland.
Roy Harvey wrote:[vbcol=seagreen]
> SELECT accounts.*
> FROM accounts
> JOIN (select account
> from packages
> where datecreated > '2007-01-12 00:00:00'
> and dateremoved is null
> group by account
> HAVING COUNT(*) > 1) as X
> ON accounts.guid = X.account
> If all you wanted was the list of account numbers, just run the inner
> query. If you also need other account information you need the rest
> too.
> Roy Harvey
> Beacon Falls, CT
> On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:
>|||You should consider using the plan analyzer, I guess Roys solution will
take longer than just using the EXISTS.
-Jens|||On 16 Jan 2007 06:29:40 -0800, "Jens" <Jens@.sqlserver2005.de> wrote:

>You should consider using the plan analyzer, I guess Roys solution will
>take longer than just using the EXISTS.
>-Jens
Recall that the requirement was "to get all accounts that have more
than 1 package where dateremoved is null." I believe the EXISTS
version posted does not test for more than one, it tests for at least
one.
Roy Harvey
Beacon Falls, CT|||You are right, I missed the "more" than one.

Group By, Max(date) query problem

Hello all:

I have an invoice header and detail tables and a customer table using sqlserver 2005. The Detail invoice table has price and product id. The header has the date and customerID.

I need to create a list of the most recent invoice date (and the product price) for each product for each customer. I can't use the group by because when I select both the max(date) and the price (as well as the productID and customerID which all have to be included in the group by) , I get more than one date per product per customer. I can only get the most recent date when I leave out the price and headerdetailID from the field selection.

Any help would be appreciated. Here is sample data & results.

invoiceHeadertable

invheaderID CustomerID InvoiceDate

1 40 1/1/2006

2 40 4/1/2006

3 80 3/1/2006

4 80 7/1/2006

5 80 8/12/2006

invoicedetailtable

invdetail ID invheaderID productcode price

11 1 AA 1.50

12 1 BB 1.30

13 1 CC 1.00

21 2 AA 1.40

23 2 CC 2.00

24 3 AA 2.00

25 3 CC 2.10

26 3 EE 1.10

27 4 AA 1.00

28 4 CC 2.00

29 4 EE 0.99

34 5 EE 1.55

CustomerTable

CustomerID Customername

40 johnCorp

80 maryCorp

Results

customer product most recent invoice(for this product) price

JohnCorp 40 AA 4/1/2006 1.40

JohnCorp 40 BB 1/1/2006 1.30

JohnCorp 40 CC 4/1/2006 2.00

maryCorp 80 AA 7/1/2006 1.40

maryCorp 80 CC 7 /1/2006 2.00

maryCorp 80 EE 8/12/2006 1.55

Something like this 'should' work for you. (Untested)

SELECT
dt.Customer,
dt.Product,
dt.InvoiceDate
d.Price
FROM InvoiceDetailTable d
JOIN ( SELECT
Customer,
Product,
InvoiceDate = max( InvoiceDate )
FROM InvoiceHeaderTable h
JOIN InvoiceDetailTable d
ON h.InvHeaderID = d.InvHeaderID
GROUP BY
Customer,
Product
) dt
ON ( d.Customer = dt.Customer
AND d.Product = dt.Product
AND d.InvoiceDate = dt.InvoiceDate
)

|||Crapola, didn't check your tables first. So ignore the prevoius exercise in 'egg on my face'.|||

IF there was no more than one invoice per day for a customer, you could, in the derived table (dt), include in the SELECT list:

InvHeaderID = max( InvHeaderID )

And then JOIN ON InvHeaderID instead of the three fields I indicated.

Of course, if there were two invoices for the same customer in a day, that would still not be the correct solution.

|||

Well, most of the time there would only be one invoice. But as is always the case, there can be an exception.

I had also wondered if the rank and partition function in sql server 2005 could apply, then one could just use a select query (if this is possible) to return the # 1 invoice per group but I have found no examples showing this used in a group by and where there are multiple tables involved.

Thanks

smhaig

|||

select

ct.CustomerName as Customer,

ct.CustomerID as CustomerID,

idt.ProductCode as Product,

iht.InvoiceDate as MostRecentInvoiceDate,

idt.Price as Price

from InvoiceDetailTable as idt

join InvoiceHeaderTable as iht

on idt.invheaderID = iht.invheaderID

join CustomerTable as ct

on ct.CustomerID = iht.CustomerID

where not exists

(select 1

from InvoiceDetailTable as idtx

join InvoiceHeaderTable as ihtx

on idtx.InvHeaderID = ihtx.InvHeaderID

where idtx.ProductCode = idt.ProductCode

and ihtx.CustomerID = iht.CustomerID

and ihtx.InvoiceDate > iht.InvoiceDate)

order by

ct.CustomerName,

idt.ProductCode

|||

-- Using SQL Server 2005

set nocount on
set dateformat mdy

create table invoiceHeadertable(invheaderID int,CustomerID int,InvoiceDate datetime)
insert into invoiceHeadertable(invheaderID ,CustomerID ,InvoiceDate )
select 1, 40, '1/1/2006' union all
select 2, 40, '4/1/2006' union all
select 3, 80, '3/1/2006' union all
select 4, 80, '7/1/2006' union all
select 5, 80, '8/12/2006'

create table invoicedetailtable(invdetailID int, invheaderID int, productcode char(2), price decimal(5,2))
insert into invoicedetailtable(invdetailID , invheaderID , productcode , price )
select 11, 1, 'AA', 1.50 union all
select 12, 1, 'BB', 1.30 union all
select 13, 1, 'CC', 1.00 union all
select 21, 2, 'AA', 1.40 union all
select 23, 2, 'CC', 2.00 union all
select 24, 3, 'AA', 2.00 union all
select 25, 3, 'CC', 2.10 union all
select 26, 3, 'EE', 1.10 union all
select 27, 4, 'AA', 1.00 union all
select 28, 4, 'CC', 2.00 union all
select 29, 4, 'EE', 0.99 union all
select 34, 5, 'EE', 1.55

create table CustomerTable(CustomerID int, Customername varchar(10))
insert into CustomerTable(CustomerID , Customername )
select 40, 'johnCorp' union all
select 80, 'maryCorp';

with cte(customer,product,[most recent invoice(for this product)],price,rn)
as (
select c.Customername,
d.productcode,
h.InvoiceDate,
d.price,
rank() over(partition by c.Customername,d.productcode order by h.InvoiceDate desc)
from CustomerTable c
inner join invoiceHeadertable h on h.CustomerID=c.CustomerID
inner join invoicedetailtable d on d.invheaderID=h.invheaderID
)
select customer,
product,
[most recent invoice(for this product)],
price
from CTE
where rn=1
order by customer,product

|||

I want to thank Mark and Ron for their solutions and Arnie for getting me to think about two invoices on the same day for the same product and customer (which do exist actually).

I found these solutions to be on the level of advanced lessons for me to study. I have always had trouble with group by when I needed a unique ID on a table where I was selecting a max or min or first one, etc. on another field in the same table.

I have not found any good examples other than very basic ones for rank and partition so if anyone has a good site let me know. Meanwhile I will study what I have as I now have two great ways to solve my problem.

I did not give a duplicate item with my sample data so I will see how this sql 2005 query deals with this. I seem to remember something about ties and ranking and perhaps I could also use select distinct when I select the rank = 1.

The second standard sql query (Ron's) shows me both invoices when there are 2 on same date. It may be that this is the way the data should be displayed if the prices are different, so I will need to check further on that and see if I can tweak these 2 queries to deal with that.

Thank you all again

smHaig

|||

And my thanks to Mark for demonstrating the more modern solution of the two!

Ron

GROUP BY and aggregate functions not supported with FOR XML AUTO

Hi All
I am trying to ouput the results from my query in the form of XML. The query
is like this:
SELECT a, b, COUNT(S.c ) AS x
FROM s
GROUP BY a,b
ORDER BY a,b
FOR XML AUTO, ELEMENTS
If run this, i get an error like this:
Server: Msg 6821, Level 16, State 1, Line 1
GROUP BY and aggregate functions are currently not supported with FOR XML
AUTO.
Is there any way i can do this? Thank you all in advance.MittyKom,
Try:
SELECT * FROM (SELECT TOP 100 PERCENT a, b, COUNT(c) AS x
FROM s
GROUP BY a,b
ORDER BY a,b ) AS Y
FOR XML AUTO, ELEMENTS
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:2D6F411D-7CA3-4EEB-A05F-1FBF03FFE7E3@.microsoft.com...
> Hi All
> I am trying to ouput the results from my query in the form of XML. The
> query
> is like this:
> SELECT a, b, COUNT(S.c ) AS x
> FROM s
> GROUP BY a,b
> ORDER BY a,b
> FOR XML AUTO, ELEMENTS
>
> If run this, i get an error like this:
> Server: Msg 6821, Level 16, State 1, Line 1
> GROUP BY and aggregate functions are currently not supported with FOR XML
> AUTO.
> Is there any way i can do this? Thank you all in advance.|||SELECT a, b, x
FROM
(SELECT a, b, COUNT(S.c) AS x
FROM S
GROUP BY a, b) AS T
ORDER BY a, b
FOR XML AUTO, ELEMENTS ;
David Portas
SQL Server MVP
--

GridView update, with SqlDataSource UpdateCommand set from Code-behind. (C#)

Hi all

I have a GridView on an aspx page, that is enabled for editing, deletion and sorting.

In the Page_Load event of the aspx page, i add a SqlDataSource to the page, and bind the source to the GridView.

When i click the update, or delete button, it makes a PostBack, but nothing is affected. I'm sure this has got something to do with the parameters.

First, i tried having the GridView.AutoGenerateColumns set to True. I have also tried adding the columns manually, but no affect here either.

The code for setting the commands, and adding the SqlDataSource to the page are as follows:

string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
string selectCommand = "SELECT * FROM rammekategori";

SqlDataSource ds = new SqlDataSource(strProvider, strConn, selectCommand);
ds.ID = "RammeKategoriDS";
ds.UpdateCommand = "UPDATE rammekategori SET Kategoribeskrivelse = @.Kategoribeskrivelse WHERE (Kategorinavn = @.Kategorinavn)";
ds.DeleteCommand = "DELETE FROM rammekategori WHERE (Kategorinavn = @.Kategorinavn)";

Parameter Kategorinavn = new Parameter("Kategorinavn", TypeCode.String);
Parameter Kategoribeskrivelse = new Parameter("Kategoribeskrivelse", TypeCode.String);
ds.UpdateParameters.Add(Kategorinavn);
ds.UpdateParameters.Add(Kategoribeskrivelse);
ds.DeleteParameters.Add(Kategorinavn);

Page.Controls.Add(ds);

SqlDataSource m_SqlDataSource = Page.FindControl("RammeKategoriDS") as SqlDataSource;

if (m_SqlDataSource != null)
{
this.gvRammeKategorier.DataSourceID = m_SqlDataSource.ID;
}

As mentioned - no affect at all!

Thanks in advance - MartinHN

It turned out, that the SQL-statements where wrong. I got it all to work now, by using a ?-mark, instead of @.Parametername in the SQL.

So this works:

string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
string selectCommand = "SELECT * FROM rammekategori";

SqlDataSource ds = new SqlDataSource(strProvider, strConn, selectCommand);
ds.ID = "RammeKategoriDS";
ds.UpdateCommand = "UPDATE rammekategori SET Kategoribeskrivelse = ? WHERE Kategorinavn = ?";
ds.DeleteCommand = "DELETE FROM rammekategori WHERE Kategorinavn = ?";

Parameter Kategorinavn = new Parameter("Kategorinavn");
Parameter Kategoribeskrivelse = new Parameter("Kategoribeskrivelse");
ds.UpdateParameters.Add(Kategorinavn);
ds.UpdateParameters.Add(Kategoribeskrivelse);
ds.DeleteParameters.Add(Kategorinavn);

Page.Controls.Add(ds);

SqlDataSource m_SqlDataSource = Page.FindControl("RammeKategoriDS") as SqlDataSource;

if (m_SqlDataSource != null)
{
this.gvRammeKategorier.DataSourceID = m_SqlDataSource.ID;
}

I was working on a MySQL server, and not a MS-SQL server, as I normally do...

|||Is there any particular reason why you are adding the SqlDataSource dynamically rather than declaring it in your .aspx code?|||

>>Is there any particular reason why you are adding the SqlDataSource dynamically rather than declaring it in your .aspx code?

Yes - there sure is. I want to define alle data-access information, such as SQL-statements in a lower-tier-layer, so i would be able to remove the GUI, and change it with another GUI. It just gives a better architecture to it...

|||

martinhn wrote:

Yes - there sure is. I want to define alle data-access information, such as SQL-statements in a lower-tier-layer, so i would be able to remove the GUI, and change it with another GUI. It just gives a better architecture to it...

It sounds like the ObjectDataSource is more suited for what you are trying to do.

HTH,
Ryan