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()

 

[HeavenCore]

when you run your application in debug mode it makes a copy of your database into the debug folder, your application may be working fine, but once you end debug mode the database is destroyed, and next time you debug the database is copied once again, this can give the illusion of it not working, if this is your problem simply select your database in solution explorer and set its Copy property to 'Copy If Newer' or 'Never Copy' i recommend ‘Copy If Newer’, Cjard did a post on this too somewhere. Hope that helps.

Regards

HeavenCore

 

[cjard]

HC; I dont think DNU (link in my sig, basically what you said) applies to MySql (as indicated by the OP's use of Dim da as MySqlDataAdapter) because it is a server based database, with no file option (unlike SQLS and Access)

I have no explanation for this failure on MySQL; I dont use it because the lack of integration with VS is not mitigated by the low cost of purchase. Personally I would rather use a free product that works with the IDE (oracle express/sqlserver express) than a free one that doesnt..

 

[cjard]

Hi

adapter.Update(myset)
conn.Close()

Why do you update immediately after you fill? Such an operation would do nothing

Ensure that you are NOT calling AcceptChanges before you call Update() to write the changes back to the database

 

[ev66]

HeavenCore,
the database isnt in the solution explorer as its on the server.
Regards
Evan

 

[Arg81]

As Cjard has asked, why in your load code are you immediately calling .update?!?

What I would try is this;

FORM LOAD:

[COLOR=blue]Dim conn As New MySqlConnection[/COLOR]
 
[COLOR=blue]Dim SQL As String[/COLOR]
[COLOR=blue]SQL = "SELECT * FROM mytable"[/COLOR]
 
[COLOR=blue]conn.ConnectionString = "server=" & text1.Text & ";" _[/COLOR]
[COLOR=blue]& "user id=" & text2.Text & ";" _[/COLOR]
[COLOR=blue]& "password=" & text3.Text & ";" _[/COLOR]
[COLOR=blue]& "database=mymedia"[/COLOR]
 
[COLOR=blue]Try[/COLOR]
[COLOR=blue]conn.Open()[/COLOR]
[COLOR=blue]Catch myerror As MySqlException[/COLOR]
[COLOR=blue]MessageBox.Show("Error Connecting to Database: " & myerror.Message)[/COLOR]
[COLOR=blue]End Try[/COLOR]
 
[COLOR=blue]Dim adapter As MySqlDataAdapter = New MySqlDataAdapter("SELECT * FROM mytable", conn)[/COLOR]
[COLOR=blue]Dim myset As New DataSet[/COLOR]
 
[COLOR=blue]' Fill the DataSet.[/COLOR]
[COLOR=blue]adapter.Fill(myset)[/COLOR]

Now, put a button on your form and set it's _click event to:

IF myset.haschanges THEN
 
me.adapter.update(myset)
 
ELSE
 
messagebox.show("There are no changes to save")
 
END IF

So if you make any changes to the dataset they should be updated, and if no changes, you'll get a messagebox to say so.

On another note, I'm not too sure regarding MySQL, but I'm sure (someone will say if I'm wrong), that when you code the .fill , .update etc etc it should be in the notion of;

.fill(me.dataset.datatable)

.update(me.dataset.datatable)

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!

 

[ev66]

I missed out the databinding in my code,
' bind datagrid
mydg.DataSource = myset

but still does not work ,
does the mydgDataSource tell the myset the new values in the datagrid ?
is that how it works ?


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)

' bind datagrid
mydg.DataSource = myset

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

 

[HeavenCore]

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!

It does matter, especially if there is more than one table in the dataset, so ev66, change your code from:

[COLOR=black]adapter.Fill(myset)[/COLOR]

to something like:

adapter.Fill(myset.mytable)

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

Also please use Code tags when posting code, easier to read, cheers.

 

[Arg81]

that's what I thought, wasn't sure if MySQL broke the rules

 

[ev66]

Ok. will do do with code tags.

code still does not work, it wont accept

adapter.Fill(myset.mytable)

thanks

 

[Arg81]

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

Is there any reason why you are doing this straight into code and not using the GUI driven wizards? they are there for a reason and 99.9% of the time stop errors like this happening.

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

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

 

[HeavenCore]

another way to find out is edit your dataset in designer and see what your table is called and the related adapter:

SO in this instance it would be:

me.CustomerTableAdapter.Fill(dsCustomers.Customer)

 

[ev66]

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.

 

[HeavenCore]

i had similar problems when i first started developing in VB.net, i am a PHP/mysql developer at heart so VB was a big change, 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. how far into your project are you? would it be feasible to switch to VB express? the IDE integration helps ALOT, VB.net is a RAD platform after all....

If you do get it working though let us know how

Good Luck

HeavenCore

 

[ev66]

ok, thanks, might look into VB express, i'll keep trying for a bit longer though.

Evan.