VOTING/RATING SYSTEM: HOW TO ADD POINTS AND UPLOAD SQL VALUE?
Sep 2, 2006
I am trying to build a voting system. Website visitors will rate a pictures from 0-10.
This value should update the amount of points already stored is SQL.
I think the way to do it is passing the value of a field to a varible, performing the operation and updating the database, but I don't know how to capture a value in Sql and pass it to variable...
Does anybody there could post a piece of code or point to a link where I could find this information?
this is probably a simple sql solution, but i could use some help.i've got one table that has rows of documents, and another table withany number of ratings for each of the documents. The rating table islinked with an id column. the issue i am running up against isreturning a single average rating for a document.SELECT document.title, (SELECT AVG(scale)FROM Rating, documentWHEREdocument.pkDocumentId = rating.documentId)FROM Documentreturns the same avg for all the ratings.So i'd ideally like to return the values of the document columns(tableA.*), with an additional column containing the average rating ofthat document (avg(tableB.Scale) where pkDocumentId = documentId).thanks in advance.
I've managed to get my query to this: Product | Color | Votes ======== ======= ======= Bus Red 10 Bus Blue 5 Train Blue 1Car Red 1 Car Blue 1
I need the most popular color of the product based on the votes for each product, so MAX(Votes) GROUP BY Product solves the Bus and the Train, but I still need a result for the Car? Not really that bothered which Color is picked up, do I need to run another query, because I need another table to join to the results.
Any help would be greatly appreciated. Thanks, Pete.
Greetings everyone, I'm posting this thread with the hope that someone will notice it and might offer me a helping hand regarding one of my problems.I have the database named "DBEXAMPLE" with the table MEMB_INFO that contains two important columns that are named cur_points and points where cur_points column contains the total available points that a member can use/spend on a game and the points column stores the total of the points that a member used so far.So as i plan to wipe all data of the database, i need to keep the member login,password and total points that each member purchased.So i will somehow need to update the cur_points column with the total of current cur_points+points columns and then wipe points column.I've personally asked a friend regarding this and he said that this should be something complicated and it might require php also.Really appreciate if someone could help me regarding this.small schema:Database: DBEXAMPLETable: MEMB_INFOColumn cur_points -> total available points that a member can spendColumn points -> total points that the member has already spentColumn memb___id -> member login aka account idTODO -> update cur_points column with the total of cur_points+points columns for each member(buyer)With best regards.
I am rewritting our DTS that upload tables in FoxPro to SQL Server using SSIS. I am finding the that SSIS is way slower. My uploads using DTS take just 7 minutes where doing the same thing in SSIS is taking 1 hour 15 minutes.
I have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.PermissionSet.Demand() at System.Data.Common.DbConnectionOptions.DemandPermission() at System.Data.SqlClient.SqlConnection.PermissionDemand() at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was: Demand The type of the first permission that failed was: System.Data.SqlClient.SqlClientPermission The Zone of the assembly that failed was: Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
I know how to create mount point in windows 20003 cluster, I am not sure how to set it up with SQL 2005 running on the cluster. Does sql need to be dependant on any of the disks? I have tried looking for a guide, but cannot find.
current setup active active cluster running. I need to add san space which will hold the databases. The san will be carved up into drive letters. each drive letter will hold 3 mount points. ie. node 1 J:-2 mount point k:2 mount point l:2 mount point
node 2- r:-2 mount point s:2 mount point t:2 mount point
each node would be able to own the disk if the other node failed over.
any help is appreciate. I have tried books online etc.. cannot find a good step by step.;
HI All, I use MSSQL as my database and ASP.NET as my front application. I want to display Price value S$23.68. The dayatype I used is smallmoney, but it display: S$23.6800. HOw do I control the number of decimals point in the column of MSSQL?? Thanks a lot Suigion
ManagerID, ManagerID2, ManagerID3, ManagerID4 are all pointing to EmployeeID in the same table.
Now I want to select the all employee records where the manager NAME is like '%Raymond%'. (i.e. any of the four managers name is like '%Raymond%'.
How do I construct the select query? I tried this, but it does not work if there are more than 1 manager named 'Raymond'
DECLARE @Name varchar(50) SET @Name = '%Raymond%'
DECLARE @EmployeeNumber nvarchar(50) Select @EmployeeNumber = EmployeeID from Employee where [Name] like @Name
SELECT 'HumanResources.Employee' as TableName, E.[Name] as EmployeeName , isnull((Select [Name] from Employee E2 where E2.EmployeeID = E.ManagerID),'') as Manager1, isnull((Select [Name] from Employee E2 where E2.EmployeeID = E.ManagerID2),'') as Manager2, isnull((Select [Name] from Employee E2 where E2.EmployeeID = E.ManagerID3),'') as AppraisingManager, isnull((Select [Name] from Employee E2 where E2.EmployeeID = E.ManagerID4),'') as ModeratingManager, E.CurrentFlag as Active FROM HumanResources.Employee E
WHERE (E.[ManagerID] = @EmployeeNumber) or (E.[ManagerID2] = @EmployeeNumber) or (E.[AppraisingManagerID] = @EmployeeNumber) or (E.[ModeratingManagerID] = @EmployeeNumber))
I understand mount points help scalability in easier maintenance. By scalability do we mean more than 26 drive letters or it means adding more space to the same mount point letter on with more ease .
Can I add more space to a mountpoint if required later on by adding hard disks .
Also if one can give some pointers to good file group configuration guidelines / storage align partitions , it will be very much helpful
Further I my server CPU has 4 cores , will having 4 filegroups help me in improving system performance.
If SAN has 2 controllers , is it preferred to run data file partition on one controller and log file partition on another.
Hello, We have a requirement to be able to monitor mount points. The xp_fixeddrives does not support mount point monitoring. Is there another way to do it. Does microsoft working on updating the xp_fixeddrives. Let me know if anyone has any ideas how to monitor mount points.
Hi,I'm making a rating system that save the results in the database.I have 1 table with 3 columns:1) name2) points3) numberofvotesnow I want to select the 5 names with the best results. In other words the 5 best results when you do (points/numberofvotes)how can I say this in SQL? I hope someone can help me! thnx!
Previously I have posted about my problem with transaction logs. Threw my research I have found the truncate log at check point. I have also been lead to think that a check point could be a full back up. I have set this option to true and then performed a full back up. Afterward I have shrank the database to find the transaction log to still be over 43 gigs. Where have I gone astray?
Hi all, I'm trying to debug storedprocedures in query analyzer from workstation, login with server adminuser.Debugger is not stoping at break points. any help regarding this will be greatly appreciated. thanks.
DECLARE @EffLevels TABLE (ChangePoint int, Value Int)
INSERT@EffLevels SELECT'1000', '767' UNION ALL--Changed SELECT'1000', '675' UNION ALL SELECT'1001', '600' UNION ALL--Changed SELECT'1001', '545' UNION ALL SELECT'1001', '765' UNION ALL SELECT'1000', '673' UNION ALL--Changed SELECT'1002', '343' UNION ALL--Changed SELECT'1002', '413' UNION ALL SELECT'1002', '334' UNION ALL SELECT'1001', '823'--Changed
-- My Result should be -- ChangePointPrevChangePointValue -- 1000Null767 -- 1001 1000 675 -- 1000 1001 765 -- 1002 1000 343 -- 1001 1002 823
Hi allI have a large data set of points situated in 3d space. I have a simpleprimary key and an x, y and z value.What I would like is an efficient method for finding the group ofpoints within a threshold.So far I have tested the following however it is very slow.---------------select *from locations a full outer join locations bon a.ID < b.ID and a.X-b.X<2 and a.Y-b.Y<2 and a.Z-b.Z<2where a.ID is not null and b.ID is not null---------------If anyone knows of a more efficient method to arrive at this results itwould be most appreciated.Thanks in advanceBevan
Hi all, I am seeking your expertise to create SQL codes (SQL server 2005) that can help me to answer the problem below.
I have two tables (points and station), presented in form of SQL codes below. I€™d like to find the 6 closest panels for each of the station. As can be seen in the result table below, the 6 closest panel names are arranged from the first closest (P1) to the sixth closest (P6). Similar procedure also applies for the distance column arrangement. This distance column (D1 €“ D6) is the distance of panels P1 €“ P6 to the station. The distance between two points (with x-y coordinates) can be calculated using a simple Cartesian formula: Distance = ( (X1 €“ X2)2 + (Y1 - Y2)2 ) 0.5 . As the sample, distance between station €˜A€™ and panel €˜P19-04W€™ is = ((737606.383 - 737599.964)2 + (9548850.844 - 9548856.856)2) 0.5 = 8.79. The expected result of the work is presented in the table below:
Table 1: create table 1 ( Panels varchar(20), X_Coord float, Y_Coord float ) go set nocount on
insert into 1 values('P19-03E','737640.722','9548882.875') insert into 1 values('P19-04E','737630.166','9548868.3') insert into 1 values('P19-05E','737619.611','9548853.726') insert into 1 values('P19-06E','737609.054','9548839.15') insert into 1 values('P19-07E','737598.495','9548824.571') insert into 1 values('P19-08E','737587.941','9548809.998') insert into 1 values('P19-09E','737577.386','9548795.425') insert into 1 values('P19-10E','737563.359','9548776.163') insert into 1 values('P19-11E','737552.795','9548761.578') insert into 1 values('P19-12E','737542.256','9548746.919') insert into 1 values('P19-13E','737531.701','9548732.345') insert into 1 values('P19-14E','737521.146','9548717.772') insert into 1 values('P19-03W','737610.519','9548871.43') insert into 1 values('P19-04W','737599.964','9548856.856') insert into 1 values('P19-05W','737589.404','9548842.275') insert into 1 values('P19-06W','737578.849','9548827.702') insert into 1 values('P19-07W','737568.294','9548813.128') insert into 1 values('P19-08W','737554.274','9548793.77') insert into 1 values('P19-09W','737543.718','9548779.195') insert into 1 values('P19-10W','737533.157','9548764.614') insert into 1 values('P19-11W','737522.603','9548750.041')
set nocount off go
Table 2: create table 2 ( Station varchar(20), X_Coord float, Y_Coord float ) go set nocount on
insert into 2 values('A','737606.383','9548850.844') insert into 2 values('B','737575.41','9548806.838') insert into 2 values('C','737544.437','9548762.832')
The total quantity(sum(woitem.qtytarget) as total) result set is showing more than 6 numbers after the decimal point (example:442.2565485). How can I limit it to 2. (example:442.25)
select wo.num, sysuser.username,sum(woitem.qtytarget) as total from woitem join wo ON wo.id = woitem.woid Join moitem on moitem.id = woitem.moitemid Join mo ON mo.id = moitem.moid LEFT JOIN SYSUSER ON mo.userid = sysuser.id group by sysuser.username, num ORDER BY CAST( REPLACE( wo.num, ':', '.') as decimal(12, 3))
The parent table has the column OrderID which is the primary key. There is a unique non-clustered index in the same table with the same column and some included ones. A child table references the parent table on the OrderID column. Unfortunatelly, in the sys.foreign_keys table the index that has been used to acomplish the referential integrity is the unique non-clustered one instead of the primary key (which is the clustered index as well).
This is causing issues in some maintenance tasks where we need to disable all the indexes of the table apart from the clustered index. Disabling these indexes leeds to disabling the not properly configured foreign key as well. Maybe dropping the index create the constraint again and then recreate the index is a workarround but I wish to have something more elegant and future proof.
How do I limit the decimal points in a computed column. SSE 2005 seems to override the precision and scale I set in either GUI or by creating the table by query.
Hello,I am using SQL Server 2000 with SP4. I have a database with two fullbackups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30PM. Is there a possible way to do a point in time restore to 4:30 PM,that is between two full backups?When I try to use the transactional log backup that is taken at 5:30, Ican never specify a time before 5:00 PM. Is the transaction logtruncated at each full backup? If so, even if you take transactionallog backup every ten minutes, and full backups every once in a while,there will be some point in time which cannot be recovered to, namelythe time between a transactional log backup and a full backup. Take alog backup at 4:50, and full backup at 5:00 and you can never recoverto 4:55, can you?Any insight on the topic will be appreciated,Regards,M. Baris Caglar
I have Team suite VS2005 sp1 installed and I can't debug my SSIS packages. If I put any break points in the Script code when I try to debug I get the message.
"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."
I click on close and it runs the package but doesn't stop at any break point. If I take out all break points it will run without this error. A couple of times I have managed to put in a single break point early in the package within a different script component which causes it to break... but at a completely different point within a completely different script component. It's so weird. Any help would be appreciated.
I have discovered an instance where the Y-AXIS displays a scale incorrectly. The scale values that are displayed do not match the data points shown on the chart itself. I am not using any Minimum or Maximum values in the properties y-axis tab. This does not occur on all charts and seems to be random.
Has anyone encountered this before? Is this a bug in SSRS 2005? Does anyone know of a fix? All sugestions are welcome, thankx!
To implement the new SQL 2005, I plan to make the environment easy to manage. The environment should be simple to document and be automated via scripts. Therefore I plan to use mount points as described below.
On a typical SQL server with multiple drives like C, D, E, F, G, H. Where each drive will have various folders to hold SQL code, data files, transaction log files, tempdb files, snapshot files, and other types of files. This typical environment is not pretty and is hard to write scripts for.
So I plan to standardize on one standard directory structure via volume mount point. On all new SQL 2005 servers, we should see drive E as the one and only SQL Server directory. Other drives will be mounted to drive E as shown.
E:
SQLSERVER local folder -sql code for each db instance
SQLSHARED local folder -sql shared tools for all db instances
SQLTLOG1 Drive H -db transaction log
SQLSNAP1 Drive F -db snapshot files
SQLTEMPDB1 Drive H -tempdb main data file
SQLWORK Drive D - DBA work area
SQLDATA1 Drive G -db data files
SQLDATA2 Future Drive -if SQLDATA1 is too large for any direct attached drive, or to get more I/O throughput.
With this implementation, I can easily write scripts to manage the environment. Also if any mounted volume is out of space, we can swap the based drive without doing any change to database configuration. We can also switch from direct attached drive to SAN in the future.
Do you think mount point is safe to use with SQL 2005? I know it is supported.
Do you have a standard directory structure for your environment? How do you do it?
