Using Stored procedure to link tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Stored procedure to link tables

Author

Topic

eyestech
Starting Member

4 Posts

Posted - 2009-05-17 : 08:01:07

[Font=Book Antiqua][size=3]
First of all let me tell you about my project senario

I'm making a survey programs with ASP.NET 3.5 and Sql Server 2008 ,

I have three tabels which is :

User tabel : has (UserID , UserName , ...)

Survey Tabel : has ( SurveyID (Primarykey), USserId(foreginkey) )

Qusetion tabel : has ( QuestionID(PrimaryKey) ,Question,SurveyID(forgienkey) , AnswerTypeId(foreginkey).

what am trying to do : when the user insert his survey i need to let multiple Questions assign to the same SurveyID everytime the user make a new survey the questions should assign to a new SurveyId andalso how i can link that survey with the userID ..

the stored procdure that am using is :

ALTER PROCEDURE [dbo].[SurveyIDTBLCONN]

AS
SET NOCOUNT ON

Declare @SurveyID Int
insert into SURVEY (UserID) values (@UserId)
Set @SurveyID = Scope_Identity()
SELECT @SurveyID

End

but it's told me that i need to define a scalar variables !!? any body could plz help to solve this situation and i'll be greatful

the code that am using to insert

cs.conn.Open()

Dim comm As New SqlCommand
comm.CommandType = CommandType.StoredProcedure
comm.CommandText = "SurveyIDTBLCONN"

Try

Select Case ddlquestionnaire.SelectedIndex
Case Is = 1

Dim cmd As New SqlCommand("insert into Questions(Question,SID,AnswerTypeID)values('" & txtq.Text & "','" & "@SurveyID" & "','" & 1 & "')", cs.conn)

cmd.ExecuteNonQuery()

MsgBox("??? ????? ??????", MsgBoxStyle.Information, " ???????? ??? ????")

Case Is = 2

Dim cmd As New SqlCommand("insert into Questions(Question,AnswerTypeID)values('" & txtq.Text & "','" & "@SurveyID" & "','" & 2 & "')", cs.conn)

cmd.ExecuteNonQuery()

MsgBox("??? ????? ??????", MsgBoxStyle.Information, " ???????? ??? ????")

Case Is = 3
Dim cmd As New SqlCommand("insert into Questions(Question,AnswerTypeID)values('" & txtq.Text & "','" & "@SurveyID" & "','" & 3 & "')", cs.conn)

cmd.ExecuteNonQuery()

MsgBox("??? ????? ??????", MsgBoxStyle.Information, " ???????? ??? ????")

Case Is = 4

Dim cmd As New SqlCommand("insert into Questions(Question,AnswerTypeID)values('" & txtq.Text & "','" & "@SurveyID" & "','" & 4 & "')", cs.conn)

cmd.ExecuteNonQuery()

MsgBox("??? ????? ??????", MsgBoxStyle.Information, " ???????? ??? ????")

End Select

[/Font][/Size]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 09:01:25

the procedure should be like this


ALTER PROCEDURE [dbo].[SurveyIDTBLCONN]

@UserId int	
AS
	 SET NOCOUNT ON
	 
Declare @SurveyID Int
insert into SURVEY (UserID) values  (@UserId)
Set @SurveyID = Scope_Identity()
SELECT @SurveyID

	End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 09:02:45

for the second part. whats the rule that determines what all questions need to be linked to surveyid? or do you want to link all questions to all surveyid?

eyestech
Starting Member

4 Posts

Posted - 2009-05-17 : 16:31:23

visakh16 first i wanna thank you for your help , what i need is a way so when the user make his survey and insert his questions all theses questions should be under one surveyId which will be later on send by email so the people can answer the survey , and the user can see the result for his survey , and if he want to make another survey all the new qusetion that he insert it will be under new surveyId , also if another user insert his qusetions it should be under another surveyId , the program is multiple user , so my proble is how to connect the user with his survey and the survey with questions ..

in simple word .. i need to let each survey belonge to thier user , by userId , by they way the user will be allowed to insert one question then the page will be refreshed to allow hi to insert another question

sorry am not good in explaining but i hope that you got my idea

thaknx agian and i appreciate your help

Regard

eyestech
Starting Member

4 Posts

Posted - 2009-05-18 : 12:30:33

also could you please tell me how i can call the prev stored procedure in my code?

cs.conn.Open()

Dim comm As New SqlCommand
comm.CommandType = CommandType.StoredProcedure
comm.CommandText = "SurveyIDTBLCONN" 'Name of Stored Procedure

Try
If (txtq.Text <> "" AndAlso ddlquestionnaire.SelectedIndex <> "0") Then

Dim cmd As New SqlCommand("insert into Questions(Question,SID,AnswerTypeID)values(@Question,@SurveyID,@AnswerTypeID)", cs.conn)

cmd.Parameters.AddWithValue("@Question", txtq.Text.Trim)
cmd.Parameters.AddWithValue("@AnswerTypeID", ddlquestionnaire.SelectedIndex)

cmd.ExecuteNonQuery()

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 10:15:43

quote:
Originally posted by eyestech

visakh16 first i wanna thank you for your help , what i need is a way so when the user make his survey and insert his questions all theses questions should be under one surveyId which will be later on send by email so the people can answer the survey , and the user can see the result for his survey , and if he want to make another survey all the new qusetion that he insert it will be under new surveyId , also if another user insert his qusetions it should be under another surveyId , the program is multiple user , so my proble is how to connect the user with his survey and the survey with questions ..

in simple word .. i need to let each survey belonge to thier user , by userId , by they way the user will be allowed to insert one question then the page will be refreshed to allow hi to insert another question

sorry am not good in explaining but i hope that you got my idea

thaknx agian and i appreciate your help

Regard

something like this. i dont know from which table you get question and answertypeid, so fill that part yurself


ALTER PROCEDURE [dbo].[SurveyIDTBLCONN]

@UserId int	
AS
	 SET NOCOUNT ON
	 
Declare @SurveyID Int
insert into SURVEY (UserID) values  (@UserId)
Set @SurveyID = Scope_Identity()

INSERT INTO QUestions (Question,SurveyID, AnswerTypeId)
SELECT Question,@SurveyID,AnswerTypeId
FROM...
..


	End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 10:18:44

quote:
Originally posted by eyestech

also could you please tell me how i can call the prev stored procedure in my code?

cs.conn.Open()

Dim comm As New SqlCommand
comm.CommandType = CommandType.StoredProcedure
comm.CommandText = "SurveyIDTBLCONN" 'Name of Stored Procedure

Try
If (txtq.Text <> "" AndAlso ddlquestionnaire.SelectedIndex <> "0") Then

Dim cmd As New SqlCommand("insert into Questions(Question,SID,AnswerTypeID)values(@Question,@SurveyID,@AnswerTypeID)", cs.conn)

cmd.Parameters.AddWithValue("@Question", txtq.Text.Trim)
cmd.Parameters.AddWithValue("@AnswerTypeID", ddlquestionnaire.SelectedIndex)

cmd.ExecuteNonQuery()

see

http://support.microsoft.com/kb/146651

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources