MySQL Adapter.Update

[NetPhi]

hi,,, im new to vb .net... im using vb .net 2003 (1.1 Framework)

my delimma was to update my mysql (MySQL Server 5.0) database from my dataset. i have already downloaded and installed the .NET connector. i used the MySqlDataAdapter to update my database from all of the changes i've done in my dataset. i got an error message with that some sort of a table key (correct me if im wrong) or maybe a unique key (most probably a primary key). i've done so... i created a primary key from my table, and turn on the option autoincrement. but then it never autoincremented (or maybe i have missed something, and i never dare to use it), so i came into realization of creating the wheel myself.

i have a good try coding that for an hour. i think its easy to use without creating a unique key. my prototype as follows:

    Function Update_Database(ByVal command As MySqlCommand, ByVal dataset As DataSet)
        Dim current_table_count As Integer = 0
        If dataset.Tables.Count > 0 Then
            While current_table_count < dataset.Tables.Count
                command.CommandText = "delete from " & dataset.Tables(current_table_count).TableName
                command.ExecuteNonQuery()
                If dataset.Tables(current_table_count).Rows.Count > 0 And dataset.Tables(current_table_count).Columns.Count Then
                    Dim current_row_count As Integer = 0
                    While current_row_count < dataset.Table(current_table_count).Rows.Count
                        command.CommandText = "insert into " & dataset.Tables(current_table_count).TableName & "("
                        Dim current_column_count As Integer = 0
                        While current_column_count < dataset.Tables(current_table_count).Columns.Count
                            command.CommandText &= dataset.Tables(current_table_count).Columns.Item(current_column_count).ColumnName
                            If current_column_count < dataset.Tables(current_table_count).Columns.Count - 1 Then
                                command.CommandText &= ", "
                            End If
                            current_column_count += 1
                        End While
                        command.CommandText &= ") values ('"
                        Dim current_row_value_count As Integer = 0
                        current_row_value_count = 0
                        While current_row_value_count < dataset.Tables(current_table_count).Columns.Count
                            command.CommandText &= Convert.ToString(dataset.Tables(current_table_count).Rows(current_row_count).Item(current_row_value_count))
                            If current_row_value_count = dataset.Tables(current_table_count).Columns.Count - 1 Then
                                command.CommandText &= "')"
                            Else
                                command.CommandText &= "', '"
                            End If
                            current_row_value_count += 1
                        End While
                        command.ExecuteNonQuery()
                        current_row_count += 1
                    End While
                End If
                current_table_count += 1
            End While
        End If
    End Function

i ended up with this code. its easy because you just have to enter the parameters for MySqlCommand (mainly for current connection porpuses), and DataSet (for you to access the tables and its values). i dont still know if its portable to other database like Microsoft Access, Microsoft SQL Server, Oracle, etc... i think it is possible by simple changing the MySqlCommand to other command that holds a connection that is specific to a certain database.

and also, the prototype will generate error if someone will modify the structure of the database such as deleting of fields, renaming tables, etc. because the structure from which the adapter fill the dataset was the database structure before it was modified.

could anyone help me how to update my MySQL database even if i don't have a unique key for my table without using the above prototype?

thanks in advance. keep deep and dark!

From: NetPhi