?
Solved

asp.net c# updating table row

Posted on 2011-10-31
20
Medium Priority
?
223 Views
Last Modified: 2012-05-12
Hello,

I have an application where I'm creating an online test.
I have a page with several panels. in one panel, users register, after registration,  a second panel opens up, users take the test and a label displays the score.
When user clicks "finish", I wrote a function to update a table row.

My codebehind follows:
---------------
public void updatetester()
        {
            // prepare command string

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

            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;

            }
        }
-------------
I am getting the following error message:

Incorrect syntax near '('.

I'm at a loss to fix t h i s

What I would ultimately like to have is a way to select the table row that contains the tester's first name (as captured in the textbox named "txt_FirstName" and update the table field called "Score" (datatype: int)

ANY help with this would be greatly appreciated...
0
Comment
Question by:Codeaddict7423
  • 9
  • 9
  • 2
20 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 37058626
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 += ")";
0
 

Author Comment

by:Codeaddict7423
ID: 37058704
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?

0
 
LVL 25

Expert Comment

by:TempDBA
ID: 37058720
@Firstname and @Score are string type variable defined, aren't they?

How you are declaring and assigning values to them?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:Tom Beck
ID: 37059013
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.
0
 

Author Comment

by:Codeaddict7423
ID: 37059046
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...
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37059195
The txt_FirstName value would have to match some value that is already in the database FirstName field exactly (although it is case-insensitive).
0
 

Author Comment

by:Codeaddict7423
ID: 37059226
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....
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37059302
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.
0
 

Author Comment

by:Codeaddict7423
ID: 37059371
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....
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37059453
>>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?
0
 

Author Comment

by:Codeaddict7423
ID: 37062208
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.

0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37062337
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;
        }

Open in new window

0
 

Author Comment

by:Codeaddict7423
ID: 37062693
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.
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37063270
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"));

0
 

Author Comment

by:Codeaddict7423
ID: 37064003
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;

            }
        }

Open in new window

0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37064513
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"].
0
 

Author Comment

by:Codeaddict7423
ID: 37065192
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...
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37065760
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?
0
 

Author Comment

by:Codeaddict7423
ID: 37065903
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.




0
 
LVL 38

Accepted Solution

by:
Tom Beck earned 2000 total points
ID: 37066303
Remove this line since you commented out your first update statement that had the @FirstName parameter:

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

Remove this line, this syntax is only used instead of the simpler Session["currentTesterID"] in case you needed a full path to access the Session state like in a class file that does not inherit System.Web.UI. Otherwise you don't need it and the syntax is incorrect anyway because it lacks assignment:

HttpContext.Current.Session["currentTesterID"];

You can set the score to "0" when you run the inserttester function. Just add that column and a value.

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

These two lines should be in your button click event for the "register" button:

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

Can't think of any reason to call inserttester() inside the updatetester() function.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question