problem regarding user naming a schema database of MySQL in Visual C#

Question

0.00/5 (No votes)

See more:

Hello,

I'm having a problem regarding renaming a schema of MySQL in Visual C#. The idea is that when user debug the program, he needs to choose either create new project or open project. When user click a button of new project, the namingForm will pop up. User will enter the name (which has a rules later on naming it) and the name will be used as schema name. here is the code

C#

private void btnExit_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

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

            if (schemaForm.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
              string connStr = "datasource=localhost;port=3306;username=root;password=root;";
              MySqlConnection conn = new MySqlConnection(connStr);
                try
                 {
                   conn.Open();
                   MySqlCommand cmdDatabase = new MySqlCommand("CREATE SCHEMA @schema; ", conn);
                   cmdDatabase.Parameters.AddWithValue("@schema", schemaForm.getData());
                   MySqlDataReader myReader;
                   myReader = cmdDatabase.ExecuteReader();
                   myReader.Close();
                 }
                 catch (Exception ex)
                 {
                   MessageBox.Show(ex.Message); 
                 }
                 conn.Close();
           
             }
        }

Once executed, the error was pop up saying that you have error in your sql syntax

Posted 10-Oct-13 17:23pm

arave0521

Updated 10-Oct-13 17:37pm

Sushil Mate

v2

Add a Solution

Comments

Ron Beyer 10-Oct-13 23:43pm

what is the value of schemaForm.getData()? Have you looked at the entire SQL command just before it tries to execute? Why are you using ExecuteReader when you should be using ExecuteNonQuery()?

arave0521 10-Oct-13 23:50pm

The value of schemaForm.getData()is user define value which is I'm not stated. the code.
Here is the code of schemaForm

public partial class SchemaName : Form
{
private string data;

public SchemaName()
{
InitializeComponent();
}

private void btnCancel_Click(object sender, EventArgs e)
{
this.Hide();
}

private void btnOK_Click(object sender, EventArgs e)
{
data=txtDB.Text;
this.Hide();
}

public string getData()
{
return data;
}

}

The error given is that You have an error in your SQL syntax, check the manual that corresponds to your MYSQL server version for the right syntax to use near "user-defined-schemaName" at line 1

Thomas ktg 11-Oct-13 1:35am

May be you can try using Database instead of Schema for create syntax like
create database @schema;
I suspect the Schema keyword is not supported for the MySql version.

arave0521 11-Oct-13 9:21am

Schema is the right word. The only problem is I'm using a ExecuteReader(). I just solved it by using ExecuteNonQuery()

Ron Beyer 11-Oct-13 9:50am

CREATE SCHEMA and CREATE DATABASE are synonymous since I think version 5.02. CREATE SCHEMA is the depreciated one, CREATE DATABASE is the one you should use for future compatibility.

arave0521 11-Oct-13 9:20am

Thanks for the help. I'm using ExecuteNonQuery(). And it is working. Thanks. May I know the differences of ExecuteNonQuery() and ExecuteReader in your perspective point of view. I thought both are quite the same

Ron Beyer 11-Oct-13 9:47am

ExecuteNonQuery returns nothing, its executing something that is not expected to return a result (like an insert or delete statement, or in your case, a create schema statement). ExecuteReader returns a IDataReader that lets you enumerate through results. ExecuteScalar executes the command and returns the first column of the first row returned (useful for selecting a count of something or aggregate data in a single column).

arave0521 11-Oct-13 9:51am

thanks for the tips once again =)

1 solution

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)

Ron Beyer · Accepted Answer · 2013-10-11T03:48:00

Solution 1

Just so this doesn't remain unanswered...

The solution was to replace ExecuteReader with ExecuteNonQuery to run a query that does not return a result.

Posted 11-Oct-13 3:48am

Ron Beyer