How I can Insert Data in the MySQL Database?
Solution 1
You should simply execute the command
....
MySqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES ('tom')";
connection.Open();
command.ExecuteNonQuery();
....
I suppose that mitarbeiter
is the real value that should be set in the database.
If this is the case remember to use parameters to insert/update your data
MySqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES (?name)";
command.Parameters.AddWithValue("?name", mitarbeiter);
connection.Open();
command.ExecuteNonQuery();
Solution 2
You forgot to execute the command by calling command.ExecuteNonQuery(). This is how I would typically do it:
public string CreateEntry(string connectionString, string valueToInsert)
{
var stringToReturn = "";
try
{
using(var connection = new MySqlConnection(connectionString))
{
//Open connection
connection.Open();
//Compose query using sql parameters
var sqlCommand = "INSERT INTO table_name (field_name) VALUES (@valueToInsert)";
//Create mysql command and pass sql query
using(var command = new MySqlCommand(sqlCommand, connection))
{
command.Parameters.AddWithValue("@valueToInsert", valueToInsert);
command.ExecuteNonQuery();
}
stringToReturn ="Success Message";
}
}
catch(exception ex)
{
stringToReturn = "Error Message: " + ex.Message;
}
return stringToReturn;
}
There are a few key things to keep in mind:
- Wrap disposable objects with a using. In the case of MySqlConnection, it will properly close and dispose the connection when its out of scope.
- Use SQL parameters when passing values inside your query. This will avoid SQL injection and its much more easier to maintain.
- Personally, I like to have one exit point in a function. In this example, the "stringToReturn" variable holds the value to return once the function is done executing both successfully or in case of a failure.
Solution 3
To do a Insert / Update / Delete u should add
connection.Open();
command.ExecuteNonQuery();
For select ()to show data from database use:
connection.Open();
command.ExecuteReader();
Solution 4
{
string MyConnection2 = "datasource=localhost;port=3306;username=root;password=1234";
string Query = "insert into DBname.TableName(id,Name,First_Name,Age,Address) values('" +this.IdTextBox.Text+ "','" +this.NameTextBox.Text+ "','" +this.FirstnameTextBox.Text+ "','" +this.AgeTextBox.Text+ "','" +this.AddressTextBox.Text+ "');";
MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);
MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
MySqlDataReader MyReader2;
MyConn2.Open();
MyReader2 = MyCommand2.ExecuteReader();
MessageBox.Show("Save Data");
while (MyReader2.Read())
{
}
MyConn2.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Solution 5
You are not executing the command use SqlCommand.ExecuteNonQuery
try
{
MySqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES ('tom')";
connection.Open();
command.ExecuteNonQuery();
return "Mitarbeiter wurde angelegt";
}
catch (Exception ex)
{
return ex.Message;
}
finally
{
connection.Close();
}
Comments
-
Tarasov almost 2 years
I have a ASP.NET Application and a MySQL Database. I want write a Class to insert,delete and show the Data from the database. I have a Connection to the Database but I can't insert data in the database.
My Class insert method:
public string CreateEntry(string Connectionstring, string mitarbeiter) { connection = new MySqlConnection(Connectionstring); try { var command = connection.CreateCommand(); command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES ('tom')"; connection.Open(); return "Mitarbeiter wurde angelegt"; } catch (Exception ex) { return ex.Message; } finally { connection.Close(); } }
The
Connectionstring
is correct. I don't get a error but there is no data in the database.My tablename: tb_mitarbeiter columns: ID and Vorname