Insert auto increment

Question

I have a method Insert(). Everything is working as expected except for the auto increment. Here's the code:

public void Insert(string m1,int y1,int new_count)
    {
        string query = "INSERT INTO page_counter (id,month,year,page_count) VALUES('','"+m1+"',"+y1+","+new_count+")";

            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, connection);

            //Execute command
            cmd.ExecuteNonQuery();

            //close connection
            this.CloseConnection();
    }

My Id column is an auto-increment. So my question is how can the value be inserted in the database an continue the auto increment in the table for id?

score 4 · Answer 1 · answered Mar 17 '14 at 03:39

4

Simply don't specify value for id :

string query = "INSERT INTO page_counter (month,year,page_count) VALUES('"+m1+"',"+y1+","+new_count+")";

And look into better approach, parameterized query, instead of concatenating query string.

answered Mar 17 '14 at 03:39

har07

88,338
12
84
137

score 3 · Accepted Answer · edited May 23 '17 at 12:14

3

All you have to do is exclude the auto-incremented IDENTITY column from your insert.

Change your query to:

//NOTE: We leave the "id" column out of the insert, SQL Server will handle this automatically
string query = "INSERT INTO page_counter (month, year, page_count) VALUES (@Month, @Year, @PageCount)";

SQL will take care of the ID field.

You might notice I used Scalar variables in my query. You can (and should) assign these in the command so that you exclude the possibility of SQL injection:

EDIT DUE TO THE FACT THAT THIS IS COMING FROM MySql.Data.MySqlClient PRE 4.0

MySqlCommand cmd = new MySqlCommand(query, connection);

cmd.Parameters.Add(new MySqlParameter("@Month", m1));
cmd.Parameters.Add(new MySqlParameter("@Year", y1));
cmd.Parameters.Add(new MySqlParameter("@PageCount", new_count));

//Execute the INSERT
cmd.ExecuteNonQuery();

For a little background on SQL Injection I would recommend reading:

edited May 23 '17 at 12:14

Community

1
1

answered Mar 17 '14 at 03:39

Evan L

3,805
1
22
31

can you tell me logically what is actually the difference between my method and using the @ sign? – Ren Mar 17 '14 at 03:46
In your method, someone can inject malicious code directly into the value `m1` or `y1`. Consider if they wrote this into wherever you are getting `m1`: `'; DROP TABLE USERS;` Your code would literally execute that code on the server because the malicious user *appended text to your string*. Using Parametarized SQL you ensure that no one can insert SQL commands directly into the query. You take the query away from a string literal and treat Data like data rather than text. – Evan L Mar 17 '14 at 03:48
@Ren the above should give you a little insight, and I added a couple links in my answer. It takes a little more typing, but overall, parametarized sql will save you security problems AND many syntax / datatype errors when you start using more complex INSERTS/UPDATES. Also sql injection can occur on even the most innocuous queries like: `SELECT customer_id FROM customer` etc... – Evan L Mar 17 '14 at 03:53
owh ok. So where should I place the cmd.Parameters.AddWithValue()? because if i place it under MySqlCommand cmd = new MySqlCommand(query, connection), it display error "no extension method AddWithValue accepting first argument.." – Ren Mar 17 '14 at 03:53
It should go under the `MySqlCommand cmd = new MySqlCommand` block but before `cmd.ExecuteNonQuery`. The syntax is as follows `cmd.Parameters.AddWithValue("@MyParameter", theValueIWantToAssign);` Make sure you have quotes around the `@Parameter`. If you literally copy/paste my code it should work. – Evan L Mar 17 '14 at 03:57
yeah, I copy paste your code. and it just display the error at AddWithValue. Not sure why.below is the error message. – Ren Mar 17 '14 at 03:59
'MySql.Data.MySqlClient.MySqlParameterCollection' does not contain a definition for 'AddWithValue' and no extension method 'AddWithValue' accepting a first argument of type 'MySql.Data.MySqlClient.MySqlParameterCollection' could be found (are you missing a using directive or an assembly reference?) – Ren Mar 17 '14 at 04:00
My guess is you have a type somewhere. Try using Intellisense. Just start typing cmd.Para and you should see Parameters. Then type `.AddWi` and you should see `AddWithValue(string,object)`; – Evan L Mar 17 '14 at 04:01
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/49860/discussion-between-ren-and-evan-l) – Ren Mar 17 '14 at 04:02
@Ren OOOHHH Ok, probably different syntax for `MySql`. Try `cmd.Parameters.Add("@Parameter").Value(m1);` etc. – Evan L Mar 17 '14 at 04:03

score 3 · Answer 3 · edited May 23 '17 at 11:43

Using AUTO_INCREMENT

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically.

string query = "INSERT INTO page_counter (month,year,page_count)
VALUES('"+m1+"',"+y1+","+new_count+")";

You can always use Parameterized query to avoid SQL Injection

string query = "INSERT INTO page_counter (month,year,page_count)
VALUES(@month,@year,@page_count)";

cmd.Parameters.AddWithValue("@month",Value1);
cmd.Parameters.AddWithValue("@year", Value2);
cmd.Parameters.AddWithValue("@page_count", Value3);

score 1 · Answer 4 · answered Mar 17 '14 at 03:39

1

string query = "INSERT INTO page_counter (month,year,page_count) VALUES('"+m1+"',"+y1+","+new_count+")";

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

answered Mar 17 '14 at 03:39

jdphenix

15,022
3
41
74

score 1 · Answer 5 · edited Mar 17 '14 at 03:52

1

You can change identity of id in database

"INSERT INTO page_counter (month,year,page_count) VALUES('"+m1+"',"+y1+","+new_count+")"

edited Mar 17 '14 at 03:52

Nagaraj S

13,316
6
32
53

answered Mar 17 '14 at 03:42

Linh Tuan

440
3
11

score 1 · Answer 6 · answered Mar 17 '14 at 03:45

Do not specify ID from here:

If you are using SQL Server, have your ID field in your DB created like so:

ID int IDENTITY(1,1) PRIMARY KEY

If MySQL then:

ID int NOT NULL AUTO_INCREMENT

Look at the following link: http://www.w3schools.com/sql/sql_autoincrement.asp

Insert auto increment

6 Answers6