Problem on Creating table on user defined database for visual C#

Question

1.00/5 (1 vote)

See more:

I'm having a problem regarding creating new table based on user define name for database. I know
Here is the code I try modified based on you given example. the problem I'm facing now is the conn is not open. I insert open connection but still the same error appear. Before I made some modification on connection, I'm getting error that database is not selected. Any advise?

private void btnNew_Click(object sender, EventArgs e)
        {
            SchemaName schemaForm = new SchemaName();
            frmMetCon form2 = new frmMetCon();

            if (schemaForm.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
               
              string connStr = "datasource=localhost;port=3306;username=root;password=root;";
              string database = schemaForm.getData();
              MySqlConnection conn = new MySqlConnection(connStr);
                try
                 {
                   MySqlCommand command = conn.CreateCommand();
                   conn.Open();
                   command.CommandText = "DROP DATABASE IF EXISTS " + database;
                   command.ExecuteNonQuery();
                   command.CommandText = "CREATE DATABASE " + database;
                   command.ExecuteNonQuery();
                   conn.Close();
                   command.ExecuteNonQuery();
                   conn.Close();
                   conn.ConnectionString = connStr;
                   MySqlCommand comm = conn.CreateCommand();
                   conn.Open();
                   comm.CommandText = "CREATE TABLE Metabolites(MetaboliteID VARCHAR(10) NOT NULL, Metabolite_Name VARCHAR(45) NULL, ReactionTime INT NULL, PRIMARY KEY (MetaboliteID)";
                   comm.ExecuteNonQuery();
                   this.Hide();
                   form2.Show();   
                 }
                 catch (Exception ex)
                 {
                   MessageBox.Show(ex.Message); 
                 }
                 conn.Close();
           
             }
        }
    }

Posted 13-Oct-13 2:42am

arave0521

Add a Solution

2 solutions

Solution 1

Look at your code: What part of this looks wrong?

C#

command.CommandText = "CREATE DATABASE " + database;
command.ExecuteNonQuery();
conn.Close();
command.ExecuteNonQuery();
conn.Close();

0) Why are you executing the same command twice? To be sure, to be sure?
1) What do you suppose is going to happen when you try to execute it the second time, having just closed the connection?

Posted 13-Oct-13 2:46am

OriginalGriff

Comments

arave0521 13-Oct-13 8:53am

Ohh, my mistake. Suppose the both of the
command.ExecuteNonQuery();
conn.Close();
are in comments mode.
But still the database is not selected.
i try modified using add parameter value the error said that fatal error occur. here is the code

try
{
MySqlCommand command = conn.CreateCommand();
conn.Open();
command.CommandText = "DROP DATABASE IF EXISTS " + database;
command.ExecuteNonQuery();
command.CommandText = "CREATE DATABASE " + database;
command.ExecuteNonQuery();
conn.Close();
conn.ConnectionString = connStr;
MySqlCommand comm = conn.CreateCommand();
conn.Open();
comm.CommandText = "CREATE TABLE @schema.Metabolites(MetaboliteID VARCHAR(10) NOT NULL, Metabolite_Name VARCHAR(45) NULL, ReactionTime INT NULL, PRIMARY KEY (MetaboliteID)";
comm.Parameters.AddWithValue("@schema", database);
comm.ExecuteNonQuery();
this.Hide();
form2.Show();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
conn.Close();

OriginalGriff 13-Oct-13 9:14am

What message does it show in the MessageBox?

OriginalGriff 13-Oct-13 9:18am

There are a number of things that look very wrong here - like you have assembled this from bits and pieces without thinking about it.
Your connection string for example: if doesn't contain a database, so how is MySql supposed to know which database you want to create the table in?

OriginalGriff 13-Oct-13 9:27am

Oh, and you're missing a close bracket in your CREATE TABLE statement.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

arave0521 · Accepted Answer · 2013-10-13T20:25:00

Thanks for help me out. I solved it myself.

private void btnNew_Click(object sender, EventArgs e)
{
    SchemaName schemaForm = new SchemaName();
    frmMetCon form2 = new frmMetCon();

    if (schemaForm.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {

      string connStr = "datasource=localhost;port=3306;username=root;password=root;";
      string database = schemaForm.getData();
      MySqlConnection conn = new MySqlConnection(connStr);
        try
         {
           MySqlCommand command = conn.CreateCommand();
           conn.Open();
           command.CommandText = "DROP DATABASE IF EXISTS " + database;
           command.ExecuteNonQuery();
           command.CommandText = "CREATE DATABASE " + database;
           command.ExecuteNonQuery();
           command.CommandText = "CREATE TABLE " +database+
                                 ".Metabolites("+
                                 "MetaboliteID VARCHAR(10) NOT NULL,"+
                                 "Metabolite_Name VARCHAR(45) NULL," +
                                 "ReactionTime INT NULL, " +
                                 "PRIMARY KEY (MetaboliteID));";

           command.ExecuteNonQuery();
           this.Hide();
           form2.Show();
         }
         catch (Exception ex)
         {
           MessageBox.Show(ex.Message);
         }
         conn.Close();

     }
}