none
MYSQL Default Values not applying on DA.Update

    Question

  • I have a MYSQL database with default values set to '0' on several tinyint(1) cols.

    Inserting a row with sql statement works as expected and sets the tinyint to 0 on insert. IE

    insert into tgt.users (ntusername) values ('Test')

    However inserting through mysqlcommandbuilder & mysqldataadapter update method fails to set the default values in tinyin col.

    Any ideas?

                                   Dim UsersCB As New MySqlCommandBuilder(UsersDA)
                                    Dim NewRow As DataRow = UsersTable.NewRow
                                    NewRow("ntusername") = Username
                                    NewRow("PendAccess") = True
                                    NewRow("DateCreated") = Now
                                    NewRow("DateModify") = Now
                                    NewRow("Updateby") = Username
                                    UsersTable.Rows.Add(NewRow)
                                    UsersDA.Update(UsersTable)


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Tuesday, May 2, 2017 6:06 PM

Answers


  • Thanks for the idea Cor, unfortunately I am still not getting default int values.

    Dim SelUsersCols As String = "ID,ntusername,FirstName,LastName,FullName,DateCreated,DateModify,GrantAdmin,GrantAccess," & "GrantAccounts,GrantHR,GrantUsers,PendAccess,PendAccounts,PendUsers,Updateby,Notes"

    Using UsersDA As New MySqlDataAdapter("SELECT " & SelUsersCols & " FROM users", MySQLConn) UsersDA.MissingSchemaAction = MissingSchemaAction.AddWithKey UsersDA.FillSchema(UsersTable, SchemaType.Source) UsersDA.Fill(UsersTable) End Using




    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    GTGripodi,

    That was the opposite from what I meant, try to avoid the columns with default values if you don't update them and for sure don't use the FillSchema, that simply add all columns to your datatable and will therefore update them. The fill creates columns which are in the select.

    Be aware that you can also set a default value in the datatable if this what I suggest won't go. 

    https://msdn.microsoft.com/en-us/library/system.data.datacolumn.defaultvalue(v=vs.110).aspx


    Success
    Cor

    • Marked as answer by Gtripodi Wednesday, May 3, 2017 1:15 PM
    Tuesday, May 2, 2017 10:35 PM

All replies

  • Rather than use a command builder, set the insert command and parameters explicitly.
    Tuesday, May 2, 2017 6:42 PM
  • Rather than use a command builder, set the insert command and parameters explicitly.
    I suppose it would be just as easy to manually set the columns to the expected default value. 

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Tuesday, May 2, 2017 7:24 PM
  • Except you would have to keep track of the defaults on the database and in the .Net code.
    Tuesday, May 2, 2017 7:29 PM
  • We don't see your Select but I assume it is something likewise:

    Select * from X

    use instead of that

    Select ntusername, PendAccess, DateCreated, DateModify, Updateby from X

    X is here the table name 


    Success
    Cor

    Tuesday, May 2, 2017 7:44 PM
  • We don't see your Select but I assume it is something likewise:

    Select * from X

    use instead of that

    Select ntusername, PendAccess, DateCreated, DateModify, Updateby from X

    X is here the table name 


    Success
    Cor

    Thanks for the idea Cor, unfortunately I am still not getting default int values.

    Dim SelUsersCols As String = "ID,ntusername,FirstName,LastName,FullName,DateCreated,DateModify,GrantAdmin,GrantAccess," & "GrantAccounts,GrantHR,GrantUsers,PendAccess,PendAccounts,PendUsers,Updateby,Notes"

    Using UsersDA As New MySqlDataAdapter("SELECT " & SelUsersCols & " FROM users", MySQLConn) UsersDA.MissingSchemaAction = MissingSchemaAction.AddWithKey UsersDA.FillSchema(UsersTable, SchemaType.Source) UsersDA.Fill(UsersTable) End Using




    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Tuesday, May 2, 2017 8:07 PM
  • Except you would have to keep track of the defaults on the database and in the .Net code.

    I think you mean other way round?

    As of now it's looking like the best solution is to address the defaults on the datatable

                    With UsersTable
                        .Columns("GrantAdmin").DefaultValue = False
                        .Columns("GrantAccess").DefaultValue = False
                        .Columns("GrantAccounts").DefaultValue = False
                        .Columns("GrantHR").DefaultValue = False
                        .Columns("PendAccess").DefaultValue = False
                        .Columns("PendAccounts").DefaultValue = False
                        .Columns("PendUsers").DefaultValue = False
                    End With


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Tuesday, May 2, 2017 8:11 PM

  • Thanks for the idea Cor, unfortunately I am still not getting default int values.

    Dim SelUsersCols As String = "ID,ntusername,FirstName,LastName,FullName,DateCreated,DateModify,GrantAdmin,GrantAccess," & "GrantAccounts,GrantHR,GrantUsers,PendAccess,PendAccounts,PendUsers,Updateby,Notes"

    Using UsersDA As New MySqlDataAdapter("SELECT " & SelUsersCols & " FROM users", MySQLConn) UsersDA.MissingSchemaAction = MissingSchemaAction.AddWithKey UsersDA.FillSchema(UsersTable, SchemaType.Source) UsersDA.Fill(UsersTable) End Using




    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    GTGripodi,

    That was the opposite from what I meant, try to avoid the columns with default values if you don't update them and for sure don't use the FillSchema, that simply add all columns to your datatable and will therefore update them. The fill creates columns which are in the select.

    Be aware that you can also set a default value in the datatable if this what I suggest won't go. 

    https://msdn.microsoft.com/en-us/library/system.data.datacolumn.defaultvalue(v=vs.110).aspx


    Success
    Cor

    • Marked as answer by Gtripodi Wednesday, May 3, 2017 1:15 PM
    Tuesday, May 2, 2017 10:35 PM
  • Hello,

    No matter the database, the default value is only displayed when using a SELECT statement unless MySQL is doing something odd.

    For instance, if I have a table in SQL-Server and insert a row with one field having a default value e.g.

    And did an INSERT

    Imports System.Data.SqlClient
    Public Class Sample
        Private ConnectionString As String =
            <Text>
                Data Source=KARENS-PC;
                Initial Catalog=ForumExamples;
            Integrated Security=True
            </Text>.Value
    
        Public Sub Demo()
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO DefaultValueExample (FirstName) VALUES (@FirstName)"
                    cmd.Parameters.AddWithValue("@FirstName", "Charles")
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
    

    Then did d a SELECT, for that record I'd expect the default value of 10 to display.

    Imports System.Data.SqlClient
    Public Class Sample
        Private ConnectionString As String =
            <Text>
                Data Source=KARENS-PC;
                Initial Catalog=ForumExamples;
            Integrated Security=True
            </Text>.Value
    
        Public Sub Demo()
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO DefaultValueExample (FirstName) VALUES (@FirstName)"
                    cmd.Parameters.AddWithValue("@FirstName", "Charles")
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
    

    Now let's set the value to 1

    Imports System.Data.SqlClient
    Public Class Sample
        Private ConnectionString As String =
            <Text>
                Data Source=KARENS-PC;
                Initial Catalog=ForumExamples;
            Integrated Security=True
            </Text>.Value
    
        Public Sub Demo()
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO DefaultValueExample (UserId,FirstName) VALUES (@UserId,@FirstName)"
                    cmd.Parameters.AddWithValue("@FirstName", "Charles")
                    cmd.Parameters.AddWithValue("@UserId", 1)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
    

    Both results

    SELECT id
          ,UserId
          ,FirstName
    FROM DefaultValueExample

    Yes I approached this differently, using a different database but that should not matter. The default value if not set should show in a SELECT statement as the default value.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, May 2, 2017 10:53 PM
    Moderator
  • Hi Gtripidi,

    I create one table in the MySQL, and set ColumnAge column default value =0. Please refer to the code below.

     Private Sub fun()
            Dim connectionString As String = "Server=127.0.0.1;Database=test;Uid=root;Pwd=;"
            Dim sql As String = "select * from test2"
            Dim dt As New DataTable()
            Dim con As New MySqlConnection(connectionString)
            Dim cmd As New MySqlCommand(sql, con)
            cmd.CommandType = CommandType.Text
            Dim sda As New MySqlDataAdapter(cmd)
            sda.Fill(dt)
            Dim builder As New MySqlCommandBuilder(sda)
            Dim dr As DataRow = dt.NewRow()
            dr("ID") = 3
            dr("ColumnName") = "Cherry"
            dr("ColumnSex") = "WOman"
            dr("ColumnAge") = 26
            dt.Rows.Add(dr)
            sda.Update(dt)
            DataGridView1.DataSource = dt
    
        End Sub

    Hope it is helpful to you.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, May 3, 2017 5:58 AM
    Moderator
  • Hello,

    No matter the database, the default value is only displayed when using a SELECT statement unless MySQL is doing something odd.

    For instance, if I have a table in SQL-Server and insert a row with one field having a default value e.g.

    And did an INSERT

    Imports System.Data.SqlClient
    Public Class Sample
        Private ConnectionString As String =
            <Text>
                Data Source=KARENS-PC;
                Initial Catalog=ForumExamples;
            Integrated Security=True
            </Text>.Value
    
        Public Sub Demo()
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO DefaultValueExample (FirstName) VALUES (@FirstName)"
                    cmd.Parameters.AddWithValue("@FirstName", "Charles")
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class

    Then did d a SELECT, for that record I'd expect the default value of 10 to display.

    Imports System.Data.SqlClient
    Public Class Sample
        Private ConnectionString As String =
            <Text>
                Data Source=KARENS-PC;
                Initial Catalog=ForumExamples;
            Integrated Security=True
            </Text>.Value
    
        Public Sub Demo()
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO DefaultValueExample (FirstName) VALUES (@FirstName)"
                    cmd.Parameters.AddWithValue("@FirstName", "Charles")
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class

    Now let's set the value to 1

    Imports System.Data.SqlClient
    Public Class Sample
        Private ConnectionString As String =
            <Text>
                Data Source=KARENS-PC;
                Initial Catalog=ForumExamples;
            Integrated Security=True
            </Text>.Value
    
        Public Sub Demo()
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO DefaultValueExample (UserId,FirstName) VALUES (@UserId,@FirstName)"
                    cmd.Parameters.AddWithValue("@FirstName", "Charles")
                    cmd.Parameters.AddWithValue("@UserId", 1)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class

    Both results

    SELECT id
          ,UserId
          ,FirstName
    FROM DefaultValueExample

    Yes I approached this differently, using a different database but that should not matter. The default value if not set should show in a SELECT statement as the default value.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Hi Karen, Thank you for the extensive example! I am not sure I am ready to ditch the CommandBuilder quite yet.

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, May 3, 2017 12:09 PM
  • Hi Gtripidi,

    I create one table in the MySQL, and set ColumnAge column default value =0. Please refer to the code below.

     Private Sub fun()
            Dim connectionString As String = "Server=127.0.0.1;Database=test;Uid=root;Pwd=;"
            Dim sql As String = "select * from test2"
            Dim dt As New DataTable()
            Dim con As New MySqlConnection(connectionString)
            Dim cmd As New MySqlCommand(sql, con)
            cmd.CommandType = CommandType.Text
            Dim sda As New MySqlDataAdapter(cmd)
            sda.Fill(dt)
            Dim builder As New MySqlCommandBuilder(sda)
            Dim dr As DataRow = dt.NewRow()
            dr("ID") = 3
            dr("ColumnName") = "Cherry"
            dr("ColumnSex") = "WOman"
            dr("ColumnAge") = 26
            dt.Rows.Add(dr)
            sda.Update(dt)
            DataGridView1.DataSource = dt
    
        End Sub

    Hope it is helpful to you.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Hello Cherry, a pleasure to meet you!

    Essentially I do the same thing to insert rows, sometimes I will use a binding source, and in which this case still does not produce a default value at the database level. Sample code below is more or less how I interact with databases that are subject to control databinding. If there are no controls that need bound to the data then I make my changes to the datatable directly and leave out the bindingsource.

    I am a bit puzzled on this one. 

    Imports MySql.Data.MySqlClient
    Public Class Form1
        Dim IsNew As Boolean = False
        WithEvents UsersTable As New DataTable
        Dim UsersBindingSource As New BindingSource
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using MYSQLConn As New MySqlConnection(My.Settings.mysqlconn)
                Using DA As New MySqlDataAdapter("SELECT * FROM users", MYSQLConn)
                    DA.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    DA.FillSchema(UsersTable, SchemaType.Source)
                    DA.Fill(UsersTable)
                    UsersBindingSource.DataSource = UsersTable
                End Using
            End Using
        End Sub
        Private Sub UsersTableNewRow() Handles UsersTable.TableNewRow
            IsNew = True
        End Sub
        Private Sub btn_insert_Click(sender As Object, e As EventArgs) Handles btn_insert.Click
            Using MYSQLConn As New MySqlConnection(My.Settings.mysqlconn)
                Using DA As New MySqlDataAdapter("SELECT * FROM users", MYSQLConn)
                    UsersBindingSource.AddNew()
                    UsersBindingSource.Current("ntusername") = Environment.UserName
                    UsersBindingSource.EndEdit()
                    Dim CB As New MySqlCommandBuilder(DA)
                    DA.Update(UsersTable)
                    If IsNew = True Then
                        UsersTable.Clear()
                        DA.MissingSchemaAction = MissingSchemaAction.AddWithKey
                        DA.FillSchema(UsersTable, SchemaType.Source)
                        DA.Fill(UsersTable)
                        IsNew = False
                    End If
                End Using
            End Using
        End Sub
    End Class
    


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, May 3, 2017 12:51 PM

  • Thanks for the idea Cor, unfortunately I am still not getting default int values.

    Dim SelUsersCols As String = "ID,ntusername,FirstName,LastName,FullName,DateCreated,DateModify,GrantAdmin,GrantAccess," & "GrantAccounts,GrantHR,GrantUsers,PendAccess,PendAccounts,PendUsers,Updateby,Notes"

    Using UsersDA As New MySqlDataAdapter("SELECT " & SelUsersCols & " FROM users", MySQLConn) UsersDA.MissingSchemaAction = MissingSchemaAction.AddWithKey UsersDA.FillSchema(UsersTable, SchemaType.Source) UsersDA.Fill(UsersTable) End Using




    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    GTGripodi,

    That was the opposite from what I meant, try to avoid the columns with default values if you don't update them and for sure don't use the FillSchema, that simply add all columns to your datatable and will therefore update them. The fill creates columns which are in the select.

    Be aware that you can also set a default value in the datatable if this what I suggest won't go. 

    https://msdn.microsoft.com/en-us/library/system.data.datacolumn.defaultvalue(v=vs.110).aspx


    Success
    Cor

    Now I see what you mean, I was figuring that you were figuring there is some difference from selecting cols by name and selecting all cols "*", I didnt catch on that you were suggesting that I omit the cols with default values.

    Anyway... you're right. Following this logic I have to assume that the DA is actually inserting a NULL value into the cols and overriding the default value rules on the database.

    I do need the cols in the statement though, so I am going to have to handle it at the datatable.

            Using MYSQLConn As New MySqlConnection(My.Settings.mysqlconn)
                Using DA As New MySqlDataAdapter("SELECT ID,ntusername FROM users", MYSQLConn)
                    UsersBindingSource.AddNew()
                    UsersBindingSource.Current("ntusername") = Environment.UserName
                    UsersBindingSource.EndEdit()
                    Dim CB As New MySqlCommandBuilder(DA)
                    DA.Update(UsersTable)
                    If IsNew = True Then
                        UsersTable.Clear()
                        DA.MissingSchemaAction = MissingSchemaAction.AddWithKey
                        DA.FillSchema(UsersTable, SchemaType.Source)
                        DA.Fill(UsersTable)
                        IsNew = False
                    End If
                End Using
            End Using

     

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi


    • Edited by Gtripodi Wednesday, May 3, 2017 1:14 PM
    Wednesday, May 3, 2017 1:13 PM