Help with SQL Group By please (results returned into / shown in C#.Net)!

Hi all - i'm trying to put together my first .Net web page (have switched from Dreamweaver to VWD - VWD keeps swapping my tab-indents for spaces, and none of the options stop it!).

Here's a table that i'm trying to query: ItemID | ReviewRating | ReviewRatingOutOf

As i'm sure you've guessed, it's a reviews table, where there can be several records with the same ItemID and different (or the same) ReviewRating and ReviewRatingOutOf's. As the reviews are collected from lots of sources, the ReviewRatingOutOf will change (one review might be 3/5, while the next, for the same ItemID, could be 8/10, etc). Now, what i'm trying to do is return a list of ItemID's ordered by their RATIO (which is the sum of each ItemID's ReviewRating's divided by the sum of each ItemID's ReviewRatingsOutOf's - in other words, average score). My first guess was this:

"SELECT DISTINCT ItemID FROM Reviews ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" - unfortunately that doesn't work (problems with the SUM aggregate functions, and overflow errors, whatever they are). Now, this string works: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)" - right now, that just adds up the ReviewRatings, so an item with 10 reviews that only got awarded 1/5, 1/10, 1/8, etc (all 1's, therefore achieving a combined ReviewRating of 10 out of a very much higher ReviewRatingOutOf), would appear higher than an item with 1 review that got 5/5. Making the string into this: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" (which is what I need), unfortunately gives me errors...

Anyone have any ideas? Is there possibly a way to simply read all the distinct ItemID's with SQL, then get the two SUM's for each ItemID, then calculate the ratio of the two SUM's, and stick the ItemID's and the ratio into some sort of array, and have C# order the array for me, based on the ratio? I'd appreciate an example of that if possible, as i'm a complete C# beginner :-)

Thanks in advance!

anyone?|||

SELECT ItemID

FROM Reviews

GROUP BY ItemID

ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)

or this:

SELECT ItemID

FROM Reviews

GROUP BY ItemID

ORDER BY AVG(ReviewRating/ReviewRatingOutOf)

If ReviewRating is an integer, and ReviewRatingOutOf is an integer, then you should cast one to a float like:

SELECT ItemID

FROM Reviews

GROUP BY ItemID

ORDER BY AVG(CAST(ReviewRating AS float)/ReviewRatingOutOf)

otherwise you won't get what you expect because (int) / (int) will always return the floor of the result. So 1/2=0 1/8=0, 1/10=0, and the only way to score a non-zero result would be a perfect 5/5, 10/10, etc. By casting one to a float, then it the divide will return a float result. So (float)1/(int)2=(float)0.5

|||

Thanks for trying Motley - but all those methods give an 'overflow' error. I'm using Access 2003 by the way - just realised that may be important and that I hadn't mentioned it!

Any more help really appreciated!

|||Is there a record that ReviewRatingOutOf is 0?|||

Ahhh - yeah there is. Is there an easy way around that, or is it best to simply remove/change it? It's a review that didn't have an accompanying score (obviously!).

Thanks again for you help so far Motley.

|||

Think I got it working myself - here's my final code:

"SELECT ItemID FROM Reviews GROUP BY ItemID HAVING SUM(ReviewRatingOutOf) > 0 ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf) DESC"

That seems to give me exactly what I was after, with the highest-rated items arriving first. Unless you see something that'll cause problems, it's the perfect solution for me (that also allows for items with ReviewRatingOutOf = 0).

Thanks again for your help!

|||Just make sure that if both ReviewRating and ReviewRatingOutOf are both defined as an integer type (int,bigint,smallint,tinyint,bit) that you cast one or both to a float before the division or you'll get unexpected results.

Help with SQL Function

Hello All:
From the Following Table, I want to enter a Temperature and then have the
SQL Function Return the Web Color
-- TemperatureIndex --
ID TempMin TempMax WebColor
1 0 9 #E59DCB
2 10 19 #8569FA
3 20 29 #3F9CFB
4 30 39 #73E96F
I would like to Enter a Temperature and return the following WebColor output
15 -> #E59DCB
28 -> #3F9CFB
32 -> #73E96FCREATE TABLE TemperatureIndex
(
ID int NOT NULL,
TempMin int NOT NULL,
TempMax int NOT NULL,
WebColor char(7) NOT NULL
)
GO
INSERT INTO TemperatureIndex VALUES(1, 0, 9, '#E59DCB')
INSERT INTO TemperatureIndex VALUES(2, 10, 19, '#8569FA')
INSERT INTO TemperatureIndex VALUES(3, 20, 29, '#3F9CFB')
INSERT INTO TemperatureIndex VALUES(4, 30, 39, '#73E96F')
GO
CREATE UNIQUE CLUSTERED INDEX TemperatureIndex_cdx
ON TemperatureIndex(TempMin, TempMax)
GO
ALTER TABLE TemperatureIndex
ADD CONSTRAINT PK_TemperatureIndex
PRIMARY KEY NONCLUSTERED (ID)
GO
CREATE FUNCTION dbo.GetWebColorForTemperature(@.Temp int)
RETURNS char(7)
AS
BEGIN
RETURN (SELECT WebColor
FROM TemperatureIndex
WHERE @.Temp BETWEEN TempMin AND TempMax
)
END
GO
SELECT dbo.GetWebColorForTemperature(15)
SELECT dbo.GetWebColorForTemperature(28)
SELECT dbo.GetWebColorForTemperature(32)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Stuart Shay" <sshay@.yahoo.com> wrote in message
news:uzg9rvG%23FHA.160@.TK2MSFTNGP12.phx.gbl...
> Hello All:
> From the Following Table, I want to enter a Temperature and then have the
> SQL Function Return the Web Color
> -- TemperatureIndex --
> ID TempMin TempMax WebColor
> 1 0 9 #E59DCB
> 2 10 19 #8569FA
> 3 20 29 #3F9CFB
> 4 30 39 #73E96F
> I would like to Enter a Temperature and return the following WebColor
> output
> 15 -> #E59DCB
> 28 -> #3F9CFB
> 32 -> #73E96F
>|||Dan:
Thanks for your help !!!
Best
Stuart
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uev6wbH%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> CREATE TABLE TemperatureIndex
> (
> ID int NOT NULL,
> TempMin int NOT NULL,
> TempMax int NOT NULL,
> WebColor char(7) NOT NULL
> )
> GO
> INSERT INTO TemperatureIndex VALUES(1, 0, 9, '#E59DCB')
> INSERT INTO TemperatureIndex VALUES(2, 10, 19, '#8569FA')
> INSERT INTO TemperatureIndex VALUES(3, 20, 29, '#3F9CFB')
> INSERT INTO TemperatureIndex VALUES(4, 30, 39, '#73E96F')
> GO
> CREATE UNIQUE CLUSTERED INDEX TemperatureIndex_cdx
> ON TemperatureIndex(TempMin, TempMax)
> GO
> ALTER TABLE TemperatureIndex
> ADD CONSTRAINT PK_TemperatureIndex
> PRIMARY KEY NONCLUSTERED (ID)
> GO
> CREATE FUNCTION dbo.GetWebColorForTemperature(@.Temp int)
> RETURNS char(7)
> AS
> BEGIN
> RETURN (SELECT WebColor
> FROM TemperatureIndex
> WHERE @.Temp BETWEEN TempMin AND TempMax
> )
> END
> GO
> SELECT dbo.GetWebColorForTemperature(15)
> SELECT dbo.GetWebColorForTemperature(28)
> SELECT dbo.GetWebColorForTemperature(32)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stuart Shay" <sshay@.yahoo.com> wrote in message
> news:uzg9rvG%23FHA.160@.TK2MSFTNGP12.phx.gbl...
>

Help with SQL DateTime

hello, i created a web app. that will query the SQL DB for the DateTime. My SQL DB has the following DateTime format: '2003-08-06 08:55:00.000', but when i i query the database and show the results to my webpage it displays a different format: '8/6/2003 8:55:00 AM', and also when i try to query the database again with the DateTime it gave to me it returns no result. why is this happening? please help... thanks!show me your sql, and what object are you setting the datetime field to?

Help with Security!

I have a web application for which I am required to authenticate users at the database level (no generic or application type logins permitted). I am not permitted to use Active Directory because we do not have AD installed. We chose to use standard SQL accounts. I have two groups of users:

1. Normal users
2. Super Users (can do everything a normal user can do, plus can add/delete/modify user accounts)

When a Super User is created, they are added to three fixed roles Security Administrator (Server Role) and db_accessadmin and db_securityadmin (Database Roles).

A normal user is assigned to some custom roles that we created, but is not assigned to any fixed roles (database or server) other than the default Public role.

The problem comes when a Super User attempt to add another Super user. The process fails because the Super user does not have sufficient privileges to run sp_addrolemember. The following two statements fail because of permissions:

sp_addrolemember 'db_securityadmin', N'mySuperUser'
sp_addrolemember 'db_accessadmin', N'mySuperUser'

Additional research indicates that I am required to be a member of the SysAdmin fixed role of the db_Owner role in order to have access to sp_addrolemember.

Does anyone have any suggestions for a workaround? This is pretty frustrating. I am unwilling to let my Super Users have sysadmin or db_owner rights. These grant far more access than is needed. I just want my super users to be able to add and administer normal user accounts and other Super User accounts.

Thanks,

Hugh ScottI think you are using SQL2K, if so how about using Application roles? I have not done this before but it might be worth checking out. Look up Application Roles in BOL.

My theory is that you could setup an application role with dbo authority. When you need to create a superuser you would make an additional connection to the db using an application role, create the super user and then drop the connection.|||Ding!

You are correct. I should have stated that we were using SQL 2K. I like your idea and I will give it a shot.

Thanks!

Hugh

Originally posted by Paul Young
I think you are using SQL2K, if so how about using Application roles? I have not done this before but it might be worth checking out. Look up Application Roles in BOL.

My theory is that you could setup an application role with dbo authority. When you need to create a superuser you would make an additional connection to the db using an application role, create the super user and then drop the connection.sql

Help with security model for RS implementation needed

We're running Reporting Services (wSP1) on a Win 2003 server box. Presently
(for development), SQL Server, the web application and RS all run on the
same box. I've configured an app pool in IIS under which Reports,
ReportServer and the web application run. I'm also collecting credentials
via forms auth which I pass as the credentials to RS during web service
calls. We are using URL access to access rendered reports.
RS Windows Service is configured to run as NT AUTH\Network Service.
All datasources are set up using trusted security.
What I'd like to be able to do to ensure that we use connection pooling is
not impersonate the credentials passed in but instead connect to the OLAP
database as a single domain account.
Is this possible and if so, what security configuration changes should I
make to make this happen?
Thanks in advance.
-TimPlease disregard my original post. The absurd amounts of caffeine I've been
consuming lately have caused temporary memory loss. :)
-Tim
"Tim Ellison" <TimEllison@.direcway.com> wrote in message
news:Oajl$kstEHA.1400@.TK2MSFTNGP11.phx.gbl...
> We're running Reporting Services (wSP1) on a Win 2003 server box.
Presently
> (for development), SQL Server, the web application and RS all run on the
> same box. I've configured an app pool in IIS under which Reports,
> ReportServer and the web application run. I'm also collecting credentials
> via forms auth which I pass as the credentials to RS during web service
> calls. We are using URL access to access rendered reports.
> RS Windows Service is configured to run as NT AUTH\Network Service.
> All datasources are set up using trusted security.
> What I'd like to be able to do to ensure that we use connection pooling is
> not impersonate the credentials passed in but instead connect to the OLAP
> database as a single domain account.
> Is this possible and if so, what security configuration changes should I
> make to make this happen?
> Thanks in advance.
> -Tim
>

Help with Reportviewer.

Hi ,
I'm using the ReportViewer control on my web site .
I need to implement the report bar : forward, back, refresh, pagecount,
print.
1) How can I know how many page are in the report, the standard report bar
display the pagecount but how can I get it ?
2) How can I implement the Print button, the standard report bar display the
Print button but how can I implement it ?
Help me.Can't you just tell the viewer to display its bar? (I believe there is a
command to do that).
José.
"Liz Matyas" <lizmts@.mail.com> wrote in message
news:%23bve6wSxFHA.3152@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> I'm using the ReportViewer control on my web site .
> I need to implement the report bar : forward, back, refresh, pagecount,
> print.
> 1) How can I know how many page are in the report, the standard report bar
> display the pagecount but how can I get it ?
> 2) How can I implement the Print button, the standard report bar display
> the
> Print button but how can I implement it ?
> Help me.
>
>|||Are you using the Report Viewer web part that comes with RS SP2? If you
select the Full toolbar, you should be able to see everything. In the Short
toolbar, you can add print and other functions by editing the apprpriate
style sheet.
"Liz Matyas" <lizmts@.mail.com> wrote in message
news:%23bve6wSxFHA.3152@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> I'm using the ReportViewer control on my web site .
> I need to implement the report bar : forward, back, refresh, pagecount,
> print.
> 1) How can I know how many page are in the report, the standard report bar
> display the pagecount but how can I get it ?
> 2) How can I implement the Print button, the standard report bar display
> the
> Print button but how can I implement it ?
> Help me.
>
>|||We arwe using RS2005 and we need to develop a toolbar with the llok and fill
of our product this is the resone we nned to implement it using code .
"Liz Matyas" <lizmts@.mail.com> wrote in message
news:%23bve6wSxFHA.3152@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> I'm using the ReportViewer control on my web site .
> I need to implement the report bar : forward, back, refresh, pagecount,
> print.
> 1) How can I know how many page are in the report, the standard report bar
> display the pagecount but how can I get it ?
> 2) How can I implement the Print button, the standard report bar display
the
> Print button but how can I implement it ?
> Help me.
>
>sql

Help with Query Strings

I am using query strings to pass data from web form to web form and I have two questions. First if i use a asp:sqldatasouce to fill up a grid view and I have my select command set to a paramater that get whatever is in the query string it will not work because whatever is in the quers string gets a " ' " put in front and in the back of it. So if the query string was 5 whene it does the sql statement it sets my paramater = '5' not just 5 so its wont work. How can I fix this using the asp:sql datasource my aspx code looks like

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:Rental PropertiesConnectionString %>"SelectCommand="SELECT * FROM [APARTMENTS] WHERE ([PROPERITY_ID] = @.PROPERITY_ID)"><SelectParameters><asp:QueryStringParameterName="PROPERITY_ID"QueryStringField="key"Type="Int32"/></SelectParameters></asp:SqlDataSource>

Also since i have not been able to get around this so i have been wrting code in vb.net to attact a dataset to a grid view to populate it based on the query string i would do the following in vb.net to get ride of the ' in front and behind the query string

Dimy as string ="'" // " ' "

key = Request.QueryString("key").trim(y.tochararray)

But now i am doing another project in C# and I have re-written the above code in C# it will run but it will not take the " ' " out form infront or behind key. How does this need to be changed up?

string

y ="'";

key = Request.QueryString[

"key"].trim(y.tochararray());

If I understand you correctly, you have 5 in database but while you select it, you get '5' correct?

SqlDataSource doesn't put any thing while it gets data. And binding itto Gridview should not be a problem

|||

I am codeing the value of my querystring in depending on a key value in another forms gridview so my code for a query string is

dim key as string 'holds the key value of the selected row in a dataview

response.redirect("Info.aspx?id=' " & key & " ' ")

when i do that, if key is 5 it places a single quote infort and behind five, '5'

So when i have my select statment in my sqldatasource and the paramater is equal to query string id it has the single quote in the select command sent to the database and that produces an erro. How can i take out the " ' " in the query sting in my aspx file ?

|||

Hi draskc03

This is my example. You can try this

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="NewsID" Width ="100%"
DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True" CellPadding="4" ForeColor="#333333" GridLines="None" HorizontalAlign="Center" PageSize="20">
<Columns>
<asp:TemplateField SortExpression="ImageUrl" HeaderText="Edit"><ItemTemplate>
<a href ="AddEditNews.aspx?ID=<%#Eval("NewsID") %>"> <img src ="../images/Edit.gif" border ="0"/></a>

</ItemTemplate>
</asp:TemplateField>
<asp:BoundField ReadOnly="True" DataField="NewsID" InsertVisible="False" Visible="False" SortExpression="NewsID" HeaderText="ID"></asp:BoundField>
<asp:BoundField DataField="AddedDate" SortExpression="AddedDate" HeaderText="AddedDate"></asp:BoundField>
<asp:TemplateField SortExpression="ImageUrl" HeaderText="Title"><ItemTemplate>
<a href ="AddEditNews.aspx?ID=<%#Eval("NewsID") %>"> <%#Eval("Title") %></a>

</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Description" Visible="False" SortExpression="Description" HeaderText="Description"></asp:BoundField>
<asp:BoundField DataField="Body" Visible="False" SortExpression="Body" HeaderText="Body"></asp:BoundField>
<asp:BoundField DataField="ImageUrl" Visible="False" SortExpression="ImageUrl" HeaderText="ImageUrl"></asp:BoundField>
<asp:BoundField DataField="language" SortExpression="language" HeaderText="Language"></asp:BoundField>
<asp:CommandField ShowDeleteButton="True" DeleteText="Xóa" DeleteImageUrl="~/images/Delete.gif" ButtonType="Image" HeaderText="Xoá"></asp:CommandField>
</Columns>

</asp:GridView>

Help with more Advanced functions

I have been able to get several basic databases to function both in playing
around and functional ones on the web but they have all been pretty simple.
I am now trying to develop a database for the web using Access. What I am
really needing help with is how to actually reduce the number of record sets
that I think i am going to need. Here is what I am trying to do.

I am having people sign up and create teams. So you come to the site and you
see Team 1, Team 2... Team 8. (These numbers will later be replaced by real
names given by the team captains and replaced in the database, this will be
done by the captain through the web)You pick a team you want to be on, enter
the info and you are placed on that team (in the database) I have two tables
in the database TeamRoster (everyone on all of the teams) and Teams (List
the captains and the names of their team)

So when I query the database to fill the tables I am confused as to how I
should go about the query. As I see it I need to build a RS (Record Set) for
each team to get the name of the team for the appropriate table, then
populate it with the players for that team (repeating region) if there are
eight teams, that means 16 RS. This does not seem right.

Am I making any sense?

Thanks for trying to understand all of this.

Houston"Houston" <houston@.hbip.com> wrote in message news:<jSgUc.1240$jj.803@.newssvr23.news.prodigy.com>...
> I have been able to get several basic databases to function both in playing
> around and functional ones on the web but they have all been pretty simple.
> I am now trying to develop a database for the web using Access. What I am
> really needing help with is how to actually reduce the number of record sets
> that I think i am going to need. Here is what I am trying to do.
>
> I am having people sign up and create teams. So you come to the site and you
> see Team 1, Team 2... Team 8. (These numbers will later be replaced by real
> names given by the team captains and replaced in the database, this will be
> done by the captain through the web)You pick a team you want to be on, enter
> the info and you are placed on that team (in the database) I have two tables
> in the database TeamRoster (everyone on all of the teams) and Teams (List
> the captains and the names of their team)
>
> So when I query the database to fill the tables I am confused as to how I
> should go about the query. As I see it I need to build a RS (Record Set) for
> each team to get the name of the team for the appropriate table, then
> populate it with the players for that team (repeating region) if there are
> eight teams, that means 16 RS. This does not seem right.
>
> Am I making any sense?
>
> Thanks for trying to understand all of this.
>
>
> Houston

It's not really clear from your description if you're using only
Access, or Access as a front end to MSSQL. If you're using MSSQL, you
would use queries (preferably inside stored procedures) to return data
such as the complete list of teams, the list of team members for a
specified team etc. How you then format and display that data in
Access, I have no idea.

If you're not using MSSQL, or if you need more information about using
RecordSet objects, you should probably post to an Access or ADO group,
with some more details about exactly which software you're using, and
which client libraries. If you are using MSSQL, then it would be
useful if you post the CREATE TABLE statements for your tables, as
well as sample data, so that it's easier to understand what you're
asking.

Simon

Help with inserting array contents to SQL Server 2000

I've been doing this in Access, but cannot find the answer to how to do it with SQL Server.

From a web form, a user can select a number of different dates. The selected dates are held as text (not DateTime) in an ArrayList.

Clicking the Submit button writes the contents of the form to a database table.

This works for Access:

insSQL &= "VALUES (@.typEvent, @.starts, @.ends, @.starts, @.ends, @.attend, @.title, @.room, @.department, @.contact, @.address, @.telephone, @.email, @.telefax, "
For i = 0 to datesArray.Count - 1
insSql &= datesArray.Item(i)
Next i

insSQL &= "VALUES (@.typEvent, @.starts, @.ends, @.starts, @.ends, @.attend, @.title, @.room, @.department, @.contact, @.address, @.telephone, @.email, @.telefax, "
For i = 0 to datesArray.Count - 1
insSql &= "#" & datesArray.Item(i) & "#, "
Next i

It doesn't work for SQL Server, and when trying to insert the value "01/29/2007" I get the error message: "The name '#1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

I have also tried the line:

For i = 0 to datesArray.Count - 1
insSql &= satesArray.Item(i)
Next i

and get: "Incorrect syntax near the keyword 'VALUES'."

I'm not sure where to find the information to correct my error.

Any help would be appreciated.

Tinker

On a quick review, before looking into the code, you dont need to enclose "#" around the dates like you do for Access. So remove that and give it a shot again.|||

And your dates have to be in single quotes '01/29/2007' to work correctly because you pass them as string, and be sure that your date format is equal to data format strings used by server because if server uses dd/mm/yyyy this date will fail or if you server use mm-dd-yyyy it will fail also.

Best solution is to remove / and - from string, do test and you will see results

see posthttp://forums.asp.net/thread/1553054.aspx for examples how data formating could crash.

|||

Thank you -- and you, too, ndinakar. I was able to remember that the #'s are required for Access, but couldn't find the information on using single quotes for SQL server on my own.

That solved it, and I am grateful for your help.

Tinker