MySql Insert what am I doing wrong?

[Ivan Sammut]

Hi,

I am using a Mysql Database with c# and I am trying to insert a record in
my table. My code is as follows.

MySqlConnection mySqlConnection = new MySqlConnection("Network
Address=localhost;Initial Catalog='test'; User
Name='root';Password='tottenham'");
//
MySqlCommand mySqlCommand = new MySqlCommand("",mySqlConnection);
mySqlCommand.CommandText = "insert into users (login,name,password) values
(@sLogin,@sName,@sPassword)";
mySqlCommand.Parameters.Add("@sLogin",MySqlDbType.VarChar,45);
mySqlCommand.Parameters.Add("@sName",MySqlDbType.VarChar,150);
mySqlCommand.Parameters.Add("@sPassword",MySqlDbType.VarChar,15);
//
mySqlCommand.Parameters["@sLogin"].Value = "ivan-edp";
mySqlCommand.Parameters["@sName"].Value = "Ivan";
mySqlCommand.Parameters["@sPassword"].Value = "abc";
//
mySqlConnection.Open();
mySqlCommand.ExecuteNonQuery();
mySqlConnection.Close();

But somehow the value I am passing in the paramters is not going and in the
table I am always ending with a record filled with null values.

Any idea?
Ivan

 

[Mark Rae]

MySqlConnection mySqlConnection = new MySqlConnection("Network
Address=localhost;Initial Catalog='test'; User
Name='root';Password='tottenham'");

I hope that's not your real password - this is a public newsgroup, you
know...

MySqlCommand mySqlCommand = new MySqlCommand("",mySqlConnection);
mySqlCommand.CommandText = "insert into users (login,name,password) values
(@sLogin,@sName,@sPassword)";
mySqlCommand.Parameters.Add("@sLogin",MySqlDbType.VarChar,45);
mySqlCommand.Parameters.Add("@sName",MySqlDbType.VarChar,150);
mySqlCommand.Parameters.Add("@sPassword",MySqlDbType.VarChar,15);
//
mySqlCommand.Parameters["@sLogin"].Value = "ivan-edp";
mySqlCommand.Parameters["@sName"].Value = "Ivan";
mySqlCommand.Parameters["@sPassword"].Value = "abc";
//
mySqlConnection.Open();
mySqlCommand.ExecuteNonQuery();
mySqlConnection.Close();

But somehow the value I am passing in the paramters is not going and in
the
table I am always ending with a record filled with null values.

Any idea?

1) ExecuteNonQuery() returns an int representing the number of records
affected by the SQL - what does it return in your case?

2) Replace your parameters temporarily with hard-coded values e.g. "insert
into users (login,name,password) values ('ivan-edp','Ivan','abc')"; - what
happens then?

3) Are you getting any errors?

4) Amend your table definition so that the three fields in question are not
nullable - what happens then?

5) Run your SQL statement against MySQL directly through your front-end
software (e.g. Navicat) - what happens then?

 

[Colin Stutley]

Can the MySql provider take named parameters?

Try replacing your parameter usage with ordinal positions and see what
happens.

- Colin

 

[Hans Kesting]

Hi,

I am using a Mysql Database with c# and I am trying to insert a record in
my table. My code is as follows.

MySqlConnection mySqlConnection = new MySqlConnection("Network
Address=localhost;Initial Catalog='test'; User
Name='root';Password='tottenham'");
//
MySqlCommand mySqlCommand = new MySqlCommand("",mySqlConnection);
mySqlCommand.CommandText = "insert into users (login,name,password) values
(@sLogin,@sName,@sPassword)";
mySqlCommand.Parameters.Add("@sLogin",MySqlDbType.VarChar,45);
mySqlCommand.Parameters.Add("@sName",MySqlDbType.VarChar,150);
mySqlCommand.Parameters.Add("@sPassword",MySqlDbType.VarChar,15);
//
mySqlCommand.Parameters["@sLogin"].Value = "ivan-edp";
mySqlCommand.Parameters["@sName"].Value = "Ivan";
mySqlCommand.Parameters["@sPassword"].Value = "abc";
//
mySqlConnection.Open();
mySqlCommand.ExecuteNonQuery();
mySqlConnection.Close();

But somehow the value I am passing in the paramters is not going and in the
table I am always ending with a record filled with null values.

Any idea?
Ivan

Are you sure you need a "@" in front of the parameter name? I had to
use a "?" instead to get parameters to work for MySql ...


Hans Kesting