Results 1 to 18 of 18

Thread: displaying queries

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2006
    Posts
    8

    displaying queries

    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

  2. #2
    Fanatic Member
    Join Date
    May 2005
    Posts
    608

    Re: displaying queries

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

    Explain it more in depth, please.

    HoraShadow
    I do like the reward system. If you find that my post was useful, rate it.

  3. #3
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174

    Re: displaying queries

    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

    Code:
    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?

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2006
    Posts
    8

    Re: displaying queries

    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.

  5. #5
    Fanatic Member
    Join Date
    May 2005
    Posts
    608

    Re: displaying queries

    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
    I do like the reward system. If you find that my post was useful, rate it.

  6. #6

    Thread Starter
    New Member
    Join Date
    Apr 2006
    Posts
    8

    Re: displaying queries

    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.

  7. #7
    C# Aficionado Lord_Rat's Avatar
    Join Date
    Sep 2001
    Location
    Cave
    Posts
    2,497

    Re: displaying queries

    All this functionality is native to SQL Server

    Code:
    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.
    Need to re-register ASP.NET?
    C:\WINNT\Microsoft.NET\Framework\v#VERSIONNUMBER#\aspnet_regiis -i

    (Edit #VERSIONNUMBER# as needed - do a DIR if you don't know)

  8. #8
    Fanatic Member
    Join Date
    May 2005
    Posts
    608

    Re: displaying queries

    Quote Originally Posted by 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.
    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:
    VB Code:
    1. Label1.Text = myCommand.ExecuteScalar()

    You get the idea...

    HTH
    HoraShadow
    I do like the reward system. If you find that my post was useful, rate it.

  9. #9
    C# Aficionado Lord_Rat's Avatar
    Join Date
    Sep 2001
    Location
    Cave
    Posts
    2,497

    Re: displaying queries

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

    Quote Originally Posted by 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

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

    Code:
    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
    Need to re-register ASP.NET?
    C:\WINNT\Microsoft.NET\Framework\v#VERSIONNUMBER#\aspnet_regiis -i

    (Edit #VERSIONNUMBER# as needed - do a DIR if you don't know)

  10. #10
    C# Aficionado Lord_Rat's Avatar
    Join Date
    Sep 2001
    Location
    Cave
    Posts
    2,497

    Re: displaying queries

    Hey! You removed the post I replied to Hora!!!
    Need to re-register ASP.NET?
    C:\WINNT\Microsoft.NET\Framework\v#VERSIONNUMBER#\aspnet_regiis -i

    (Edit #VERSIONNUMBER# as needed - do a DIR if you don't know)

  11. #11
    Fanatic Member
    Join Date
    May 2005
    Posts
    608

    Re: displaying queries

    Quote Originally Posted by Lord_Rat
    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
    I do like the reward system. If you find that my post was useful, rate it.

  12. #12
    C# Aficionado Lord_Rat's Avatar
    Join Date
    Sep 2001
    Location
    Cave
    Posts
    2,497

    Re: displaying queries

    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.
    Need to re-register ASP.NET?
    C:\WINNT\Microsoft.NET\Framework\v#VERSIONNUMBER#\aspnet_regiis -i

    (Edit #VERSIONNUMBER# as needed - do a DIR if you don't know)

  13. #13
    Fanatic Member
    Join Date
    May 2005
    Posts
    608

    Re: displaying queries

    Yep, that's a very good db design

    That's why the other one surprised me man haha

    HoraShadow
    I do like the reward system. If you find that my post was useful, rate it.

  14. #14
    C# Aficionado Lord_Rat's Avatar
    Join Date
    Sep 2001
    Location
    Cave
    Posts
    2,497

    Re: displaying queries

    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.
    Need to re-register ASP.NET?
    C:\WINNT\Microsoft.NET\Framework\v#VERSIONNUMBER#\aspnet_regiis -i

    (Edit #VERSIONNUMBER# as needed - do a DIR if you don't know)

  15. #15
    C# Aficionado Lord_Rat's Avatar
    Join Date
    Sep 2001
    Location
    Cave
    Posts
    2,497

    Re: displaying queries

    Quote Originally Posted by HoraShadow
    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. =)
    Need to re-register ASP.NET?
    C:\WINNT\Microsoft.NET\Framework\v#VERSIONNUMBER#\aspnet_regiis -i

    (Edit #VERSIONNUMBER# as needed - do a DIR if you don't know)

  16. #16
    Fanatic Member
    Join Date
    May 2005
    Posts
    608

    Re: displaying queries

    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
    I do like the reward system. If you find that my post was useful, rate it.

  17. #17
    C# Aficionado Lord_Rat's Avatar
    Join Date
    Sep 2001
    Location
    Cave
    Posts
    2,497

    Re: displaying queries

    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
    Need to re-register ASP.NET?
    C:\WINNT\Microsoft.NET\Framework\v#VERSIONNUMBER#\aspnet_regiis -i

    (Edit #VERSIONNUMBER# as needed - do a DIR if you don't know)

  18. #18

    Thread Starter
    New Member
    Join Date
    Apr 2006
    Posts
    8

    Re: displaying queries

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width