insert statement adding null values

Not Me

Here is my code

string sqlInsert = "Insert into status (name) values (@name)";
            string connStr = "DSN=StatusChecker;User Id=user;Password=mypass;";

            OdbcConnection odbcCon = new OdbcConnection(connStr);
            try
            {
                odbcCon.Open();
                OdbcCommand cmdInsert = new OdbcCommand(sqlInsert, odbcCon);
                cmdInsert.Parameters.AddWithValue("@name",textboxUserName.Text);
                cmdInsert.ExecuteNonQuery();
                cmdInsert.Parameters.Clear();
                cmdInsert.Dispose();
                cmdInsert = null;


            }
            catch(Exception ex)
            {
                throw new Exception(ex.ToString(), ex);

            }
            finally
            {
                odbcCon.Close();
            }

i am using mysql and if i do a select * form status at the mysql prompt i get

mysql> select * from statu
    -> ;
+----------+------+
| StatusID | name |
+----------+------+
|        1 | NULL|
|        2 | NULL |
|        3 | NULL |
|        4 | NULL |
|        5 | NULL |
|        6 | NULL |
|        7 | NULL |
|        8 | NULL |
|        9 | NULL |
|       10 | NULL |
|       11 | NULL |
|       12 | NULL |
|       13 | NULL |
|       14 | NULL |

I have check to make sure the text box does have text in it. I don't see where the problem is?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kirtan Patel17y ago
Accepted answer
Friend ,

Here is Small Code For what you are trying to do ( using Global or Local Variable is your choice :) and you should close connection after every use :)

here when you use

using()
{

}

it will automatically Close connection :) without writing con.Close()

using MySql.Data.MySqlClient;

//Form Load Event
private void Form1_Load(object sender, EventArgs e)
        {
            ShowData();
        }

//Show Data Function to Show Data In DatagridView
        public void ShowData()
        {
            using (MySqlConnection con = new MySqlConnection("server=localhost;user id=root;;password=12345;database=test1"))
            {
                con.Open();
                MySqlCommand comm = new MySqlCommand("select * from TestTable",con);
                DataTable dt = new DataTable();
                MySqlDataAdapter da = new MySqlDataAdapter(comm);
                da.Fill(dt);
                dataGridView1.DataSource = dt;
            }
        }
//Code For Inserting Data and Showing in DataGridView

        private void btnInsert_Click(object sender, EventArgs e)
        {
            using (MySqlConnection con = new MySqlConnection("server=localhost;user id=root;password=12345;database=test1"))
            {
                con.Open();
                MySqlCommand comm = new MySqlCommand("insert into TestTable(name) values(@name)", con);
                comm.Parameters.AddWithValue("@name", txtName.Text.Trim());
                comm.ExecuteNonQuery();
            }
            //Show New Inserted Data In Grid
            ShowData();
        }

0
Not Me17y ago
WOW

not only did that work it was easy to figure you. Thanks a lot. I don't do a lot of coding so it takes me a long time to remember stuff like connection to DB. What is the best practice for setting some like this up. Below is my code. should i be declaring all that stuff twice or should i make it global? Also should i leave the DB connection open until i close the app or open and close it when i need to select/insert/update/drop.?

private void frmAdmin_Load(object sender, EventArgs e)
        {

            MySqlConnection dbcon = new MySqlConnection();
            MySqlDataAdapter dbdata = new MySqlDataAdapter();
            dbcon.ConnectionString = "server=localhost;database=statuschecker;User Id=me;Password=pass;";
            dbcon.Open();

            MySqlCommand command = dbcon.CreateCommand();
            command.CommandText = "select * from status";
            dbdata.SelectCommand = command;
            DataSet dbdataset = new DataSet();
            dbdata.Fill(dbdataset, "status");

            datagridShowUsers.DataSource = dbdataset;
            datagridShowUsers.DataMember = "status";
            datagridShowUsers.Dock = DockStyle.Bottom;

            dbcon.Close();

        }

        private void buttonAddUser_Click(object sender, EventArgs e)
        {
            MySqlConnection dbcon = new MySqlConnection();
            MySqlDataAdapter dbdata = new MySqlDataAdapter();
            dbcon.ConnectionString = "server=localhost;database=statuschecker;User Id=me;Password=pass;";


            try
            {
                          dbcon.Open();

                MySqlCommand command = dbcon.CreateCommand();
                command.CommandText = "Insert into status (name) values (@name)";
                dbdata.InsertCommand = command;
                dbdata.InsertCommand.Parameters.AddWithValue("@name", textboxUserName.Text);
                dbdata.InsertCommand.ExecuteNonQuery();
                dbdata.InsertCommand.Parameters.Clear();
                dbdata.InsertCommand.Dispose();
                dbdata.InsertCommand = null;


            }
            catch(Exception ex)
            {
                throw new Exception(ex.ToString(), ex);

            }
            finally
            {
                dbcon.Close();
            }



        }
0
Kirtan Patel17y ago
Friend ,

you should use MySQL Connector to Connect to My sql Database instead of Odbc

http://dev.mysql.com/downloads/connector/net/6.1.html

Download it From here . :)

after downloading install it and Add reference to "MySql.Data" to your project

then you can use namesapce

Mysql.data.MySqlClient

now your coding will be very easy as you do in sql Server :)

dont forget to mark "Do you like answer" please it will give me some credits :)

and if you need further help i will help you :)
0