Stumped - GridView not providing correct MySql statements for UPDATE

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
Cody21
Posts: 112
Joined: Thu 01 Mar 2007 00:58

Stumped - GridView not providing correct MySql statements for UPDATE

Post by Cody21 » Tue 24 Apr 2007 16:44

OK - I'm stumped on this one. I've looked at the documenation and for the life of me can't find the proper statements for GRIDVIEW to Update my data elements. nor can I find any Sample that speaks to this.

basically what I did afterr a lot of trial and error is configure my MySqlDataSource in Gridview to select data from 2 tables and display it. I added the EDIT ability. When I click on Edit, modify a field or 2 and click on UPDATE, I get a very ugly error -- basically that the "...following MySql statement is not correct ..".... but I can't figure out what it needs to look like or how to do this. Any help would be greatly apreciated. Thanks in advance. PS - I'm also wanting to allow Inserts ... Are there any Samples that show how to do this? All I have to reference is the MSDN site. But they assume SQL is being used; not MySql. I'm thinking that MySqlDataAdapter is what needs to be used ... but I can't seem to figure out how to use it.. Is there a step-by-step GUIDE to using that?


UpdateCommand="INSERT INTO aspnet_membership(email, firstName, lastName, workPhone, homePhone, cellPhone)">

By the way, I followed the instructions I found on your site:

http://crlab.com/forums/viewtopic.php?t ... +statement

But on the screen for the Configure the Select Statement, the 2nd option to select from a Table is greyed out... I can only specify a SQL Statement (the 1st option.) I supposed if the other option wasn't greyed out, it might actually work.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Wed 25 Apr 2007 07:51

I get a very ugly error -- basically that the "...following MySql statement is not correct .."....
Provide me with your SQL statements for select and for update. You may get VS generate update, insert and delete commands for you by pressing "Advanced" button on "Configure the Select Statement" page and ticking appropriate checkbox.
But on the screen for the Configure the Select Statement, the 2nd option to select from a Table is greyed out...
Please send me all screenshots showing how you are configuring MySqlDataSource.

Cody21
Posts: 112
Joined: Thu 01 Mar 2007 00:58

Post by Cody21 » Wed 25 Apr 2007 14:26

Sent to your personal email Alexeky ..

Thanks so much.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 26 Apr 2007 08:45

The option to select from a Table can be greyed out only if your database has no tables.

Cody21
Posts: 112
Joined: Thu 01 Mar 2007 00:58

Post by Cody21 » Thu 26 Apr 2007 13:45

But this is the Roles/Membership Database -- and has 6 Tables in it. I built it using your SQL file provided. In fact the SELECT statement (which I manually created & now works correctly) is pulling table data from 2 tables... However, that one is also is GREYED out when I try to go thru the same exercise as the UPDATE; I can't get the "Specify Colums from a Table or View" option to select it.

Alexey - I am just trying to use the "canned" processes you guys provided in the Product; including the MySql DB for Roles/Membership. I *really* need to be able to provide a RECORD UPDATE ability for my authorized users in my page design; else MySqlDirect is worthless to me (since I am on the hook to do all DB management using direct table acccess).

So I tried something -- I used your provided "InstallWebTables.sql" to create a brand new Database & Tables. Ran fine. Without modifying anything, I went into VS2005 and added a MySqlDataSource. Once again, when I get to the Configure Select Statement screen, the "Specify Columns from Table or View" is greyed out !!!!

So, I decided to try this with a different database - one I created with simply 2 tables. When I added the MySqlDataSource as before, I *now* get the second option that was previously greyed out.

Soooo, I have to conclude that it has something to do with the Roles/Membership database that works with your product. Can you confirm same behaviour?

Cody21
Posts: 112
Joined: Thu 01 Mar 2007 00:58

Post by Cody21 » Thu 26 Apr 2007 17:57

Alexey - if we can't get this figured out (why the Select is being greyed out), can you provide the proper Command Syntax of the UPDATE statement ???????

This is what the VS "tool" is generating from the MySqlDataSource control for teh UPDATE tab:

UPDATE aspnet_subs
SET firstName =, lastName =, subEmail =, instrument =, subHomePhone =, subWorkPhone =, subCellPhone =, subComments =


As you know, when I run this, MySql complains that there is an Invalid Syntax ... So How do I get the actual values that were Updated in the GridView into the command syntax?????? Am I suppose to be entering something in the "VALUE" column in the Query Builder screen? Itis not obvious.

Lastly, I searched the MSDN forums as well for the syntax and what might be missing. The following is the Code I've come up with so far ... I continue to get an error ... this is quite frustrating for something I had expected to be somewhat straightforward to implement with yor product.


"
ProviderName=""
SelectCommand="SELECT aspnet_subs.* FROM aspnet_subs"
UpdateCommand="UPDATE aspnet_subs
SET firstName = @firstName,
lastName = @lastName,
subEmail = @subEmal,
instrument = @instrument,
subHomePhone = @subHomePhone,
subWorkPhone = @subWorkPhone,
subCellPhone = @subCellPhone,
subComments = @subComments
WHERE subEmail = @subEmail">















error message =

'firstName' parameter is missing at the statement
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: 'firstName' parameter is missing at the statement

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Fri 27 Apr 2007 06:42

For the table "dept" the code should look as follows:

Code: Select all

        "
            DeleteCommand="DELETE FROM `dept` WHERE `DEPTNO` = ?" InsertCommand="INSERT INTO `dept` (`DEPTNO`, `DNAME`, `LOC`) VALUES (?, ?, ?)"
            ProviderName="" SelectCommand="SELECT * FROM `dept`"
            UpdateCommand="UPDATE `dept` SET `DNAME` = ?, `LOC` = ? WHERE `DEPTNO` = ?">
            
                
            
            
                
                
                
            
            
                
                
                
            
        
Try to use SqlDataSource component instead of MySqlDataSource one in the same scenario.

Cody21
Posts: 112
Joined: Thu 01 Mar 2007 00:58

Post by Cody21 » Fri 27 Apr 2007 14:24

Perfect !! Works great -- Thanks you so much !!!!!!!!!!!!!!!!

I will try out the SqlDataSource as you suggest. (I was just thinking (wrongly?) that I should ALWAYS use MySql(anything) when designing and using MySql databases... hmmm. Unlcear on the concept I guess.

PS - VS2005 doesn't like the single quotes around the table field names like you indicated. This is what my final UPDATE looks like - not that I have no quotes around the field names:

UpdateCommand="UPDATE aspnet_subs
SET firstName = ?,
lastName = ?,
instrument = ?,
subHomePhone = ?,
subWorkPhone = ?,
subCellPhone = ?,
subComments = ?
WHERE subEmail = ? " >

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Sat 28 Apr 2007 06:16

VS2005 doesn't like the single quotes around the table field names like you indicated
This is not a single quote, but the backtick.

Cody21
Posts: 112
Joined: Thu 01 Mar 2007 00:58

Post by Cody21 » Sat 28 Apr 2007 14:10

Thanks for clarifying .... I'll give that a shot.. You can close this.

Post Reply