I am using mysql server 2012 and visual studio 2012 C#, i created a database and i can read the data from my database.
The problem is that i cannot insert data into my database. Although the return value of the execution is 1, i dont see any changes in the table. I executed the same query inside the database explorer and it works.

Anyone can help me??

CODE:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace database
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

    SqlConnection con;


    private void Form1_Load(object sender, EventArgs e)
    {
        con = new SqlConnection();
        con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\myworkers.mdf;Integrated Security=True;User Instance=True";
        con.Open();
        MessageBox.Show("OPEN!");

        SqlCommand thiscommand = con.CreateCommand();
        thiscommand.CommandText = "SELECT * FROM player_info";          /*The data was shown correctly*/
        SqlDataReader thisReader = thiscommand.ExecuteReader();
        while (thisReader.Read()) 
        {

            MessageBox.Show(thisReader["player_name"].ToString());

        }
        thisReader.Close();  
    }

    private void label1_Click(object sender, EventArgs e)
    {

    }

    private void button1_Click(object sender, EventArgs e)
    {


        int x;
        SqlCommand thiscommand = con.CreateCommand();
        thiscommand.CommandText = "INSERT INTO player_info(player_id, player_name) VALUES (1000, 'chene')";


     x =  thiscommand.ExecuteNonQuery();     /*The return value is 1, but i dont see any changes inside the table. :(  */
     MessageBox.Show(x.ToString());
        con.Close();

    }
}

}

*Hi... i can help you... your error is because you are using the SQL SERVER connector (SQLConnection),
you need to change to MySQL connector (MYSQLCONNECTOR)...
*
1° - The Connector is free at http://www.mysql.com/downloads/connector/net/

2° - After the installation, you will need to get the reference at Visual Studio -> Add Reference -> .NET -> Mysql.Data

3° - Change all (SQL_______ ex: SQLCONNECTION , SQLDATAREADER) to (MYSQL_______ ex: MYSQLCONNECTION, MYSQLDATAREADER)

IF WORKS, TELL US...

I am downloading that. I will let you know asap.
But i have a question, what is the difference between sql server and mysql connector?
When i used sql server i do things like this way : add new item, dataset, and then i created tables, add rows, keys, etc.
Which seems perfectly make sense to me.

I have couples of questions.
So do i create the database in VS C# as before? Add new item, and dataset. etc? My connection cannot establish after i changed all sql to mysql.
And do i have to add reference everytime?(seems like yes), if yes, then it doesnt really make sense, because it must have a more elegant way to do so.

well, the properties are the same (SQLConnection , MYSQLConnection) , (SqlDataReader, MysqlDataReader) and so on, but...

example:

        private void button1_Click(object sender, System.EventArgs e)
        {
            string MyConString = "SERVER=localhost;DATABASE=mydatabase;UID=testuser;PASSWORD=testpassword;";
            MySqlConnection connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader Reader;
            command.CommandText = "select * from mycustomers";
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                //DO SAMETHING
                listBox1.Items.Add(thisrow);
            }
            connection.Close();
        } `Inline Code Example Here`  `Inline Code Example Here` 

We have specific connections for:
- SQLServer (SqlConnection)
- Mysql (MysqlConnection)
- Oracle (OracleConnection) and so on...

you just need install each connection software...

for .NET external components like Mysql, Oracle, Firebird, you have to install the software and add its reference to project..

Why is not necessary for the SqlServer?
beacouse SQLServer is integrated at .NET Framework...

if you have a MSN menssenger account, add me:
alessandroalves_mtv@hotmail.com

I see! Thank you so much for your time!
But if i really want to stick with sqlServer, do you see why my code in the first place didnt work?
I added you in MSN btw.

NVM, I find out using mysql is much more easier than sqlserver.
the problem solved,thanks for the helps !

I have always found I mus specify the database and owner when using SQL Server as in:
INSERT INTO MYDB.MYOWNER.MYTABLE (mythis, mythat) values ('thisvalue, 'thatvalue')

Edited 4 Years Ago by thines01: code formatting

Maybe you dont insert into the right database. If you use this connection string:

"Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\myworkers.mdf;Integrated Security=True;User Instance=True";

And using "|DataDirectory| inside of it, this means you are inserting into database, that is attached into your project.
And you will not see any changes made.

SOLUTION: Specify the full path to the database.

Hope it helps.
bye

This article has been dead for over six months. Start a new discussion instead.