Devart Forums

Posted: **Fri 15 Apr 2005 09:01**

I'm using MySQLDirect .NET 2.70. I have set QuotePrefix and QuoteSuffix to "`" to fix a problem about table names with spaces and special characters but it doesn't work.

It returns me an exception because it's not using prefixes correctly.

example:

You put this code
-->Insert command on Access (QuotePrefix and Quotesuffix = "[/]"):

And you have this Insert Command (tables could have spaces & special characters)
-->Insert Into [Table A]( [User Id], [User Name])

You put this code
-->Insert command on MySQL (QuotePrefix and Quotesuffix = "`"):

And MySQL seems not to work properly. It doesn't use prefix and suffix to contain table and colums names (I can't use spaces)
-->Insert Into Table A( User Id, User Name)

As you could see, it doesn't use prefixes to delimit table names and it causes an exception.

Maybe I'm not doing correctly. Do you have any idea?

Thanks

Posted: **Mon 18 Apr 2005 07:29**

MySQLDirect .NET automatically quotes entity names in components properties and in CommandBuilder.
Example:

Code: Select all

     // create table: create table `table 1` (`col 1` int)
     MySqlConnection connection = new MySqlConnection("host=localhost;database=test;user id=root;");
     connection.Open();
     MySqlDataAdapter adapter = new MySqlDataAdapter("select * from `table 1`", connection);
     MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
     commandBuilder.Quoted = true;
     commandBuilder.QuotePrefix = commandBuilder.QuoteSuffix = "`";
     MessageBox.Show(commandBuilder.GetInsertCommand().CommandText); // INSERT INTO `table 1`(`col 1`) VALUES (:p1)

Our provider does not change user queries, so you must use valid quoting in your queries.
Example:

Code: Select all

MySqlCommand command;
...
command.CommandText = "insert into `a b c`.`table1` values (...";

Note that in new version of MySQLDirect .NET we added support for quoting and unquoting names that consist of numbers only or contain quotes.

Posted: **Mon 18 Apr 2005 14:22**

Thanks Serious, the problem was I was not using code line:

Code: Select all

commandBuilder.Quoted = true;

Now it's ok.