question in statment with asp.net
question in statment with asp.net
question in statment with asp.net
question in statment with asp.net
question in statment with asp.net
question in statment with asp.net question in statment with asp.net question in statment with asp.net question in statment with asp.net question in statment with asp.net question in statment with asp.net question in statment with asp.net question in statment with asp.net
question in statment with asp.net question in statment with asp.net
question in statment with asp.net
Go Back  Xtreme Visual Basic Talk > > > question in statment with asp.net


Reply
 
Thread Tools Display Modes
  #1  
Old 04-24-2009, 03:57 PM
eyestech eyestech is offline
Newcomer
 
Join Date: Apr 2009
Posts: 6
Default question in statment with asp.net


Dear all ,

the senario is : i have asp.net project am using sql 2008 and asp.net 3.5

i have tow table first one have three col. :

Question ID
Question
SurveyID

and the second one have two col.

SurveyID
UserId

my problem is the user could enter multiple question which will has multiple QuestionID i want the question to be under one suerveyID and alson when the surveyid increment in the question table should be at the same time increment in the Suervey table ... so could you plz give a hint for the insert code that will be fit for this problem and i';; appreciate that .. thanx
Reply With Quote
  #2  
Old 04-24-2009, 07:08 PM
Targe Targe is offline
Contributor
 
Join Date: Nov 2006
Posts: 615
Default

Firstly, I'm not 100% I understand your question but I'll take a guess at what you want. Secondly, you need to make sure these ID fields are auto-incrementing identity primary key fields. Thirdly, this needs to be set in a stored procedure:
Code:
INSERT INTO tblSurvey(UserID) VALUES('user');

DECLARE @SurveyID int
SET @SurveyID = SCOPE_IDENTITY();

INSERT INTO tblQuestion(Question, SurveyID) VALUES('question', @SurveyID);
Reply With Quote
  #3  
Old 04-26-2009, 10:40 AM
eyestech eyestech is offline
Newcomer
 
Join Date: Apr 2009
Posts: 6
Default

First of all i would thank you for trying to help me ..

let me tell about the tables :

Question ID is identity and autoincrement and also it's primarykey
SurveyID in the Question table is forigenkey but it's not autoincrement cuz i need to let the user to insert multiple Questions under one survey id

SurveyID in the second table is Primary key and it's autoincrement ..


so could you plz tell me how i can do it by code
Reply With Quote
  #4  
Old 04-26-2009, 08:34 PM
Targe Targe is offline
Contributor
 
Join Date: Nov 2006
Posts: 615
Default

I gave you the SQL code above, which is what you wanted. What more are you asking for?
Reply With Quote
  #5  
Old 04-27-2009, 02:57 PM
eyestech eyestech is offline
Newcomer
 
Join Date: Apr 2009
Posts: 6
Default

so it's stored procedure i think you worte it in C# ?

could you plz explain to me what is SCOPE_IDENTITY()?

also in INSERT INTO tblSurvey(UserID) VALUES('user'); : user what do u meant by it ?

thanx for everything

Last edited by eyestech; 04-27-2009 at 03:46 PM.
Reply With Quote
  #6  
Old 04-27-2009, 07:11 PM
Targe Targe is offline
Contributor
 
Join Date: Nov 2006
Posts: 615
Default

It's in SQL, not C# or VB. That's a stored procedure to use on your database. You'll use that to get your data to ASP. 'user' is whatever UserID's value is supposed to be. I wrote it as a string but you can put an int, bit, etc. in that spot.

As for SCOPE_INDENTITY(), when you do the first insert, it pulls back the primary key so you can use that in your next insert statement. It's how you can relate the two tables. Since (I've assumed this the entire thread) you need the primary key from the first insert to go in your foreign key on the other insert, that is how you accomplish it.
Reply With Quote
  #7  
Old 05-04-2009, 07:26 PM
eyestech eyestech is offline
Newcomer
 
Join Date: Apr 2009
Posts: 6
Default

thanx for your help .

just one more question let us assume that the tblsurvey has only one col. which is SID can i insert in it ? if yes how would be the insert statment ? (INSERT INTO tblSurvey(SID) VALUES('SID'))?

also could you plz tell me what do you think is the best idea to link the survey with the user?

Last edited by eyestech; 05-04-2009 at 07:32 PM.
Reply With Quote
  #8  
Old 05-04-2009, 09:01 PM
Targe Targe is offline
Contributor
 
Join Date: Nov 2006
Posts: 615
Default

SID would be your column name and the 'SID' would either be a number or string, don't put SID literally there...or that is what you'll get.

Also, I already linked the two tables (survey and user) based on the ID field. I really think we're having communication problems here. Either I can't figure out what you're wanting or you're not understanding what I'm giving you...
Reply With Quote
  #9  
Old 05-07-2009, 12:55 PM
eyestech eyestech is offline
Newcomer
 
Join Date: Apr 2009
Posts: 6
Default

you said that youm linked tables ( survey and users ) but what i need to do is to link tables ( survey and questions) also
Reply With Quote
  #10  
Old 05-07-2009, 08:50 PM
Targe Targe is offline
Contributor
 
Join Date: Nov 2006
Posts: 615
Default

No, I linked the survey and question tables. Look at the table names I used.
Reply With Quote
  #11  
Old 05-18-2009, 03:31 PM
eyestech eyestech is offline
Newcomer
 
Join Date: Apr 2009
Posts: 6
Default

sorry , it's my mistake i dont know how i was thinking ..


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 there is something worng with calling the procedure ??

i need to let the userid in the surveytable is the Id for user how login and making the survey ..

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
Reply With Quote
  #12  
Old 05-18-2009, 07:36 PM
Targe Targe is offline
Contributor
 
Join Date: Nov 2006
Posts: 615
Default

We need to clean this up...a lot (sorry if anything fails, this is off the top of my head):
Code:
If (txtq.Text <> "" AndAlso ddlquestionnaire.SelectedIndex <> "0") Then   
   Try
      ' Insert Query using stored procedure
      Using cmd As New SqlCommand("STORED PROCEDURE NAME HERE", cs.conn)
         cmd.CommandType = CommandType.StoredProcedure

         cmd.Parameters.AddWithValue("@Question", txtq.Text.Trim)
         cmd.Parameters.AddWithValue("@AnswerTypeID", ddlquestionnaire.SelectedIndex.ToString)
         cmd.Parameters.AddWithValue("@SurveyID", "1")   'Just set it to 1, not sure where you want to get this value from

         cs.conn.Open()
         cmd.ExecuteNonQuery()
      End Using

      cleartextboxz()
      Response.Redirect("~/addsurvey.aspx", False)
   Catch ex As Exception
      MsgBox(ex.ToString)
   Finally
      cs.conn.Close()
   End Try
End If
Reply With Quote
  #13  
Old 05-18-2009, 07:41 PM
Targe Targe is offline
Contributor
 
Join Date: Nov 2006
Posts: 615
Default

As for your questions, when the user logs in, you need to associate his login with a key (number). This key will be his ID which links himself to those tables. To do this, when they log in, simply do a database query to pull back that value from the database, then it's just:
Code:
Session("ID") = "THE VALUE"
Then, throughout all the stored procedures where you need that ID (like getting all of his surveys/questions for instance) you simply pass that in:
Code:
..."SELECT...WHERE SurveryID = @SurveyID"...
...
cmd.Parameters.AddWithValue("@SurveyID", Session("ID"))
...
The ... just means more code goes there. I just gave you the skeleton. Hopefully this all makes sense. I'm not so good at explaining things.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
question in statment with asp.net
question in statment with asp.net
question in statment with asp.net question in statment with asp.net
question in statment with asp.net
question in statment with asp.net
question in statment with asp.net question in statment with asp.net question in statment with asp.net question in statment with asp.net question in statment with asp.net question in statment with asp.net question in statment with asp.net
question in statment with asp.net
question in statment with asp.net
 
question in statment with asp.net
question in statment with asp.net
 
-->