error"No value given for one or more required parameters."

[Josef01]

I have a remove button like i would like to delete just sigle selected row from both datagridview and the actual database.the problem I am having is in deleting selected row.if i dont use the parameter the contents of the table gets deleted ,i dont have any primery key just an ID field (AutoNumber).i am struggling with it for while .

Dim sqlstring As String = "Delete from sample where id =@id"

I appreciate your help

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        If Not DataGridView1.CurrentRow.IsNewRow Then
            If DataGridView1.SelectedRows.Count = 0 Then
                MsgBox("No row was selected. If you are trying to remove a row, highlight the entire row by clicking on the identifier column on the far left.", MessageBoxIcon.Error, "Entire Row Not Selected")

            ElseIf DataGridView1.SelectedRows.Count = 1 Then
                Dim response As DialogResult = MessageBox.Show("Are you sure you want to delete the selected row?", "Delete row?", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
                If (response = DialogResult.Yes) Then
                    DataGridView1.Rows.Remove(DataGridView1.CurrentRow)

                    Dim cn As OleDbConnection
                    Dim cmd As OleDbCommand
                    Dim cnstring As String = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\testdb1.accdb;Persist Security Info=False")
                    Dim sqlstring As String = "Delete from sample where id =@id"
                    cn = New OleDbConnection(cnstring)
                    cmd = New OleDbCommand(sqlstring, cn)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    cn.Close()

               
                End If
            ElseIf DataGridView1.SelectedRows.Count > 1 Then
                MsgBox("Multiple rows are currently selected. The remove function can only delete one row at a time. Please select a single row and try again.", MessageBoxIcon.Error, "Single Row Not Selected")
                DataGridView1.ClearSelection()
            End If
        End If

    End Sub
End Class

[Marcel_Go]

why not using the datagridview.UserDeletingRow?
e.row.index gives the row one did ask to delete.

[Josef01]

Thank you for response, i am absolate new to vb.net .if possible can you explain little bit more.

[Mariandi]

First of all you are using an sql that need a parameter (@ID) and you don't provide that parameter. you need to tell the db what is that ID that you want to delete.

cmd.addparameterwithvalue('@id',[the selected row ID])

Secondly i would first delete from the Database and from the grid as if deleting from the Database fails you should warn your user and not remove it from the grid.

Thirdly, you should put some error handling whenever you are interacting with the Database (try catch statements) and see whether the deletion was successful.

Lastly, I think that your error messages you show to the user are really long. make your messages to the user simple short and clear (but that just my personal taste)

[Marcel_Go]

this is an example of my code, using to delete a row. Hope you find it usefull :

Private Sub Grid_2_Contacts_UserDeletingRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowCancelEventArgs) Handles Grid_2_Contacts.UserDeletingRow
Dim qry As String
Dim C_row As Integer
Dim answer As DialogResult
C_row = e.Row.Index
answer = MessageBox.Show("Sure to delete ?", "Deleting contact", MessageBoxButtons.YesNo)
If answer = Windows.Forms.DialogResult.Yes Then
qry = "delete from Tbl_Cnt where tbl_Cnt.Cnt_id=" & Grid_2_Contacts.Item("Cnt_Id", C_row).Value
Dim ItsOk As Boolean = Exec_Query(qry)
if ItsOk then
' code to delete the row in the grid
end if
Else
e.Cancel = True
End If
End Sub

in a module :

Function Exec_Query(ByVal sql As String) As Boolean
Dim todo As OleDbCommand
If connect.State = ConnectionState.Closed Then ConnToDB_Service()
todo = New OleDbCommand(sql, connect)
Try
todo.ExecuteNonQuery()
Return True ' ok
Catch ex As Exception
MessageBox.Show(ex.Message, "Watch out", MessageBoxButtons.OK)
Return False ' some error was made
End Try
' todo.Connection.Close()
' todo.Dispose()
End Function

Sub ConnToDB_Service()
Try
With connectie
If .State = ConnectionState.Open Then .Close()
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='\\Server_03\Devel\accdb\ServUsa.accdb';"
.Open()
End With
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Application.Exit()
End
End Try
End Sub

[Mariandi]

Oh you changed it! what's is the problem with this code?

[Marcel_Go]

Mariandi, it was my code to help josef01. And there is nothing wrong with it (I guess ;-)

[Mariandi]

sorry i answered in a hurry! didn't notice the different name on the post. although i did ask what's wrong with the new code cause it seems fine.

Josef01 on the first code you posted you need set the parameter @id in the following query. that is why you get the error "No value given for one or more required parameters."

Dim sqlstring As String = "Delete from sample where id =@id"
somewhere you need to add what @id is.

use this command
cmd.addparameterwithvalue('@id',[the selected row ID])

[Josef01]

Thank you so much, I got it to work with your help .now i can delete from both datagridview and table.Thank all of you again