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.
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
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
Star
8670 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
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.
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 !!!