I am working on a Tourism website in which i have to make a question-answer forum,
i know the fundamentals but i do not know the sequence or you can say i am confused in the thing,
here is what i am doing,
i am registering the users in a table and then redirecting them to a forum page in which they can ask their question,the question they are asking is submitted in a question table with unique question_id that part is going right,but my problem is how can i take
replies for particular answers and post them in a answer table(Question_id is the unique field in both the tables),do i have to take two gridviews for Replies page, one to show the answer and one to show the replies,
you should have one single table to hold all repliest. and this table should have the question id as one part of the key and the reply id as another part so each row in the answer table is a logical representation of an reply. You can have one more filed
in this table to mark a reply as an answer. If you need to maintain if a reply was applied to another reply from some one else you can have one more field to relate a reply to another.
hope this helps. with this structure you can have just one gridview that displays all replies to a question with some sort of indicator that marks a specific post as an answer. like a green background or something of that sort.
i have a web form that takes question and stores it in Question Table and if anyone give replies to that question it is stored in Answer table(Question_id is common in both the tables),so when i want to show the Question the coprresponding replies are also
seen.
PROCEDURE [dbo].[InfoInsert]
-- Add the parameters for the stored procedure here
@Question varchar(500),
@Answer varchar(500)
AS
BEGIN
INSERT INTO [Questions] (Question)
VALUES (@Question)
select @QuestionID = @@IDENTITY
INSERT INTO [Answers] (QuestionID,Answer) (@QuestionID,@Answer );
END
using (SqlConnection con = new SqlConnection(dc.Con)) {
using (SqlCommand cmd = new SqlCommand("[dbo].[InfoInsert]", con)) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Question", SqlDbType.VarChar).Value = txtQuestion.Text;
cmd.Parameters.Add("@Answer", SqlDbType.VarChar).Value = txtAnswer.Text;
con.Open();
cmd.ExecuteNonQuery();
}
sameer.khanjit@gmail.com
View Blog Click "Mark as Answer" on the post that helped you.
Marked as answer by Angie xu - MSFT on Dec 28, 2012 06:44 AM
(1)how can i do this it is not clicking to me,suppose i am able to show the question with some replies and if any other person also wants to reply and clicked on Reply how can i disable all the posts below it(Like in this Forum)
(2)what problem i am facing is that wheni try to display question with replies by joining the two tables(Question and Reply),some field are repeated in the Gridview,how can i achieve duplication in Gridview,
joining the two tables is the problem that you have. you should not join. you should do a union. I understand that you have the user id associated with a question and a reply. so one of your field in the result set will contain user detail. And i believe
both the question and reply are text fields so you should end up joining the question and answer table result set with jus the user table. I will be really glad if you send me the table structures and i can help you with some sample query...
Gaurav Balya...
Member
35 Points
69 Posts
How to insert data in two tables at the same time
Dec 21, 2012 07:17 AM|LINK
Dear,
I am working on a Tourism website in which i have to make a question-answer forum,
i know the fundamentals but i do not know the sequence or you can say i am confused in the thing,
here is what i am doing,
i am registering the users in a table and then redirecting them to a forum page in which they can ask their question,the question they are asking is submitted in a question table with unique question_id that part is going right,but my problem is how can i take replies for particular answers and post them in a answer table(Question_id is the unique field in both the tables),do i have to take two gridviews for Replies page, one to show the answer and one to show the replies,
any help or link is appreciated, thanks
sameer_khanj...
Contributor
7046 Points
1376 Posts
Re: How to insert data in two tables at the same time
Dec 21, 2012 07:35 AM|LINK
string stmt = "INSERT INTO dbo.TestOLD(id, name) VALUES(@ID, @Name)"; SqlCommand cmd = new SqlCommand(smt, _connection); cmd.Parameters.Add("@ID", SqlDbType.Int); cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100); cmd.Parameters["@ID"].Value = i; cmd.Parameters["@Name"].Value = i.ToString(); cmd.ExecuteNonQuery(); stmt = "INSERT INTO dbo.TestNew(id, name) VALUES(@ID, @Name)"; cmd = new SqlCommand(smt, _connection); cmd.Parameters.Add("@ID", SqlDbType.Int); cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100); cmd.Parameters["@ID"].Value = i; cmd.Parameters["@Name"].Value = i.ToString(); cmd.ExecuteNonQuery();sameer.khanjit@gmail.com
View Blog
Click "Mark as Answer" on the post that helped you.
TheGr8Wall
Member
115 Points
55 Posts
Re: How to insert data in two tables at the same time
Dec 21, 2012 07:39 AM|LINK
my suggestion to the table design would be
you should have one single table to hold all repliest. and this table should have the question id as one part of the key and the reply id as another part so each row in the answer table is a logical representation of an reply. You can have one more filed in this table to mark a reply as an answer. If you need to maintain if a reply was applied to another reply from some one else you can have one more field to relate a reply to another.
hope this helps. with this structure you can have just one gridview that displays all replies to a question with some sort of indicator that marks a specific post as an answer. like a green background or something of that sort.
Gaurav Balya...
Member
35 Points
69 Posts
Re: How to insert data in two tables at the same time
Dec 21, 2012 07:42 AM|LINK
Dear,
Take it as this,
i have a web form that takes question and stores it in Question Table and if anyone give replies to that question it is stored in Answer table(Question_id is common in both the tables),so when i want to show the Question the coprresponding replies are also seen.
Thanks
sameer_khanj...
Contributor
7046 Points
1376 Posts
Re: How to insert data in two tables at the same time
Dec 21, 2012 07:49 AM|LINK
Then please create store procedure for it like
PROCEDURE [dbo].[InfoInsert] -- Add the parameters for the stored procedure here @Question varchar(500), @Answer varchar(500) AS BEGIN INSERT INTO [Questions] (Question) VALUES (@Question) select @QuestionID = @@IDENTITY INSERT INTO [Answers] (QuestionID,Answer) (@QuestionID,@Answer ); ENDusing (SqlConnection con = new SqlConnection(dc.Con)) { using (SqlCommand cmd = new SqlCommand("[dbo].[InfoInsert]", con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Question", SqlDbType.VarChar).Value = txtQuestion.Text; cmd.Parameters.Add("@Answer", SqlDbType.VarChar).Value = txtAnswer.Text; con.Open(); cmd.ExecuteNonQuery(); }sameer.khanjit@gmail.com
View Blog
Click "Mark as Answer" on the post that helped you.
Gaurav Balya...
Member
35 Points
69 Posts
Re: How to insert data in two tables at the same time
Dec 21, 2012 07:53 AM|LINK
Dear TheGr8Wall,
Thanks for your suggetion,
Please guide me on this,
(1)how can i do this it is not clicking to me,suppose i am able to show the question with some replies and if any other person also wants to reply and clicked on Reply how can i disable all the posts below it(Like in this Forum)
(2)what problem i am facing is that wheni try to display question with replies by joining the two tables(Question and Reply),some field are repeated in the Gridview,how can i achieve duplication in Gridview,
Thanks
TheGr8Wall
Member
115 Points
55 Posts
Re: How to insert data in two tables at the same time
Dec 21, 2012 07:55 AM|LINK
simple redirect them to another page.. i hope this site also does the same..
TheGr8Wall
Member
115 Points
55 Posts
Re: How to insert data in two tables at the same time
Dec 21, 2012 08:01 AM|LINK
joining the two tables is the problem that you have. you should not join. you should do a union. I understand that you have the user id associated with a question and a reply. so one of your field in the result set will contain user detail. And i believe both the question and reply are text fields so you should end up joining the question and answer table result set with jus the user table. I will be really glad if you send me the table structures and i can help you with some sample query...
Gaurav Balya...
Member
35 Points
69 Posts
Re: How to insert data in two tables at the same time
Dec 21, 2012 09:17 AM|LINK
Dear TheGr8Wall,
Please forgive me as i am relatively new to the joins concept,
please visit the folowing link www.journey2grnoida.com/Forums.aspx
and see what i am trying to achieve,this is a test page and the username and password are Balyans and Test respectively.
the structure of my tables are,
p_question(username,userimage,question_id,question_time,subject,emailflag,imagestatus,views,status,replies)
p_answer(question_id,answer,answer_time,answered by)
i have not taken anything directle from user table,
i think that the structure of my tables need to be changed,
please share your opinions with me,
Thanks
oned_gk
All-Star
31017 Points
6352 Posts
Re: How to insert data in two tables at the same time
Dec 21, 2012 10:00 AM|LINK
Try this
Table Question : ID(Unique), Question
Table Answer : ID(Unique), QuestionID (not Unique), Answer