MySqlDataSource Still Not Working

hamsterlegs · Post by **hamsterlegs** » Wed 09 Jul 2008 16:08

The MySqlDataSource still does not seem to work correctly in the latest build.

I drag the control onto an ASP.NET form, I can’t chose my existing configuration settings so have to use <%# directives in the tags.

Also, I can’t find much help on it and confess to be confused without any help or documentation. The content in your help pages for the Design Time Support for MySqlDataSource is not very helpful (ms-help://CoreLab.MySql/MyDirect/SqlDataSource.html). Is there any information about the various tabs? Specifically the Select Parameters tab.

I am disappointed that the functionality in this control which featured in the trial version (a few months ago) is now not included.

hamsterlegs · Post by **hamsterlegs** » Wed 09 Jul 2008 16:33

Also, I think it would be very useful to have some instructions how to setup this control. For example, I have a MySQL database I just want to setup a MySqlDataSource control to use that database and use the GridView control with the MySqlDataSource to allow Editing and Deleting of fields.

This was very intuitive with previous versions and worked much like Microsoft's SQLDataSource but now I am getting errors in the GridView control when I press Update and Delete, so I'm guess the Update and Delete strings are not setup correctly. I'd really appreciate some instructions how to use this now.

AndreyR · Post by **AndreyR** » Tue 15 Jul 2008 10:56

Hello, Hamsterlegs.

Could you please figure out what kind of problems have you encountered when working with configuration settings?
For proper work of Update, Delete and Insert commands you should create these commands.
For this, go to the MySqlDataSource Editor (use the smart tag of MySqlDataSource -> Configure Data Source),
provide valid select command or select TableDirect and choose one of the tables,
go to Command Generator tab and press Generate Commands button.

Regards, Andrey.

hamsterlegs · Post by **hamsterlegs** » Tue 22 Jul 2008 13:58

Hi Andrey,

This simple scenario causes a problems. I just want to add a GridView of my table.

Create a simply table

CREATE TABLE `alpha`.`newtable` (
`index` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`data` VARCHAR(45) NOT NULL,
PRIMARY KEY (`index`)
)
ENGINE = InnoDB;

Also add a couple of rows of data.

In Visual Studio 2008: Create a Web application project

Drag a MySQLDataSourced, manually setup the connection strings.

Use ConfigureDataSource, enter select stamtemet:

select * from newtable;

Command Generator | Generate Commands (as instructed)

Add a GridView (using the MySQLDataSource) include Edit and Delete functions.

Run the application, you should see the grid view data with the rows.

Select Edit, then press Update:

The following error:

Server Error in '/' Application.
--------------------------------------------------------------------------------

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 'index = NULL))' at line 1

Also, if you try Delete

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 'index = NULL))' at line 1

This functionality used to work well in your earlier versions but now it does not. It might be that the Generate Commands is failing, but there is currently little documentation on the subject.

Please advise.

HL

AndreyR · Post by **AndreyR** » Thu 24 Jul 2008 15:06

Hello, Hamsterlegs.

The problem is quite involved and needs thorough investigation. Unfortunately, no timeframe is available for now.
If you wish, you can use some workaround.
Check the "Quoted names" checkbox and regenerate the commands.
Then go to the declarative markup of the page and replace the :_data parameter name with :data.
After this everything should work properly.

Regards, Andrey.

AndreyR · Post by **AndreyR** » Fri 25 Jul 2008 10:22

After performing further investigation we have found that the problem is associated with the use MySQL keywords ("data" and "index") as names of the columns.
We are working on the solution of this issue.

hamsterlegs · Post by **hamsterlegs** » Fri 25 Jul 2008 10:45

Hi,

I don't think that is the cause of the problem as I get the same problem with other column names, e.g.

CREATE TABLE `alpha`.`newtable2` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`contents` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

AndreyR · Post by **AndreyR** » Fri 25 Jul 2008 13:18

We have created the table `newtable2`from your script, created a new ASP.NET Web Application, dragged a MySqlDataSource from the Toolbox, generated commands and it worked properly. (both edit and delete succeded).
The testing was made on MyDirect .NET 4.70.31.
Generated ASPX:

Code: Select all

hamsterlegs · Post by **hamsterlegs** » Fri 25 Jul 2008 13:28

I've been using 4.70.30, I'll download 4.70.31 and see how it goes.

Thanks,

hamsterlegs · Post by **hamsterlegs** » Fri 25 Jul 2008 15:32

A note for anyone else who updates their project to 4.70.31: The provider names have changed

from:

providerName="CoreLab.MySql.Entity"

To:

providerName="CoreLab.MySql"

I found this buried in the release notes only available at http://devart.com/mysqlnet/History.html

HL

hamsterlegs · Post by **hamsterlegs** » Fri 25 Jul 2008 16:23

After solving the new issue introduced by the upgrade to 4.70.31 (see above) one of these issues is fixed.

However, there are still open issues:

1: MySqlDataSource does not show up connection stings in the web.config file. e.g Add a MySqlDataSource to a form when there are connection string in the web.config. These do not appear as options and have to be manually added in the ASPX (this used to work fine).

2: There is very little documenation on the design time support for MySqlDataSource. Please provide details of how to use this. In previous versions there was a WHERE button which I chould use to specify a QueryString parameter. It is not clear how this can be done and there is no help. I'm guessing it might be the Select Parameters button but who knows?

For example, I'd like to pass in the QueryString id to the select statement, e.g.

mypage.aspx?id=123

Do I need to write this in the select statement or what? With all the other issues I'm not sure if this feature is present or if there are issues? Please advise.

3: Underscores are added when certain column names are used e.g. id, datetime, data. These need to be manually moved from the ASPX.

AndreyR · Post by **AndreyR** » Mon 28 Jul 2008 12:07

The solution of the web.config issue is planned, but no timeframe is available. The situation is quite involved.
You can use CommandType.Text and specify a query like "select * from table where queryString = :queryString" and set :querySttring parameter on the "Select Parameters" tab.
The limitation on the underscores is under investigation.