Add record that has default values to database

[Pinto]

I have a table with several fields.

All but 2 of the fields have a default value specified in the Table.
I was assuming that I only needed to specify the fields I want changed and I did not have to specify each field value. (Isn't That the purpose of a default value?)
If i do not specify each field I can an error saying the field can not be null.

Below is my code. Can anyone show me the erros of may ways.

         sql = "Select [Tool Crib].* From [Tool Crib]"

        Try
            m_cnToolCrib.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PathCMDB()
            m_cnToolCrib.Open()
            m_daToolCrib = New OleDb.OleDbDataAdapter(sql, m_cnToolCrib)
            m_cbToolCrib = New OleDb.OleDbCommandBuilder(m_daToolCrib)
            m_cbToolCrib.QuotePrefix = "["
            m_cbToolCrib.QuoteSuffix = "]"
            m_daToolCrib.Fill(m_dtToolCrib)

            drNewRow("Type ID") = (TryCast(cboToolSetupAvailTools.EditValue, myItemData)).ItemData.ToString()

            For Each row As CategoryRow In vgToolParameters.Rows
                Dim childRows As VGridRows = row.ChildRows


                For Each childRow As EditorRow In childRows
 
                    If (UCase(childRow.Properties.Caption) = "COLOR") Then
                        drNewRow("Color") = ColorTranslator.ToOle(childRow.Properties.Value)
                    Else
                        drNewRow(childRow.Properties.Caption) = childRow.Properties.Value
                    End If
                Next childRow
            Next row

            m_dtToolCrib.Rows.Add(drNewRow)
            m_daToolCrib.Update(m_dtToolCrib)

        Catch ex As Exception
            MessageBox.Show("AddRecordToToolCrib: " & ex.Message)
        End Try

        m_dtToolCrib.Dispose()
        m_daToolCrib.Dispose()

 

[jmcilhinney]

You obviously have default values set for your database columns but do you have the DefaultValue property set for each DataColumn in the DataTable? If not then each column in the a DataRow will contain DBNull.Value by default.

I'm a little confused by your code. The fact that you use CategoryRow and EditorRow as types suggests that you have a typed DataSet and yet you're using a data adapter and a command builder. Do you have a typed DataSet, i.e. an XSD file in the Solution Explorer that was generated by the Data Source wizard? If so then your code should be changed significantly and you can check and/or set the DefaultValue of each column in the DataSet designer. If not then you can check and/or set the DefaultValue of each column in code after calling Fill. I would have thought that the DefaultValue would be determined automatically from the database but maybe not.

 

[Pinto]

you can ignore the CategoryRow and EditorRow they are or a 3rd party control so I understand why you were confused.

Just like you I assumed that the default value would be automatically determined. I am using a acess database with a flat table.

I am not clear with regards to your statement "You obviously have default values set for your database columns but do you have the DefaultValue property set for each DataColumn in the DataTable?"

Again using an access database I would assume it is taking care of.

So I just want to open a database and add a row with a few values and the rest of the values should use the default value already set in the access table.

Is what I am doing just wrong?

 

[jmcilhinney]

Are you creating the schema in your DataTable yourself or are you relying on its being generated automatically when you call Fill? If it's the former then that may be your issue. If it's the latter then try setting the MissingSchemaAction of the data adapter to AddWithKey before calling Fill and then loop through the Columns of the DataTable after calling Fill and see what the DefaultValue of each one is. If they're all DBNull.Value then you may have to set them explicitly.

 

[Pinto]

No I am not createing the datatable. I am trying to convert some old VB6 code. So the database has already been established.

In VB6 they used a recordset like this

Set db = DBEngine.Workspaces(0).OpenDatabase(PathCMDB(), True)
    If m_ToolCribMode = MODE_ADD Then
        Set rs = db.OpenRecordset("Tool Crib", dbOpenDynaset)
        
        With rs
            .AddNew
            
                rs.Fields("Type ID").Value = cboToolSetupAvailTools.ItemData(cboToolSetupAvailTools.ListIndex)
                rs.Fields("Color").Value = txtToolCribColor.BackColor
            
            .Update
            .Bookmark = .LastModified
       
        End With

I will keep plugging away at it and try to figure it out

 

[jmcilhinney]

Like I said, loop through the Columns of the DataTable after calling Fill and check the DefaultValue or each one. Have you set the MissingSchemaAction of the data adapter as I suggested?

 

[Pinto]

I did set the missingschemaaction and I also looped through the columns. The defaultvalue is retuning a value of nothing which is stranged since if you look at the table in MSAccess they clearly have defaultvalues.

Some thing in VB.Net are just so much harder to do then in VB6

 

[Pinto]

This is the database I am using. You can see the Tool Crib table has default values.
I just can not seem to get it to work where I only set the new values for some of the fields.
I think the only way I can get it to work is to set every value, which just makes setting default values in the database irrelevant.

This is such a simple task I just don't understand why it is so difficult.

Thanks for nay help you may be able to give.

 

[jmcilhinney]

It does seem like a deficiency but, as far as I can tell, ADO.NET will not automatically get the default value for a column when generating schema for a DataTable. Here's how you can do it:

Private Sub SetColumnDefaultValuesFromDatabase(table As DataTable, tableName As String, connectionString As String)
    Using connection As New OleDbConnection(connectionString)
        connection.Open()

        Dim schema = connection.GetSchema("Columns", {Nothing, Nothing, tableName})

        For Each row As DataRow In schema.Rows
            table.Columns(CStr(row("COLUMN_NAME"))).DefaultValue = row("COLUMN_DEFAULT")
        Next
    End Using
End Sub

 

[Pinto]

Thanks for helping with this. I may send a message to Microsoft. I certainly don't like loosing functionality when I upgrade a product.
Maybe the answer is to not use ADO and use something else. I know I don't want to use dao anymore since it is so old.

Anyway thnaks for the help