string comparison question




been away for a while and am obviously rusty.

Please assume 'today' is Saturday.

I am trying to output the days on which a product is available either side of today. So it could be Thursday and Tuesday. I have tried using the following query to return the max(day_of_week) before today - which is in fact Friday but, it returns 'Monday'.

here's the table

Code MySQL: CREATE TABLE `product_times` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `product_on_sale_id` bigint(20) NOT NULL, `day_of_week` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, `time_of_day` time DEFAULT NULL, `max_spaces` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL, `spaces_sold` int(11) DEFAULT NULL, `remaining_spaces` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL, `on_off` char(3) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `product_on_sale_id` (`product_on_sale_id`,`day_of_week`,`time_of_day`), CONSTRAINT `productTimes_productsOnSale_fk` FOREIGN KEY (`product_on_sale_id`) REFERENCES `products_on_sale` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1985 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
and the query

Code MySQL: select max(day_of_week) from product_times as pt inner join products_on_sale as pos on pos.id = pt.product_on_sale_id and pos.product_range_id = 199 where pt.day_of_week < 'Saturday'
I can't seem to work out what I think should be easy.
bazz
Posted On: Monday 31st of December 2012 01:45:19 AM Total Views:  459
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Undo drop database?

I accidentally dropped a database in phpmyadmin. Is there any way to undo this either in phpmyadmin or mysql console Using WAMP.
VIEWS ON THIS POST

84

Posted on:

Monday 29th October 2012
View Replies!

Grouping by Date, Week, Month

, I am having an issue with this...I've read up on it, and found about 20 different solutions to the problem. I've tried a few, and some haven't worked. I am needing to group by date, week or month on a table that includes the date in the YYYY-MM-DD HH:MM:SS format. I cannot group by on this table, as it'll aggregate results down to the second. I need to group by either date, month, or year. To do this, I've spent the past 2 days looking up solutions for this problem. From my understanding, there are a few solutions: - Build a calendar table that has individual dates (such as '2009-06-21'), weeks ('2009-06-22') and months ('2009-06-01'). This may be do-able, but I am not the guy in charge of the DB, but I use it extensively, and need a fast solution for aggregating results in various formats for analytics...Also, I was told by the person that built the DB that doing a join on such a calendar would be rather slow. - Use the DATEADD and DATEDIFF callouts to strip the HH:MM:SS from the column, and work that way. - Use a left substring in the SELECT field (but this returns 1 result if I want to COUNT the number of occurrences within a given date range). So I have proceeded with Option #2, the DATE_ADD and DATEDIFF strings. I found a nice one on a forum, but it seems not to work: SELECT DATE_ADD(dd, DATEDIFF(dd, 0, transaction_date), 0), SUM(IST.transaction_amount) "Revenue" FROM IA.Services.transactions IST WHERE transaction_date >'2009-06-10' GROUP BY DATE_ADD(dd, DATEDIFF(dd, 0, transaction_date), 0); transaction_date is the column that needs the HH:MM:SS altered so I can group by date, week, or month. It would seem from the manuals that the DATE callouts also can return results by the week and month, which makes me think it'll work.
VIEWS ON THIS POST

67

Posted on:

Monday 29th October 2012
View Replies!

Double order by

i order currently by registration date (desc) and want to add another order to it. I want to order by contract so that the people that have a contract with us that they come first. This query: PHP Code: SELECT* FROM`user` WHEREaktief='Y' ORDERBYregdatumDESC,contractDESC LIMIT0,30 So if i have these users in my database: User1 regdatum: 2005-12-06, contract 0 User2 regdatum: 2005-11-06, contract 1 User3 regdatum: 2005-10-06, contract 0 User4 regdatum: 2005-09-06, contract 1 I want to reach that User4 comes first, then User2 (he has a contract as well but registered later), then User3 and finally User1. (i do not get these results with above query) So i order on date but the users that has a contract has priority and come higher on the list. Hope someone can help me out,
VIEWS ON THIS POST

120

Posted on:

Monday 29th October 2012
View Replies!

print patient and doctor for operation on specific date (was "Very Confused!!")

Hi all, Having a few troubles again now lol. Ive got 3 Tables one of which is patient (name, address, ageetc), Doctor (again name address etc) and finally Operation (PatientCode, DoctorCode, Type and date etc). Im trying to get a SQL query which will print all the details of the patient and doctor who had/performed an operation on a specific date. So far I have this Code: SELECT PatientCode, DoctorCode From Operation WHERE Date = '21/11/05' Now that gives me a table containing 4 patient codes in patientcode colum and 4 doctorcodes in the doctorcode column. Now my qquestion is how would I go about getting the patient and doctors details from here\t. I've looked around the internet on tutorials and such but have come to no avail as all examples which are simlair to this are simlair in a way of their is only one table to compare it to (would be like just obtaining the patient details only) which I can do fine. Just when it comes to merging the two tables I cant do it. Any Ideas\t
VIEWS ON THIS POST

88

Posted on:

Monday 29th October 2012
View Replies!

1292 - Truncated incorrect DOUBLE value?

Ok, I'm confused & was wondering if anyone could work out what was going on. n.b. Version 5.0.15-standard, running on OSX. I have a usertable which contains user names and passwords hashed used the standard md5() algorithm. It looks like this: Code: mysql> describe usertable; +----------+------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned zerofill | NO | PRI | NULL | auto_increment | | name | varchar(15) | NO | | | | | password | char(32) | NO | | | | | fullname | varchar(50) | NO | | | | +----------+------------------------------+------+-----+---------+----------------+ 4 rows in set (0.04 sec) Right, now I want to update this table, say if I want to change my username or password. Starting data: Code: mysql> select * from usertable limit 1; +-----+-------+----------------------------------+-----------------+ | id | name | password | fullname | +-----+-------+----------------------------------+-----------------+ | 001 | simon | 098f6bcd4621d373cade4e832627b4f6 | Simon Greenhill | +-----+-------+----------------------------------+-----------------+ 1 row in set (0.00 sec) So - change username: Code: mysql> update usertable set fullname = 'Joe Smith' where id = 001; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from usertable limit 1; +-----+-------+----------------------------------+-----------+ | id | name | password | fullname | +-----+-------+----------------------------------+-----------+ | 001 | simon | 098f6bcd4621d373cade4e832627b4f6 | Joe Smith | +-----+-------+----------------------------------+-----------+ 1 row in set (0.01 sec) Good. Now let's change the password to another md5 hash: Code: mysql> UPDATE usertable SET password = '111f6bcd4621d373cade4e832627b4f6' WHERE id = 001; Query OK, 1 row affected (0.14 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from usertable limit 1; +-----+-------+----------------------------------+-----------+ | id | name | password | fullname | +-----+-------+----------------------------------+-----------+ | 001 | simon | 111f6bcd4621d373cade4e832627b4f6 | Joe Smith | +-----+-------+----------------------------------+-----------+ 1 row in set (0.00 sec) Cool. Now what happens if I try to change the fullname and password at the same time Code: mysql> update usertable set password = '098f6bcd4621d373cade4e832627b4f6' AND fullname = 'Simon Greenhill' where id = 001; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from usertable limit 1; +-----+-------+----------+-----------+ | id | name | password | fullname | +-----+-------+----------+-----------+ | 001 | simon | 0 | Joe Smith | +-----+-------+----------+-----------+ 1 row in set (0.00 sec) Huh where'd the password go What does that warning say Code: +---------+------+----------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '098f6bcd4621d373cade4e832627b4f6' | +---------+------+----------------------------------------------------------------------+ 1 row in set (0.00 sec) .......... maybe if I md5'd inside MySQL: Code: mysql> update usertable set password = MD5('test') AND fullname = 'Simon Greenhill' where id = 001;Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 Great! no warnings! One row matched - but none changed. If I change the order (update fullname before password ) the same thing happens, except it now complains the fullname is an incorrect double value. Any ideas Am I missing something obvious File a bug report --Simon
VIEWS ON THIS POST

647

Posted on:

Monday 29th October 2012
View Replies!

The Last Step - I hope

Hi , I am struggling with the last step in developing my 'clumsy idiot avoidance' code. An attempt to avoid my getting captions matched to wrong image or wrong price etc.. It's still an academic curiosity at the moment but I feel sure it will come in useful. This gives me exactly what I want to see, a formatted, prescribed table complete with content for a specific set of values for catalogue=A0002. [] [] However, I want to make it a sub procedure or function then call it in various page positions for different values of 'catalogue' in the WHERE part of my conditional SQL. Such as [] in the following code. This code does not work but I cannot see why it shouldn't. [] [ ] I am sure I've done something stupid but I just can't see it, and when I test it through the server resulting in '500 Internal Server Error', I feel so frustrated that there are no clues pointing to where the error lies.
VIEWS ON THIS POST

37

Posted on:

Wednesday 7th November 2012
View Replies!

Concatenated field for formula

I am trying to gather a formula based off two fields inside my stored procedure. The formula is: Code: Total_Approved / (Sum(Total_Approved) + Sum(Denied)) Inside my stored procedure, this is what I am using currently to gather these fields: Code: '$' + convert(varchar(12),sum(Loan_Detail.Regular)-sum(CASE WHEN Loan_Header.Credit_Type = 'P' THEN Regular+promo ELSE 0 END)+sum(Loan_Detail.Promo),1) AS Total_Approved '$' + convert(varchar(12),sum(Loan_Detail.Denied),1) AS Denied I have tried doing this to no avail inside my stored proc: Code: (sum(Loan_Detail.Regular)-sum(CASE WHEN Loan_Header.Credit_Type = 'P' THEN Regular+promo ELSE 0 END)+sum(Loan_Detail.Promo)) / (sum(Loan_Detail.Regular)-sum(CASE WHEN Loan_Header.Credit_Type = 'P' THEN Regular+promo ELSE 0 END)+sum(Loan_Detail.Promo)+sum(Loan_Detail.Denied)) as Approval_Rate Any idea on how I could do this and get the right formula
VIEWS ON THIS POST

76

Posted on:

Wednesday 7th November 2012
View Replies!

Arithmetic overflow error

I am getting the following error: Microsoft OLE DB Provider for SQL Server error 80040e57. Arithmetic overflow error converting numeric to data type numeric positionDisplay.asp line 21 ---- Here is line 21 rsCountPos.open appCountSQL,conn,1,1,1 The strange thing is that this just started after January 1st. On our site we get online job applications and there is also a way for us to go into an 'admin' area of our site to view the app details. We are getting this error only on applications submitted after Jan 1st. Everytime someone submits an application they receive a number. The number is the month, day, year, and then the number of the application received for that day. For example, app_id = 092120040005 - this would be the fifth application we received on 09/21/2004. I know practically nothing about SQL and this was kind of put in my lap. Please help! Point me in some direction.
VIEWS ON THIS POST

113

Posted on:

Wednesday 7th November 2012
View Replies!

SELECT * WHERE cell = cookie data

i need to display the name of a user on a 'confirm page'. request.form wont work, im now using a cookie. The user has already inputted his membership number which is a unique identifyer. how do i tell SQL to bring back only the row which starts with the membership number contained in the cookie maybe this will demostrate what im trying to do. Dim memRS Dim memRS_numRows Dim memNumber memNumber = Request.Cookies("memberCookie") Set memRS = Server.CreateObject("ADODB.Recordset") memRS.ActiveConnection = MM_Poll_YMS_STRING memRS.Source = "SELECT * FROM dbo.Member_Data WHERE Member_Num = ('memNumber' )" memRS.CursorType = 0 memRS.CursorLocation = 2 memRS.LockType = 1 memRS.Open() later on i will have to make it check that that user has voted only once, but i'll be back for that no doubt.
VIEWS ON THIS POST

60

Posted on:

Wednesday 7th November 2012
View Replies!

syntax of passing query to stored procedur

Originally posted by : Donna (donnaz@webportinc.com)I keep getting syntax error near the WHERE clause when passing variables to MS-SQL stored proc. Would someone like a few paying hours to help get these variables into the sp and make the results page. I must search by date, number, and varchar. I'm on server 7.0 using a temp table. I've looked at all the paging info on 4 and other sites. HELP!
VIEWS ON THIS POST

157

Posted on:

Wednesday 7th November 2012
View Replies!

Submit values to one column

I have the below query that gets a list of engineers names, however i want all the names to be put into one column, how do i go about doing this Currenlty when they get pulled out its as a column and i want a comma list Currenlty Gets: Engineer1 Engineer2 Engineer3 Beed to get: Enginner1, Enginner2, Enginner3 Code: ALTER PROCEDURE alsp_HandheldsNotWorking AS DECLARE @ENGINEER VARCHAR(30) SELECT @ENGINEER = ENGINEER FROM tbl_engineer WHERE (ACTIVE = 1) AND (HASHHT = 1) AND (RDTLOGGEDON = 0) SELECT @ENGINEER AS Engineers
VIEWS ON THIS POST

76

Posted on:

Saturday 10th November 2012
View Replies!

Help with SELECT using Left Join

My understanding is that a Left Join is used to get all the rows from the first table , even if there are no matches in the second table. My first table has the days of the week and my second table has the records that relate to days of the week. But I need to limit the records to an OpenID (in the second table), but still want all the days of the week to show so that the days that do not have the openID I can add them. I realise that by putting a WHERE clause in, I am not getting all of the days of the week. How do I change the code to get all of the days of the week as well as which days are connected to the OpenID Here is what I have so far. Code: set rsMealDays = My_Conn.Execute("SELECT tbl_days.Days, tbl_MealServices.OpenID, tbl_days.DaysID FROM tbl_days LEFT JOIN tbl_MealServices ON tbl_days.DaysID = tbl_MealServices.DaysID WHERE OpenID = " & OpenID & " ORDER BY tbl_days.DaysID ASC") Thank you for your time
VIEWS ON THIS POST

92

Posted on:

Saturday 10th November 2012
View Replies!

Multiple emails with one DTS package

I have a web application that prompts a user for some info and creates databases on the fly with VB.NET. Multiple databases can be created in one day. I need to create a DTS package that will be scheduled to run daily. This dts gathers some info on each database that got created. For example, 5 databases were created. The DTS creates an email with some statistics in the body of the email and a csv file attachment of some info from the databases. At this point I haven't decided (or actually wasn't told by my manager) if the package should create: 1. separate emails (in this cases 5 emails) with the csv file attached to each email and stats in the body OR 2. one email with 5 attachments (for each db) and stats of all 5 dbs in the body OR 3. one email with one attachment, but 5 worksheets and stats of all 5 dbs in the body. Are all of these scenaries possible within a DTS package. Remember, I won't know how many databases were created. I'd have to figure that out dynamically.
VIEWS ON THIS POST

101

Posted on:

Saturday 10th November 2012
View Replies!

Query returning wrong rows!!

Hi I have this query, but instead of returning the most recent permission for each hcp, it's just returning the most recent permissions!! SELECT hcp.HCP_ID, Lastname, Firstname,product, permission_type.Type_Name, Date_Entered FROM hcp, permission_type, permission_details, permission_header WHERE hcp.HCP_ID=permission_header.hcp_id AND permission_header.Permission_ID=permission_details .permission_id AND permission_details.Type_Name=permission_type.type_ name and permission_details.type_name 'Withdrawn' AND permission_details.Date_Entered=(select max(permission_details.date_entered) from permission_details,hcp,permission_header where permission_details.permission_id=permission_header .permission_id and permission_header.hcp_id=hcp.hcp_id) ORDER BY hcp.Lastname Can anyone help\t
VIEWS ON THIS POST

125

Posted on:

Saturday 10th November 2012
View Replies!

which to use: LIKE, =, IN... help!!!

im pulling information out of a DB2 database on an AS/400 and get different, sometimes strange results depending on which i use. im not doing any math on any of the fields i pull in, although most of the fields hold numerical data... but im only displaying it on a webpage... so for example lets say a customer number is 150 and i want to display records that match that customer number: A) SELECT CUSNO WHERE CUSNO = 150 B) SELECT CUSNO WHERE CUSNO = '150' C) SELECT CUSNO WHERE CUSNO LIKE '150' and then i can also do something with the "IN" keyword like: SELECT CUSNO WHERE CUSNO IN (150) SELECT CUSNO WHERE CUSNO IN ('150') so which way is the "right" way i think im being confused by whether or not to use "=" and whether or not to use single quotes... anyone wanna shed some light on this for me
VIEWS ON THIS POST

133

Posted on:

Saturday 10th November 2012
View Replies!

SQL Stored Prodcedure Group by and Sum clauses

I have a SQL stored procedure that is returning results in a table as follows (Below): - I would like to have the results grouped by price so as it only appears the once. I would like to have it so as the copies and Rev totals for each price are summed up. To show exactly what I mean please look at the revised table below: No publication_name calculated_price Copies Rev 1 Belfast Telegraph 0.01 1549 15.49 1 Belfast Telegraph 0.01 149 1.49 1 Belfast Telegraph 0.375 16578 6216.75 1 Belfast Telegraph 0.375 1578 986.75 1 Belfast Telegraph 0.375 9578 2245.75 1 Belfast Telegraph 0.375 18578 6546.75 1 Belfast Telegraph 0.41 8530 3497.3 1 Belfast Telegraph 0.41 8530 3497.3 1 Belfast Telegraph 0.41 8530 3497.3 1 Belfast Telegraph 0.5 9854 4927 1 Belfast Telegraph 0.5 9854 4927 1 Belfast Telegraph 0.5 9854 4927 2 Sunday Life 0.25 547 136.75 2 Sunday Life 0.25 156 39.5 2 Sunday Life 0.25 156 39.5 2 Sunday Life 0.76 68 14.58 2 Sunday Life 0.76 3568 2711.68 2 Sunday Life 0.61 1689 1030.29 2 Sunday Life 0.61 1689 1030.29 2 Sunday Life 0.61 1689 1030.29 2 Sunday Life 0.76 3568 2711.68 2 Sunday Life 0.76 3568 2711.68 I would like the results to come back as follows: - 1 Belfast Telegraph 0.01 1849 18.49 1 Belfast Telegraph 0.375 46312 17637 1 Belfast Telegraph 0.41 8530 3497.3 1 Belfast Telegraph 0.5 9854 4927 2 Sunday Life 0.25 703 175.75 2 Sunday Life 0.76 7204 5475.04 2 Sunday Life 0.61 1689 1030.29 My stored procedure at present is as follows: - SELECT REPORT_WKENDING.No, REPORT_WKENDING.publication_name, REPORT_WKENDING.calculated_price, REPORT_WKENDING.Copies, REPORT_WKENDING.Rev FROM dbo.REPORT_WKENDING order by dbo.REPORT_WKENDING.PubNumber Could someone please help me to re-write this so as it groups the prices together and sums up the Copies and revenue for each price. I would be so grateful for any help anyone is able to give me. Im pretty knew to stored procedures. Thank you
VIEWS ON THIS POST

98

Posted on:

Saturday 10th November 2012
View Replies!

SQL Query Problem (Advanced Help needed!)

, I have three tables, Blog, Cat, and BlogCatXRef: Blog Column Names: BlogID, BlogName Cat Column Names: CatID, CatName BlogCatXRef Column names: BlogCatID, BlogID, CatID I built these tables so that I could cross reference multiple categories per blog. I need to create an SQL Query that will give me three columns: Query Column Names: CatID, CatName, BlogID And populate these columns with ALL the categories (i.e. ID's 1 to 8) I can get the CatID and CatName quite easily by using a standard SELECT Query. What I need is the BlogID column to show either the BlogID number or nothing (i.e. NULL) but still have ALL the categories listed (i.e. ID's 1 to 8) when I pass a BlogID of 1 to the query. I'm finding this rediculously difficult. So say that, BlogID 1 is related to CatID 1, 2 and 3, BlogID 2 is related to CatID 1, 4 and 5 via the BlogCatXRef table. I want my query to show CatID 1, 2, 3, 4, 5, 6, 7 and 8, the relevant CatNames, and in the BlogID column the BlogID variable that I pass to the query (i.e. 1) or NULL for all the other Categories. With everything that I've tried, it either shows ONLY the categories that BlogID 1 is related to, or categories that BlogID 1 is related to and NULL values, missing out the categories that BlogID 2 is related to. I need it to show ALL the categories (whether related to BlogID 1 or BlogID 2 or Nothing) and IF THEY ARE related to BlogID 1, put the BlogID number in the BlogID column. I think I've made sense there. I know this is possible, it has to be... I'm just tearing my hair out as to how. I've tried Left Outer Joins, Inner Joins, Sub Queries etc, but I don't know how to put them together to get my result. Help me, please, before I have an embolism.
VIEWS ON THIS POST

120

Posted on:

Sunday 11th November 2012
View Replies!

Help Dudes !!!! Im Sinking Fast

First time user, and in so much need of urgent assistance if poss. Senario: I have a list of Apartment Id set in an array being pulled out thus (as an example) 1,3,50,12,21 Now, I need to SELECT from my SQL excluding these Id's. Its doing my lid in !! Cheers Dudes
VIEWS ON THIS POST

74

Posted on:

Sunday 11th November 2012
View Replies!

Insert into using Domino front end

I'm creating a front end via Domino 6.0.3 to access an AS400 and insert values captured from a form into an existing table When the agent executes I gett he following error: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0104 - Token was not valid. Valid tokens: ( : WITH SELECT VALUES ., Connector 'odbc2', Method -Execute- (-104) The agent is as follows: Sub Initialize On Error Goto Handler Dim sess As NotesSession Dim FieldOut As New LCFieldList Dim count As Long Dim Session As New LCSession Dim server As String session.ClearStatus Dim ZConType As String Dim ZConSvr As String Dim ZConUid As String Dim ZConPwd As String Set src = New LCConnection ("odbc2") src.server = "Live400" src.UserID = "LOTUS" src.Password = "SUTOL" src.Connect Set sess = New NotesSession Set doc = sess.DocumentContext Dim xs As String Dim y As String xs= sess.CommonUserName If xs = "xxxxx" Then y="xxxxx.domain name.org" Else y="yyyyyy.domain name.com" End If Server =y &"/"& doc.DbName(0) TxtDta = doc.xusr(0) NumPdln = doc.xprdln(0) NumPo = doc.xpo(0) MxtDta = doc.xso(0) CnCde= doc.xcnfcd(0) SF = "Insert Into TEST2.TSTPOCON (XUSR,XPRDLN,XPO,XSO#, XNCFCD)" SF = SF & " values ('" & TxtDta & "', " & NumPdln & ", " & NumPo & ", " & MxtDta &","" & CnCde &"')" doc.MsgFld = SF (thisis where the error message is shown) success = src.Execute(SF, FieldOut) doc.MsgFld = "Record Added" Exit Sub End End Sub The initial SF= call seems to be the casue of the token error.
VIEWS ON THIS POST

108

Posted on:

Sunday 11th November 2012
View Replies!

Counting problem

, I'm having some trouble with the counting function. I've a table with the following fields: Section_title Subsection_title Each subsection is asociated with a section. What I need is an script that displays all that information and also counts how many of each sections are displayed. ie: Section_title Subsection_title Count IT bibliography 2 Users Administration 1 IT free courses 2 How can I do that that 1/2 number count appears PS: sorry 4 my english.
VIEWS ON THIS POST

79

Posted on:

Sunday 11th November 2012
View Replies!

please help update sql

Hi i am trying to build an updatable profile area for members but am getting Microsoft VBScript runtime error '800a01a8' Object required: '[string: "utown"]' /myspace/edit_userloc.asp, line 31 when i run this bit of code : username = Request.cookies("isLoggedInAs")("username") town = cstr(Request.form("utown")) postcode = cstr(Request.form("upostcode")) alerts = cstr(Request.form("alerts")) set racDB = Server.CreateObject("ADODB.Connection") racDB.Open "rac" set insertset = Server.CreateObject("ADODB.RecordSet") insertset.Open "memb_loc", racdb, _ adOpenstatic, adCmdTable if p_existing = "" then insertset.AddNew else updateset.Find "username =" &p_existing end if set town = "utown" set postcode = upostcode set("alerts") = alerts updateset.Update updateset.Close set insertset = Nothing racDB.Close set racDB = Nothing Response.Clear this is the form action i am using Town: Postcode: Send me 1 2 3 4 5 6 7 8 9 10 text alerts per week please help steve
VIEWS ON THIS POST

131

Posted on:

Sunday 11th November 2012
View Replies!

Database password

Originally posted by : Simon Pocklington (ca8spo@isis.sund.ac.uk)I'm trying to open a database secured by a password with an ASP pageThe database, without a password, opens with the code shown below:'Create a connection objectSet Conn = Server.CreateObject("ADODB.Connection")'Select providerConn.Provider = "Microsoft.Jet.OLEDB.4.0"'Select data source - the Kingswood database.Conn.ConnectionString = "Data Source=" & Server.MapPath ("kingswood.mdb")'Open the connectionConn.OpenHow do I modify this code to open the database when it has a password
VIEWS ON THIS POST

41

Posted on:

Sunday 11th November 2012
View Replies!

Updating help

hEYA all, Im doing an asp page which can run an update command. Now im getting the values from the pervious page which are fine- but i want to update that record in ms access using update- but i keep getting this error Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x8007000E) [Microsoft][ODBC Microsoft Access Driver] Too many fields defined. I cant seem to find out a way around this- as i need all the fields updated. Im not to concerned about creating a loop- but i have an array- which stores the inforamtion of each field please any suggestions or code heres my update code Code: sub UpdateCmd( ) '========================================================================================= SET objRs = SERVER.createObject( "ADODB.recordSet" ) dim sqlStatment sqlStatment = "UPDATE Members SET " 'sqlStatment = sqlStatment & "( " sqlStatment = sqlStatment & "[Managing Director] = '"&FieldsArray(1)&"', [Title] = '"&FieldsArray(2)&"', " sqlStatment = sqlStatment & "[Buying Group Member] = '"&FieldsArray(3)&"',[M D Sal] = '"&FieldsArray(4)&"', " sqlStatment = sqlStatment & "[Rebate Contact] = '"&FieldsArray(5)&"', [Rebate Title] = '"&FieldsArray(6)&"', " sqlStatment = sqlStatment & "[Rebate Sal]='"&FieldsArray(7)&"', [Replied]='"&FieldsArray(8)&"', " sqlStatment = sqlStatment & "[Member Name]='"&FieldsArray(9)&"', [S/Holder2]='"&FieldsArray(10)&"', " sqlStatment = sqlStatment & "[S/Holder]='"&FieldsArray(11)&"', [ALPHA TYPE]='"&FieldsArray(12)&"', " sqlStatment = sqlStatment & "[Member Type]='"&FieldsArray(13)&"', [Short Name]='"&FieldsArray(14)&"', " sqlStatment = sqlStatment & "[Address_1]='"&FieldsArray(15)&"', [Address_2]='"&FieldsArray(16)&"', " sqlStatment = sqlStatment & "[Address_3]='"&FieldsArray(17)&"', [Address_4]='"&FieldsArray(18)&"', " sqlStatment = sqlStatment & "[Postcode]='"&FieldsArray(19)&"', [Area]='"&FieldsArray(20)&"', " sqlStatment = sqlStatment & "[Area Code]='"&FieldsArray(21)&"', [Buying Centers]='"&FieldsArray(22)&"', " sqlStatment = sqlStatment & "[Showrooms]='"&FieldsArray(23)&"', [Bathroom]='"&FieldsArray(24)&"', " sqlStatment = sqlStatment & "[Kitchen]='"&FieldsArray(25)&"', [Heating]='"&FieldsArray(26)&"', " sqlStatment = sqlStatment & "[Security]='"&FieldsArray(27)&"', [ToolHire]='"&FieldsArray(28)&"', " sqlStatment = sqlStatment & "[Brick]='"&FieldsArray(29)&"', [Glass]='"&FieldsArray(30)&"', " sqlStatment = sqlStatment & "[Landscaping]='"&FieldsArray(31)&"', [Decking]='"&FieldsArray(32)&"', " sqlStatment = sqlStatment & "[Others]='"&FieldsArray(33)&"', [Telephone]='"&FieldsArray(34)&"', " sqlStatment = sqlStatment & "[Telephone1]='"&FieldsArray(35)&"', [Fax No]='"&FieldsArray(36)&"', " sqlStatment = sqlStatment & "[Fax No1]='"&FieldsArray(37)&"', [Email]='"&FieldsArray(38)&"', " sqlStatment = sqlStatment & "[MD Tel]='"&FieldsArray(39)&"',[MD Fax]='"&FieldsArray(40)&"', " sqlStatment = sqlStatment & "[FD Contact]='"&FieldsArray(41)&"', [FD Title]='"&FieldsArray(42)&"', " sqlStatment = sqlStatment & "[FD Salutation]='"&FieldsArray(43)&"', [Rebate Tel]='"&FieldsArray(44)&"', " sqlStatment = sqlStatment & "[Rebate Fax]='"&FieldsArray(45)&"', [Rebate Email]='"&FieldsArray(46)&"', " sqlStatment = sqlStatment & "[Accounts Contact]='"&FieldsArray(47)&"', [Accounts Title]='"&FieldsArray(48)&"', " sqlStatment = sqlStatment & "[Accounts Sal]='"&FieldsArray(49)&"', [FD Tel]='"&FieldsArray(50)&"', " sqlStatment = sqlStatment & "[FD Fax]='"&FieldsArray(51)&"', [BT Contact]='"&FieldsArray(52)&"', " sqlStatment = sqlStatment & "[BT Title]='"&FieldsArray(53)&"', [BT Salutation]='"&FieldsArray(54)&"', " sqlStatment = sqlStatment & "[Plumbing & Heating Contact]='"&FieldsArray(55)&"', [Accounts Tel]='"&FieldsArray(56)&"', " sqlStatment = sqlStatment & "[Accounts Fax]='"&FieldsArray(57)&"', [Plumbing & Heating Title]='"&FieldsArray(58)&"', " sqlStatment = sqlStatment & "[Plumbing & Heating Sal]='"&FieldsArray(59)&"', [Hardware & Ele Contact]='"&FieldsArray(60)&"', " sqlStatment = sqlStatment & "[HE Title]='"&FieldsArray(61)&"', [HE Salutation]='"&FieldsArray(62)&"', " sqlStatment = sqlStatment & "[Contact 1]='"&FieldsArray(63)&"', [XML Tel:]='"&FieldsArray(64)&"', " sqlStatment = sqlStatment & "[XML Fax:]='"&FieldsArray(65)&"',[TITLE 1]='"&FieldsArray(66)&"', " sqlStatment = sqlStatment & "[SALUTATION 1]='"&FieldsArray(67)&"', [General Lit 1]='"&FieldsArray(68)&"', " sqlStatment = sqlStatment & "[Contact 2]='"&FieldsArray(69)&"', [TITLE 2]='"&FieldsArray(70)&"', " sqlStatment = sqlStatment & "[B T Tel]='"&FieldsArray(71)&"', [B T Fax]='"&FieldsArray(72)&"', " sqlStatment = sqlStatment & "[Sal 2]='"&FieldsArray(73)&"', [General Lit 2]='"&FieldsArray(74)&"', " sqlStatment = sqlStatment & "[Contact 3]='"&FieldsArray(75)&"', [TITLE 3]='"&FieldsArray(76)&"', " sqlStatment = sqlStatment & "[P & H Tel]='"&FieldsArray(77)&"', [P & H Fax]='"&FieldsArray(78)&"', " sqlStatment = sqlStatment & "[SALUTATION 3]='"&FieldsArray(79)&"', [Email 1]='"&FieldsArray(80)&"', " sqlStatment = sqlStatment & "[B G 1]='"&FieldsArray(81)&"', [C D 1]='"&FieldsArray(82)&"', " sqlStatment = sqlStatment & "[HE Tel]='"&FieldsArray(83)&"', [HE Fax]='"&FieldsArray(84)&"', " sqlStatment = sqlStatment & "[Rebate 1]='"&FieldsArray(85)&"', [Phone 4]='"&FieldsArray(86)&"', " sqlStatment = sqlStatment & "[Fax4]='"&FieldsArray(87)&"', [General Lit 3]='"&FieldsArray(88)&"', " sqlStatment = sqlStatment & "[B G 2]='"&FieldsArray(89)&"', [Email 2]='"&FieldsArray(90)&"', " sqlStatment = sqlStatment & "[C D 2]='"&FieldsArray(91)&"', [Rebate 2]='"&FieldsArray(92)&"', " sqlStatment = sqlStatment & "[B G 3]='"&FieldsArray(93)&"', [C D 3]='"&FieldsArray(94)&"', " sqlStatment = sqlStatment & "[Rebate 3]='"&FieldsArray(95)&"', [Contact 4]='"&FieldsArray(96)&"', " sqlStatment = sqlStatment & "[TITLE 4]='"&FieldsArray(97)&"', [SAL 4]='"&FieldsArray(98)&"', " sqlStatment = sqlStatment & "[B S 1]='"&FieldsArray(99)&"', [Email 3]='"&FieldsArray(100)&"', " sqlStatment = sqlStatment & "[Contact 5]='"&FieldsArray(101)&"', [TITLE 5]='"&FieldsArray(102)&"', " sqlStatment = sqlStatment & "[SAL 5]='"&FieldsArray(103)&"', [General Lit 5]='"&FieldsArray(104)&"', " sqlStatment = sqlStatment & "[Queries]='"&FieldsArray(105)&"', [Website]='"&FieldsArray(106)&"', " sqlStatment = sqlStatment & "[MD Email]='"&FieldsArray(107)&"', [B G 4]='"&FieldsArray(108)&"', " sqlStatment = sqlStatment & "[BS 2]='"&FieldsArray(109)&"', [Email 4]='"&FieldsArray(110)&"', " sqlStatment = sqlStatment & "[C D 4] ='"&FieldsArray(111)&"', [Rebate 4]='"&FieldsArray(112)&"', " sqlStatment = sqlStatment & "[General Lit 4]='"&FieldsArray(113)&"', [FD Email]='"&FieldsArray(114)&"', " sqlStatment = sqlStatment & "[Accounts Email]='"&FieldsArray(115)&"', [Building & Timber Email]='"&FieldsArray(116)&"', " sqlStatment = sqlStatment & "[HE Email]='"&FieldsArray(117)&"', [B G 5]='"&FieldsArray(118)&"', " sqlStatment = sqlStatment & "[Email 5]='"&FieldsArray(119)&"', [BS 3]='"&FieldsArray(120)&"', " sqlStatment = sqlStatment & "[C D 5]='"&FieldsArray(121)&"', [Rebate 5]='"&FieldsArray(122)&"', " sqlStatment = sqlStatment & "[Spot Buy Fax]='"&FieldsArray(123)&"', [Spot Buy Contact]='"&FieldsArray(124)&"', " sqlStatment = sqlStatment & "[Lit]='"&FieldsArray(125)&"', [Spot Buy Email]='"&FieldsArray(126)&"', " sqlStatment = sqlStatment & "[Plumbing & Heating Email]='"&FieldsArray(127)&"', " sqlStatment = sqlStatment & "[XML]='"&FieldsArray(128)&"', [BS 4]='"&FieldsArray(129)&"', " sqlStatment = sqlStatment & "[XML Contact]='"&FieldsArray(130)&"', [XML Title]='"&FieldsArray(131)&"', " sqlStatment = sqlStatment & "[XML Sal]='"&FieldsArray(132)&"', [XML Email]='"&FieldsArray(133)&"', " sqlStatment = sqlStatment & "[Email 6]='"&FieldsArray(134)&"', [B G 6]='"&FieldsArray(135)&"', " sqlStatment = sqlStatment & "[B S 5]='"&FieldsArray(136)&"', [C D 6]='"&FieldsArray(137)&"'," sqlStatment = sqlStatment & "[B S Contact]='"&FieldsArray(138)&"', [B S Title]='"&FieldsArray(139)&"', " sqlStatment = sqlStatment & "[Contact 6]='"&FieldsArray(140)&"', [Title 6]='"&FieldsArray(141)&"', " sqlStatment = sqlStatment & "[Sal 6]='"&FieldsArray(142)&"', [Gen Lit 6]='"&FieldsArray(143)&"', " sqlStatment = sqlStatment & "[Rebate 6]='"&FieldsArray(144)&"', [B S Sal]='"&FieldsArray(145)&"', " sqlStatment = sqlStatment & "[B S 6]='"&FieldsArray(146)&"', [B S Tel]='"&FieldsArray(147)&"', " sqlStatment = sqlStatment & "[B S Fax]='"&FieldsArray(148)&"', [B S Email]='"&FieldsArray(149)&"', " sqlStatment = sqlStatment & "[Member A/C]='"&FieldsArray(150)&"' " 'sqlStatment = sqlStatment & ") " sqlStatment = sqlStatment + "Where [A/C no] = '"& No &"' And [Address_code] = '"& C &"'" 'Response.write(sqlStatment) 'Response.end objRs.OPEN sqlStatment, objConn,1,3 '========================================================================================= end sub
VIEWS ON THIS POST

196

Posted on:

Monday 12th November 2012
View Replies!

Create user

in ms sql server how can i create db useer if i follow the steps in book online i get errors my databas name is "myTestBD" CREATE LOGIN mike WITH PASSWORD = '123456'; USE myTestBD; CREATE USER mike; GO error is Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near 'LOGIN'. Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'USER' any idea do you know how to create users in(DERBY or Cloudscap or DB2) thx
VIEWS ON THIS POST

78

Posted on:

Monday 12th November 2012
View Replies!

Data from three tables? (join problem I guess)

Hi I've got a parent-child-grandchild hierarchy divided into three tables: Parents, Children and GrandChildren. I'm using ASP VBScript with Microsoft Access databases. Each table has a bunch of info, but they all have ID values with which the hierarchies are defined (eg. sharing the ParentID value in the Children table with a 1-to-many relationship). With the SQL query I included below, I can get the info I need from 'Parents' and 'Children' (and I know this only does just that) but I don't know how (and if) I can include the GrandChildren table so it can be used within the same Recordset in ASP. Code: SELECT Children.ChildID, Children.ChildTitle, Parents.ParentID, Parents.ParentTitle FROM Children INNER JOIN Parents ON Children.ParentID = Parents.ParentID ORDER BY Parents.ParentID
VIEWS ON THIS POST

100

Posted on:

Monday 12th November 2012
View Replies!

SELECT statment question

In my application I want to let the user choose a number of articles to be displayed randomly on a page. Some articles should always be displayed and some not. Lets say my database (access) has one table, tblArticle, which contains this: id article showAlways 1 article1 yes 2 article2 no 3 article3 yes 4 article4 yes 5 article5 no Now lets say that the users chooses to display 4 articles. What I want is a SELECT statment that can garantees that it always includes my showAlways=yes and fills upp the count with some showAlways=no. My deed output may look something like this: article3 article4 article2 article1 Or articel5 article4 article3 article1 What I want is an SQL statmenet that looks something like this: Set recset = Server.CreateObject("ADODB.Recordset") SQL = SELECT DISTINCT id FROM tblArticle WHERE showAlways = 'yes' AND then something else maybe....\t\t recset.Open SQL, conn p = recset.GetRows (I only SELECT id and use GetRows because I will later use the content of GetRows to randomize theese ids) As you can see in this SQL statement it only chooses the fields WHERE showAlways=yes, meaning that I have one empty id number if the user chooses 4 articles. What I want is the SQL statement to garantee that it all showAlways=yes with the addition of some showAlways=no. Is this possible to do this in one SQL statement I have tried different approaches using AND OR LIKE. And I don't want to start by displaying the 3 showAlways articles because they should also be included in the randomize function. And I don't want to start by randomize the showAlways=yes articles and then randomize the showAlways=no articles.
VIEWS ON THIS POST

81

Posted on:

Monday 12th November 2012
View Replies!

date range

hi, i have three tables. one is the room table, which can have a number of room rates (room rate table) which are depicted by the hotel season (hotel season table) is it possible to get what i want from sql or would i need to do it with asp what i would like to do is get the room name, season name, and room rate - when someone has input an arrival date and a departure date. my tables are as follows (may help) room_table ------------- room_id (PK) hotel_id (FK) room_name hotel_season_table -------------------- hotel_season_id (PK) hotel_id (FK) hotel_season_name hotel_season_start_date hotel_season_end_date room_rate_table -------------------- room_rate_id (PK) hotel_season_id (FK) room_id (FK) room_rate_name room_rate_amount
VIEWS ON THIS POST

97

Posted on:

Monday 12th November 2012
View Replies!

Delete records

I am trying to delete a record (productID) using delete command from dreamweaver server behaviour,but I get the message: 'supplierproducts' includes related records" is it because there is ProductID in the 'supplierproducts' table I tried with sql but it seems that it's not working as well here is my table products table: ProductID(PK) ...attributes... supplierproducts table: ProductID (FK) SupplierID(PK) I suppose I could select the itmes to delete as follow: select ProductID.products, productID.supplierproducts from products,supplierproducts where products.productID=supplierproducts.productIDis this correct
VIEWS ON THIS POST

67

Posted on:

Monday 19th November 2012
View Replies!

need help with complicated select distinct query

I have a table called gms_tests that keeps track of the results of tests run against servers. Columns are hostname, testname, status and time. The tests are run (about 5 against each server) every half hour, and the results are put in the table. I need to be able to search this table and obtain the most recent result for each test. For example, I need something similar to: select distinct hostname,testname from gms_tests order by time desc but I also need to be able to see the time and status columns. I've tried something like this: select hostname,testname,status,max(time) from gms_tests group by hostname,testname but the data returned in the status column is not correct - it doesn't match the status that corresponds with the latest test time. Any ideas what might work I'm using MySQL version 3.23.58, so I can't use subqueries.
VIEWS ON THIS POST

396

Posted on:

Monday 19th November 2012
View Replies!

having problem during upsizing from local access table to remote sql server

!! i have problem in upsizing data to remote sql server . my test connectivity is ok. some time if i specifi my database which is in remote sql server it is giving "overflow error " , if i use master data base i am not getting any error ,but i am not getting the repote generated when sql table is created. i am very new to sql is there any other method i any one guite me . i have to write a vc++ code , through which i have to update local access table and once the access table is updated , i have to transfer all the data to remote sql server using ip address. once the data is updated in sql server i have to delete the data in access table . please help me !!!
VIEWS ON THIS POST

178

Posted on:

Monday 19th November 2012
View Replies!

query question for tree menu

Just startd with oracle 9i and I cant figure out how to properly query the tree from following table with zort ordering PHP Code: ID|SUBID|TREEE|LEVEL|ZORT|NAME --------------------------------------------------------------- 1\t|0\t\t\t\t|/\t\t\t\t\t|1\t\t\t\t|1\t\t\t\t|firstnode --------------------------------------------------------------- 2\t|1\t\t\t\t|/1/\t\t\t\t|2\t\t\t\t|2\t\t\t\t|firstsubnode --------------------------------------------------------------- 3\t|2\t\t\t\t|/1/2/\t\t|3\t\t\t\t|3\t\t\t\t|firstsubsubnode --------------------------------------------------------------- 4\t|2\t\t\t\t|/1/2/\t\t|3\t\t\t\t|4\t\t\t\t|secondsubsubnode --------------------------------------------------------------- The tree column holds the paths for tree nodes so i can query the tree with one simple query but i don't know how to query tree by using zort for ordering the tree nodes Select * from thistable order by concat(tree,to_char(id)) first node |-first subnode |--second subsubnode (zort = 4) |--first subsubnode (zort = 3) can this be done with a query or i have to find out some other way
VIEWS ON THIS POST

89

Posted on:

Tuesday 20th November 2012
View Replies!

Using IF in calculation statement

I have a statement that is calculating days that an employee has been working for the company. This is the statement so far: Code: (SELECT SUM(DATEDIFF(dd, EmpStart, EmpEnd)) AS Expr1 FROM dbo.Employment AS Employment_2 WHERE (dbo.STAFF.AppNo = AppNo) GROUP BY AppNo) AS TimeServed The bit I need help with is the DATEDIFF bit. It calculates OK if there is an end date. But if end date is null, OR end date is > today, it should use today's date for the calculation. So I tried something like this: Code: SUM(DATEDIFF(dd, EmpStart, (IF EmpEnd IS NULL OR EmpEnd > GetDate() THEN GetDate() ELSE EmpEnd))) But it didn't work - please can anyone help Many
VIEWS ON THIS POST

55

Posted on:

Tuesday 20th November 2012
View Replies!

How to create insert statement to store variable amount of data.

Hi I used Javascript code that lets user to add new fields, let's say columns and rows I used Javascript code put it in my htm page, that code allows me to modify the amount of data that I need to submit through my ASP page and store the data received in the database variably without the need to change the code each time, cause this would be silly:d, anyway, the code provided options to add/delete rows or columns we want, after we determine the number or rows and columns we fill in the entire form and send it to the ASP page, of course, the JS code generates the variables sent by users, and the next line shows exactly what is the situation when submitting a form with 2 rows and 5 columns: http://localhost/...../page.aspc_1_1=1&c_1_2=2&c_1_3=3&c_1_4=4&c_1_5=5&c _2_1=6&c_2_2=7&c_2_3=8&c_2_4=9&c_2_5=10 the first letter is C, _ and the number of row comes next, after that the number of column of each cell, so it's like a grid. :d, to make it short, my problem is how to insert/store the whole data into my tables in database let say SQL/Access/MySQL whatever, or exactly how to make the SQL insert statement, actually I tried too many times to make up this code, up unfortunately, I gave up, so please help me, BTW, i dont know if the htm file that contains the JS code is necessary to use to solve this issue and bring up an ASP code, but no problem I can post it anyway, but I think the trick is in the ASP code neither the JS. BTW, after all, i need this code so I can let user to add only "new rows" so i can let the SQL insert to enter a for/loop, cause I already chose the columns and created the table in the database, so we can focus on how much rows we can receive and store in the same time. And it's ok to assume the table/database/pages you want. Thanx in advance and patiently waiting your replies.
VIEWS ON THIS POST

111

Posted on:

Tuesday 20th November 2012
View Replies!

Alternative to a bunch of Exists clauses

Currently I am working on a site that will allow a user to browse through a list of products and filter that list based on a series of options. That list of selected options is going to be passed to a back end and will act as a set of attributes that a product listing must have in order to be displayed. of this is working just fine, the only problem is that it probably isn't going to perform that well under large load. Explaining the entire data model would take far longer than a single post but I'll sum it up. We have two main entities a Product and an Attribute. One product is made up of its collection of attributes. A product record returned by the view has as many rows as it has attributes. Each product can have an arbitrary number of attributes associated with it. Products are connected to attributes in a ProductAttribute table containing (*product_id, *attribute_id). I will have a list of required attribute_id's based on the filters the user has selected. I need to retrieve all products, including all of their attributes, that have entries for all of the required attributes. The following query is how the whole thing is currently implemented and does in fact work fine: This example has a list of required attributes with id (2, 646, 647) SELECT sbp.product_id, sbp.product_number, at.attribute_type_id, a.attribute_id, AttributeTypeNames.attribute_type_name, t.value AS attribute_value, l.locale_id, l.iso_code FROM dbo.SelectByProduct AS sbp INNER JOIN dbo.ProductAttribute AS pa ON sbp.product_id = pa.product_id INNER JOIN dbo.Attribute AS a ON pa.attribute_id = a.attribute_id INNER JOIN dbo.Translation AS t ON a.value_tid = t.translation_id INNER JOIN dbo.AttributeType AS at ON a.attribute_type_id = at.attribute_type_id INNER JOIN dbo.Locale AS l ON t.locale_id = l.locale_id INNER JOIN (SELECT dbo.Translation.value AS attribute_type_name, dbo.AttributeType.attribute_type_id, dbo.Translation.locale_id FROM dbo.Translation INNER JOIN dbo.AttributeType ON dbo.Translation.translation_id = dbo.AttributeType.name_tid) AS AttributeTypeNames ON at.attribute_type_id = AttributeTypeNames.attribute_type_id AND l.locale_id = AttributeTypeNames.locale_id AND EXISTS (SELECT * FROM [dbo].[ProductAttribute] AS attribute WHERE attribute.product_id = sbp.product_id AND attribute.attribute_id=2) AND EXISTS (SELECT * FROM [dbo].[ProductAttribute] AS attribute WHERE attribute.product_id = sbp.product_id AND attribute.attribute_id=646) AND EXISTS (SELECT * FROM [dbo].[ProductAttribute] AS attribute WHERE attribute.product_id = sbp.product_id AND attribute.attribute_id=647) ORDER BY sbp.product_id, a.attribute_id The problem with actually using this query is that I have to append one of the AND EXISTS() clauses dynamically to the query for every single required attribute. EXISTS winds up calling for a costly Table Scan every time. I have been trying to find alternate methods of doing this for a while and have come up empty handed, if anyone has any ideas I'd appreciate it. Also if I was less than clear feel free to ask for some clarification and I'll provide it.
VIEWS ON THIS POST

104

Posted on:

Tuesday 20th November 2012
View Replies!

SQL Query With SUM()

I have one single table with the follwing fields: ID, Status,Group, Owner,Code The code field contains (high,Low,Medium) Data I would like to retrieve the sum of the code field for a specific owner and group. Owner Code(total of highs, lows and Mediums) ------- ---------------- name 7 Here is my sql SELECT ID, OWNER,GROUP,CODE, STATUS FROM tablename GROUP BY OWNER, CODE HAVING sum( Status) 'Closed' AND OWNER='name' AND Group='grpname' I'm new to ORacle and I hope this make sens.
VIEWS ON THIS POST

40

Posted on:

Tuesday 20th November 2012
View Replies!

SQL Grouping problem!

Hi I am working on a simple database for a local skatepark, and there are two reports I am struggling with. The first is a report which details the parks members, it currently counts all the members and groups them by postcode the problem with this is that the entire postcode (eg EX12 5RT) is to specific they would prefer the groups to be based in the first three or four digits (eg EX2 or EX12) of the postcode any ideas on how to do this would be grately recieved. The second problem is that they would like a list of the top 10 members with the most visits, the amount of visits for each member is held in a table it is purely the problem of getting the top 10\t The reports are currently based on 2 queries and their running on Access 2002
VIEWS ON THIS POST

76

Posted on:

Tuesday 20th November 2012
View Replies!

Commands to reference another table

Hi all, I am new to this site and I hope anyone out there can help me. I was tasked to change the constraints of my existing table. Lets call it table1. This table has an attribute that needs to take the value of another attribute of another table ( let's call it tables2) and that attribute must satisfy a certain expression ( I suppose I can isolate it by using the select statement ). Anyone know how to get this done. Please advice.
VIEWS ON THIS POST

70

Posted on:

Tuesday 20th November 2012
View Replies!

DB Design Theory...

all, I got one for ya. Here's what I'm trying to do - I have a database of products. Some of these products are parents with no children. Others are children with a parent that I can make up. Here's the kicker, these products do not have very similar characteristics. For instance, some products have a ton of specification information, while others are fairly simply described. And almost all my products share SOME criteria, while there are many special groups that don't share any. Lets say here's my database: Table - Products Core Model Number | Product Type Widget1 | Ball Widget2 | Ball Widget3 | Frisbee Widget4 | Hat So I also have a Specifications table: Model Number | SpecType | SpecDetail Widget1 | Diameter | 12" Widget1 | Color | Blue Widget1 | Texture | Glossy Widget2 | Diameter | 14" Widget2 | Color | Red Widget2 | Texture | Rubber Widget2 | Bounce Rating | Excellent Widget3 | Diameter | 11" Widget3 | Color | Orange Widget3 | Material | Plastic Widget3 | Flyer Rating | 8 Widget4 | Bill Color | Green Widget4 | Hat Color | White Widget4 | Material | Fabric So now I've got this big group of data. I am trying to organize my data in such a way that I can, for instance, find all items in my database that have a diameter between 12 -> 14. By doing that search (SELECT DISTINCT ModelNo FROM SpecsTable WHERE SpecType = 'Diameter' AND ... [criteria] ...). But that simply returns: Widget1, Widget2. And that's okay, but then in order to pull all the information about those items out, I have to query the DB again using (SELECT * FROM SpecsTable WHERE ModelNo ... [go through array of previous results]... ORDER BY ModelNo)... That seems not only very ugly, but I can also see a great deal of problems arising in large scale (ie this products db will most likely carry 10,000 - 20,000 products when completed). Any suggestions Also, this system needs to be flexible to allow for FUTURE product additions with specifications & functionality that I don't even know of yet, so I need this to be extremely flexible (ie the purpose of what I've done for the 3 column product db).
VIEWS ON THIS POST

51

Posted on:

Tuesday 20th November 2012
View Replies!

Recordset bases in another to calculate (Sum)

In Access i can do this: ---------------------------- QUERY: c4 SELECT codMediador, refProduto, 0 as valor FROM Mediador, Produto ORDER BY codMediador, refProduto; UNION SELECT codMediador, refProduto, quantidade FROM MedProd; QUERY: D SELECT DISTINCTROW c4.codMediador, c4.refProduto, Sum(valor) AS Soma FROM c4 GROUP BY c4.codMediador, c4.refProduto; In ASP i have: ----------------- Set conn = Server.CreateObject("ADODB.Connection") conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.Mappath("med.mdb") Set rs = Server.CreateObject("ADODB.Recordset") ComandoSQL = "SELECT codMediador, refProduto, 0 FROM Mediador, Produto" ComandoSQL = ComandoSQL & " ORDER BY codMediador ASC, refProduto ASC" ComandoSQL = ComandoSQL & " UNION SELECT codMediador, refProduto, quantidade FROM MedProd" Set rs=conn.execute(ComandoSQL) And then How i write my second Recordset ("D") --------------------------------------------------
VIEWS ON THIS POST

107

Posted on:

Tuesday 20th November 2012
View Replies!

Help needed (Delete Query)

how can i delete all the records from a table that have relation with the current deleted record . table : Network fields type Net_id numeric Descn varchar parent_id numeric net_id and parent_id are self joined net_id descn parent_id 1 cccccccccc 0 2 cdfdfdgfdfgdf 1 3 fgdsgfgfdg 2 4 rrrrrrrrttrt 2 when i delete the record having net_id=1,i want to delete the records having net_id 2,3,4
VIEWS ON THIS POST

84

Posted on:

Tuesday 20th November 2012
View Replies!

MySQL query join problem

I have a problem writing the SQL manually. Im trying to get the same output in MySQL. that im getting here -> http://www.webdecor.dk/gfx/db_tbl.gif But the SQL you see here is generated by SQL Server and it doesnt work in MySQL. Can someone tell me how to do it, I have been trying and trying... I am quite desperate
VIEWS ON THIS POST

285

Posted on:

Tuesday 20th November 2012
View Replies!

Doing a Count between to Date fields

Originally posted by : Si (Simon@myangle.co.uk)I want to be able to perform a count of RS for a selected period between it on hire Date and Offhire Date.ie I have a list of Vehicles on hire and I want to know how many are on hire between X and Y
VIEWS ON THIS POST

66

Posted on:

Tuesday 20th November 2012
View Replies!

Date query using Calendar Control in ASP.NET

strSQL = "SELECT * " & _ " FROM Tracker2 " & _ " WHERE Start_date >= '" & BeginDate.SelectedDate & "' AND Ticket_type='" & ddlTicketType.SelectedValue() & "' ORDER By Start_date" This query works to retrieve all records 'from' the date I choose in my calendar control. I would like to display for ONLY the date I choose on my calendar control, but when I change my query to " WHERE Start_date = '" & BeginDate.SelectedDate & "' it doesn't diaplay anything\t Why
VIEWS ON THIS POST

82

Posted on:

Tuesday 20th November 2012
View Replies!

Autonumbered field/datatype in SQL

Originally posted by : Filip (Filip.wilsens@pandora.be)Is there a sort of autonumbered field in SQL like there is in Access Or a special query-statement from which I can see how many entries I have in my databasetableEx:Database: MyDataTable: MyTableHow do I make an ASP-page (statements) from where I can query this devices and display just one entry of the whole table (ex. "Name") which implifies URLs who link to the complete entry (Name, Mail, ICQ, ...)! Not the whole table, just one full entry from the table!I can't get this to work with the standard frontpage-statements!Thx in advance,Filip.
VIEWS ON THIS POST

67

Posted on:

Tuesday 20th November 2012
View Replies!

I am New to asp and I need Help

Originally posted by : Lalit Hira (prodes@vsnl.com)I had downloaded the asp code for Random Password function by Robert It works correctly Okay !But I want to send this code to my database which in turn could send it to the visitor registering can U pl help me do this my asp code for the remaining form has automatically been generated by front page so I cannot write in the same \t Lalit Hira
VIEWS ON THIS POST

102

Posted on:

Tuesday 20th November 2012
View Replies!

SQL Subqueries

Does anyone know of any good tutorial sites or have any sql subquery examples I've heard people refer to this type of a query as "in-line SQL". Is this correct I have a good understanding of basic SQL queries, I just want to expand my knowledge.
VIEWS ON THIS POST

116

Posted on:

Monday 26th November 2012
View Replies!

Sql Update not working-HELP!!

Can anyone tell me why this update doesn't work I think it's the punctuation but I don't know what to change. Updating an access database. fields except empID are character. Please help!! SQL = "UPDATE employees SET empname = '"& wrkname&"', &_ elogin = '"&wrklogin&"', emptype = '"&Request.Form("etype")&"', &_ empDefloc = '"&Request.Form("defsite")&"', &_ empemail = '"&Request.Form("eml")&"', &_ adminflag = '"&Request.Form("adminstat")&"', &_ epassword = '"&Request.Form("epw")&"' WHERE empId = '& Request.Form("ID")'" objCon.Execute SQL
VIEWS ON THIS POST

66

Posted on:

Monday 26th November 2012
View Replies!

DB Locking?

Originally posted by : Ramanathan (plrams@yahoo.com)If i have multiple users trying to insert into my MSAccess database at the same time, with the single asp code, and i have a table lock on, will the inserts be queued or will they return errors.
VIEWS ON THIS POST

52

Posted on:

Monday 26th November 2012
View Replies!

SQL 2000 Slowsdown when using 2 recordset on one connection object

Originally posted by : Thomas Rojsel (thomas@netit.se)SQL 2000 Slowsdown when using 2 recordset on one connection object. Was running great when using SQL 7.0, other wise like the current.------SQL server waits 4 seconds before returnung records after every request on the asp-page.Any ideasRunning:SQL 2000, NT 2000, MDAC 2.6
VIEWS ON THIS POST

89

Posted on:

Monday 26th November 2012
View Replies!

Update TABLE from a Related Table with Multiple Entries (only wanting the latest val)

The example below is not real, but if I can solve this, I know I can solve the bigger problem I have! Using MYSQL, I have two tables. Farm and Animals. Animals can have several items connected to farm with the "farmid" serving as a related key. FARM id farmname latestanimal ANIMALS animalid farmid animalname dateadded What I need to do is run an update query, that automatically populates "farm.latestestanimal" with the "animalname" from the latest entry of Animals that is connected to the farm. I know this sample below is wrong, but it might show what I am trying to do: "UPDATE farm SET farm.latestanimal = animals.animalname WHERE animal.farmid = farm.id ORDER BY animal.dateadded DESC LIMIT 1" Can someone point me in the right direction to making this work
VIEWS ON THIS POST

457

Posted on:

Monday 31st December 2012
View Replies!