Switching gridview's datasource table for saving [Answered]RSS

• • Reply
  CPorras

  Member

  6 Points

  43 Posts

  Switching gridview's datasource table for saving

  May 10, 2017 03:49 AM|CPorras|LINK

  Having an entity framework data model in my "Asp.net Dynamic Data Web Site"

  I want to display the data of one table but when saving I'd like switching to another table in which I am going to save the answer

  I want to save the data not in the original table but a second one

  Imagine that first table is just a list of questions

  Imagine second list will gather answers to those questions so I'd like to save in the second table 4 things 

  1. questionnaire number XXXXX
  2. question number
  3. description of the question as it written in the 1st table (because it might at well can be changed in time)
  4. the answer for that question: answered by the person interviewed  in questionnaire number XXXXX

  Example

  Table One

  Questions

  QuestionID 535
  Colour?

  QuestionID 536
  Nationality?

  QuestionID 537
  Height?

  QuestionID 538
  Sex?

  QuestionID 941
  Religion?

  QuestionID 4545
  Profession?

  QuestionID 4774
  Gross Yearly Income?

  Table Two
  Questionnaire 222

  222,535, Colour,Red
  222,536, Nationality,Russian
  222,537, Height,.75
  222,538, Sex,Male
  222,941, Religion,Arabic
  222,4545, Profession,Salesman
  222,4774, Gross Yearly Income,45,000

  Questionnaire 223

  223,535, Colour,Blue
  223,536, Nationality,Spain
  223,537, Height,1.85 
  223,538, Sex,female
  223,941, Religion,Islam
  223,4545, Profession,Carpenter
  223,4774, Gross Yearly Income,35,000

  Questionnaire 224

  224,535, Colour,Yellow
  224,536, Nationality,USA
  224,537, Height,1.95
  224,538, Sex,Male
  224,941, Religion,Etruscan
  224,4545, Profession,Craftsman
  224,4774, Gross Yearly Income,40,000

• • Reply
  Cathy Zou

  Star

  8650 Points

  2882 Posts

  Re: Switching gridview's datasource table for saving

  May 11, 2017 09:48 AM|Cathy Zou|LINK

  Hi johnzee,

  Base on your description and needs, I make sample . It work well. You could refer to it:

  QuestionsTable:

  CREATE TABLE [dbo].[QuestionsTable]
  (
  	[QuestionID] INT NOT NULL PRIMARY KEY, 
      [Content] NVARCHAR(MAX) NULL
  )

  
  

  QuestionnaireTable 

  CREATE TABLE [dbo].[QuestionnaireTable]
  (
  	[QuestionnaireId] INT NOT NULL , 
      [QuestionID] NVARCHAR(MAX) NULL, 
      [Question] NVARCHAR(MAX) NULL, 
      [Answer] NVARCHAR(MAX) NULL
  )
  

  page.aspx:

    QuestionnaireID: <asp:TextBox ID="QuestionIDtxt" runat="server"></asp:TextBox>
          <br />
          <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnDataBound="GridView1_DataBound">        
                  <Columns>               
                  <asp:BoundField  DataField="QuestionID " HeaderText="QuestionID"/>
                   <asp:BoundField  DataField="Content" HeaderText="Content"/>
                  <asp:TemplateField>
                      <ItemTemplate>
                          Answer:
                          <asp:TextBox ID="Answertxt" runat="server"></asp:TextBox>
                      </ItemTemplate>
                  </asp:TemplateField>
              </Columns>
          </asp:GridView> 
          <asp:Button ID="Button1" runat="server" Text="Complete" OnClick="Button1_Click" />

  CodeBehind:

  using System.Data;
  using System.Data.SqlClient;
  using System.Configuration;
  
   protected void Page_Load(object sender, EventArgs e)
          {
              if (!this.IsPostBack)
              {
                  this.BindGrid();
              }
          }
  
          private void BindGrid()
          {
              string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
              using (SqlConnection con = new SqlConnection(constr))
              {
                  using (SqlCommand cmd = new SqlCommand("SELECT * FROM QuestionsTable"))
                  {
                      using (SqlDataAdapter sda = new SqlDataAdapter())
                      {
                          cmd.Connection = con;
                          sda.SelectCommand = cmd;
                          using (DataTable dt = new DataTable())
                          {
                              sda.Fill(dt);
                              GridView1.DataSource = dt;
                              GridView1.DataBind();
                            
                          }
                      }
                  }
              }
          }
  
          protected void GridView1_DataBound(object sender, EventArgs e)
          {
            
          }
  
          protected void Button1_Click(object sender, EventArgs e)
          {
            
              int  QuestionnaireId =Convert.ToInt32( QuestionIDtxt.Text);
              foreach (GridViewRow row in GridView1.Rows) {
                  if (row.RowType ==DataControlRowType.DataRow) {
                      string QuestionID = row.Cells[0].Text;
                    //  string Question = row.Cells[1].Text;
                      string Question = row.Cells[1].Text.Remove(row.Cells[1].Text.Length-1);
                      string Answer = ((TextBox)row.Cells[2].FindControl("Answertxt")).Text;
  
                    string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                      using (SqlConnection con = new SqlConnection(constr))
                      {
                          using (SqlCommand cmd = new SqlCommand("INSERT INTO [QuestionnaireTable] VALUES(@QuestionnaireId, @QuestionID, @Question,@Answer)"))
                          {
                              cmd.Connection = con;
                              cmd.CommandType = CommandType.Text;
                              cmd.Parameters.AddWithValue("@QuestionnaireId", QuestionnaireId);
                              cmd.Parameters.AddWithValue("@QuestionID", QuestionID);
                              cmd.Parameters.AddWithValue("@Question", Question);
                              cmd.Parameters.AddWithValue("@Answer", Answer);
                              con.Open();
                              cmd.ExecuteNonQuery();
                              con.Close();
                          }
                      }
  
                  }
              }
          }

  Output after user enter answer for each question:

  
  

  After click Complete button. the data Questionnaire table a below:

  
  

  Best regards

  Cathy

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  
• • Reply
  CPorras

  Member

  6 Points

  43 Posts

  Re: Switching gridview's datasource table for saving

  May 11, 2017 03:44 PM|CPorras|LINK

  thank you

  i'll give it a try right away !!!