Updating MySql counter when users log in

Question

I have a website where users can log in. The client wants there to be a way to record the number of times any particular user logs in. I have a "Counter" row in the table. How do I program the app (built in C# ASP.NET) to update the counter when people log in? Is this code correct:

cmd.ExecuteNonQuery = "UPDATE Counter FROM brokercenter"

I just recently graduated (as in the 10th of this month) so I am new to this, plus I know nothing about databases, I am just learning on the job. Please let me know if I need any other parameter or connection string or aything else? This is in the button click event and there is already a connection string there to check the username and password so I don't think I need another connection string, but I don;t know for sure. Thanks in advance!

For that matter, here is the whole event (the login stuff works fine, just the update is my question):

string connectionString =         
    ConfigurationManager.ConnectionStrings["moverschoiceConnectionString"].ConnectionString;    
OdbcConnection conn = new OdbcConnection(connectionString);   
conn.Open();   OdbcCommand cmd = new OdbcCommand();   
cmd.Connection = conn;   
cmd.CommandText = "select Email, Password from brokercenter where Email = '" + txtLoginEmail.Text + "'";   
OdbcDataReader reader = cmd.ExecuteReader();   

while(reader.Read())   
{     
    if (reader["Password"].ToString() == txtLoginPassword.Text)     
    {         
        reader.Close();
        if (cbRememberMe.Checked == true)
        {
            Response.Cookies["username"].Value = txtLoginEmail.Text;
            Response.Cookies["username"].Expires = DateTime.Now.AddMonths(1);
            Response.Cookies["password"].Value = txtLoginPassword.Text;
            Response.Cookies["password"].Expires = DateTime.Now.AddMonths(1);
        }
        else
        {
            Response.Cookies["username"].Expires = DateTime.Now.AddMonths(-1);
            Response.Cookies["password"].Expires = DateTime.Now.AddMonths(-1);
        }

            Response.Redirect("BrokerResources.aspx");     
        }     
        else     
        {         
            lblLoginError.Text = "Invalid Password";     
        }   
    }   
    lblLoginError.Text = "Invalid Email or Password";    
    reader.Close();

    cmd.ExecuteNonQuery = "UPDATE counter FROM brokercenter";
}

You might want to read up on basic SQL syntax first: http://en.wikipedia.org/wiki/SQL#Queries while we can help fix broken code (if you provide it), we're not here to be your teachers for basics. — Marc B, Feb 23 '12 at 20:20
Sorry for thinking this is a community to help people. I am not asking you to sit next to me and teach me databases (though I would not mind it, haha), I am asking for help though. I do not know how to do this and directing me to wikipedia further confuses me. Consider my code broken in that it does not work, hehe. — hammer.pr, Feb 23 '12 at 20:31
read up on the syntax of update queries, and you'll see why the counter isn't updating (hint: because you're not writing a proper query). — Marc B, Feb 23 '12 at 20:51

score 3 · Answer 1 · edited May 23 '17 at 12:20

3

For a start, you should read about using UPDATE in MySQL's Reference Manual.

There is even an example for exactly what you want to do.
Quote from the link:

If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. For example, the following statement sets col1 to one more than its current value:
UPDATE t1 SET col1 = col1 + 1;

This is basically all you need, you just need to add a WHERE clause and filter for the username or for the email.

Plus, you should read about SQL Injection because of this:

where Email = '" + txtLoginEmail.Text + "'";

Concatenating strings like this to pass parameters can cause problems as described in the link.
Here's an example how to do it better.

edited May 23 '17 at 12:20

Community

1
1

answered Feb 23 '12 at 21:01

Christian Specht

35,843
15
128
182

So do I not need the cmd.ExecuteNonQuery part in there? I thought that is what increments it. – hammer.pr Feb 23 '12 at 21:07
Yes, you **do** need `ExecuteNonQuery`. I mentioned only the SQL in my answer because my impression was that your main problem is how to get the `UPDATE` query right (the one you posted in the question is not valid SQL). But you still need `ExecuteNonQuery` to actually **execute** the query. – Christian Specht Feb 23 '12 at 21:12
Ok, just checking, that is in there like I thought. Thank you guys for all your help! I am one piece of the puzzle away from being done with this project. – hammer.pr Feb 23 '12 at 21:21

score -1 · Answer 2 · answered Feb 23 '12 at 20:59

-1

cmd.ExecuteNonQuery = String.Format("UPDATE brokercenter SET counter = {0} WHERE Email = {1}", myCounter++, txtLoginEmail.Text);

where "myCounter" is a local counter variable (which should also be read from the database). Does this make sense now?

answered Feb 23 '12 at 20:59

lkaradashkov

8,609
1
15
12

Yes, thank you. I was reading up on the update queries like the other poster had suggested, and for the most part I had it right as I was redoing it, but that ExecuteNonQuery was confusing me. Thank you! – hammer.pr Feb 23 '12 at 21:01
-1 because it's: a) vulnerable to SQL injection (see my answer about that); b) won't work as written because `Email` is a string; c) increasing a counter variable in C# is not the best way to update a value in a SQL table. – Christian Specht Feb 23 '12 at 21:03
Excellent feedback, Christian!! What I really was trying was to make a quick point and get @hammer.pr going... – lkaradashkov Feb 23 '12 at 21:07

Updating MySql counter when users log in

2 Answers2