asp.net c# updating table row

Its coming here:-

updateString += "VALUES (";
            updateString += "@FirstName,@Score)";


since the variables are already string, you needn't write them with double quote as it then goes to the server without the actual values but @firstname and @score only..

instead do something like below:-

updateString += "VALUES (";
            updateString += @FirstName;
            updateString += ",";
            updateString += @Score;
            updateString += ")";

TempDBA,

Thank you for the quick reply.

When I implemented your suggested code, I'm getting error messages from VS2008 as follows:

----------
    string updateString;
            updateString = "Update Testers (";
            updateString += "FirstName, Score)";
            updateString += "VALUES (";
            updateString += @FirstName;
            updateString += ",";
            updateString += @Score;
            updateString += ")";
----------

VS indicates error as: "The name 'FirstName' does not exist in the current context
and
"The name 'Score' does not exist in the current context/

Can you please help?

@Firstname and @Score are string type variable defined, aren't they?

How you are declaring and assigning values to them?

I think it's the syntax of the SQL statement. An Update statement should be:

UPDATE Testers SET Score=@Score WHERE FirstName=@FirstName

Assuming that there are fields called FirstName and Score in the database table or aliases have been mapped. If not then, 'FirstName' does not exist... error will occur.

tommyBoy,
Thank you fory your help.
I edited my code as follows:
---------
  public void updatetester()
        {
            // prepare command string

            //string updateString;
            //updateString = "Update Testers (";
            //updateString += "FirstName, Score)";
            //updateString += "VALUES (";
            //updateString += "@FirstName";
            //updateString += ",";
            //updateString += "@Score";
            //updateString += ")";

             string updateString;
             updateString = "UPDATE Testers SET Score=@Score WHERE FirstName=@FirstName";

            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(updateString, con);

            cmd.Parameters.AddWithValue("@FirstName", txt_FirstName.Text);

            cmd.Parameters.AddWithValue("@Score", lblScore.Text);

            //cmd.Parameters["@Score"].Value = lblScore.Text;

            try
            {
                con.Open();

                // 1. Instantiate a new command with command text only
           
                // 2. Set the Connection property
                cmd.Connection = con;

                // 3. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
             

            }
            catch (Exception err)
            {
                lblUpdateScore.Text = "Error Updating Score. ";
                lblUpdateScore.Text += err.Message;

            }

            finally
            {
                // Close the connection
                cmd.Connection.Close();

                Panel1.Visible = true;

            }
        }
----------

However, the score is not being written to the database.
ANY help would be greatly appreciated...

The txt_FirstName value would have to match some value that is already in the database FirstName field exactly (although it is case-insensitive).

tommyBoy,

Thanks for the quick reply.

The database field name is indeed "FirstName". I was thinking that if we could call either the value of the first name text box (txt_FirstName) or a select MAX (SELECT MAX( testerID ) to get the latest entry, we could use that to identify the current test taker and update the "Score" field....

SELECT MAX() would get the largest value in a column, not the latest, besides, how can you be sure the tester on the other end really is the latest to take the test.

Do you require a login OR do you create a new user in the database with a unique id each time someone wants to take the test? If so, just keep track of that unique id during the session and use that to identify the row you want to update with the score. Don't depend on textbox values.

tommyBoy,

Great suggestions. Indeed, I was thinking that this might be a one-tester-at-time scenario, however, there might be more than one person taking hte test at a time. So, the ID is probably th e best field to connect the score to.  NOW, how to i edit my code to capture the field (called testerID).


ANY suggestions would be greatly appreciated....

>>there might be more than one person taking hte test at a time

You should definitely plan for that possibility.

When is testerID created?

Here's a typical scenario. Person visits your site and wants to take the test. You collect some information via a form and create a row in a database table to hold the information. When the row is created, also create a unique id for the user and save it in a Session state variable: Session("currentTesterID") = testerID;. Now when ever you need to select from the tester's row in the database or update it you have an identifier for the row. You can call up the user's id by simply assigning the Session variable to a string: string testerID = Session("currentTesterID");

Do you need some sample code for adding the new tester and getting the unique id back out?

tommyBoy,

Yes, I would appreciate some sample code on creating a session state variable so I can update the table "Testers" with the individual's test score.

Creating a Session variable is simple, as I demonstrated in my last post.

Create the Session variable and assign it a value:

Session("currentTesterID") = testerID;

Retrieve the value:

testerID = Session("currentTesterID");

For creating the unique ID in the database when you add a new tester, the simplest way is to add an "Identity" column in SQL Server when you create the table to hold the testers (need help with that?). Then, when you have a new tester to add to the table, use something like the code below to add the tester and retrieve the unique ID so you can put it in a Session variable. The ID is auto-generated for every insert by SQL so you never need to include that column name in your SQL statement. The call to the inserttester function and creation of the Session variable would look like this:

string testStr = inserttester();
Session("currentTesterID") = testStr;

public string inserttester()
        {
            string testerID = String.Empty;

            // prepare command string

            string insertString = "INSERT INTO Testers (FirstName, LastName) VALUES (@FirstName, @LastName);SELECT SCOPE_IDENTITY()";

            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(insertString, con);

            cmd.Parameters.AddWithValue("@FirstName", txt_FirstName.Text);

            cmd.Parameters.AddWithValue("@LastName", txt_LastName.Text);

            try
            {
                con.Open();

                // 1. Instantiate a new command with command text only

                // 2. Set the Connection property
                cmd.Connection = con;

                // 3. Call ExecuteScaler to add the new tester and return the scope identity
                testerID = cmd.ExecuteScalar().ToString();

            }
            catch (Exception err)
            {
                lblUpdateScore.Text = "Error Updating Score. ";
                lblUpdateScore.Text += err.Message;

            }

            finally
            {
                // Close the connection
                cmd.Connection.Close();

            }
            return testerID;
        }

Select allOpen in new window

tommyBoy,

Thank you for the code example.  just one more thing, can you provide a sample of code for retrieving the sting testStr .  I copied the function code, but don't know how to call the session ("currentTesterID") = testStr;

ANY help in getting this to work would be greatly appreciated.

When you have a score to record in an existing table row then it's just a matter of applying the Session("currentTesterID") value to the UPDATE statement in the updatetester function:

updateString = UPDATE Testers SET Score=@Score WHERE testerID=@testerID

...

...

cmd.Parameters.AddWithValue("@testerID", Session("currentTesterID"));

tommyBoy,

Thank you for your help.  I'm still struggling with the additiof of your suggestion to my code (please see attached...).

under the function called "public void updatetester()" ,
VS is indicating errors ('Sessions' does not exist in the current context)

ANY help you could provide would be most appreciated...

//----insert tester function starts 

        public string inserttester()
        {
            string testerID = String.Empty;

            // prepare command string
            string insertString = "INSERT INTO Testers (FirstName, LastName) VALUES (@FirstName, @LastName);SELECT SCOPE_IDENTITY()";

            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(insertString, con);

            cmd.Parameters.AddWithValue("@FirstName", txt_FirstName.Text);
            cmd.Parameters.AddWithValue("@LastName", txt_LastName.Text);

            try
            {
                con.Open();

                // 1. Instantiate a new command with command text only

                // 2. Set the Connection property
                cmd.Connection = con;

                // 3. Call ExecuteScaler to add the new tester and return the scope identity
                testerID = cmd.ExecuteScalar().ToString();
            }
            catch (Exception err)
            {
                lblUpdateScore.Text = "Error Updating Score. ";
                lblUpdateScore.Text += err.Message;
            }

            finally
            {
                // Close the connection
                cmd.Connection.Close();
            }
            return testerID;
        } 

    //----insert tester function ends




        public void updatetester()
        {
            string updateString;

            string testerID;

            Session("currentTesterID") = testerID;


            string testStr = inserttester();
            Session("currentTesterID") = testStr;


            //updateString = "UPDATE Testers SET Score=@Score WHERE FirstName=@FirstName";

            updateString = "UPDATE Testers SET Score=@Score WHERE testerID=@testerID";

            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(updateString, con);

            cmd.Parameters.AddWithValue("@FirstName", txt_FirstName.Text);
            cmd.Parameters.AddWithValue("@Score", lbl_scorecopy.Text);
            cmd.Parameters.AddWithValue("@testerID", Session("currentTesterID"));

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                lblUpdateScore.Text = "Error Updating Score. ";
                lblUpdateScore.Text += err.Message;
            }

            finally
            {
                // Close the connection
                cmd.Connection.Close();

                Panel1.Visible = true;

            }
        }

Select allOpen in new window

Two possibilities:

1.) The syntax I gave you is for VB instead of C #. Correct syntax is: Session["currentTesterID"] = whatever; with square braces instead of parenthesis.

2.)the other possibility is that you are trying to access the Session state from a class file that does not inherit System.Web.UI. The easy fix in that case is to append this to the call: HttpContext.Current.Session["currentTesterID"].

tommyBoy,

I'm almost there... I'm adding your suggestions to my code but VS is returning the following errors:


Below, please find my codebehind
------------
public void updatetester()
        {
            string updateString;

            string testerID;

            Session["currentTesterID"] = testerID;


            string testStr = inserttester();
            Session["currentTesterID"] = testStr;

           

            //updateString = "UPDATE Testers SET Score=@Score WHERE FirstName=@FirstName";

            updateString = "UPDATE Testers SET Score=@Score WHERE testerID=@testerID";

            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(updateString, con);

            cmd.Parameters.AddWithValue("@FirstName", txt_FirstNameRegister.Text);
            cmd.Parameters.AddWithValue("@Score", lblScore.Text);

            HttpContext.Current.Session["currentTesterID"];
            cmd.Parameters.AddWithValue("@testerID", Session("currentTesterID"));

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                lblUpdateScore.Text = "Error Updating Score. ";
                lblUpdateScore.Text += err.Message;
            }

            finally
            {
                // Close the connection
                cmd.Connection.Close();

                Panel1.Visible = true;

            }
        }
-----------------

VS is returning these lines as errors:
--------------
   HttpContext.Current.Session["currentTesterID"];

ERROR: only assigmnent, call, incvrement, decrement can be used

            cmd.Parameters.AddWithValue("@testerID", Session("currentTesterID"));
ERROR: System.web.UI.Page.Session is a property bu used as a method


----------

Please review and let me know where I've gone wrong...

This line needs the square braces as well:

cmd.Parameters.AddWithValue("@testerID", Session["currentTesterID"]);

This error...

 HttpContext.Current.Session["currentTesterID"];

ERROR: only assigmnent, call, incvrement, decrement can be used

...is saying that you cannot just have HttpContext.Current.Session["currentTesterID"]; on a line by itself. You have to use the value it represents by assigning it to aother variable, or incrementingl/decrementing it (as in the case of an integer).

Next point: In your updatetester function, you would not need these lines:

            string testerID;

            Session["currentTesterID"] = testerID;

The Session variable should be created and assigned in the inserttester function.

Last Point: I am also confused as to why you are inserting the new tester in the update function. What I thought you were doing is allowing the user to enter information in a form, click a button to start the test at which point you postback to add the tester to the database and create a unique ID, then return the test to the user. Then, when they "submit" the test by clicking another button, postback again to tally the score and run the updatetester function to record the score.

The way your code reads, you are recording the tester's information, creating the unique ID and recording the score in one button click. If this is true, then why is accessing the proper row in the database even a question. If you are filling in the entire row, including the score, in one step, then you don't need to save the ID to session. In fact, you don't even need an ID in that scenario.

See my confusion? Can you clarify?

tommyBoy,

Sorry for being vague.  My thinking/logic was as follows:

The user(s) record their information thorugh a series of textboxes.
When they click a button "register", it fires a function that stores their information to the "testers" table, registering a score of "0"
This function also makes visible a button that starts the test (on a separate panel).  The newly-registered user takes the test and when finished clicks a "finish" button which fires another function which totals the score. Then a label (lblScore) displays the test score.

What I was trying to do is to write a function that is also fired by the "finish" button which updates the "tester" table with the user's score.  Perhaps this is managing time in the application such that the registration of the  user occurs first, then the user takes the test and when finished, the user clicks a button which records the test score by updating the specific table row in the table "testers" with the score.

My code behihd now looks like this:
-------------
//----insert tester function starts
        public string inserttester()
        {
            string testerID = String.Empty;

            // prepare command string
            string insertString = "INSERT INTO Testers (FirstName, LastName) VALUES (@FirstName, @LastName);SELECT SCOPE_IDENTITY()";

            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(insertString, con);

            cmd.Parameters.AddWithValue("@FirstName", txt_FirstName.Text);
            cmd.Parameters.AddWithValue("@LastName", txt_LastName.Text);

            try
            {
                con.Open();

                // 1. Instantiate a new command with command text only

                // 2. Set the Connection property
                cmd.Connection = con;

                // 3. Call ExecuteScaler to add the new tester and return the scope identity
                testerID = cmd.ExecuteScalar().ToString();
            }
            catch (Exception err)
            {
                lblUpdateScore.Text = "Error Updating Score. ";
                lblUpdateScore.Text += err.Message;
            }

            finally
            {
                // Close the connection
                cmd.Connection.Close();
            }
            return testerID;
        }
    //----insert tester function ends
-------------

the updatetester functions looks like this:
------------
 public void updatetester()
        {
            string updateString;

            string testStr = inserttester();
            Session["currentTesterID"] = testStr;

            //updateString = "UPDATE Testers SET Score=@Score WHERE FirstName=@FirstName";

            updateString = "UPDATE Testers SET Score=@Score WHERE testerID=@testerID";

            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(updateString, con);

            cmd.Parameters.AddWithValue("@FirstName", txt_FirstNameRegister.Text);
            cmd.Parameters.AddWithValue("@Score", lblScore.Text);

            HttpContext.Current.Session["currentTesterID"];

            cmd.Parameters.AddWithValue("@testerID", Session["currentTesterID"]);

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                lblUpdateScore.Text = "Error Updating Score. ";
                lblUpdateScore.Text += err.Message;
            }

            finally
            {
                // Close the connection
                cmd.Connection.Close();

                Panel1.Visible = true;

            }
        }
------------

VS is still indicating an error in this line:
------------
HttpContext.Current.Session["currentTesterID"];
-----------

If the code needs a bit of tweaking or I need to move some portion of the code to the "insert tester" function, please let me know.

Again, thank you for helping me.