How to insert data in two tables at the same time RSS

• • Reply
  

  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

• • Reply
  

  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.
• • Reply
  

  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.

• • Reply
  

  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

• • Reply
  

  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 );
  
  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
• • Reply
  

  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

• • Reply
  

  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..

  • Edited by TheGr8Wall on Dec 21, 2012 07:56 AM
• • Reply
  

  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...

• • Reply
  

  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

• • Reply
  

  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

  Programming to simplify, dont look for hard way