dcsimg
Results 1 to 4 of 4

Thread: OleDb.OleDbDataAdapter InsertCommand Parameter Default Value

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    232

    OleDb.OleDbDataAdapter InsertCommand Parameter Default Value

    I have an OleDb.OleDbDataAdapter with an InsertCommand parameter as follows:

    Code:
    insertData.Parameters.Add("@Notes", OleDb.OleDbType.LongVarWChar, 65536).SourceColumn = "Notes"
    The "Notes" field in the MS Access database table is not a required field, so I set the IsNullable property of the InsertCommand parameter to True as follows:

    Code:
    insertData.Parameters("@Notes").IsNullable = True
    I am setting the InsertCommand parameter values before calling the ExecuteNonQuery method of the DataAdapter. If there is no data for the Notes field, then I don't set that parameter.

    When I call the DataAdapter.ExecuteNonQuery() method, my code raises the following error:

    Name:  2019-05-19_13-28-30.jpg
Views: 124
Size:  24.1 KB

    I don't know how to set a default value for an InsertCommand parameter.

    What can I do to avoid this error?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,757

    Re: OleDb.OleDbDataAdapter InsertCommand Parameter Default Value

    Quote Originally Posted by Mark@SF View Post
    I am setting the InsertCommand parameter values before calling the ExecuteNonQuery method of the DataAdapter.
    That is a nonsensical statement. A data adapter has no such method. A command object has an ExecuteNonQuery method but you would never call that directly on a command inside a data adapter. The whole point of a data adapter is that you call its Update method and pass a DataTable and it then executes the appropriate command based on the RowState of each DataRow. The whole point of the SourceColumn of the parameter is that it specifies which column of that DataTable the parameter value should be drawn from. You're not supposed to be setting the Value of a parameter yourself.

    I suggest that you follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data for some ADO.NET code examples that show how to use commands and data adapters in various scenarios. If you still can't work out what to do from that, post ALL of the relevant code.

    To be specific, if you are getting that particular error message then you are either not setting the Value of a parameter or setting it to Nothing. In ADO.NET, NULL is represented by DBNull.Value, not Nothing. Even if you're using a data adapter properly with a DataTable, if your DataTable contains Nothing rather than DBNull.Value then you will still get that same error message.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    232

    Re: OleDb.OleDbDataAdapter InsertCommand Parameter Default Value

    jmc -

    Appreciate your quick response. Always learn a lot from your feedback!

    I probably didn't use the correct wording in my original posting, but I am setting the InsertCommand parameters immediately prior to calling the DataAdapter.InsertComand.ExecuteNonQuery() method.

    Code:
                conState = con.State    '...capture initial connection state
                If con.State <> ConnectionState.Open Then con.Open()
    
                daStockSplits.InsertCommand.Parameters("@AddDate").Value = Date.Now
                daStockSplits.InsertCommand.Parameters("@ModDate").Value = Date.Now
                daStockSplits.InsertCommand.Parameters("@SplitDate").Value = dtmDate
                daStockSplits.InsertCommand.Parameters("@AssetID").Value = lngAssetID
                daStockSplits.InsertCommand.Parameters("@OldShares").Value = CDbl(1)
                daStockSplits.InsertCommand.Parameters("@NewShares").Value = dblRatio
                '...see also the the ImportTransactions procedure for additional discussion about handling "Notes" values that are Null 
                If String.IsNullOrEmpty(strNote) Then
                    daStockSplits.InsertCommand.Parameters("@Notes").Value = DBNull.Value
                Else
                    daStockSplits.InsertCommand.Parameters("@Notes").Value = strNote
                End If
    
                '...insert new record into the MS Access dB table "Stock Splits"
                daStockSplits.InsertCommand.ExecuteNonQuery()
                Application.DoEvents() '...allow the OS to complete any pending tasks (e.g., datatable I/O)
                '...do not call the dsMain.Tables("StockSplits").AcceptChanges() method because we're not going to change the StockSplits DataTable at this point
                '   (we'll reload this DataTable from the MS Access dB table "Stock Splits" after all the Quicken transactions have been imported)
    It's not the answer to my original question, but I did manage to figure out how to pass a Null value to a string argument of a procedure in order to avoid the "Conversion from type 'DBNull' to type 'String' is not valid." error.

    [CODE]
    CreateStkSplit(CDate(rowData("RecDate")), CLng(rowData("AssetID")), CDbl(rowData("Quantity")), rowData("Note").ToString, flgAdjShares:=True)
    [/[CODE]

    The rowData("Note").ToString converts the DBNull.Value to an empty string.

    In the CreateStkSplit procedure I then test if the "strNote" argument IsNullOrEmpty and set the "@Notes" parameter of the daStockSplits.InsertCommand accordingly (as shown in the above code snippet).

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    232

    Red face Re: OleDb.OleDbDataAdapter InsertCommand Parameter Default Value

    jmc -

    You asked to see my code.

    Here's the procedure that I use to initialize the daStockSplits DataAdapter.

    Code:
        Sub InitializeStockSplitsDataAdapter()
    
            'https://support.microsoft.com/en-us/kb/320435
    
            '...MS Access database table "Stock Splits" field information:
            '	RecID field is the auto-number field
            '	AddDate field is required, DefaultValue = Date()
            '	ModDate field is required, DefaultValue = Date()
            '	SplitDate field is required, DefaultValue = Date()
            '	AssetID field is required
            '	OldShares field is required
            '	NewShares field is required
            '	Notes field is not required
    
            Dim strMethodName = New System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name    '...this procedure's name
    
            Dim flgDebug As Boolean = False   '...debug/test purposes only
    
            'Dim selectData As New OleDb.OleDbCommand("SELECT * FROM [Stock Splits] ORDER BY SplitDate", con)
            Dim selectData As New OleDb.OleDbCommand("SELECT * FROM [Stock Splits] WHERE AssetID = @AssetID ORDER BY SplitDate", con)
            Dim deleteData As New OleDb.OleDbCommand("DELETE * FROM [Stock Splits] WHERE RecID = @RecID", con)
            Dim insertData As New OleDb.OleDbCommand("INSERT INTO [Stock Splits] (AddDate, ModDate, SplitDate, AssetID, OldShares, NewShares, Notes) " & _
                                                     "VALUES (@AddDate, @ModDate, @SplitDate, @AssetID, @OldShares, @NewShares, @Notes)", con)
            Dim updateData As New OleDb.OleDbCommand("UPDATE [Stock Splits] SET ModDate = @ModDate, SplitDate = @SplitDate, AssetID = @AssetID, OldShares = @OldShares, NewShares = @NewShares, Notes = @Notes " & _
                                                     "WHERE RecID = @RecID", con)
    
            Try
                selectData.Parameters.Add("@AssetID", OleDb.OleDbType.BigInt).SourceColumn = "AssetID"
    
                deleteData.Parameters.Add("@RecID", OleDb.OleDbType.BigInt).SourceColumn = "RecID"
    
                insertData.Parameters.Add("@AddDate", OleDb.OleDbType.Date).SourceColumn = "AddDate"
                insertData.Parameters.Add("@ModDate", OleDb.OleDbType.Date).SourceColumn = "ModDate"
                insertData.Parameters.Add("@SplitDate", OleDb.OleDbType.Date).SourceColumn = "SplitDate"
                insertData.Parameters.Add("@AssetID", OleDb.OleDbType.BigInt).SourceColumn = "AssetID"
                insertData.Parameters.Add("@OldShares", OleDb.OleDbType.Double).SourceColumn = "OldShares"
                insertData.Parameters.Add("@NewShares", OleDb.OleDbType.Double).SourceColumn = "NewShares"
                insertData.Parameters.Add("@Notes", OleDb.OleDbType.LongVarWChar, 65536).SourceColumn = "Notes"
    
                '...set IsNullable property for insertData.Parameters that are not required
                insertData.Parameters("@Notes").IsNullable = True
    
                updateData.Parameters.Add("@ModDate", OleDb.OleDbType.Date).SourceColumn = "ModDate"
                updateData.Parameters.Add("@SplitDate", OleDb.OleDbType.Date).SourceColumn = "SplitDate"
                updateData.Parameters.Add("@AssetID", OleDb.OleDbType.BigInt).SourceColumn = "AssetID"
                updateData.Parameters.Add("@OldShares", OleDb.OleDbType.Double).SourceColumn = "OldShares"
                updateData.Parameters.Add("@NewShares", OleDb.OleDbType.Double).SourceColumn = "NewShares"
                updateData.Parameters.Add("@Notes", OleDb.OleDbType.LongVarWChar, 65536).SourceColumn = "Notes"
                updateData.Parameters.Add("@RecID", OleDb.OleDbType.BigInt).SourceColumn = "RecID"
    
                '...set IsNullable property for updateData.Parameters that are not required
                updateData.Parameters("@Notes").IsNullable = True
    
                daStockSplits.SelectCommand = selectData
                daStockSplits.DeleteCommand = deleteData
                daStockSplits.InsertCommand = insertData
                daStockSplits.UpdateCommand = updateData
    
                '...delegate for handling RowUpdated event (needed to get the the Identity column value (@@IDENTITY) of the added record)
                AddHandler daStockSplits.RowUpdated, AddressOf Row_Updated
    
                daStockSplits.MissingSchemaAction = MissingSchemaAction.AddWithKey
    
            Catch ex As Exception
                MessageBox.Show(ex.GetType.ToString & vbCrLf & vbCrLf & ex.Message & " (err=" & Err.Number & ")", strMethodName, MessageBoxButtons.OK, MessageBoxIcon.Error)
    
            Finally
                selectData = Nothing
                deleteData = Nothing
                insertData = Nothing
                updateData = Nothing
    
            End Try
    
        End Sub
    Here's the procedure that I use to load the Stock Splits data from the MS Access db table into the OleDb DataTable.

    Code:
        Sub LoadStockSplitsData(lngAssetID As Long)
    
            '...MS Access database table "Stock Splits" field information:
            '	RecID field is the auto-number field
            '	AddDate field is required, DefaultValue = Date()
            '	ModDate field is required, DefaultValue = Date()
            '	SplitDate field is required, DefaultValue = Date()
            '	AssetID field is required
            '	OldShares field is required
            '	NewShares field is required
            '	Notes field is not required
    
            '...if lngAssetID = 0, then the datatable will be loaded but empty (columns defined, but no rows)
    
            Dim strMethodName = New System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name    '...this procedure's name
    
            Dim flgDebug As Boolean = False   '...debug/test purposes only
    
            Dim conState As ConnectionState
    
            Try
                conState = con.State
                If con.State = ConnectionState.Closed Then con.Open()
    
                dsMain.EnforceConstraints = False
    
                If Not IsNothing(dsMain.Tables("StockSplits")) Then
                    dsMain.Tables("StockSplits").Rows.Clear() '...fires the corresponding BindingSource's PositionChanged() event
                Else
                    '...create columns (data-types cannot be changed after the DataAdapter fills the DataTable)
                    dsMain.Tables.Add("StockSplits")
                    dsMain.Tables("StockSplits").Columns.Add("AddDate").DataType = System.Type.GetType("System.DateTime")
                    dsMain.Tables("StockSplits").Columns.Add("ModDate").DataType = System.Type.GetType("System.DateTime")
                    dsMain.Tables("StockSplits").Columns.Add("SplitDate").DataType = System.Type.GetType("System.DateTime")
                    dsMain.Tables("StockSplits").Columns.Add("AssetID").DataType = System.Type.GetType("System.Int32")
                    dsMain.Tables("StockSplits").Columns.Add("OldShares").DataType = System.Type.GetType("System.Double")
                    dsMain.Tables("StockSplits").Columns.Add("NewShares").DataType = System.Type.GetType("System.Double")
                    dsMain.Tables("StockSplits").Columns.Add("Notes").DataType = System.Type.GetType("System.String")
    
                    '...unless explicitly set, DefaultValue properties are System.DBNull
                    With dsMain.Tables("StockSplits")
                        .Columns("AddDate").DefaultValue = Date.Now
                        .Columns("ModDate").DefaultValue = Date.Now
                        .Columns("SplitDate").DefaultValue = Date.Now
                        '.Columns("AssetID").DefaultValue = vbnull	'...TBD, required but no number specified
                        '.Columns("OldShares").DefaultValue = vbnull	'...TBD, required but no number specified
                        '.Columns("NewShares").DefaultValue = vbnull	'...TBD, required but no number specified
                        .Columns("Notes").DefaultValue = DBNull.Value   '...not required and no number specified
                    End With
                End If
    
                daStockSplits.SelectCommand.Parameters("@AssetID").Value = lngAssetID
    
                '...fill StockSplits DataTable
                daStockSplits.Fill(dsMain, "StockSplits")
    
                dsMain.EnforceConstraints = True
    
                If flgDebug Then Debug.WriteLine("StockSplits datatable has " & dsMain.Tables("StockSplits").Rows.Count & " rows.")
    
            Catch ex As Exception
                MessageBox.Show(ex.GetType.ToString & vbCrLf & vbCrLf & ex.Message & " (err=" & Err.Number & ")", strMethodName, MessageBoxButtons.OK, MessageBoxIcon.Error)
    
            Finally
                RestoreConnection(conState)
                conState = Nothing
    
            End Try
    
        End Sub
    Here's the procedure that I use to insert a record into the MS Access dB "Stock Splits" table.

    Code:
        Sub CreateStkSplit(dtmDate As Date, lngAssetID As Long, dblRatio As Double, strNote As String, flgAdjShares As Boolean)
    
            Dim strMethodName = New System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name    '...this procedure's name
    
            Dim flgDebug As Boolean = False   '...debug/test purposes only
    
            Dim strSQL As String
            Dim conState As ConnectionState
    
            Try
                conState = con.State    '...capture initial connection state
                If con.State <> ConnectionState.Open Then con.Open()
    
                daStockSplits.InsertCommand.Parameters("@AddDate").Value = Date.Now
                daStockSplits.InsertCommand.Parameters("@ModDate").Value = Date.Now
                daStockSplits.InsertCommand.Parameters("@SplitDate").Value = dtmDate
                daStockSplits.InsertCommand.Parameters("@AssetID").Value = lngAssetID
                daStockSplits.InsertCommand.Parameters("@OldShares").Value = CDbl(1)
                daStockSplits.InsertCommand.Parameters("@NewShares").Value = dblRatio
                '...see also the the ImportTransactions procedure for additional discussion about handling "Notes" values that are Null 
                If String.IsNullOrEmpty(strNote) Then
                    daStockSplits.InsertCommand.Parameters("@Notes").Value = DBNull.Value
                Else
                    daStockSplits.InsertCommand.Parameters("@Notes").Value = strNote
                End If
    
                '...insert new record into the MS Access dB table "Stock Splits"
                daStockSplits.InsertCommand.ExecuteNonQuery()
                Application.DoEvents() '...allow the OS to complete any pending tasks (e.g., datatable I/O)
                '...do not call the dsMain.Tables("StockSplits").AcceptChanges() method because we're not going to change the StockSplits DataTable at this point
                '   (we'll reload this DataTable from the MS Access dB table "Stock Splits" after all the Quicken transactions have been imported)
    
                If flgAdjShares Then
                    '...update the ShareBalance in the MS Access dB table "Transactions"
                    strSQL = "UPDATE Transactions SET ShareBalance = ShareBalance * " & dblRatio & " WHERE AssetID = " & lngAssetID & " AND ShareBalance > 0 AND RecDate <=#" & dtmDate & "#"
                    Using cmdInsert As New OleDb.OleDbCommand(strSQL, con)
                        cmdInsert.ExecuteNonQuery()
                    End Using
                    Application.DoEvents() '...allow the OS to complete any pending tasks (e.g., datatable I/O)
                    '...update the PositionShares in the MS Access dB table "Positions"
                    strSQL = "UPDATE Positions SET PositionShares = PositionShares * " & dblRatio & " WHERE AssetID = " & lngAssetID & " AND PositionShares > 0"
                    Using cmdInsert As New OleDb.OleDbCommand(strSQL, con)
                        cmdInsert.ExecuteNonQuery()
                    End Using
                    Application.DoEvents() '...allow the OS to complete any pending tasks (e.g., datatable I/O)
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.GetType.ToString & vbCrLf & vbCrLf & ex.Message & " (err=" & Err.Number & ")", strMethodName, MessageBoxButtons.OK, MessageBoxIcon.Error)
    
            Finally
                RestoreConnection(conState)
                conState = Nothing
    
            End Try
    
        End Sub
    My prior posting showed how I call the CreateStkSplit procedure and pass the data to its arguments.

    Since I'm importing thousands of Quicken transactions (20+ years of financial history), I am working directly with the underlying MS Access dB instead of populating the OleDb DataTable and calling the dsMain.Tables("StockSplits").AcceptChanges() method to send the imported data to the MS Access db table. Once I've finished the data import, then I re-load the OleDb DataTable and refresh the bound controls on the form via the BindingSource.ResetBindings method.

    Sorry for the "code dump", but hopefully it will be useful to understand what I'm trying to do
    Last edited by Mark@SF; May 19th, 2019 at 08:18 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width