Adam Hardie Adam Hardie - 6 months ago 40
SQL Question

Store a timer value in a database? c#

I have a global variable which is set up for the timer, "I", the program I'm creating has a user play a game level and store their level time in a database with other users. This is what I have already.

public static class Global
{
public static int I = 0;
}


^ this is the global variable for the timer.

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Adam\Documents\Data2.mdf;Integrated Security=True;Connect Timeout=30");
SqlDataAdapter sad = new SqlDataAdapter("Select Count(*) From Login where Username= '" + txtUsername.Text + "' and Password='" + txtPassword.Text + "'", con);
SqlCommand cmd = new SqlCommand("INSERT INTO HighScore (Username, Score) VALUES(@Username,@Score)", con);
DataTable dt = new DataTable(); //empty table
sad.Fill(dt);
if (dt.Rows[0][0].ToString() == "1")
{
con.Open();
cmd.Parameters.AddWithValue("@USERNAME", txtUsername.Text);
cmd.Parameters.AddWithValue("@Score", Global.I);
}
else // else it will display this error
{
MessageBox.Show("Please enter the correct login details");
}


^^ this is the code for the end screen of the game, as you can see i've tried taking the Global.I and addwithvalue @Score which is in the HighScore table in my database.
Now when i click the button it doesn't write anything to the database but I don't get any errors when i try and save, this is why i'm confused.
Any help would be appreciated, Thanks.

Answer

before executing any command you first must open the connection. You are opening the connection after calling Fill()!!. You will also have to execute cmd. Try:

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Adam\Documents\Data2.mdf;Integrated Security=True;Connect Timeout=30"); 
SqlDataAdapter sad = new SqlDataAdapter("Select Count(*) From Login where Username= '" + txtUsername.Text + "' and Password='" + txtPassword.Text + "'", con); 

SqlCommand cmd = new SqlCommand("INSERT INTO HighScore (Username, Score) VALUES(@Username,@Score)", con);
con.Open();
cmd.Parameters.AddWithValue("@USERNAME", txtUsername.Text);
cmd.Parameters.AddWithValue("@Score", Global.I);
DataTable dt = new DataTable(); //empty table
sad.Fill(dt);
cmd.ExecuteNonQuery(); 

It is also admirable that you know how to correctly use parameters, however you use them only in 1 case instead of both queries.

Comments