How to make FormView1.ChangeMode(FormViewMode.ReadOnly) display the recently entered data?

I have a basic formview page with two text box... which I am using OnCommand via a linkbutton to insert data.  So for the data get entered correctly into the database. Then I use the  FormView1.ChangeMode(FormViewMode.ReadOnly);  to display the recently entered entry.  The problem I am having is that is does not show the most recently enterd data but shows the first record in the database... how I get it to show the most recenly entered data.



    protected void InsertButton_Command(object sender, CommandEventArgs e)
    {
        if (e.CommandName == "NewInsert")
        {

            TextBox theFirst = (TextBox)FormView1.FindControl("TextBox1");
            if (theFirst == null) { theFirst.Text = ""; }
            TextBox theSecond = (TextBox)FormView1.FindControl("TextBox2");
            if (theSecond == null) { theSecond.Text = ""; }
               
            DateTime entryDate = DateTime.Now;
            using (SqlConnection MyConnection = new SqlConnection(myGLOBAL_CONNECTION))
            {

                string sqlString = "INSERT INTO [n_tblEvaluation] ([theFirst], [theSecond) VALUES (@theFirst, @theSecond)";
                SqlCommand objCmd = new SqlCommand(sqlString, MyConnection);
                objCmd.Connection.Open();
                objCmd.Parameters.Add(new SqlParameter("@theFirst", theFirst.Text));
                objCmd.Parameters.Add(new SqlParameter("@project_title", theSecond.Text));
                SqlDataReader dataReader = objCmd.ExecuteReader();
                objCmd.Connection.Close();

                FormView1.ChangeMode(FormViewMode.ReadOnly);
                FormView1.DataBind();
              }
        }
    }
prophytAsked:
Who is Participating?
 
strickddConnect With a Mentor Commented:
You could try the multipart SQL query like this:
 using (SqlConnection MyConnection = new SqlConnection(myGLOBAL_CONNECTION))
            {

                string sqlString = "INSERT INTO [n_tblEvaluation] ([theFirst], [theSecond) VALUES (@theFirst, @theSecond); SELECT SCOPE_IDENTITY() AS ID;";
                SqlCommand objCmd = new SqlCommand(sqlString, MyConnection);
                objCmd.Connection.Open();
                objCmd.Parameters.Add(new SqlParameter("@theFirst", theFirst.Text));
                objCmd.Parameters.Add(new SqlParameter("@project_title", theSecond.Text));
                SqlDataReader dataReader = objCmd.ExecuteReader();

if (dataReader.Read())
                {

                    if (dataReader["ID"] != DBNull.Value)
                    {
                        id = Convert.ToInt32(dataReader["ID"]);
                    }
                    else
                    {
                        Response.Write("value is null");
                    }
                }

                objCmd.Connection.Close();

                FormView1.ChangeMode(FormViewMode.ReadOnly);
                FormView1.DataBind();
              }

Open in new window

0
 
strickddCommented:
You have to perform a "SELECT SCOPE_IDENTITY()" before you close the connection. This will give you the ID of the last inserted record. Then you update the parameter for you datasource to this ID and databind.
0
 
strickddCommented:
Cleaned up your code a little.
int id = 0;
            using (SqlConnection MyConnection = new SqlConnection(myGLOBAL_CONNECTION))
            {

                string sqlString = "INSERT INTO [n_tblEvaluation] ([theFirst], [theSecond) VALUES (@theFirst, @theSecond)";
                SqlCommand objCmd = new SqlCommand(sqlString, MyConnection);
                objCmd.Connection.Open();
                objCmd.Parameters.Add(new SqlParameter("@theFirst", theFirst.Text));
                objCmd.Parameters.Add(new SqlParameter("@project_title", theSecond.Text));
                objCmd.ExecuteNonQuery();

                sqlString = "SELECT SCOPE_IDENTITY() as ID";
                objCmd = new SqlCommand(sqlString, MyConnection);
                SqlDataReader dataReader = objCmd.ExecuteReader();
                if(dataReader.Read())
                {
                     id = Convert.ToInt32(dataReader["ID"]);
                }

                objCmd.Connection.Close();

//Set parameter ID here

                FormView1.ChangeMode(FormViewMode.ReadOnly);
                FormView1.DataBind();
              }

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
prophytAuthor Commented:
I have made the following modification but I keep getting an "Must declare the variable '@id'. "  error message:

    protected void InsertButton_Command(object sender, CommandEventArgs e)
    {
        if (e.CommandName == "NewInsert")
        {

            TextBox theFirst = (TextBox)FormView1.FindControl("TextBox1");
            if (theFirst == null) { theFirst.Text = ""; }
            TextBox theSecond = (TextBox)FormView1.FindControl("TextBox2");
            if (theSecond == null) { theSecond.Text = ""; }
               
            DateTime entryDate = DateTime.Now;
            using (SqlConnection MyConnection = new SqlConnection(myGLOBAL_CONNECTION))
            {
            int @id = 0;
                string sqlString = "INSERT INTO [n_tblEvaluation] ([theFirst], [theSecond) VALUES (@theFirst, @theSecond); SELECT @id = SCOPE_IDENTITY()";
                SqlCommand objCmd = new SqlCommand(sqlString, MyConnection);
                objCmd.Connection.Open();
                objCmd.Parameters.Add(new SqlParameter("@theFirst", theFirst.Text));
                objCmd.Parameters.Add(new SqlParameter("@project_title", theSecond.Text));
                SqlDataReader dataReader = objCmd.ExecuteReader();
                objCmd.Connection.Close();

                SqlDataSource1.SelectCommand = "SELECT [id], [theFirst, [theSecond] FROM [n_tblEvaluation] WHERE [id] = @id";
                FormView1.ChangeMode(FormViewMode.ReadOnly);
                FormView1.DataBind();
              }
        }
    }
0
 
prophytAuthor Commented:
sorry I just noticed your earlier post... I hadn't refresh...
But I tried as slightly modify version of your suggestion but it seem like the "ID" keep returning a null value... but the data was successfully entered into the database.


    protected void InsertButton_Command(object sender, CommandEventArgs e)
    {
        if (e.CommandName == "NewInsert")
        {

            TextBox theFirst = (TextBox)FormView1.FindControl("TextBox1");
            if (theFirst == null) { theFirst.Text = ""; }
            TextBox theSecond = (TextBox)FormView1.FindControl("TextBox2");
            if (theSecond == null) { theSecond.Text = ""; }
               
            DateTime entryDate = DateTime.Now;
            int id = 0;

            using (SqlConnection MyConnection = new SqlConnection(myGLOBAL_CONNECTION))
            {
                string sqlString = "INSERT INTO [n_tblEvaluation] ([theFirst], [theSecond) VALUES (@theFirst, @theSecond)";
                SqlCommand objCmd = new SqlCommand(sqlString, MyConnection);
                objCmd.Connection.Open();
                objCmd.Parameters.Add(new SqlParameter("@theFirst", theFirst.Text));
                objCmd.Parameters.Add(new SqlParameter("@project_title", theSecond.Text));
                objCmd.ExecuteNonQuery();

                sqlString = "SELECT SCOPE_IDENTITY() as ID";
                objCmd = new SqlCommand(sqlString, MyConnection);
                SqlDataReader dataReader = objCmd.ExecuteReader();
               
                if (dataReader.Read())
                {

                    if (dataReader["ID"] != DBNull.Value)
                    {
                        id = Convert.ToInt32(dataReader["ID"]);
                    }
                    else
                    {
                        Response.Write("value is null");
                    }
                }

                objCmd.Connection.Close();


                SqlDataSource1.SelectCommand = "SELECT [id], [theFirst], [theSecond] FROM [n_tblEvaluation] WHERE [id] = '" + id + "'";
                FormView1.ChangeMode(FormViewMode.ReadOnly);
                FormView1.DataBind();


              }
        }
    }
0
 
prophytAuthor Commented:
Thanks for you help....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.