C# MySQL Data Insertion Problem

  • (2 Pages)
  • +
  • 1
  • 2

21 Replies - 10783 Views - Last Post: 05 March 2011 - 06:02 AM Rate Topic: -----

#1 cheenz87  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 25-July 07

C# MySQL Data Insertion Problem

Posted 22 February 2011 - 09:02 AM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: C# MySQL Data Insertion Problem

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9287
  • View blog
  • Posts: 34,811
  • Joined: 12-June 08

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 09:14 AM

Try replacing the '?' with '@' for your parameters.
Was This Post Helpful? 1
  • +
  • -

#3 cheenz87  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 25-July 07

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 09:23 AM

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

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9287
  • View blog
  • Posts: 34,811
  • Joined: 12-June 08

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 09:25 AM

Better.. comment out this line "cmd.BeginExecuteNonQuery();"
Was This Post Helpful? 1
  • +
  • -

#5 cheenz87  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 25-July 07

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 09:50 AM

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 ...
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9287
  • View blog
  • Posts: 34,811
  • Joined: 12-June 08

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 09:51 AM

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

Was This Post Helpful? 3
  • +
  • -

#7 cheenz87  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 25-July 07

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 10:05 AM

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 :rockon: :smartass:
Was This Post Helpful? 0
  • +
  • -

#8 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4498
  • View blog
  • Posts: 7,850
  • Joined: 08-June 10

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 10:10 AM

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.
Was This Post Helpful? 2
  • +
  • -

#9 cheenz87  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 25-July 07

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 10:20 AM

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 !!
Was This Post Helpful? 0
  • +
  • -

#10 m-e-g-a-z  Icon User is offline

  • Winning
  • member icon


Reputation: 496
  • View blog
  • Posts: 1,453
  • Joined: 19-October 09

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 10:32 AM

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.
Was This Post Helpful? 1
  • +
  • -

#11 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4498
  • View blog
  • Posts: 7,850
  • Joined: 08-June 10

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 10:48 AM

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

Was This Post Helpful? 1
  • +
  • -

#12 m-e-g-a-z  Icon User is offline

  • Winning
  • member icon


Reputation: 496
  • View blog
  • Posts: 1,453
  • Joined: 19-October 09

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 10:55 AM

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.
Was This Post Helpful? 0
  • +
  • -

#13 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4498
  • View blog
  • Posts: 7,850
  • Joined: 08-June 10

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 11:01 AM

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.
Was This Post Helpful? 1
  • +
  • -

#14 m-e-g-a-z  Icon User is offline

  • Winning
  • member icon


Reputation: 496
  • View blog
  • Posts: 1,453
  • Joined: 19-October 09

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 11:09 AM

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.
Was This Post Helpful? 1
  • +
  • -

#15 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4498
  • View blog
  • Posts: 7,850
  • Joined: 08-June 10

Re: C# MySQL Data Insertion Problem

Posted 22 February 2011 - 11:13 AM

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.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2