displaying queries

[Phil2006]

Hi,

In VS2005 I have created in the dataset a query to return Total where total is the sum of 5 fields in the database table.

eg each customer has a circuit reading 1,2,3,4 and 5. How can I assign the total field in the same customer table to be this Query. ie if I change the figures in either of the circuit reading fields the total will be updated?

When I run the query preview it returns the correct value as total but I cannot find out how to get this to work in my table Total field. Thanks a lot for the help.

Phil

[HoraShadow]

I'm not understanding your question. Are you asking how to display the value of the DataSet?

Explain it more in depth, please.

HoraShadow

[mendhak]

If I've understood correctly, you have a TOTAL field in your table, in addition to fields1,2,3,4 and 5.

You want to be able to update any of the 5 fields and have TOTAL be updated... right?

Your SQL Query should look like... this

SELECT field1,field2,field3,field4,field5,totalfields FROM tablename WHERE somecondition

Since they will (ought to) be in a dataset, update the 'total' field to be the sum of the other five fields whenever the user clicks on the submit button.

Although, a better question is, why are you storing the 'total' field in the database, when this is a purely dynamic value?

[Phil2006]

Hi all,

Thanks so much for the help so far. Yes first of all i undertand that the value is dynamic. I will try to explain in more depth now.

My system stores electricity meter readings for customers on a month by month basis. At the end of every month data will enter the database from Circuits 1 to 5. ie a nuberic value. I want the database to be able to calculate the total of these 5 circuits in a total field. I need to keep this field as customers have a facility to log in and view their past monthly bills.

Therefore i need that total data to be held. So I am wondering can such a sql statement be hooked into a field in a table. If not can you advise me on how to display it. Please note I am a relative novice at VS2005.

Again, your advice is much appreciated.

[HoraShadow]

The way I would do it, each time you need to display the total, just SUM the fields.

SELECT SUM(field1, field2, field3, field4, field5) as Total FROM table WHERE condition

I would not store the total on a field.

And you are already storing the date, so the condition for the DATE part should be pretty easy.

You can do: WHERE MONTH(myDate)='MAY' AND YEAR(myDate)='2006'
That way you only get the right 5 values date wise, you still have to do the clause for the correct client, and all the other related conditions.

HTH
HoraShadow

[Phil2006]

OK thats great thanks, I have built this query in the dataset and the correct result is displayed in the preview query function. How then do I get this total to be displayed on my site? Thanks.

[Lord_Rat]

All this functionality is native to SQL Server

CREATE TABLE DataTable
(
  PRIKEY INT NOT NULL PRIMARY KEY IDENTITY,
  Col1 INT NOT NULL DEFAULT(0),
  Col2 INT NOT NULL DEFAULT(0),
  Col3 INT NOT NULL DEFAULT(0),
  Col4 INT NOT NULL DEFAULT(0),
  Col5 INT NOT NULL DEFAULT(0),
  TOTCOL AS (Col1 + Col2 + Col3 + Col4 + Col5)
)

To insert data:
INSERT INTO DataTable(Col1, Col2, Col3, Col4, Col5) VALUES (1, 2, 3, 4, 5)

Then,
SELECT * FROM DataTable

Results:
PRIKEY, COL1, COL2, COL3, COL4, COL5, TOTCOL
1, 1, 2, 3, 4, 5, 15

To Update a value:
UPDATE DataTable SET Col5 = 10 WHERE PriKey = 1

Then, reselecting data:
SELECT * FROM DataTable

Results:
PRIKEY, COL1, COL2, COL3, COL4, COL5, TOTCOL
1, 1, 2, 3, 4, 10, 20


Hope this is what you were looking for.

[HoraShadow]

OK thats great thanks, I have built this query in the dataset and the correct result is displayed in the preview query function. How then do I get this total to be displayed on my site? Thanks.

To access that data, you can either fill a DataSet, a DataReader, or use the executeScalar method of the Command object (SqlCommand, oledbCommand, etc, depends which one you are using).

The best way, since it's just one value, is to use the ExecuteScalar(). The other two options are a waste of resources.

Just assign the result of the ExecuteScalar() to a label.

Ex:

Label1.Text = myCommand.ExecuteScalar()

You get the idea...

HTH
HoraShadow

[Lord_Rat]

I answered according the requirements as posted by the original user:

Hi,

In VS2005 I have created in the dataset a query to return Total where total is the sum of 5 fields in the database table.

eg each customer has a circuit reading 1,2,3,4 and 5. How can I assign the total field in the same customer table to be this Query. ie if I change the figures in either of the circuit reading fields the total will be updated?

When I run the query preview it returns the correct value as total but I cannot find out how to get this to work in my table Total field. Thanks a lot for the help.

Phil

If I were making the database design by scratch it would look instead like this:

CREATE TABLE t_Customer
(
	PRIKEY INT NOT NULL PRIMARY KEY IDENTITY,
	CustomerName VARCHAR(100) NOT NULL DEFAULT(''),
	CustomerAddress VARCHAR(100) NOT NULL DEFAULT('')
)

CREATE TABLE t_Meters
(
	PRIKEY INT NOT NULL PRIMARY KEY IDENTITY,
	MeterName VARCHAR(100) NOT NULL DEFAULT(''),
	MeterAddress VARCHAR(100) NOT NULL DEFAULT('')
)

CREATE TABLE t_BillingPeriods
(
	PRIKEY INT NOT NULL PRIMARY KEY IDENTITY,
	BillingStart DATETIME NOT NULL,
	BillingEnd DATETIME NOT NULL
)

CREATE TABLE t_MeterReads
(
	PRIKEY INT NOT NULL PRIMARY KEY IDENTITY,
	MeterID INT NOT NULL,
	CustomerID INT NOT NULL,
	BillingPeriodID INT NOT NULL,
	MeterValue INT NOT NULL --Contains the actual reading for the period
)

CREATE PROCEDURE sp_GetCustomerMeterReadings
	@CustID INT
AS
BEGIN
	SELECT BillingEnd, MeterName, MeterValue 
		FROM t_MeterReads 
			LEFT JOIN t_Meters ON t_MeterReads.MeterID = t_Meters.PRIKEY
			LEFT JOIN t_Customer ON t_MeterReads.CustomerID = t_Customer.PRIKEY
			LEFT JOIN t_BillingPeriods ON t_MeterReads.BillingPeriodID = t_BillingPeriods.PRIKEY
		WHERE CustomerID = @CustID
		GROUP BY BillingEnd, MeterName
		ORDER BY BillingEnd, MeterName
	WITH ROLLUP
END

[Lord_Rat]

Hey! You removed the post I replied to Hora!!!

[HoraShadow]

Hey! You removed the post I replied to Hora!!!

Yeah man, because after I posted I thought maybe you would take it bad. And there's no reason for that.

So I just erased it. But you were fast :P that post only lived like two minutes.

HoraShadow

[Lord_Rat]

I didn't take it personally. I thought perhaps you saw something I didn't.

I usually answer questions using the rules asked in the original question, even if they break rules I would normally follow.

Hopefully the revised code block helps set your mind at ease regarding my abilities at implementation. If I still have done something that you find to be glaring please let me know so that I can consider and improve.

[HoraShadow]

Yep, that's a very good db design

That's why the other one surprised me man haha

HoraShadow

[Lord_Rat]

Or, just curous... Did you mean that someone should not have table columns that have functions? (Calculate column expressions)

While such columns will break normalization in theory, they don't technically because the column isn't actually stored as data, and certainly not updatable data. (If you try to update a calculated column expression, you get an error). Finally, they add flexibility and performance, as long as they only reference columns defined within the same table.

[Lord_Rat]

Yep, that's a very good db design

That's why the other one surprised me man haha

HoraShadow

OK Cool. =)

Yeah, I don't usually take the time to preach normalization to people. It takes a mind to learn normalization.

I only preach it here at work where it directly affects me if people don't follow. =)

[HoraShadow]

Actually, I only read the table structure and went Hmmm?

The core was already not normalized, that was enough for me.

Yeah, I don't usually take the time to preach normalization to people. It takes a mind to learn normalization.

For me it depends on the day. Sometimes I do the full explanation, sometimes just answer the particular question. And sometimes I go like 'it's 3 am, what the heck am I doing reading a forum'.

HoraShadow

[Lord_Rat]

lol, understood.

The big offender here at work is that people get almost to the point of normalization, but they keep making tables that dont have primary keys.

grr

[Phil2006]

Thanks all so much for the help, much appreciated. Ill have a play about with your solutions.