[cheenz87]

Hello Members ,
I m developing a C# windows application , whose backend is MySQL.
All I m trying to do is inserting a data from C# windows form .. here is my coding ...

 private void button1_Click(object sender, EventArgs e)
        {

            try //begin try block
            {



                string cmdstring = "insert into vehicle(own,addr,carmanu,bodytype,cmake,color,cmodel,platen,rcn,cmmnts,upn,passtype,cbalance) values(?own,?addr,?carmanu,?bodytype,?cmake,?color,?cmodel,?platen,?rcn,?cmmnts,?upn,?passtype,?cbalance)";
                MySqlConnection conn = new MySqlConnection("database=test;server=localhost;username=root;password=cheenu;");
                MySqlCommand cmd = new MySqlCommand(cmdstring, conn);
                conn.Open();


                cmd.CommandType = CommandType.Text;

                cmd.Parameters.Add("?own ", MySqlDbType.VarChar).Value = textBox1.Text;
                cmd.Parameters.Add("?addr", MySqlDbType.VarChar).Value = textBox2.Text;
               cmd.Parameters.Add("?carmanu", MySqlDbType.VarChar).Value  = listBox1.SelectedItem;
                cmd.Parameters.Add("?bodytype", MySqlDbType.VarChar).Value = textBox8.Text;
                cmd.Parameters.Add("?cmake", MySqlDbType.VarChar).Value  = listBox2.SelectedItem;
                cmd.Parameters.Add("?color", MySqlDbType.VarChar).Value = textBox3.Text;
                cmd.Parameters.Add("?cmodel", MySqlDbType.VarChar).Value = textBox10.Text;
                cmd.Parameters.Add("?platen", MySqlDbType.VarChar).Value = textBox4.Text;
                cmd.Parameters.Add("?rcn", MySqlDbType.VarChar).Value = textBox6.Text;
                cmd.Parameters.Add("?cmmnts", MySqlDbType.VarChar).Value = textBox5.Text;
                cmd.Parameters.Add("?upn", MySqlDbType.VarChar).Value = textBox7.Text;
               cmd.Parameters.Add("?passtype", MySqlDbType.VarChar).Value  = listBox3.SelectedItem;
                cmd.Parameters.Add("?cbalance", MySqlDbType.Double).Value = textBox9.Text;


                cmd.BeginExecuteNonQuery();
                int result=cmd.ExecuteNonQuery();
                MessageBox.Show("Data Saved");
                conn.Close();

                 



            }
            catch (Exception ex)
            {
               label13.Text =ex.ToString()   ;
            }



        }

I m getting the error of MySql.Data.MySqlClient.MySqlException: Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException: Parameter '?own' must be defined.
at MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName)
at MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet)
at MySql.Data.MySqlClient.Statement.BindParameters()
at MySql.Data.MySqlClient.PreparableStatement.Execute()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(Commandbehavior behavior)
--- End of inner exception stack trace ---
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(Commandbehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at FS.addvehicle.button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\HP_Owner\My Documents\Visual Studio 2008\FS\FS\addvehicle.cs:line 54 ..... I have also included "Allow user variables=true" and also updated the MySQL .Net Connector , but still this problem is persisting.

[modi123_1]

Try replacing the '?' with '@' for your parameters.

[cheenz87]

I have replaced ? with @ , my error has changed to this

MySql.Data.MySqlClient.MySqlException: There is already an open DataReader associated with this Connection which must be closed first.
at MySql.Data.MySqlClient.MySqlCommand.CheckState()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(Commandbehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at FS.addvehicle.button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\HP_Owner\My Documents\Visual Studio 2008\FS\FS\addvehicle.cs:line 54

[modi123_1]

Better.. comment out this line "cmd.BeginExecuteNonQuery();"

[cheenz87]

error

MySql.Data.MySqlClient.MySqlException: Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException: Parameter '@own' must be defined.
at MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName)
at MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet)
at MySql.Data.MySqlClient.Statement.BindParameters()
at MySql.Data.MySqlClient.PreparableStatement.Execute()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(Commandbehavior behavior)
--- End of inner exception stack trace ---
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(Commandbehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at FS.addvehicle.button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\HP_Owner\My Documents\Visual Studio 2008\FS\FS\addvehicle.cs:line 54




own , addr etc are my database field names ...

[modi123_1]

What does your code look like now?

Edit: you have a space after "own" when adding the params

Quote

cmd.Parameters.Add("?own ", MySq

This post has been edited by modi123_1: 22 February 2011 - 09:52 AM

[cheenz87]

my code

  private void button1_Click(object sender, EventArgs e)
        {

            try //begin try block
            {



                string cmdstring = "insert into vehicle(own,addr,carmanu,bodytype,cmake,color,cmodel,platen,rcn,cmmnts,upn,passtype,cbalance) values(@own,@addr,@carmanu,@bodytype,@cmake,@color,@cmodel,@platen,@rcn,@cmmnts,@upn,@passtype,@cbalance)";
                MySqlConnection conn = new MySqlConnection("database=test;server=localhost;username=root;password=cheenu;");
                MySqlCommand cmd = new MySqlCommand(cmdstring, conn);
                conn.Open();


                cmd.CommandType = CommandType.Text;

                cmd.Parameters.Add("@own", MySqlDbType.VarChar).Value = textBox1.Text;
                cmd.Parameters.Add("@addr", MySqlDbType.VarChar).Value = textBox2.Text;
               cmd.Parameters.Add("@carmanu", MySqlDbType.VarChar).Value  = listBox1.SelectedItem;
                cmd.Parameters.Add("@bodytype", MySqlDbType.VarChar).Value = textBox8.Text;
                cmd.Parameters.Add("@cmake", MySqlDbType.VarChar).Value  = listBox2.SelectedItem;
                cmd.Parameters.Add("@color", MySqlDbType.VarChar).Value = textBox3.Text;
                cmd.Parameters.Add("@cmodel", MySqlDbType.VarChar).Value = textBox10.Text;
                cmd.Parameters.Add("@platen", MySqlDbType.VarChar).Value = textBox4.Text;
                cmd.Parameters.Add("@rcn", MySqlDbType.VarChar).Value = textBox6.Text;
                cmd.Parameters.Add("@cmmnts", MySqlDbType.VarChar).Value = textBox5.Text;
                cmd.Parameters.Add("@upn", MySqlDbType.VarChar).Value = textBox7.Text;
               cmd.Parameters.Add("@passtype", MySqlDbType.VarChar).Value  = listBox3.SelectedItem;
                cmd.Parameters.Add("@cbalance", MySqlDbType.Double).Value = textBox9.Text;


             //   cmd.BeginExecuteNonQuery();
                int result=cmd.ExecuteNonQuery();
                MessageBox.Show("Data Saved");
                conn.Close();

                 



            }
            catch (Exception ex)
            {
               textBox11.Text =ex.ToString()   ;
            }



        }

I removed the space that you pointed out .... and the data is getting saved .... Whooaa .. a space can make so much of difference , hope i dont get any errors now... and A BIG THANKS TO YOU

[Curtis Rutland]

You can thank him by clicking the green "+" button beneath his helpful posts.

For further reference, I think you should learn a bit about debugging. This is a situation that every developer will face at one point or another, and we all have to learn how to sit there and figure it out. Try stuff. You won't break anything. If you're worried about losing a mostly working set of code, copy it and comment out the original. But debugging is a huge, necessary skill that must be practiced.

[cheenz87]

yes i have clicked the 'plus' sign ....oh yes , i should definitely .. but this error was seriously mind wretching , and came out to be a "white space" error.
Thanks to You and Your forum !!

[m-e-g-a-z]

Your try block should be as minimal as possible since you only want to check if you can connect to the database. You can then carry out other operations OUTSIDE the try block.

[Curtis Rutland]

Totally agree with m-e-g-a-z. Before I switched to LINQ to SQL, my DB code looked a lot like this:

//create strings
string connStr = "--snip--";
string cmdStr = "select field1, field2 from table1 where field3 = @field3";
//create objects
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(cmdStr, conn);
//add parameters
cmd.Parameters.AddWithValue("@field3", "someValue");
//open and execute
try {
    conn.Open();
    cmd.ExecuteNonQuery();
}
//handle SqlExceptions
catch (SqlException ex) {
    //handle Exceptions
}
//handle generic exceptions
catch (Exception ex) {
    //handle exceptions
}
//no matter what, make sure the connection is closed
finally {
    if (conn.State != ConnectionState.Closed)
        conn.Close();
}

Notice that you don't Open until you're about to do your operation. Also, you close in a finally block, because we want that done no matter what, even if there is an exception.

This post has been edited by insertAlias: 22 February 2011 - 03:45 PM
Reason for edit:: removing pastebin and updating code

[m-e-g-a-z]

Not only that but making a database connection within a button1_Click method? Very bad design.

What would be a good design is if the code was split up and a generic singleton design was used for database connectivity so that there are not multiple connections occurring thus saving a heck alot of problems in the future.

[Curtis Rutland]

That's a good idea, but it might be a bit advanced for the OP right now. It's good to learn how to actually do database stuff before we start getting into real design patterns.

Also, I'm not sure a singleton is the best idea for this. There's theoretically nothing wrong with multiple concurrent DB connections, assuming that the DB handles locking properly. I don't know about MySql, but MSSQL handles it pretty well.

[m-e-g-a-z]

I agree with you , although many people read forum posts and learn from posts such as these.

I was just saying that it would be better to have a class to deal with database connectivity.

In general, why risk having 3 connections to the database when you only need one?

I was thinking in terms of that.

[Curtis Rutland]

True, true.

If we're looking to encapsulate database functionality though, I'd just say use an ORM like Entity Framework 4. It's spectacular, and you don't have to know a lick of SQL code. You never see an open/close statement. You just have collections of objects mapped to your database tables/views/objects.