Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 24th, 2006, 09:05 AM
Authorized User
 
Join Date: Aug 2006
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default sql statement for asp.net page

Good morning,
I have a database (Tests) that has the following fields:
Doc_ID (Text)
Doc_Title (Text)
Test1 (yes/no)
Test2 (yes/no)
Test3 (yes/no)
Test4 (yes/no)
Test5 (yes/no)

Here is my dilema, I do not want to put the same Doc_ID (eg. doc_123) for 3 different tests to be run. (eg. Doc_123 needs Test 1, Test 3, and Test 5 to be run) Is there a SQL statement or maybe a script I can write to call to the Database for that one record to display the results 3 times on my webpage? (Instead of me creating a new Doc_Id for the same record 3 times in the Database) Does this make any sense?
thank you for your help.

 
Old August 26th, 2006, 11:44 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

Based on your datatypes I would have to say that you are using ACCESS rather than SQL Server.

Next, if I understand your schema correctly you are violating 1st Normal Form and that is causing your difficulties.

I would look at normalizing your database design.

In the meantime this might work
SELECT Doc_ID, Doc_Title, 'Test1'
FROM Docs -- or whatever your table is named
WHERE DOC_Id = 'Doc_123'
AND Test1 = 'y' -- or true or whatever is in access
UNION ALL
SELECT Doc_ID, Doc_Title, 'Test2'
FROM Docs -- or whatever your table is named
WHERE DOC_Id = 'Doc_123'
AND Test2 = 'y'
UNION ALL
SELECT Doc_ID, Doc_Title, 'Test3'
FROM Docs -- or whatever your table is named
WHERE DOC_Id = 'Doc_123'
AND Test3 = 'y'
UNION ALL
SELECT Doc_ID, Doc_Title, 'Test4'
FROM Docs -- or whatever your table is named
WHERE DOC_Id = 'Doc_123'
AND Test4 = 'y'
UNION ALL
SELECT Doc_ID, Doc_Title, 'Test5'
FROM Docs -- or whatever your table is named
WHERE DOC_Id = 'Doc_123'
AND Test5 = 'y'



IF You normalize your design then you could do something like this

SELECT d.Doc_ID, DOc_Title, TT.TestTypeDesc
FROM Docs D
JOIN DocTests DT
  ON D.Doc_ID = DT.Doc_ID
JOIN TestTypes TT
  ON DT.TestTypeID = TT.TestTypeID
WHERE D.Doc_ID = 'Doc_123' -- although you will be better off if you switch that to an integer identity (in access autonumber)




David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
 
Old August 28th, 2006, 01:05 PM
Authorized User
 
Join Date: Aug 2006
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you. You are correct, presently the database i am using is Access 2003, but i am going to migrate to sql server 2005 this week. This should help me out alot







Similar Threads
Thread Thread Starter Forum Replies Last Post
Help writing SQL Statement/ .net code for function carswelljr Classic ASP Databases 2 August 24th, 2006 03:31 PM
login page in asp.net using sql server 2000 shahzeb ADO.NET 4 December 1st, 2004 06:27 AM
sql server and asp if statement shoakat Classic ASP Databases 2 November 2nd, 2004 07:38 PM
DELETE statement with ASP.NET kaz VS.NET 2002/2003 1 December 9th, 2003 09:35 PM
ASP sql select statement scorpio Classic ASP Databases 5 July 30th, 2003 06:48 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.