Using variables with Mysql insert statement

Question

I am trying to insert some data into my MySql database and have successfully made a connection and submitted data, however my variables are saving as the actual name of the variable and not the value I am trying to assign. I am collecting the logged in user of my asp system using identity and am collecting a score of a user from some radio button lists on my main page.

What I try to save the score with the id for a user with id of 12345 for example and a score of 4. it will save the word Id and the word score instead of the numbers.

Here is my current code:

    if (score != 0) ;
    {
        string Id = User.Identity.GetUserId();
        string MyConString = "SERVER=localhost;" +
        "DATABASE=synther_physics;" +
        "UID=root;" +
        "PASSWORD=rootpass;";
        MySqlConnection connection = new MySqlConnection(MyConString);
        MySqlCommand command = connection.CreateCommand();
        MySqlDataReader Reader;
        command.CommandText = "INSERT INTO userscores (Id, momentsandenergytestscore) VALUES ('Id','score');";

        connection.Open();
        Reader = command.ExecuteReader();
        connection.Close();


    }

You never pass your values into the insert statement

trailmax
– trailmax

2017-04-10 22:32:23 +00:00
Commented Apr 10, 2017 at 22:32 — trailmax
– trailmax, Commented Apr 10, 2017 at 22:32
how do i get the data into my table then?

Daniel Turville
– Daniel Turville

2017-04-10 22:33:19 +00:00
Commented Apr 10, 2017 at 22:33 — Daniel Turville
– Daniel Turville, Commented Apr 10, 2017 at 22:33

Chaim Eliyah · Accepted Answer · 2018-10-31 18:03:57Z

1

First thing is that placing the variables directly in the statement is a no-no, leads to a very well-known problem called SQL Injection. To avoid this we are going to change your command to use parameters, which will be populated with the variables later

command.CommandText = "INSERT INTO userscores (Id, momentsandenergytestscore) VALUES (@Id,@score);";

We will then add in these 2 lines to populate the parameters with your variables

command.Parameters.AddWithValue("@Id", Id);
command.Parameters.AddWithValue("@score", score);

As this is not a SELECT statement, no reader is needed. You can use the ExecuteNonQuery command which will return the count of rows affected (should be 1 for this INSERT statement.

int RowsAffected = command.ExecuteNonQuery();

edited Oct 31, 2018 at 18:03

Chaim Eliyah

2,9734 gold badges26 silver badges37 bronze badges

answered Apr 11, 2017 at 0:30

Mad Myche

1,0751 gold badge7 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Daniel Turville Over a year ago

Hi there, thankyou for the responce, i have made the changes you suggest but am getting the error: 'Id cannot be null'

Mad Myche Over a year ago

Try a breakpoint after Id should be defined, and see what the User.Identitu has for values.... Or defining before the if(...) and checking for Id as well as score.

Daniel Turville Over a year ago

id is null here, i think the code for collecting the logged in users id is incorrect. would you agree?

Mad Myche Over a year ago

That would be my assessment as well. Try a manual over-ride of Id with something useful for testing iwhen its null, possible "NULL at " + DateTime.Now.ToString():

Daniel Turville Over a year ago

well it breaks my execution anyway as its the primary key in the fields of the table i am trying to use, ill maybe ask a new question if i cant find out a way of doing it. Thankyou for your help

Collectives™ on Stack Overflow

Using variables with Mysql insert statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related