none
error in cmd.Parameters.AddWithValue RRS feed

  • Question

  • Dear All

    Why i am getting this error in below code 

    The parameterized query '(@d60 datetime,@d61 nvarchar(8),@d62 nvarchar(4000),@d63 nvarcha' expects the parameter '@d71', which was not supplied.

       Dim cb2 As String = "insert into ledgerdetails(date,transactionid,description,debit,credit,balance,coaiddr,accountnamedr,coaidcr,accountnamecr,userid,blncecr) VALUES (@d60,@d61,@d62,@d63,@d64,@d65,@d66,@d67,@d68,@d69,@d70,@d71)"
            cmd = New SqlCommand(cb2)
            cmd.Connection = con
            ' Prepare command for repeated execution
            cmd.Prepare()
            ' Data to be inserted
            For Each row As DataGridViewRow In CashpaymentDataGridView.Rows
                If Not row.IsNewRow Then
                    cmd.Parameters.AddWithValue("@d60", row.Cells(2).Value)  'date
                    cmd.Parameters.AddWithValue("@d61", "CPV" & "" & row.Cells(1).Value) 'transactionid
                    cmd.Parameters.AddWithValue("@d62", row.Cells(9).Value)  'description
                    cmd.Parameters.AddWithValue("@d63", row.Cells(7).Value)  'debit
                    cmd.Parameters.AddWithValue("@d64", row.Cells(8).Value)  'credit
                    cmd.Parameters.AddWithValue("@d65", row.Cells(11).Value)  'balance
                    cmd.Parameters.AddWithValue("@d66", row.Cells(3).Value)  'coaiddr
                    cmd.Parameters.AddWithValue("@d67", row.Cells(4).Value)  'accountnamedr
                    cmd.Parameters.AddWithValue("@d68", row.Cells(5).Value)  'coaidcr
                    cmd.Parameters.AddWithValue("@d69", row.Cells(6).Value)  'accountnamecr
                    cmd.Parameters.AddWithValue("@d70", row.Cells(10).Value)  'userid
                    cmd.Parameters.AddWithValue("@d71", row.Cells(12).Value)  'userid
    
    
                    '      cmd.Parameters.AddWithValue("@d10", row.Cells(9).Value) 'userid
                    cmd.ExecuteNonQuery()
                    cmd.Parameters.Clear()
                End If
            Next
            con.Close()
    
    

    Monday, July 29, 2019 10:02 AM

Answers

  • i found the problem and solved it 

    the issue was ,the value of @d71 was string instead of int 

    • Marked as answer by Omi4u Monday, July 29, 2019 1:36 PM
    Monday, July 29, 2019 1:36 PM

All replies

  • Hard to say as there are 12 fields, 12 values and 12 parameters. Make sure there is a value in row.Cells(12).Value.

    I would on a side note use [date] rather than date in the insert statement.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, July 29, 2019 10:21 AM
    Moderator
  • value im taking from textbox......means when user fill the form and press enter, the details will move to datagridview and from that datagridview this code will update ledger ... but during update im getting this error 
    Monday, July 29, 2019 10:36 AM
  • value im taking from textbox......means when user fill the form and press enter, the details will move to datagridview and from that datagridview this code will update ledger ... but during update im getting this error 

    Does it happen on all rows or just one row?

    Another thing I would recommend is not to create the parameters in the for each statement, instead have a function like this.

    Private Function AddRecordCommand(cn As SqlConnection) As SqlCommand
        Dim cmd As New SqlCommand With {.Connection = cn}
    
        cmd.CommandText =
            <SQL>
                INSERT INTO Customer 
                (
                    CompanyName,
                    ContactName,
                    EstablishedYear,
                    Incorporated
                ) 
                VALUES 
                (
                    @CompanyName,
                    @ContactName,
                    @EstablishedYear,
                    @Incorporated
                )
            </SQL>.Value
    
        cmd.Parameters.Add(New SqlParameter() With {.ParameterName = "@CompanyName", .DbType = DbType.String})
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@ContactName", .DbType = DbType.String})
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@EstablishedYear", .DbType = DbType.Int32})
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Incorporated", .DbType = DbType.Date})
    
        Return cmd
    
    End Function

    Then iterate the data (yes the following does not iterate a DataGridView, it's a pattern which can be followed with a DataGridView)

    Public Function AddNewCustomerRecords(customersList As List(Of Customer)) As Boolean
        Dim affected As Integer = 0
    
        Try
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                cn.Open()
    
                For Each customer As Customer In customersList
                    '
                    ' Create a command for adding a new record. This must
                    ' be done each iteration as in the code to get the new primary
                    ' key the command is altered.
                    '
                    Dim cmdAdd = AddRecordCommand(cn)
    
                    cmdAdd.Parameters("@CompanyName").Value = customer.CompanyName
                    cmdAdd.Parameters("@ContactName").Value = customer.ContactName
                    cmdAdd.Parameters("@EstablishedYear").Value = customer.EstablishedYear
                    cmdAdd.Parameters("@Incorporated").Value = customer.Incorporated
    
                    '
                    ' Add record
                    '
                    affected = cmdAdd.ExecuteNonQuery()
    
                    '
                    ' If affected equals 1, this means the record was added,
                    ' in turn get the new primary key by changing the command text.
                    ' No need to clear parameters.
                    '
                    If affected = 1 Then
                        cmdAdd.CommandText = "Select @@Identity"
                        customer.Id = CInt(cmdAdd.ExecuteScalar)
                    End If
    
                Next
    
            End Using
        Catch ex As Exception
    
            ' write code to handle exception
    
            Return False
        End Try
    
        Return True
    
    End Function


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, July 29, 2019 10:53 AM
    Moderator
  • only for one which is @d71

    but if i change row.cells(12).value) to textbox12.text 

    then it work fine 

    and only this line making issue 

    Monday, July 29, 2019 11:08 AM
  • i found the problem and solved it 

    the issue was ,the value of @d71 was string instead of int 

    • Marked as answer by Omi4u Monday, July 29, 2019 1:36 PM
    Monday, July 29, 2019 1:36 PM
  • i found the problem and solved it 

    the issue was ,the value of @d71 was string instead of int 

    Good you found it but if you follow how I presented a solution providing the wrong data type is impossible as each item's properties are strong typed thus preventing this issue. I never rely on values right from a DataGridView but instead use the DataGridView.DataSource which means never work with a DataGridView by adding rows to the DataGridView unless the DataGridView is read only, never used to export to a database etc.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, July 29, 2019 2:15 PM
    Moderator