Data-Binding: DataTable - TextBox

[KriZa]

Hi, I try to bind a MySQL Table.

I'm using following:
1. Connection to my MySQL DB with:

connStr = ...
conn = New MySqlConnection(connStr)
conn.Open()

2. Connecting 1 MySQL table:

Dim daten_diverses As DataTable
Dim da_daten_diverses As MySqlDataAdapter
Dim cb_daten_diverses As MySqlCommandBuilder

daten_diverses = New DataTable
da_daten_diverses = New MySqlDataAdapter("SELECT * FROM daten_diverses, conn)
da_daten_diverses.Fill(daten_diverses)

3. binding to a textbox

Forms.Form1.Info.DataBindings.Add("Text", daten_diverses, "Info")

no problems till here

now, if I change the context of 'Info' it should be written into my MySQL-Table - but it ain't.

Following code I tried:
4. Triggert on a simple Buttonevent

Dim changes As DataTable = daten_diverses.GetChanges()
        If Not changes Is Nothing Then
            da_daten_diverses.Update(changes)
            daten_diverses.AcceptChanges()
        End If

But nothing happens - the value in my MySQL-Table is still the same as before. It seems, that 'changes' is 'Nothing' - why - where is my bug?

thanks for helping

KrZa

 

[KriZa]

I tried following on the buttonevent:

daten_diverses.Rows(0)("Info_Bank") = "new value"
MessageBox.Show(daten_diverses.Rows(0).RowState.ToString())
Dim changes As DataTable = daten_diverses.GetChanges()
        If Not changes Is Nothing Then
            da_daten_diverses.Update(changes)
            daten_diverses.AcceptChanges()
        End If

and I get the information, that it is "modified"
if I omit the first line, and change the context in the form, I get "Unchanged"

that means, that the databinding using:
'Forms.Form1.Info.DataBindings.Add("Text", daten_diverses, "Info")'
is just oneway-binding?
do I have to write a 'Validated'-trigger:
'daten_diverses.Rows(0)("Info) = Forms.Form1.Info.Text'
to write the new data back to the datatable? I think that is not the thought of the property 'DataBindings' - or am I wrong?

 

[alander]

Totally wrong u are using .net framework 2.0, u should not be coding all this thingys, vs 2005 supports drag and drop

Do the following instead save u lots of work

1. Click on data on the menu, and add new data source

2. Find ur SQL DB, set up the connection string, when the system prompts you to copy the db into the project select YES... and select ur tables

3. Go to data again, click on show data source, now beside ur solution explorer, there will be a data source tab. Click on it and it will show ur datasets, within the dataset is ur data table when u expand ur data table it is the fields in ur table

4. Drag and drop the field u want into ur form, note u can select textbox, combo box, etc. as every field contains a drop down list\

To persist changes into ur database

me.Validate()
me.yourBindingsource.endEdit()
me.yourTableAdapter.Update(yourDataset)

The end

 

[KriZa]

it's not the way I want it to be! I think that progging with VB.NET is more powerful and efficient.

ok, I got it. thx for helping.

me.yourBindingsource.endEdit() was the initial way

thx

 

[cjard]

Totally wrong u are using .net framework 2.0, u should not be coding all this thingys, vs 2005 supports drag and drop

Not for MySQL.. Only for Oracle, SQLServer and Access OLE. Be careful with the "totally wrong" roastings

now, if I change the context of 'Info' it should be written into my MySQL-Table - but it ain't.

Following code I tried:
4. Triggert on a simple Buttonevent

Dim changes As DataTable = daten_diverses.GetChanges()
        If Not changes Is Nothing Then
            da_daten_diverses.Update(changes)
            daten_diverses.AcceptChanges()
        End If

You dont need to GetChanges(), it's far easier to Update() the whole thing. GetChanges produces a cloned table, that is then uploaded (saved) and any values the database calculates are stored in it.. problem is you throw it away later, so the UI never sees any updates from the database (calculation of autonumber PK for example)
Also, all rows in the cloned table are marked as "Saved" but those rows in the original table are still marked as "unsaved" and they will be saved again.. this might generate ANOTHER PK. Not what you want

Golden rule:
DONT use GetChanges when doing an Update, unless youre prepared to merge in changes and mark rows as saved yourself


Alander's most significant advice was the "EndEdit"
You should also call Validate() on the form first, which will cause any textboxes involved in an edit op, to send their values for validation.

In short your bindings code should look like:

Dim daten_diverses As DataTable
Dim da_daten_diverses As MySqlDataAdapter
Dim cb_daten_diverses As MySqlCommandBuilder

daten_diverses = New DataTable
da_daten_diverses = New MySqlDataAdapter("SELECT * FROM daten_diverses, conn)
da_daten_diverses.Fill(daten_diverses)

Dim bs_daten_diverses as New BindingSource
bs_daten_diverses.DataSource  = daten_diverses

Me.Info.DataBindings.Add("Text", [B]bs_daten_diverses[/B], "Info")

[/code]


and your save code should look like alander posted:


me.Validate()
me.bs_daten_diverses.EndEdit()
me.da_daten_diverses.Update(bs_daten_diverses)




BindingSources are handy things for relationships, filtering and maintaining independent position. Without them, just using a table as a datasource will cause that table's defaultview to be used which is fine.. but it causes some weirdness if you use it in multiple places; you cant view 2 different records from the same table at the same time

-

Other comments on your code:

Unless you have a strong reason for doing so (e.g. your employer says you must) you should name your variables inCamelCaseLikeThis, not_with_underscores_like_this

Windows controls usually have their type suffixed, and variable names start with a lowercase letter:
Info should read infoTextBox

 

[KriZa]

But do I still need the commandbuilder?

in your code

Dim daten_diverses As DataTable
Dim da_daten_diverses As MySqlDataAdapter
Dim cb_daten_diverses As MySqlCommandBuilder

daten_diverses = New DataTable
da_daten_diverses = New MySqlDataAdapter("SELECT * FROM daten_diverses, conn)
da_daten_diverses.Fill(daten_diverses)

Dim bs_daten_diverses as New BindingSource
bs_daten_diverses.DataSource  = daten_diverses

Me.Info.DataBindings.Add("Text", bs_daten_diverses, "Info")

you declare it, but you dont use it!
I miss following:

cb_antragsteller = New MySqlCommandBuilder(da_antragsteller)

if the code 'me.da_daten_diverses.Update(bs_daten_diverses)' is handled, do it update my DB? and when should I write 'me.da_daten_diverses.Update(bs_daten_diverses)'?
On 'Validated' of each textbox, or on shutting my form? (>press shutbutton >do code >shut form)

that is the way my form (or system) works:
- costumer MySQL DB as Back End
- login and so on is irrelevant - db connection will be open and hold

1. open searchform for costumers - dblclick will open the costumer
2. ask the DB, whether the costumer is locked by another user (see 3.) if not, next(3.) else next(quit)
3. the costumer will be locked by an SQL-UPDATE command! one of the tables got a 'LOCKED' column. into that I put the userID.
4. load costumerform, declaring datatables and so on, and fill them
5. change textboxcontent and so on...
6. finishing work by pressing closebutton
7. save updates to DB, send unlock SQL command to DB, shut form.

last question again: when to update the me.bs_daten_diverses.EndEdit()?
everytime I'm in '5.' and switching between textboxes (Trigger: 'Validated') or on '7.', before sending changes to DB?

...tricky problem...

thx for answering!!! and thx for great help till here!!!

 

[cjard]

you declare it, but you dont use it!

Declare what but dont use it? The BindingSource? Look at the BOLD word!

Here, i'll make it more clear:

Dim [B]YES_I_DO_USE_IT_bs_daten_diverses[/B] as New BindingSource
[B]YES_I_DO_USE_IT_bs_daten_diverses[/B].DataSource  = daten_diverses

Me.Info.DataBindings.Add("Text", [SIZE="4"][COLOR="Magenta"][B]YES_I_DO_USE_IT_bs_daten_diverses[/B][/COLOR][/SIZE], "Info")

I miss following:

cb_antragsteller = New MySqlCommandBuilder(da_antragsteller)

if the code 'me.da_daten_diverses.Update(bs_daten_diverses)' is handled, do it update my DB?

When you make the DataAdapter, and give it an SELECT sql, it should automatically make a CommandBuilder and use it to work out the INSERT/UPDATE/DELETE - i,e, you dont have to do it yourself

and when should I write 'me.da_daten_diverses.Update(bs_daten_diverses)'?

You should never write that. DataAdapter doesnt take a BindingSource as an argument to Update()

BindingSource is a thing that sits on top of a data model and maintains position, filters the model etc. It contains no data itself. Think of it like a proxy.

On 'Validated' of each textbox, or on shutting my form? (>press shutbutton >do code >shut form)

The save code should look like alander posted. Not on validate f every TB, and only on close of form if you want to save on close.

Actually, your biggest hardship now is that youre having to use new .net2 but in old manual way like .net 1, because MySQL doesnt integrate with the IDE. As a teaching point you should maybe use Access or something to start off with, to make a small project and understand most of how the data access works with BIndingSources etc.

Actually, it is possible to design a data access layer for SQL Server then port it to MySQL.. its just tedious and involves manual editing of autogenerated code but it is faster and gives better code than writing it all yourself

that is the way my form (or system) works:
- costumer MySQL DB as Back End

Yeah, you know what.. I'd dump MySQL and use iether Oracle or SQLServer. The day you spend moving the data to another free db will be recouped when you come to develop in the IDE..

- login and so on is irrelevant - db connection will be open and hold

Dumb idea, to be honest..

1. open searchform for costumers - dblclick will open the costumer
2. ask the DB, whether the costumer is locked by another user (see 3.) if not, next(3.) else next(quit)
3. the costumer will be locked by an SQL-UPDATE command! one of the tables got a 'SAVED' column. into that I put the userID.
4. load costumerform, declaring datatables and so on, and fill them
5. change textboxcontent and so on...
6. finishing work by pressing closebutton
7. save updates to DB, send unlock SQL command to DB, shut form.

Yeah, again.. Modern, decent RDBMS have concurrency handling built in. The IDE supports it too, so you dont have to implement it yourself.. Its usually as simple as handling an exception that is thrown because the database detects that your recent edit should have saved 1 row, but it saved 0 rows because someone else changed the data. You can then either:
Overwrite their changes
Show thair changes to the user and ask which to accept

last question again: when to update the me.bs_daten_diverses.EndEdit()?

You call this every time you want to end any edit operations that are in progress before you Update() to save a data table. If two textboxes are looking at the same data cell, and one is editing, EndEdit will cause the other to display the updated value. EndEdit may be called implicitly at various times. Typically we only call it explicitly just before we save.

everytime I'm in '5.' and switching between textboxes (Trigger: 'Validated')

No. Changing textboxes usually causes validation to occur in which the newly edited value is sent for validation.. if it passes, focus leaves the textbox and enters the next one

or on '7.', before sending changes to DB?

This is typically only time I ever call EndEdit.. But in some situations I need to call it to ensure the latest data is being passed to somewhere else, not just the DB.. For example if I am to write the dataset out to XML, I call EndEdit. If you really want more info, read about it in MSDN!

 

[KriZa]

Think I got it! THX

there was a mistake in following code:

me.da_daten_diverses.Update([B][COLOR="Red"]bs_[/COLOR][/B]daten_diverses)

correct is:

me.da_daten_diverses.Update(daten_diverses)

BUT!!!! whithout declaring the commandbuilder it ain't work!

 

[KriZa]

My final solution: (changed the tablenames...)

Imports System
Imports System.Data
Imports System.Windows.Forms
Imports MySql.Data.MySqlClient
Imports System.Net

Public Class Form1
    Dim conn As MySqlConnection
    Dim dt_antragsteller As DataTable
    Dim da_antragsteller As MySqlDataAdapter
    Dim cb_antragsteller As MySqlCommandBuilder
    Dim bs_antragsteller As New BindingSource

    Public Function connect()
        'dbconnection
        Dim connStr As String
        connStr = String.Format("server=xx.xx.xx; user id=xx; password=xx; database=xx; CharSet=utf8; pooling=false;")
        Try
            conn = New MySqlConnection(connStr)
            conn.Open()
        Catch ex As MySqlException
            MessageBox.Show("Fehler bei Verbindung zum Server: " + ex.Message)
        End Try
        Return Nothing
    End Function

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connect()
        dt_antragsteller = New DataTable
        da_antragsteller = New MySqlDataAdapter("SELECT * FROM antragsteller WHERE antragsteller.KundenNr = 'xx'", conn)
        da_antragsteller.Fill(dt_antragsteller)
        cb_antragsteller = New MySqlCommandBuilder(da_antragsteller)
        bs_antragsteller.DataSource = dt_antragsteller
        Nachname.DataBindings.Add(New Binding("Text", bs_antragsteller, "Nachname"))
        Vorname.DataBindings.Add(New Binding("Text", bs_antragsteller, "Vorname"))
        Geb.DataBindings.Add(New Binding("Text", bs_antragsteller, "Geburtsdatum"))
        Wohnort.DataBindings.Add(New Binding("Text", bs_antragsteller, "Wohnort"))
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.ValidateChildren()
        bs_antragsteller.EndEdit()
        da_antragsteller.Update(dt_antragsteller)
    End Sub
End Class

works fine!

THX for all your help!

 

[cjard]

You know, i never really looked at CommandBuilder because i dont have to use it.. but lookinga t the example code on MSDN, its totally retarded:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx

Firstly, the variable is instantiated but never used, apart from the calling of a Get() function, the return value of which is not stored. I cant believe some intelligent person at Microsoft wrote that crap.

It would appear that it does some voodoo in the constructor. What, I dont know, but thats a poor way of engineering it to work.

 

[alander]

lol i cant believe MSDN has that example siting all those errors