Solved

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

Posted on 2009-04-05
14
633 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
ID: 24071663
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 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 24071674
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
ID: 24071689
kaufmed you rock!!! thank you; I've been going nuts with this for almost three days.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 4

Expert Comment

by:brawney
ID: 24071690
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
ID: 31566744
thank you thank you!
0
 

Author Comment

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

Author Comment

by:tom_burkhardt
ID: 24071703
brawney, how would you phrase the Update query using the @?
0
 

Author Comment

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

Author Comment

by:tom_burkhardt
ID: 24071727
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
ID: 24071728
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
ID: 24071734
oops, typo ... there should be no quotes on the parameters!!

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

Expert Comment

by:käµfm³d 👽
ID: 24071986
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 75

Expert Comment

by:käµfm³d 👽
ID: 24071989
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
ID: 24072235
uh oh. sorry about the points, brawney!!   :-(  newbie got too excited upon seeing code working after three days.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
RUNRMTCMD from AS/400 12 47
AWS EC2 & RDS Instance 5 36
Help with exporting to excel 4 28
Get number of Files in Directory and Sub Directories 2 42
Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

803 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