need an adivse

hey gep13

Quote:

---

Can I ask why you are using a case statement in the above code?

---

am using case statment cuz i have different answertype everyone of it has there # , if you look at the end of each statment u'll find that all three are not the same ... but thanx for the hint and i'll try to do it by parameters to avoid Sql injection

thanx agian

dear mend ,

thank for helping me

i'm little confused .. and i didnt get what you meant exactly .

did you meant the SID in question should be autoincrement?

I'm choosing the answertype from dropdown list and type the question then insert it in the database .. but at everytime i can insert one question then return back to insert the second one ..etc , after that i should generate a page that has the survey depend on what was insert

am still confused about how to link the SID in Q table with SID in survey table , and also how i can let multiple qusetion has one SID ..

should i use innerjoin?

Quote:

---

Originally Posted by alqous

hey gep13



am using case statment cuz i have different answertype everyone of it has there # , if you look at the end of each statment u'll find that all three are not the same ... but thanx for the hint and i'll try to do it by parameters to avoid Sql injection

thanx agian

---

Hey,

But this is what I am talking about. Your answertype is exactly what the value of ddlquestionnaire.SelectedIndex is. i.e. within your case statement you do something like this:

If ddlquestionnaire.SelectedIndex is 1, you put 1 in the SQL Query, if it is 2 you put a 2 in the query etc so in the same way as you add txtq.Text into the query, you can simply add ddlquestionnaire.SelectedIndex as well, the case statement is completely unnecessary.

So your code would become:

Code:

---

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

---

Where
@QuestionText is a parameter with a value of txtq.Text
@SurveryID is a parameter with a value of (not sure where this comes from in your code)
@AnswerTypeID is a parameter with a value of ddlquestionnaire.SelectedIndex

Gary

First, do an INSERT into the Survey table. Then do a select on SCOPE_IDENTITY(), this will return to you the ID of the new survey row. (This has to be done in a stored procedure).

Then, take that new Survey ID and everytime you INSERT into the questions table, include the SID as one of the values, which is this statement


insert into Questions(Question,AnswerTypeID,SID)values('" & txtq.Text & "','" & "@SurveyID" & "','" & 2 & ",intSid')

Is that making sense now?

the stored procedure :

Code:

---


       
AS
        SET NOCOUNT ON
       
        insert into SURVEY (UserID) values  ('1')
       
        Declare @SurveyID Int
        Begin
       
        Set @SurveyID = Scope_Identity()
       
        Insert into Questions (SID) values (@SurveyID)
       
        End

---

is it right ? i feel there is some thing wrong with it , am trying to figure out how to insert the userid in the surveytable withing the stored procedure.

You can't insert into Questions if you don't have any questions. From this stored proc, you must return @SurveyId and hold on to that value in your code. When it's time to insert a new question, that's when you can use @SurveyId in the next stored procedure.

Code:

---

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

---

ok , the stored procedure is now look like :

Code:

---

@UserId int
       
AS
       
        SET NOCOUNT ON
       
Declare @SurveyID Int

insert into SURVEY (UserID) values  (@UserId)

Set @SurveyID = Scope_Identity()

SELECT @SurveyID

---

and my code is became :

Code:

---

cs.conn.Open()


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

        Try
            If (txtq.Text <> "" AndAlso ddlquestionnaire.SelectedIndex <> "0") Then
                ' Insert Query using stored procedure

                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.Parameters.AddWithValue("@SurveyID", comm.ExecuteScalar)
                cmd.ExecuteNonQuery()
          Else
   
            End If


        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        cs.conn.Close()
        cleartextboxz()


        Response.Redirect("addsurvey.aspx")

---

it's not working i dont now why ?

and also could you tell me what is the way to let the survey belong to their users , so when the user login his CP page he can veiw his survey??

thanx for everything ..

TC