Immediate help!!! User Responses and Database Table updation

Posted by Sina under Sql Server on 5/31/2012 | Points: 10 | Views : 1799 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi..i have a doubt..I have a designed a page which is a kind of blog..i.e there are few topics and users can respond to the topics.My doubt is there a way to store user responses into database to retrieve later and also how can i store the responses for a particular topic corresponding to the title?Since there are other topics too and also how can we define the field to store responses since it is not predictable as to how many users will respond....Hope i am clear...Please help me..Thanks in advance..

@Sina

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Ksuresh on: 5/31/2012 [Member] Starter | Points: 25

0	Hi By using AutoId of title you solve above problem and for different topics give Topic Id so that for which topic response you can differentiate , Hope this helps you Regards Suresh Sina, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Pandians on: 5/31/2012 [Member] [MVP] Silver | Points: 25

0	Hi Please follow the sample Tables/Data 1. Topic Related Information IF OBJECT_ID('Tbl_Topics') IS NULL Create Table Tbl_Topics ( TopicID INT IDENTITY(1,1) PRIMARY KEY, TopicName VARCHAR(100) ) go 2. Questions on Topics/Multiple Topics IF OBJECT_ID('Tbl_Questions') IS NULL Create Table Tbl_Questions ( QuestionID INT IDENTITY(1,1) PRIMARY KEY, Question VARCHAR(500), TopicID INT FOREIGN KEY REFERENCES Tbl_Topics(TopicID) NOT NULL, PostedOn DATETIME, PostedBy INT ) go 3. Responses on various Questions IF OBJECT_ID('Tbl_Responses') IS NULL Create Table Tbl_Responses ( ResponsesID INT IDENTITY(1,1) PRIMARY KEY, DetailResponse VARCHAR(MAX), QuestionID INT FOREIGN KEY REFERENCES Tbl_Questions(QuestionID) NOT NULL, ResponseDate DATETIME, ResponseBy INT ) go 4. Sample Data for Topics, Questions and Responses INSERT Tbl_Topics(TopicName) VALUES('SQL Server') INSERT Tbl_Topics(TopicName) VALUES('ASP.Net') Go INSERT Tbl_Questions(Question,TopicID,PostedOn,PostedBy) VALUES('Where we use Collation ?',1,GETDATE(),1001) INSERT Tbl_Questions(Question,TopicID,PostedOn,PostedBy) VALUES('Can we force Referential Integrity on Temp tables ?',1,GETDATE(),1002) INSERT Tbl_Questions(Question,TopicID,PostedOn,PostedBy) VALUES('Why should we use "App.Copnfig" ?',2,GETDATE(),1001) Go INSERT Tbl_Responses(DetailResponse,QuestionID,ResponseDate,ResponseBy) VALUES('To force Sensitivity on Data(i.e: Case, Accent, width)',1,GETDATE(),1010) Go 5. Query to fetch What are all the Questions have got responses on which Topics SELECT T.TopicName,Q.Question, Q.PostedBy, Q.PostedOn,R.DetailResponse, R.ResponseDate, R.ResponsesID FROM Tbl_Responses R JOIN Tbl_Questions Q ON (R.QuestionID = Q.QuestionID) JOIN Tbl_Topics T ON (T.TopicID = Q.TopicID) ORDER BY T.TopicName, Q.Question, R.ResponseDate DESC Go Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Sina, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

Please follow the sample Tables/Data

1. Topic Related Information

IF OBJECT_ID('Tbl_Topics') IS NULL

Create Table Tbl_Topics

(

TopicID	INT IDENTITY(1,1) PRIMARY KEY,

TopicName	VARCHAR(100)

)

go

2. Questions on Topics/Multiple Topics

IF OBJECT_ID('Tbl_Questions') IS NULL

Create Table Tbl_Questions

(

QuestionID	INT IDENTITY(1,1) PRIMARY KEY,

Question	VARCHAR(500),

TopicID		INT FOREIGN KEY REFERENCES Tbl_Topics(TopicID) NOT NULL,

PostedOn	DATETIME,

PostedBy	INT

)

go

3. Responses on various Questions

IF OBJECT_ID('Tbl_Responses') IS NULL

Create Table Tbl_Responses

(

ResponsesID	INT IDENTITY(1,1) PRIMARY KEY,

DetailResponse	VARCHAR(MAX),

QuestionID		INT FOREIGN KEY REFERENCES Tbl_Questions(QuestionID) NOT NULL,

ResponseDate	DATETIME,

ResponseBy		INT

)

go

4. Sample Data for Topics, Questions and Responses

INSERT Tbl_Topics(TopicName) VALUES('SQL Server')

INSERT Tbl_Topics(TopicName) VALUES('ASP.Net')

Go



INSERT Tbl_Questions(Question,TopicID,PostedOn,PostedBy) VALUES('Where we use Collation ?',1,GETDATE(),1001)

INSERT Tbl_Questions(Question,TopicID,PostedOn,PostedBy) VALUES('Can we force Referential Integrity on Temp tables ?',1,GETDATE(),1002)

INSERT Tbl_Questions(Question,TopicID,PostedOn,PostedBy) VALUES('Why should we use "App.Copnfig" ?',2,GETDATE(),1001)

Go



INSERT Tbl_Responses(DetailResponse,QuestionID,ResponseDate,ResponseBy) VALUES('To force Sensitivity on Data(i.e: Case, Accent, width)',1,GETDATE(),1010)

Go

5. Query to fetch What are all the Questions have got responses on which Topics

SELECT T.TopicName,Q.Question, Q.PostedBy, Q.PostedOn,R.DetailResponse, R.ResponseDate, R.ResponsesID

FROM Tbl_Responses R JOIN Tbl_Questions Q

ON (R.QuestionID = Q.QuestionID)

JOIN Tbl_Topics T 

ON (T.TopicID = Q.TopicID)

ORDER BY  T.TopicName, Q.Question, R.ResponseDate DESC

Go

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sina, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts