locked
Switching gridview's datasource table for saving RRS feed

  • Question

  • User-1847166894 posted

    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

    Wednesday, May 10, 2017 3:49 AM

Answers

  • User-707554951 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 11, 2017 9:48 AM

All replies

  • User-707554951 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 11, 2017 9:48 AM
  • User-1847166894 posted

    thank you

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

    Thursday, May 11, 2017 3:44 PM