Solved

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

Posted on 2009-04-05
14
625 Views
Last Modified: 2013-12-20
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.
0
Comment
Question by:tom_burkhardt
  • 9
  • 3
  • 2
14 Comments
 
LVL 4

Expert Comment

by:brawney
Comment Utility
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
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
Comment Utility
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
 

Author Comment

by:tom_burkhardt
Comment Utility
kaufmed you rock!!! thank you; I've been going nuts with this for almost three days.
0
 
LVL 4

Expert Comment

by:brawney
Comment Utility
The newer versions of the .NET MySql Connector v5.2 recommends that we use @ now, instead of the older ? prefix.  I use @.
0
 

Author Closing Comment

by:tom_burkhardt
Comment Utility
thank you thank you!
0
 

Author Comment

by:tom_burkhardt
Comment Utility
BTW I'm using MySQL Connector Net 5.2.1, and the @ symbol was definitely not working.
0
 

Author Comment

by:tom_burkhardt
Comment Utility
brawney, how would you phrase the Update query using the @?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:tom_burkhardt
Comment Utility
sorry, the points are already gone. But I was curious.
0
 

Author Comment

by:tom_burkhardt
Comment Utility
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
 

Author Comment

by:tom_burkhardt
Comment Utility
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
 

Author Comment

by:tom_burkhardt
Comment Utility
oops, typo ... there should be no quotes on the parameters!!

so,
UPDATE `mytable` SET `fld1` = @fld1 WHERE `Id` = @Id;
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
The syntax should be as you originally had it. You may just need to include this connection string option.
0
 

Author Comment

by:tom_burkhardt
Comment Utility
uh oh. sorry about the points, brawney!!   :-(  newbie got too excited upon seeing code working after three days.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now