Mode with multilple categories


I need to find the mode of price for multiple different locations. The way I have the query now returns the mode over the entire data set- but not location by location:

SELECT top 1 COUNT (*)frequency, price mode, locat
FROM Tickets
WHERE locat in ('017','018','030')
group by locat, price
order by COUNT (*) desc

result:
frequency mode locat
361 26.00 017

Help on the fixing this would be greatly appreciated!!
Edited by xxxcr, 12 November 2012 - 03:18 PM.
Posted On: Sunday 30th of December 2012 11:23:06 PM Total Views:  208
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




SQL help needed?

Hi. In a ASP / Access page I had this code on a dynamic page that inserted "one hit" in the database each time the page was displayed. Like this page has been viewed 100 times. Code: ViewCount is the name of the integer db field that stores the number value. In MSSQL this recordset causes a "0x80040E31 timeout error" and the page cant be displayed. Any suggestions for this error If I remove the "insert one hit in db function" in the recordset everything works ok. Bjorn.
VIEWS ON THIS POST

119

Posted on:

Wednesday 7th November 2012
View Replies!

The query calculates too long and no answers!

Hi all, I wrote a query but somehow it calculates too long even some days and doesnt give me an answer... Can someone help me where am i doing wrong By them time if i use the Assortment Codes seperatly (one by one) than the query works perfectly!! Why it can be
VIEWS ON THIS POST

103

Posted on:

Saturday 10th November 2012
View Replies!

Dataware house Query ..

Hi pals, I need some help from u. This is datawarehousing related stuff. I am having a source table as "test" and target table as "trg". I need to extract the data in required format as per below loading instructions and then load the data into "trg" table. Below sample data is only given one zipcode.There can be several codes. drop table test create table test ( currentyear int, district varchar(10), school varchar(10), rollno int, zipcode varchar(10), flag1_handicapped char(1), flag2_disadvantaged char(1), status varchar(10), relation varchar(10) ) /* inserted 11 rows */ insert into test values(2005,'D1','S1',101,'530024','Y','Y','E','R' ) insert into test values(2005,'D1','S1',101,'530024','N','N','E','R' ) insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','Y','Y','E','NR ') select * from test --- Structure of the target table create table trg ( cyear int, district varchar(10), school varchar(10), RollNo int, zipcode varchar(10), type varchar(20), /* This is an extra column with hard coded values which we need to assume as Total,flag1_handicapped,flag2_Disadvantaged.For Every unique zipcode i need to GROUP BY these 3 values. These values never come from the source table i.e "test".But we can make use of the 2 source columns "flag1_handicapped" & "flag2_Disadvantaged"*/ actaul_cnt int, empl_related int, empl_not_related int, modified_date datetime ) -- The below table shows what values should get loaded into trg table ---------------------------------------------------------------------- trg table column value to be loaded Description ----------------------------------------------------------------------- cyear test.currentyear district test.district school test.school rollno test.rollno zipcode test.zipcode type /* here we to load 3 rows with 3 values This table contains some calculated columns. such as "actual_cnt","empl_related","empl_not_related" and so on... Every calculation should be grouped by this "type" column.For reference the you can see the bottom output rows how they should look like. The 3 valid values for this type column is "Total","flag1_handicapped","Disadavantaged". "Total" means = the records which satisfies the calculation. "flag1_handicapped" means = the records which statisfies the calculation and have test.flag1_handicapped = 'Y' "flag2_Disadvantaged" means = the records which satisfies the calculation and have test.disadvanatged = 'Y'*/ actaul_cnt This is a calculated column. The calc is as follows: count of records grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total) empl_related This is again calculated column. The calc is as follows. count of records where status='E' and relation = 'R' grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total) empl_not_related This is again calculated column. The calc is as follows. count of records where status='E' and relation = 'NR' grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total) modified_date getdate() ----------------------------------------------------------------------------------------------- Here is the sample template which i felt like using to load the data. We need to modify this query littlt bit accordingly as per above rules. select currentyear as "CYear", district as "District", school as "School", rollno as "RollNo", zipcode as "zipcode", count(*) "actaul_count", sum(case when (status='E' and relation='R') then 1 else 0 end) "Emp_Related", sum(case when (status='E' and relation='NR') then 1 else 0 end) "Emp_Not_Related", getdate() "Date" from test group by currentyear, district, school, rollno, zipcode /* Using the above query we need to load 3 rows into below target table whose structure is defined as follows */ ------------------------------------------------------------------------------------------------------------------------ Expected Output Rows using above sample data ---------------------------------------------------- CYEAR|DISTRICT|SCHOOL|ROLLNO|ZIPCDE| TYPE |ACTUALCOUNT| EMPL_RELATED |EMPL_NOT_RELATED |MODIFIED_DT ------------------------------------------------------------------------------------------------------------------------- 2005 | D1 | S1 | 101 | 530024 | Total | 11 | 2 | 9 | 2002-01-26 2005 | D1 | S1 | 101 | 530024 | flag1_handicapped | 2 | 1 | 1 | 2002-01-26 2005 | D1 | S1 | 101 | 530024 | flag2_Disadvantaged | 2 | 1 | 1 | 2002-01-26 ------------------------------------------------------------------------------------------------------------------------ But using above SELECT,i am able to get only row as output that to i am not able to show the "type" column in the output 2005 | D1 | S1 | 101 | 530024 | 11 | 2 | 1 | 2002-01-26 12:57:53.420 | ------------------------------------------------------------------------------------------------------------------------ Basically i am not getting how to build the Group by clause and displaying the type code using above rules. Can anyone help me out in solving the problem. Do we need to perform any UNION ALL ON test.flag1_handicapped and test.flag2_Disadvantaged columns. This is totally seems out of box for me.
VIEWS ON THIS POST

77

Posted on:

Saturday 10th November 2012
View Replies!

Field problem

A few weeks back, shadow wizard suggested me that I should put all of the thread's data in my forum DB in the post table and not the thread table. The thread table should contain information like thread ID, thread subject, date/time, user id. And the post table should have the root post as well as other posts. Originally Posted by shadow wizard no, having post data in the threads table is wrong. threads table should contain information regarding threads only. posts table should contain everything related to the posts. Thread Address: http://forums.aspfree.com/classic-asp-development-5/new-post-criteria-190220.html Now I am developing a similar DB. It's a 50 word comment DB. A user posts a topic with some [b]explanation/[b] on that topic/issue and then other users post comments in 50 words. Here again I have two tables. First one is COMMENT TOPIC which has fields topic id, subject date, time etc and second table is COMMENTS table which has field topic id and comments. So should I put the explanation in the COMMENT TOPIC table or in the COMMENTS table I personally think that I should divide the data load on two separate tables. COMMENT TOPIC without the comment explanation would be a very small table in terms of data. the load (in terms of page and extent size) would be on COMMENTS table. CB
VIEWS ON THIS POST

113

Posted on:

Saturday 10th November 2012
View Replies!

Get Value for Trigger

I'm using a update trigger on a table. How do I get the value for a specific field in a trigger before it was updated.
VIEWS ON THIS POST

38

Posted on:

Saturday 10th November 2012
View Replies!

Computing a value

I'm wondering if someone knows how to do this. I am trying to add a sum of to two values within a column. I know it's easy but for some reason I can't this to function properly so I can use this value in Crystal. I'm trying to add hudnumber 801 and 808 which are Broker Fee 1 and Broker Fee 2 in my case stmt. I'm trying to add their fees but I can't seem to get the syntax right. Here is the code: SELECT DISTINCT loan_main.loanid, loan_query.cmlastname + ',' + loan_query.cmfirstname AS 'Borrower Name', loan_main.totalloanamount, loan_main.loanrate AS 'Int. Rate', setups_businesschannels.channelalias AS 'Business Channel', loan_main.businesschannelid, custom_loanmain.Type4DisburseDT, loan_main.dateofdisclosure, loan_main.actualclosingdate, loan_postclosing.disbursementdate, loan_query.lsstatusalias, loan_query.pmstate, --loan_funding_detail.[description] AS 'Fee Description', Case hudnumber When 809 Then 'YSP' When 811 Then 'Admin Fee' When 813 Then 'Appraisal' When 814 Then 'Credit Report' When 801 Then 'Broker Fee 1' - When 808 Then 'Broker Fee 2' - I'm trying to add these two description fee amounts End As Fees, loan_funding_detail.fundamount AS 'Fee Amount', loan_funding_detail.hudamount, re.name AS 'Broker' FROM loan_main INNER Join loan_channelentities lce ON loan_main.lenderdatabaseid = lce.entitylenderdatabaseid AND loan_main.loanrecordid = lce.loanrecordid INNER JOIN rolodex_entity re ON lce.lenderdatabaseid = re.lenderdatabaseid AND lce.entityid = re.entityid AND (lce.entitycategoryid = 66 OR lce.entitycategoryid = 67) INNER JOIN loan_query ON loan_main.lenderdatabaseid = loan_query.lenderdatabaseid AND loan_main.loanrecordid = loan_query.loanrecordid AND loan_main.statusid = loan_query.lsstatusid INNER JOIN loan_postclosing ON loan_main.lenderdatabaseid = loan_postclosing.lenderdatabaseid AND loan_main.loanrecordid = loan_postclosing.loanrecordid INNER JOIN custom_loanmain ON loan_main.lenderdatabaseid = custom_loanmain.lenderdatabaseid AND loan_main.loanrecordid = custom_loanmain.loanrecordid INNER JOIN loan_funding ON loan_main.loanrecordid = loan_funding.loanrecordid INNER JOIN loan_funding_detail ON loan_funding_detail.loanrecordid = loan_funding.loanrecordid AND loan_funding.sequencenumber = loan_funding_detail.sequencenumber INNER JOIN setups_businesschannels ON loan_main.businesschannelid = setups_businesschannels.channelid WHERE ((loan_main.businesschannelid = 8) OR (loan_main.businesschannelid = 2) OR (loan_main.businesschannelid = 7 )) AND loan_funding_detail.hudnumber in (801, 808, 809, 811, 813, 814) order by setups_businesschannels.channelalias
VIEWS ON THIS POST

77

Posted on:

Tuesday 20th November 2012
View Replies!

Displaying multiple results in one row

Here's what my table looks like (1st Table) ID | Name -- |------ 1 | violato 2 | Steve (2nd Table) ID | Hobby ---|------ 1 | Basket 1 | Pool 1 | Soccer 2 | Tennis 2 | Golf And I want it to be displayed as: ID | Name | Hobby ---|------|-------------------- 1 |Violato|Basket, Pool, Soccer ---|------|-------------------- 2 |Steve |Tennis, Golf ------------------------------- So basically put all my hobbies in one column separated by , instead of one row on every hobby and duplicate all my name over and over again. I DO understand this can be done with MySQL by using GROUP_CONCAT() BUT i'm using MS SQL and I've been trying couple hours to fix it. This is an ASP.NET application is built on VB.NET using dataset and datatable if that matters.
VIEWS ON THIS POST

92

Posted on:

Tuesday 20th November 2012
View Replies!

help....can't even install SQL server in XP!

Hi all, i need help for the installation.. SQL server 2000 gave me the error "The logon account cannot be validated for the SQL service . Verify that the user name and password entered are correct. The logon attempt failed." I used my windows XP administrator account username and password .. why it won't allowed me to install using a Domain user account instead of the local system account\t The funny thing is that i did installed SQL server before but the SQL server service cannot be logon when i tried to use it..so i reformatted my HD. Please help.....i do not wish to reformat my HD again or use the local system account during the installation. Local System account vs Domain User account.. which is better\t
VIEWS ON THIS POST

140

Posted on:

Tuesday 20th November 2012
View Replies!

Using Apostrophes in text boxes--not supported by SQL

, Anyone know a way to be able to use apostrophes in text boxes I'm using SQL server 2000, ASP and JScript/JavaScript but SQL doesn't support the character ' , it has to be replaced with ' ' for example: ================== if (typeof Request.Form(txtRequestorName).Item == "string") { str = Request.Form(txtRequestorName).Item.replace(/\'/g, "''") } else { str = "" } ================= Thank you, Barnes , Okay, you are passing the function a string, it doesn't make a difference what you want to call it in the function. function replace(blah) { var pattern = /\'/g; var newString = blah.replace(pattern, "''"); return newString } As you can see you can name it whatever you want for the function, but you MUST pass it a string: Response.Write(replace(txtRequestorName))...or what ever you are using. it is a function, it returns a string value, and it has a string for an argument. Pass it the string you want to convert and it will return the converted string. , I'm glad that I can help you (or at least try ). Let me know how it goes...I'm curious about how your going to get it all to work.
VIEWS ON THIS POST

66

Posted on:

Tuesday 20th November 2012
View Replies!

App data

hi,' I m fresher IN MVC Framework I have install sql server 2005 and, visual studio..2008. I m using a mvc 1.0frame work in visual studio 2008, I m trying to connect to data base.from app data,using sql server data base..templete(database.mdf),but does not connect some exception is there.. { connection to sql server files(*mdf) sql server express 2005 to function properly.please verify the installtion pf componentvor download from url//go.microsoft.com/flink/link=49251}, Ihave download express sp3.but is not working second one exception.. {Error 2 The type or namespace name 'LoginDBContext' could not be found (are you missing a using directive or an assembly reference) D:\Login\Login\Login\Controllers\LoginController.c s 12 9 Login } plz help it..
VIEWS ON THIS POST

135

Posted on:

Monday 26th November 2012
View Replies!

Query Help - Combining 4 tables into 1 query

again, I have 4 tables. First table has TicketID and 3 addtional columns for cause IDs like MTTR table Ticket ID1 ID2 ID3 AB345 1 4 2 Then I have 3 more tables. Each Table containing a ID and the Corresponding text LIKE Cause1 table ID TEXT 1 Process 2 Troubleshooting 3 Customer Related ect, ect, ect Cause2 table ID TEXT 1 Reduced Ticket Focus 2 Monitor Ticket Not Used ect,ect,ect Cause3 table ID TEXT 1 Outside of Local Repair 2 Ticket not resolved when issues repaired I need the resulting query to basically Contain TICKET ID1 ID1text ID2 ID2text ID3 ID3text is this possible
VIEWS ON THIS POST

134

Posted on:

Monday 26th November 2012
View Replies!

Delete against linked server

, I'm having a problem with SQL Server 2000 deleting against a linked server using an Access DB; I've tried the next syntax: Code: DELETE OPENQUERY(myLinkedServer, 'Select * From MyTable') but it returns the next error message Server: Msg 7320, Level 16, State 2 Could not execute query against OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. The provider could not support a required row lookup interface. The provider indicates that conflicts occurred with other properties or requirements. and using the four part syntax: Code: DELETE FROM MyLinkedServer...MyTable I get the next error: Msg 7345, Level 16, State 1, Line 1 The OLE Database provider "Microsoft.Jet.OLEDB.4.0" could not delete from table "MyTable". There was a recoverable, provider-specific error, such as an RPC failure. SELECTS works fine btw... How can I fix this Thank you very much in advance.
VIEWS ON THIS POST

126

Posted on:

Monday 26th November 2012
View Replies!

Force primary ID

How to force the primary key for a table to begin at a set Primary Id and not standard count (1,2,3,4,5..10,11,12) etc. Pretty much how to force it being on number I would like to begin.
VIEWS ON THIS POST

64

Posted on:

Monday 26th November 2012
View Replies!

Store Procedure - How to save the update in SQL 2005 Express??

Hi! I'm using the SQL 2005 Express to connect to the SQL Server 2000. There I found a stored-procedure of what I'm looking for, use the "MODIFY" command via right-mouse click, made some update to it and save it. But it save the SQL file to a "My Document" folder. It doesn't update the SQL Server 2000's stored-procedure file. So, how do I update it from the SQL Server 2000
VIEWS ON THIS POST

96

Posted on:

Monday 26th November 2012
View Replies!

Comparing two colums in different tables using a join to find non match?

and gals, I have two tables and need to delete some strays in two related tables. The column names are idProduct and Product_id. I wrote an asp script to import and reverse an import except I had forgotten to reverse one of the two tables. So consiquently there are 151 stray records that I need to find. How would I write a join, or not to find these strays and delete them Thank you so much in advance. cbadchris
VIEWS ON THIS POST

85

Posted on:

Monday 26th November 2012
View Replies!

Concatenate records from a sub-query together.

Hi , I'm building a website for a floor-tiles company and Im currently working on the online sample request section. I have a table called tblRequest and another table called tblSample. tblSample stores a list of information on all of the tiles available and tblRequest contains info on each request made by a customer. I have a third table calledtblSampleRequest which basically acts as the middle man for the many-to-many relationship between tblRequest and tblSample... tblRequest -> tblSampleRequest
VIEWS ON THIS POST

133

Posted on:

Monday 26th November 2012
View Replies!

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or acces

just got this error msg: Code: Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied. /aoms/default.asp, line 31 line 31: Code: conn.Open objConn My connection string: objConn = "Driver={SQL Code: Server};Server=SHEMPC\WEBSERVER;Database=aoms;Uid=nottelling;Pwd=nottelling;" I have just changed my TCP/IP settings to obtain address automatically, could this be something to do with it
VIEWS ON THIS POST

82

Posted on:

Monday 26th November 2012
View Replies!

SQL Service Logon

We use a domain account as our SQL 2000 Service Account logon. This has always worked great and we never had any problems until this morning. I came in and saw that all of our SQL Servers were down due to log in failures... Error 1069: The service did not start due to a logon failure. I checked the service logon in the services administration tool, and saw that the domain logon was still correct. I tried reseting the password and it worked, the services started and I did this for all our SQL Servers. Basically, I fixed the problem, but I am confused on what happened and why this occured. I checked with our network and the password for the domain account was not changed and the only thing they did was security updates for all the servers. Any insight to what might of happened would be very much appreciated.
VIEWS ON THIS POST

40

Posted on:

Monday 26th November 2012
View Replies!

Stored Procedure Error...

I'm trying to build a quick stored procedure here but I get error with "BackupId"... Why Code: CREATE PROCEDURE dbo.sp_tblBackupListINSERT /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ AS SET NOCOUNT OFF INSERT INTO tblFileInvalid ( BackupID, AccountNo, CompanyName, StoragePath, PhoneNumber, Active, RelayActive, TimeStamp ) VALUES ( '86','0607-2114-0910','zzUnknownCompanyName','E:\BACKUP\Processed\','0000000000','1','1','10/04/2006 6:30:00 PM' ) /* RETURN */
VIEWS ON THIS POST

64

Posted on:

Monday 26th November 2012
View Replies!

Sored procedure

Hi , could anyone please help here This is very URGENT.
VIEWS ON THIS POST

87

Posted on:

Monday 26th November 2012
View Replies!

IF statement query

Hi Does anyone know the SQL code for the following If statement. IF "a record exists in a database table" THEN Update a row within a a table. Cna anyone help
VIEWS ON THIS POST

131

Posted on:

Monday 26th November 2012
View Replies!

New login domain users missings

I am having trouble adding new domain logins to a SQL server 2000 system. When logging on from my pc I open the new login option in Enterpise manager and select the required domain. of the standard users are there alongside the global groups. What is missing is all of the domain users and the local groups. If I remote on to a server logging on using my credentials I can add the user ok. This does not affect all dba's and is not restricted behaviour to W2k or XP on local machines! Anyone seen this and managed to fix it before. TIA Iain
VIEWS ON THIS POST

108

Posted on:

Monday 26th November 2012
View Replies!

Model Database detached...help!

We detached the model database to move it to another drive, but now we cannot start the SQL Server Service. Can anyone out there help us
VIEWS ON THIS POST

92

Posted on:

Monday 26th November 2012
View Replies!

Copy Database design & tables to SQL

I need to upload my local database to one on my server - but i dont have permissions to use the Database Restore function. Is there anyway that i can get SQL Server to Generate a script to create all the tables, stored procedures and data in the tables
VIEWS ON THIS POST

97

Posted on:

Monday 26th November 2012
View Replies!

SQL Aggregate function that combines like CONCAT

all, I'm trying to find a way to query a database, using several GROUP BY's, but I need the IDENTITYCOL to be put in an aggregate function that will give me a string representation of each IDENTITYCOL value. Something in between a SUM and a CONCAT. So basically if I have records with IDENTITYCOL's 1, 3, 4 and 5, I'd like to be able to call THISFUNCTION(IDENTITYCOL) AS Expr1 and get Expr1 to be "1,3,4,5". Is there anything remotely like this
VIEWS ON THIS POST

113

Posted on:

Monday 26th November 2012
View Replies!

Help in select Query

Hi Everyone, I have a table with six date fields, when the user enters data in the form The database populates with TextBox values. Some fields could be null. My question is how to obtain the last NOT NULL Value from the table Row. For Example: Date1 Date2 Date 3 ......... ------ ------ ------- Value1 Value2 Value3 ......... if Value3 is NULL I want to get Value2. I'm not using Stored procedures.
VIEWS ON THIS POST

109

Posted on:

Monday 26th November 2012
View Replies!

How transfer access tables to sql server 2000 ?

Hi . I got a db in access and want to transfer it to sql server with all its reletionship and connect my access bounded forms to sql server . i be happy if some one show me how to transfer my access 2000 tables to sql server 2000 and make connection to sql sever tables from bounded access 2000 forms
VIEWS ON THIS POST

180

Posted on:

Monday 26th November 2012
View Replies!

SQL, .Net, and Crystal Reports version conflict?

I have the following Software Installed on 2000 server box: A) SQL Server 2000 B) Crystal Reports 8.5 Developer C) Visual Studio .Net Professional version 2002 Everything is working fine except I can run reports that are already made. We used to run these reports on a 2000 server box with the following Software Installed: A) MS Assess 2000 (Instead of MS SQL) B) Crystal Reports 8.5 Developer C) Visual Studio .Net Professional version 2002 So I am wondering two things: 1) Does SQL server have a version of Crystal reports/designer built in. If this is true 2) Do I have a version conflict issue The reason why I am pondering that possibility is the following: When I look into windows explorer- (c:\Program files\Common files\Crystal Decision) What I see are two folders. One labeled 1.0 and the other 2.0. Any ideas based on your experience are appreciated.
VIEWS ON THIS POST

52

Posted on:

Monday 26th November 2012
View Replies!

Selecting a specific row

right now, i use "SELECT TOP 1...". If i wanted to get the second, and only second row, what would i have to do "SELECT TOP 2" selects the top 2, instead of just the second. Help anyone
VIEWS ON THIS POST

97

Posted on:

Monday 26th November 2012
View Replies!

How to execute DTS package from ASP

all. Please, help! I don't know how to execute DTS package from ASP application. I need some sample code.
VIEWS ON THIS POST

77

Posted on:

Monday 26th November 2012
View Replies!

View over multiple databases

i have 2 databases (on 1 instance of sql server) i would like a view in database A bases on data in database B 1) is this possible 2) if yes, how do i do this 3) how are permissions and user involved
VIEWS ON THIS POST

120

Posted on:

Monday 26th November 2012
View Replies!

Constraints by triggers

after the bit field Locked is 1, no changes may occur to the record, the following trigger is the closest i can get to it, but now i can't put the Locked field to 1 either can anyone correct my trigger or give me an alternitive way of implementing this on a database level PHP Code: CREATETRIGGERdbo.Locked_Constraint_VersieONdbo.Offerte_VersieAFTERUPDATEASIF(0
VIEWS ON THIS POST

83

Posted on:

Monday 26th November 2012
View Replies!

Invalid object name with SQL db

Moved a SQL database to another server and now the website gives the following error. Microsoft OLE DB Provider for ODBC Drivers error '80040e37' [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'active_users'. /activeusers.asp, line 10 Just changed the DSN to reflect teh new server and added the db to the new machine and setup the user as dbo. Can't see why this happens, if I point the DSN to the old server it works fine. These are the lines the error references: 9 = StrSql = "DELETE FROM active_users WHERE Last_Seen < '" & StrOnlineTimedout & "'" 10 = conn.execute (StrSql) Any ideas on why this happens I've gotten nowhere with it
VIEWS ON THIS POST

176

Posted on:

Monday 26th November 2012
View Replies!

DTS Import File Advice Needed

Basically I just need some advice. I am using DTS to import a CSV file into my database. This import will take place each day, as a scheduled task. This import will need to totally replace what was in the table before (delete data in the table and then import the csv). The bit I need advice on though is, I want to run some checks on the data that is being imported before it is commited. If there are any errors or probs then i want to just keep the data as it was the day before (ie. don't commit the new data). What is the best way to do this I was thinking along the lines of creating a 2nd table, which i first import the data to, run the checks on it (like row count etc) and then if all OK, copy this table into the final table. If there are any errors or the checks highlight a prob then this final copy will not take place, therefore leaving the data as it was the day before. Or is there a better (simpler / more efficient) way to do it Cheers.
VIEWS ON THIS POST

45

Posted on:

Monday 26th November 2012
View Replies!

SQL cannnot update data from php session

, please see these images on bellow : When I click "BUY NOW", then I login into account it's successful to added to database. And this the result : But when I buy same or other product like this : it's result like this : it cause the product quantity and total price became 0 and sadly the new product cannot be added. and this my code : if($_SESSION['cart']) { $show_order = mysql_query("select * from order where o_user='$m_user'and o_status='O'"); $order_all = mysql_num_rows($show_order); foreach($_SESSION['cart'] as $product_id => $quantity) { $sql = sprintf("SELECT p_id, p_title, p_price, p_image FROM product WHERE p_id = %d;", $product_id); $result = mysql_query($sql); if(mysql_num_rows($result) > 0 && $order_all == 0 && $_SESSION['m_user']) { list($p_id, $p_title, $p_price, $p_img) = mysql_fetch_row($result); $line_cost = $p_price * $quantity; $total = $total + $line_cost; mysql_query("insert into order(pid,o_user,o_title,o_img,o_price,o_qty,o_total) values ('$p_id','$m_user','$p_title','$p_img','$p_price','$quantity','$line_cost')"); } else if(mysql_num_rows($result) > 0 && $order_all !== 0){ mysql_query("update order set o_qty ='$quantity',o_total='$line_cost' where o_user='$m_user' and o_status='O'"); } } } E : I know the cause of change the price and quantity into 0 and the new product can't be added because this code: if(mysql_num_rows($result) > 0 && $order_all == 0 && $_SESSION['m_user']) { list($p_id, $p_title, $p_price, $p_img) = mysql_fetch_row($result); $line_cost = $p_price * $quantity; $total = $total + $line_cost; mysql_query("insert into order(pid,o_user,o_title,o_img,o_price,o_qty,o_total) values ('$p_id','$m_user','$p_title','$p_img','$p_price','$quantity','$line_cost')"); } else if(mysql_num_rows($result) > 0 && $order_all !== 0){ mysql_query("update order set o_qty ='$quantity',o_total='$line_cost' where o_user='$m_user' and o_status='O'"); } Q : But how to fix this code, in short how to create sql/mysql code so that : Case1 : if the product not exist on user account / database , it will do : mysql_query("insert into order(pid,o_user,o_title,o_img,o_price,o_qty,o_total) values ('$p_id','$m_user','$p_title','$p_img','$p_price','$quantity','$line_cost')"); so that when user buy new product it can be added though user has buy some product before. and Case2 : if the product already exist on user account / database , it will do : mysql_query("update order set o_qty ='$quantity',o_total='$line_cost' where o_user='$m_user' and o_status='O'"); so that when user buy same product it can be change the quantity and total price, based on product that user had buy before. \t Important : I buy product when in condition not login in other word as anonymous, and this image shown after I logged as user. so, image on above displayed based on database not on session. please help me......
VIEWS ON THIS POST

196

Posted on:

Saturday 29th December 2012
View Replies!