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