Filtering results from SQL table and display them

[Violet_82]

Hi, I run into an interesting thing. I'm looking into SQL a bit, and was reading some tutorial on W3C, so I built a small application to play with SQL queries: inserting info in a table and then retrieve it as it is, filter it etc.
The table contains 5 columns: Id(int and primary key), Name (nvarchar), Surname (nvarchar), Town (nvarchar), Price (decimal) and the data is inserted upon submission of a form. Now, I was trying to retrieve all the surnames and prices of all people living in London. The query in itself is pretty simple: SELECT Surname,Price FROM transactions WHERE Town='London' but there is a problem: I want to display the results in a label as its value and therefore I need another table where copying these results. Here is the full code but unfortunately I get an error, the compiler complains about the "AS":

protected void displayLondonPrices(object sender, EventArgs e) {
        string londonPrice;
        hookup = new SqlConnection("Server=localhost\\SqlExpress;Database=tests;" + "Integrated Security=True");
        strInsert = "SELECT Surname,Price FROM transactions AS Londoners WHERE Town='London'";
        sqlCmd = new SqlCommand(strInsert, hookup);
        hookup.Open();
        reader = sqlCmd.ExecuteReader();
        while (reader.Read()) {
            londonPrice = Convert.ToString(reader["Londoners"]);
            queryResults.Text = "Total in London is " + londonPrice;
        }
        reader.Close();
        hookup.Close();

    }

Essentially, I need something that the reader can read, which is why I used AS Londoners in the query. Is there another way?

[djjeavons]

Hi

Are you trying to show the Total price for all Londoners? If so, the SQL would be different, something like:

SELECT SUM(Price) AS TotalPriceForLondon FROM transactions WHERE Town='London'

Then in your code you can use the TotalPriceForLondon column name which has been created using the AS keyword.

In your previous example, the AS is in the wrong place and while I haven't tested it would indicate that you were trying to create an in memory table (although syntactically it is incorrect).

HTH

[Violet_82]

Thanks, no I'm trying to show the surnames and prices of all the users that live in london, without summing the prices up, so the result will display probably about 2 or 3 rows
I dont' think I'm far from it, I replaced my query with this strInsert = "SELECT Surname,Price AS Londoners FROM transactions WHERE Town='London'"; and the results with this queryResults.Text += "users in London are " + londonPrice; (sorry I just realized that my text says the sum of prices, which is wrong as that's not what I wanted) and get this result:
users in London are 345.00users in London are 23.00users in London are 345.00 which is almost there, except that I want also the Surname

[Violet_82]

OK, I think I got it. There was no need to create a new table in the first place, facepalm, as I can simply read the results from the current tables as they WHERE filter will do the job for me!

 protected void displayLondonPrices(object sender, EventArgs e) {
        string londonPrice;
        string london_surname;
        string london_name;
        hookup = new SqlConnection("Server=localhost\\SqlExpress;Database=tests;" + "Integrated Security=True");        
        strInsert = "SELECT Name,Surname,Price FROM transactions WHERE Town='London'";
        sqlCmd = new SqlCommand(strInsert, hookup);
        hookup.Open();
        reader = sqlCmd.ExecuteReader();
        queryResults.Text = " ";
        while (reader.Read()) {            
            london_name = Convert.ToString(reader["Name"]);
            london_surname = Convert.ToString(reader["Surname"]);
            londonPrice = Convert.ToString(reader["Price"]);
            queryResults.Text += "user in London is " + london_name + " " + london_surname + " and price paid is " + londonPrice + "<br />";
        }
        reader.Close();
        hookup.Close();

    }

The result returned now is:

user in London is Antonio Borrillo and price paid is 345.00
user in London is Thomas Hardy and price paid is 23.00
user in London is Hanna Moos and price paid is 345.00