Please help with datagrid update table

[ev66]

Hi
I want to update my database table by saving the whole datagrid to it. Ive
managed to get data from dB table and display it in datagrid so i can edit it, but
cant save it. It does not show any errors but the new data is not saved.
I've read load of articles and google searches but still cant get it to work.
The only thing the articles do not elaborate on is the SELECT statement, is mine
ok ?
Please help, I've been trying to get this to work for 2 days now.
Thanks

Dim conn As New MySqlConnection

Dim SQL As String
SQL = "SELECT * FROM mytable"

conn.ConnectionString = "server=" & text1.Text & ";" _
& "user id=" & text2.Text & ";" _
& "password=" & text3.Text & ";" _
& "database=mymedia"

Try
conn.Open()
Catch myerror As MySqlException
MessageBox.Show("Error Connecting to Database: " & myerror.Message)
End Try



Dim adapter As MySqlDataAdapter = New MySqlDataAdapter("SELECT * FROM mytable", conn)
Dim myset As New DataSet

' Fill the DataSet.
adapter.Fill(myset)



'update
adapter.Update(myset)
MsgBox("Update OK")
conn.Close()

 

[cjard]

I see your code you are only setting it at dataset level and not at the table level.....not sure whether that means anything or not!

Doesnt matter, for untyped sets, the result of teh query will either be put into a discerned name (in the case of a single select) or something generic like "Table1" in the case of a join..

And he'll probably access it with .Tables(0) anyways.

MySQL -> dirty!

 

[cjard]

It does matter,

Not if he makes a brand new, untyped, generic dataset on the immediately preceeding line.

adapter.Fill(myset.mytable)

Cant do that. In your code, myset is typed, and it has a property called "mytable" that returns a typed table.
MySQL doesnt work with the designer, so you cant get it to generate typed dataset code for you.

as for binding the grid, just bind it via the IDE

Again, MySQL doesnt integrate with the IDE, this dataset is untyped, it cannot be bound visually.

 

[cjard]

probably because that isn't the name of your datatable that exists in your dataSet.

Not in untyped datasets (see previous line of evo's code)

..It's also why I didnt jump in here with my usualy cry of "Read DW2" -> there's nothing in it for hapless MYSQL users

Is there any reason why you are doing this straight into code and not using the GUI driven wizards?

They dont work with MySQL

they are there for a reason

Reasons like OracleExpres and SQLServer Express

and 99.9% of the time stop errors like this happening.

Lol, True. Dunno where you got the statistic that only 0.1% of RDBMS in .NET 2.0 are MySql but it seems quite a good statistic!

All I can suggest is when you type adapter.fill(myset.

Tsk, you should always seek to determine from the posted code, what the type of "myset." is, before you hand out Intellisense advice
In this case, the code tells you, so whether MySQL works or not with the IDE, you wont get anything other than generic DataSet intellisense

you'll get the intellisense pop up. From the list you should be able to find what your dataTable is called...

Would you like custard or cream with your humble pie?

 

[cjard]

with regards to the wizards, I can add a MySqlConnection and configure it ok ,
but when I add a MySqlDataAdapter the generate a dataset is disabled so I cant do that.

I'll keep looking on web , thanks for trying.
Evan.

Mmmhhh.. What the others have posted is correct, for Access, SQLServer and Oracle. It also is correct in some limited senses for some ODBC connections too, but not MySQL, so you wont find what they are advocating.

Cant I suggest that you use another RDBMS, such as Access, Oracle or SQLS, and read the DW2 link in my signature, section "Creating a simple app" - then read other sections as you choose.

That will give you an idea of how easy data access is in .NET 2.0

However, it may not solve your problem that you HAVE to use MySQL because of e.g. a work constraint. In this case, i'm going to give you some advice that you will definitely need to:
a) be a good coder
b) be intelligent
c) have an inquiring mind and love digging about under the hood
d) not be afraid of breaking something
e) very familiar with what a tableadapter is, how it is manifest and what the ide does and doesnt do for you
..in order to follow.

Once you have your skillset meeting those 5 points, you can export a create script for your MySQL and recreate the sceham in SQLServer. You can then have the IDE create a huge amount of code for you in a very short time - do all your data access bits, and generate the dataset code. Then, nip into the code that has been generated behind the scenes (the TableAdapters namespace) and rip it all out to a separate class.
Now go and delete all your tableadapters from the dataset, leaving just the tables.
In the ripped class you made, exchange all the sqlserver things for the commensurate mysql things (SqlClient.SqlCommand -> MySqlClient.MySqlCommand), change the connection string to point to your mysql and give it a whirl..

Using this technique, though the designer cannot understand mysql, I reckon I could generate a full data access layer with all the functionality offered by the IDE's version for supported databases, in about half a day, maybe a few hours..

Using my help, forum member dazlerd has done exactly this, so we know it to be possible. Caveats to remember:
Be case senstitive at all times
Use Ansi quotes on MySQL
The parameter marker for MySQL is ?, not @

 

[Arg81]

Dunno where you got the statistic that only 0.1% of RDBMS in .NET 2.0 are MySql but it seems quite a good statistic!

Maybe 0.1% is a little too high

Tsk, you should always seek to determine from the posted code, what the type of "myset." is, before you hand out Intellisense advice
In this case, the code tells you, so whether MySQL works or not with the IDE, you wont get anything other than generic DataSet intellisense

Yeah I realised after posting that MySQL doesn't link in with the VS IDE and at that point I couldn't be bothered to post otherwise

Would you like custard or cream with your humble pie?

Neither. Raspberry Ripple ice-cream please and thank you.

 

[cjard]

i had similar problems when i first started developing in VB.net, i am a PHP/mysql developer at heart

And you chose VBN over C# ? Crazy boy!

so VB was a big change

No kidding! Where I come from, we call it "frying pan into the fire"

but i wanted to stick with MySQL, after days of struggling i decided to save time and just give up with MySQL and just use msSQLexpress.

When you get deep into how it all works, and come to realise that a tableadapter is nothing but a fabrication of the deviousness of the IDE, you can follow the method described in my code above to return to MySQL

Bear in mind though, that both MySQL and SQLServer suck harder than a Dyson vacuum cleaner, compared to the legendary amazingness that is Oracle*

*opinionsexpressedhereinarethesoleviewoftheposterandinnowayshapeorformaretheytobeconstruedasrepresentativeofvbdotnetforums.com;cjarddoesnotacceptpaymentfromoraclecorporationforendorsementsofitsbrilliantproducts

If you do get it working though let us know how

As a Londoner would say.. Aksk and aww wiw be reveeled, my san.

 

[cjard]

Neither. Raspberry Ripple ice-cream please and thank you.

Dint your mother ever tell you beggars can't be choosers? Now clear orf before I give you a thick ear, you cheeky monkey