looking for an Update Query in VB.NET that will work with a MySQL database

hi experts, This newbie would be grateful for any help here.

What I am looking for is a DatagridView Update Query in VB.NET that will work with a MySQL database. Failing that, code for a "Save Changes" button, but it's frustrating that the Visual part of VB isn't doing the job.

The datagridview on my VB.NET Form, when run,
will add new rows to the MySQL database, but
will NOT update changes there.

I have tried to run an ultimately simple version - one table with 2 fields - and still no luck.

Here's what  I've done:

created a MySQL database named {tomsdb1}
created a table  `mytable` with two fields:
(1)   `ID` (key, auto_increment)
(2)   `fld1` (integer)

Using VS 2008, Visual Basic.
created a database connection
created {tomsdb1dataset}
dragged the dataset over to form1 to create a DatagridView/TableAdapterManager

the code for SaveItem_Click is:
        Me.Validate()
        Me.MytableBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.Tomsdb1DataSet)


My command text for the UpdateCommand of the
mytableTableAdapter is:
UPDATE `mytable` SET `fld1` = @fld1 WHERE `Id` = @original_Id;


When I run the form (F5), the dataset shows up fine, and is scrollable., so I know the FILL function and the SELECT query work.

when I add a new row and click the Save icon, that works too, so I know the INSERT query works

However if I make a change to any cell and try to save it,
I get  
>>>> "Parameter '@fld1' must be defined" <<<<

which leads me to believe the Update query needs to be fixed.
No matter how many online examples of this I go through, just can't seem to get it to run right.

I have recreated the exact same table with an MS ACCESS database, and it works fine; couldn't help noticing that with the MS ACCESS connection, Visual Studio goes ahead and creates the INSERT, UPDATE and DELETE queries. With the MySQL connection there is only the INSERT query and you have to write the UPDATE and DELETE yourself.

I've tried two other versions of the UPDATE query:

(1) UPDATE `mytable` SET `fld1` = @fld1 WHERE `Id` = @oldId;
where changing the second parameter to @oldId also gives me "Parameter '@fld1' must be defined"

(2) UPDATE `mytable` SET `fld1` = '@fld1' WHERE `Id` = '@original_Id';
where adding quotes around the parameters gives me a CONCURRENCY VIOLATION

Also tried without the terminating semi-colon, which made no difference.
Am now officially completely stumped.
tom_burkhardtAsked:
Who is Participating?
 
käµfm³d 👽Connect With a Mentor Commented:
Can you try changing your @'s to ?'s in all of your update queries?

http://www.programmingado.net/a-389/MySQL-NET-parameters-in-query.aspx
0
 
brawneyCommented:
I don't use VB and I don't use the drag and drop dataset tools in the virual designer, so maybe I can't help much.  :)  But I have played with the drag and drop stuff, using MySql and C# in the past.  I seem to recall that when defining your own UPDATE statement there was a way to define the parameters that ae used.  Poke aronud the VS IDE to see if you can find that.  Sorry I can't offer much more info that that, but I thought I'd throw out something that might help.
0
 
tom_burkhardtAuthor Commented:
kaufmed you rock!!! thank you; I've been going nuts with this for almost three days.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
brawneyCommented:
The newer versions of the .NET MySql Connector v5.2 recommends that we use @ now, instead of the older ? prefix.  I use @.
0
 
tom_burkhardtAuthor Commented:
thank you thank you!
0
 
tom_burkhardtAuthor Commented:
BTW I'm using MySQL Connector Net 5.2.1, and the @ symbol was definitely not working.
0
 
tom_burkhardtAuthor Commented:
brawney, how would you phrase the Update query using the @?
0
 
tom_burkhardtAuthor Commented:
sorry, the points are already gone. But I was curious.
0
 
tom_burkhardtAuthor Commented:
For anyone else who runs into this problem, the query also works without the "original_" bit.

so my final update query is :

UPDATE `mytable` SET `fld1` = '@fld1' WHERE `Id` = '@Id';
0
 
tom_burkhardtAuthor Commented:
For anyone else who runs into this problem, the query also works without the "original_" bit.

so my final update query is :

UPDATE `mytable` SET `fld1` = '@fld1' WHERE `Id` = '@Id';
0
 
tom_burkhardtAuthor Commented:
oops, typo ... there should be no quotes on the parameters!!

so,
UPDATE `mytable` SET `fld1` = @fld1 WHERE `Id` = @Id;
0
 
käµfm³d 👽Commented:
It appears that brawney was correct. According to MySQL:

>Note

>Using the '@' symbol for parameters is now the preferred approach although the old pattern of using >'?' is still supported.

>Please be aware however that using '@' can cause conflicts when user variables are also used. To >help with this situation please see the documentation on the Allow User Variables connection string >option, which can be found here: Section 21.2.3.3.3, ConnectionString. The Old Syntax connection >string option has now been deprecated.

http://dev.mysql.com/doc/refman/5.1/en/connector-net-examples-mysqlcommand.html
http://dev.mysql.com/doc/refman/5.1/en/connector-net-examples-mysqlconnection.html#connector-net-examples-mysqlconnection-connectionstring
0
 
käµfm³d 👽Commented:
The syntax should be as you originally had it. You may just need to include this connection string option.
0
 
tom_burkhardtAuthor Commented:
uh oh. sorry about the points, brawney!!   :-(  newbie got too excited upon seeing code working after three days.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.