VB.NET DataAdapter ? ? ?

[merianos]

Hello all . . .

I try to create a form that managing the access level of user managment system.

The model I use is the following :


I create a connection to database
I create a DataAdapter to transfer the data from the database
I create a Dataset to hold my data
My form consists of the following :

A ListBox that list all the access level names
Three text boxes:
The first for the record ID
The second for the access level name
And the third for the discription of the access level
Five check boxes:
One for writing rights
One for the deleting rights
One for the Updating rights
One for Printing rights
And one for Special area access rights

So what i do is the following :

I create a connection string
I create a new connection to my database server
I create the DataAdapter
I initializing the DataAdapter command such as SelectCommand, DeleteCommand, e.t.c..
I fill my DataSet with the data DataAdapter get from DataBase
I binding my controls to DataSet
I setting the DataSource property of the ListBox to my DataSet
I setting the DisplayMember to the ACCL_NAME column of the DataTable inside the DataSet
I setting the ValueMember to ACCL_ID column of the DataTable inside the DataSet

Until this time all are fine ! ! !

The question is :

When i try to add a new row to dataset by using the following code i have a problem :

Dim newRow As DataRow = Dataset.Tables("TableName").NewRow

newRow("COLUMN_NAME") = Binded Control Value

DataSet.Tables("TableName").Rows.Add(newRow)

The problem is that when i try to add a new row by this way then i get two rows of data in my ListBox

Then if i try to add another then i have bigger scale of this problem ! ! !

What can i do ? ? ?

Many many thanks ! ! ! !

[jmcilhinney]

Have you set up all this binding in the designer or are you doing it in code? Also, what version of VB are you using?

[merianos]

I'm using the VB.NET 2008 Express Editions and I have bind all my controls by writting code and I'm using MySQL.

If somebody know any Tutorial on how to manage data thru DataAdapter it will help me too . . . ! ! !

Many many thanks ! ! !

[jmcilhinney]

Can you show us all the relevant code then? By that I mean the code to retrieve the data, the code to bind the data and the code to add the new data. Please wrap your code snippets in Code or VBCode tags for readability.

[merianos]

' On load form event

conn = New MySqlConnection
        conn.ConnectionString = db_connectionString

        'Ενέργειες αρχικοποίησης του DataAdapter και του DataSet
        Try
            'Άνοιγμα της σύνδεσης
            conn.Open()

            dsRights = New DataSet("Rights")

            'Ορισμός της εντολής επιλογής δεδομένων από τη βάση δεδομένων
            daRights = New MySqlDataAdapter("SELECT ACCL_ID, ACCL_NAME, ACCL_DESC, ACCL_INSERT, ACCL_UPDATE, ACCL_DELETE, ACCL_PRINT, ACCL_ALLOW_ACCESS FROM access_level ORDER BY ACCL_NAME", conn)

            'Ορισμός της εντολής αναβάθμισης δεδομένων στη βάση δεδομένων
            daRights.UpdateCommand = New MySqlCommand("UPDATE access_level SET " & _
                                                      "ACCL_NAME = ?accl_name, " & _
                                                      "ACCL_DESC = ?accl_desc, " & _
                                                      "ACCL_INSERT = ?accl_insert, " & _
                                                      "ACCL_UPDATE = ?accl_update, " & _
                                                      "ACCL_DELETE = ?accl_delete, " & _
                                                      "ACCL_PRINT = ?accl_print, " & _
                                                      "ACCL_ALLOW_ACCESS = ?accl_allow_access " & _
                                                      "WHERE ACCL_ID = ?accl_id", conn)

            'Ορισμός των παραμέτρων για την εντολή αναβάθμισης δεδομένων
            daRights.UpdateCommand.Parameters.Add("?accl_name", MySqlDbType.VarChar, 15, "ACCL_NAME")
            daRights.UpdateCommand.Parameters.Add("?accl_desc", MySqlDbType.VarChar, 150, "ACCL_DESC")
            daRights.UpdateCommand.Parameters.Add("?accl_insert", MySqlDbType.VarChar, 5, "ACCL_INSERT")
            daRights.UpdateCommand.Parameters.Add("?accl_update", MySqlDbType.VarChar, 5, "ACCL_UPDATE")
            daRights.UpdateCommand.Parameters.Add("?accl_delete", MySqlDbType.VarChar, 5, "ACCL_DELETE")
            daRights.UpdateCommand.Parameters.Add("?accl_print", MySqlDbType.VarChar, 5, "ACCL_PRINT")
            daRights.UpdateCommand.Parameters.Add("?accl_allow_access", MySqlDbType.VarChar, 5, "ACCL_ALLOW_ACCESS")
            daRights.UpdateCommand.Parameters.Add("?accl_id", MySqlDbType.VarChar, 9, "ACCL_ID").SourceVersion = DataRowVersion.Original

            'Ορισμός της εντολής εισαγωγής δεδομένων στη βάση δεδομένων
            daRights.InsertCommand = New MySqlCommand("INSERT INTO access_level(ACCL_ID, ACCL_NAME, ACCL_DESC, ACCL_INSERT, " & _
                                                      "ACCL_UPDATE, ACCL_DELETE, ACCL_PRINT, ACCL_ALLOW_ACCESS) " & _
                                                      " VALUES(?accl_id, ?accl_name, ?accl_desc, ?accl_insert, ?accl_update, " & _
                                                      "?accl_delete, ?accl_print, ?accl_allow_access) ", conn)

            'Ορισμός των παραμέτρων για την εντολή εισαγωγής δεδομένων
            daRights.InsertCommand.Parameters.Add("?accl_id", MySqlDbType.VarChar, 9, "ACCL_ID").SourceVersion = DataRowVersion.Original
            daRights.InsertCommand.Parameters.Add("?accl_name", MySqlDbType.VarChar, 9, "ACCL_NAME")
            daRights.InsertCommand.Parameters.Add("?accl_desc", MySqlDbType.VarChar, 9, "ACCL_DESC")
            daRights.InsertCommand.Parameters.Add("?accl_insert", MySqlDbType.VarChar, 9, "ACCL_INSERT")
            daRights.InsertCommand.Parameters.Add("?accl_update", MySqlDbType.VarChar, 9, "ACCL_UPDATE")
            daRights.InsertCommand.Parameters.Add("?accl_delete", MySqlDbType.VarChar, 9, "ACCL_DELETE")
            daRights.InsertCommand.Parameters.Add("?accl_print", MySqlDbType.VarChar, 9, "ACCL_PRINT")
            daRights.InsertCommand.Parameters.Add("?accl_allow_access", MySqlDbType.VarChar, 9, "ACCL_ALLOW_ACCESS")

            'Φόρτωμα των δεδομένων από τη βάση δεδομένων στο DataSet
            daRights.Fill(dsRights, "UserRights")

            'Σύνδεση του lsbRights με το DataSet
            lsbRights.DataSource = dsRights.Tables("UserRights")
            lsbRights.DisplayMember = "ACCL_NAME"
            lsbRights.ValueMember = "ACCL_ID"

            'Σύνδεση των στοιχείων της φόρμας στο DataSet
            txtRecordID.DataBindings.Clear()
            txtRecordID.DataBindings.Add("text", dsRights.Tables("UserRights"), "ACCL_ID")
            txtRightName.DataBindings.Clear()
            txtRightName.DataBindings.Add("text", dsRights.Tables("UserRights"), "ACCL_NAME")
            txtDescription.DataBindings.Clear()
            txtDescription.DataBindings.Add("text", dsRights.Tables("UserRights"), "ACCL_DESC")
            chkInsert.DataBindings.Clear()
            chkInsert.DataBindings.Add("checked", dsRights.Tables("UserRights"), "ACCL_INSERT")
            chkUpdate.DataBindings.Clear()
            chkUpdate.DataBindings.Add("checked", dsRights.Tables("UserRights"), "ACCL_UPDATE")
            chkDelete.DataBindings.Clear()
            chkDelete.DataBindings.Add("checked", dsRights.Tables("UserRights"), "ACCL_DELETE")
            chkPrint.DataBindings.Clear()
            chkPrint.DataBindings.Add("checked", dsRights.Tables("UserRights"), "ACCL_PRINT")
            chkSpecialAreas.DataBindings.Clear()
            chkSpecialAreas.DataBindings.Add("checked", dsRights.Tables("UserRights"), "ACCL_ALLOW_ACCESS")
            lsbRights.SelectedIndex = 0

        Catch ex As MySqlException
            Dim errMsg As String = Nothing

            errMsg = "Προκλήθηκε ένα σφάλμα κατά την διαχείριση των δεδομένων αυτής της φόρμας."
            errMsg &= vbNewLine & vbNewLine
            errMsg &= "Κωδικός σφάλματος : " & ex.ErrorCode.ToString
            errMsg &= vbNewLine
            errMsg &= "Περιγραφή σφάλματος : " & ex.Message

            MsgBox(errMsg, MsgBoxStyle.Exclamation, "Σφάλμα εκτέλεσης")
            Me.Dispose()

        End Try

And this is what i use when i save

Dim newID As String = createNewRecordID(dsRights.Tables("UserRights").Compute("MAX(ACCL_ID)", ""))
        newRow = dsRights.Tables("UserRights").NewRow()
newRow("ACCL_NAME") = txtRightName.Text
        newRow("ACCL_DESC") = txtDescription.Text
        newRow("ACCL_INSERT") = chkInsert.Checked.ToString
        newRow("ACCL_UPDATE") = chkUpdate.Checked.ToString
        newRow("ACCL_DELETE") = chkDelete.Checked.ToString
        newRow("ACCL_PRINT") = chkPrint.Checked.ToString
        newRow("ACCL_ALLOW_ACCESS") = chkSpecialAreas.Checked.ToString

        dsRights.Tables("UserRights").Rows.Add(newRow)
        daRights.Update(dsRights.Tables("UserRights"))
        dsRights.Clear()
        daRights.Fill(dsRights, "UserRights")
        lsbRights.SelectedIndex = 0