Binding fails due to square brackets -- thinks is it MSSQL
Posted by: Jeff Woods
Date: May 02, 2014 01:12PM

Hello All,

Driving myself nuts trying to solve this. I am working with a commercial derivative of a BaseDataBoundControl (DayPilot), and can easily get it to bind to and work with a local MSSQL Express table.

When I try to bind to a remote MySQL database, the connection is fine, but the syntax being submitted includes square brackets around the fields and tables:

select * from [reservations]

Obviously, MySql doesn't like this. How can I get the databinding to submit its queries in MySQL format rather than TSQL?

Here's how I am doing this. From the ASPX:

<DayPilot:DayPilotCalendar ID="ReservationsPilot" runat="server"

<!--.... lots of custom markup for DayPilot left out for clarity -->

</DayPilot:DayPilotCalendar>


web.config relevant lines:

<connectionStrings>
</connectionStrings>

<system.data>
<DbProviderFactories>
<remove name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.8.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>

<assemblies>
<add assembly="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />
<add assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
<add assembly="System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />
<add assembly="System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />
<add assembly="MySql.Data, Version=6.8.3.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D" />
<add assembly="MySql.Web, Version=6.8.3.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D" />
</assemblies>

I am starting with NO connection strings, so that when I do the binding in the designer, it creates them from there.

So, I click on the upper right arrow of the Daypilot control, select "New Data Source" from the Choose Data Source drop down.

I am presented with the standard Data Source Configuration Wizard, asking me to choose a Data Source Type, and MySQL is NOT one of the options, which are:

Access, Database, Entity, LINQ, Object, Site Map, XML

I am not using EF or Access, so the only one I can see that would match is "Database". I do that, and specify an ID for the data source: ResPilotData

When I click OK, I am asked to Choose Data Connection. I click on "New Connection" (since I have none) and reach the standard "Add Connection" dialog. I click on "Change" to change the Data Source from Microsoft SQL Server (SqlClient) to MySQL Database. I then add my connection data, and click "Test Connection", which SUCCEEDS. I choose my database name via the drop-down (and all of my schemas are listed -- I am DEFINITELY reaching the remote database. All is well thus far).

I say "Yes" to saving the connection string in the web.config file.

Here's where the problem comes in. The next screen is the "Configure the Select Statement" dialog. When I "specify columns from a table or view" and choose the proper table from the drop-down, the "Select Statement" at the bottom is:

SELECT * FROM [reservations]

...and we all know that MySQL doesn't like those pesky square brackets.

Here's what it looks like at the end:

https://www.flickr.com/photos/66600175@N08/14087651841/

Why isn't the MySQL Connector.NET 6.8.3 being used to compose this test query, and how can I change the affinity here such that the query strings are correct?

Thanks for any help anyone can offer.

Jeff Woods
Reading, PA

Options: ReplyQuote


Subject
Written By
Posted
Binding fails due to square brackets -- thinks is it MSSQL
May 02, 2014 01:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.