Cleaing up SQL SELECT function

	Home Register Search Help Login

Sponsors

Hosting from $3.99 per month!

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions. dd

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Cleaing up SQL SELECT function

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> Cleaing up SQL SELECT function

Page: [1]

adam2804

Posts: 34
Joined: 6/6/2006
Status: offline

Cleaing up SQL SELECT function - 6/28/2006 11:29:10

Hello guys,

wondered if you could help me with a little query. I have a hell of a lot of pages that use coded SQL statements on the page, and was looking for ideas on how I can clean these up so when a modification needs to be made we can just access through Enterprise Manager and change a view or stored procedure. Let me give you an example:

	set rs = Server.CreateObject("ADODB.recordset")
	strSQL = "SELECT Forenames, Surname, w.PayrollNumber, t.Description AS WorkTypeDesc, w.WorkType, w.WorkDate, RTRIM(w.JobNumber) AS JobNumber"
 	strSQL = strSQL & ", w.BoxNumber, CAST(w.Hours AS DECIMAL(7,1)) AS Hours, dbo.FN_ConvNullToNumber(w.Quantity, 1) AS Quantity"
 	strSQL = strSQL & ", RTRIM(CAST((CAST(w.Hours AS INT)) AS CHAR)) + '.' + RIGHT('00' + RTRIM(CAST((CAST( ( (w.Hours - CAST(w.Hours AS INT)) * 60) AS INT)) AS CHAR)), 2) AS FormatHours "	
	strSQL = strSQL & ", CONVERT(VARCHAR, w.StartTime, 13) AS StartTime"
	strSQL = strSQL & " FROM WorkDetails w INNER JOIN"
	strSQL = strSQL & " WorkType t ON w.WorkType=t.WorkType INNER JOIN"
	strSQL = strSQL & " Employees e on w.PayrollNumber=e.PayrollNumber INNER JOIN"
	strSQL = strSQL & " Jobs j ON w.JobNumber=j.JobNumber"
	strSQL = strSQL & " WHERE w.SiteID LIKE '" & Request("SiteID") & "'"

I initially thought of using a 'View' but couldn't find any information on how I would connect to this through Front Page.

Any ideas folks?

Thanks,

Adam


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Cleaing up SQL SELECT function - 6/28/2006 11:33:41 quote: I initially thought of using a 'View' but couldn't find any information on how I would connect to this through Front Page. Views are accesable thru the DRW just like tables. Have you tried to use them at all? Create a simple one in EM and then try to select it in a new DRW. That help any? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to adam2804)


adam2804 Posts: 34 Joined: 6/6/2006 Status: offline	RE: Cleaing up SQL SELECT function - 6/28/2006 12:16:10 Thanks for your quick reply, and yes it did help to have a look at using the DRW to display views. One question...You see in the code that I have included in the initial message the SQL uses a Request("SiteID") in the query, now when I'm trying to save the view Enterprise manager is telling me that it doesn't recognise that function, and therefore won't save, is there a correct syntax that would work? Thanks (in reply to rdouglass)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Cleaing up SQL SELECT function - 6/28/2006 12:57:39 quote: SQL uses a Request("SiteID") in the query, What I'd do is to save the query as a View without the WHERE clause so that essentially returns all those records. Then in the DRW, you'd use the criteria there so the SQL (in a DRW) would look something like: fp_sQry="SELECT * FROM myQry(VIEW) WHERE w.SiteID = '::SiteID::'" or something to that effect. Does that make sense? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to adam2804)


adam2804 Posts: 34 Joined: 6/6/2006 Status: offline	RE: Cleaing up SQL SELECT function - 6/29/2006 4:19:21 I like the idea, it seems like I could use this theory in a number of my pages. However I can't put a 'WHERE' query in, as I get an error saying 'w' is not a recognized table prefix or alias, I tried WorkDetails (which is the real table name) but that didn't work. Any ideas or should I just continue to work as the pages are? Thanks, Adam (in reply to rdouglass)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Cleaing up SQL SELECT function - 6/29/2006 9:27:30 quote: However I can't put a 'WHERE' query in Where exactly are you trying to put this query? You should not be using it in EM but in the DRW or your ASP. See, what I'm doing is building the query without the 'WHERE' clause in EM. Then the DRW recognizes the view as if it were a table. Then (in the DRW) you would put the criteria and that would put the 'WHERE' clause back in there. Is that the way you're attempting it? What you want to do can be done and is done quite frequently so don't give up hope. _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to adam2804)


Spooky Posts: 26617 Joined: 11/11/1998 From: Middle Earth Status: offline	RE: Cleaing up SQL SELECT function - 6/29/2006 15:50:18 Is w.siteID numeric? if so, you dont use the apostrophe's _____________________________ If you arent part of the solution, then there is good money to be made prolonging the problem ��k� Database / DRW help Check out the Tutorials for Frontpage Databases Forum FAQ' s Developers Edition Spooky Login User Registration and Management tool Personalized Frontpage Hosting (in reply to rdouglass)