Insert into mysql table using c#

Question

I'm trying to insert some data to my MySql table using C# but I'm facing some problems I've added the MySql.Data.MySqlClient reference.

I've gone through some tutorials on the web and I found some codes like cmd.Parameters.AddWithValue; however when I try to write this code it does not show up any idea why? It only shows .Add.

I've created a simple table 'inf' which has a id, name and surname where id is the primary key and it's on autoIncrement. My code is as follows:

string connString = "Server=localhost;Database=insertdeneme;Uid=****;Pwd=********;";
MySqlConnection mcon = new MySqlConnection(connString);
mcon.Open();

string cmdText = "INSERT INTO inf VALUES (null, @name, @surname)";
MySqlCommand cmd = new MySqlCommand(cmdText, mcon);
cmd.Parameters.Add("@name", txtname.Text);
cmd.Parameters.Add("@surname", txtsurname.Text);
cmd.ExecuteNonQuery();

The program compiles however when I press on the button I get the following error:

Column count doesn't match value count at row 1

When I remove the null from values I get:

Column 'name' cannot be null

So what do you think is the problem with the code?

Edited: I have one form, two text boxes and a button (txtname.text,txtsurname.text, btn.register). I added the mysql.data.mysqlclient reference.

I created a database using phpMyAdmin, and in my table I have id name and surname where id is primary and on auto increment with 11 digits and name surname are type of varchar with the limitation of 255 chars and the rest of the code are above.

What I also don't understand is why am I not able to use addwithvalue.

Error 1 '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?) C:\Users\FOREVER\documents\visual studio 2010\Projects\InsertInto1\InsertInto1\Form1.cs 63 32 InsertInto1

When your parameter is null value try setting it to DBNull.Value — Palak.Maheria
– Palak.Maheria, Commented Apr 8, 2014 at 12:25
yes, is it possible that my textboxes return null? even if I type in? everything seems normal and whatever I try the execute reader gives the error name column can not be null — Asım Gündüz
– Asım Gündüz, Commented Apr 8, 2014 at 13:03

jmail · Accepted Answer · 2014-04-08 12:21:34Z

4

I think your giving id is autoincrement, if your mention the autoincrement means not mention the null value you should must remove the null,..

update:1

string cmdText= "INSERT INTO inf(name, surname) VALUES (@name, @surname)";
MySqlCommand cmd = new MySqlCommand(cmdText, mcon);
cmd.Parameters.AddWithValue("@name", TextBox1.Text);
cmd.Parameters.AddWithValue("@surname", TextBox2.Text);
cmd.ExecuteNonQuery();

edited Apr 8, 2014 at 12:21

answered Apr 8, 2014 at 12:07

jmail

6,1503 gold badges23 silver badges35 bronze badges

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

3 Comments

Asım Gündüz Over a year ago

when I do that I get this error " Column 'name' cannot be null"

Asım Gündüz Over a year ago

Hi, I'm trying to do this but It seems that I am not able to use addwithValue method.. I don't have it .. I don't know why

Asım Gündüz Over a year ago

in the tutorials I saw on the web, that method is being used and it works perfectly but when I type in parameters. I have Add not AddwithValue

David · Accepted Answer · 2014-04-08 12:03:19Z

3

id is the primary key and it's on autoIncrement

Then don't try to insert an id. Especially don't try to insert a null value into a primary key. Since you're only inserting data into two of the table's columns, specify those columns:

INSERT INTO inf (name, surname) VALUES (@name, @surname)

answered Apr 8, 2014 at 12:03

David

221k42 gold badges245 silver badges337 bronze badges

4 Comments

Asım Gündüz Over a year ago

when I do that I get this error " Column 'name' cannot be null"

David Over a year ago

@user3456351: What are the values being inserted? Does txtname.Text have a value? Since this is MySQL, are the parameter markers different? Maybe it should be ?name instead of @name?

Asım Gündüz Over a year ago

I don't know,let me try

David Over a year ago

@user3456351: "Didn't work" isn't very specific. It seems like there's some information you're not sharing with us. Can you update the question with the table definition? What happens when you attempt this query directly instead of through code? First define a working query, then add it to the application.

Collectives™ on Stack Overflow

Insert into mysql table using c#

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related