Delete SQL statement Issues with Access db

Question

User-1188570427 posted

I'm receiving an error message when trying to run this delete statement. Does anyone see anything wrong?

  Try
                Dim sqlstring2 As String = ""
                Using conn As New Data.OleDb.OleDbConnection(mainconnection2)
                    sqlstring2 = "Delete FROM tblmain " + _
                    "WHERE year_id = @year_id AND giver_name = @giver_name " + _
                    "AND rec_name = @rec_name"

                    MsgBox(sqlstring2)
                    Dim cmd As New Data.OleDb.OleDbCommand(sqlstring2, conn)
                    cmd.Parameters.AddWithValue("@year_id", HttpContext.Current.Session("year").ToString())
                    cmd.Parameters.AddWithValue("@giver_name", giver_name)
                    cmd.Parameters.AddWithValue("@rec_name", rec_name)
                    conn.Open()
                    delete_success = cmd.ExecuteNonQuery() : conn.Close()
                End Using
            Catch ex As Exception
                logging.errors(ex.ToString, error_l)
            End Try
            If delete_success = 1 Then
                MsgBox("record deleted")
            Else
                MsgBox("failed deleted/no record found")
            End If

Error: 

12/21/2011 4:38:23 AM----> System.Data.OleDb.OleDbException: No value given for one or more required parameters.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

Answer 1

User-1199946673 posted

This error is caused by 2 reasons:
1. You didn't specify 1 or more parameters, like the error says. But in your code I see 3 parameters and you specify 3 parameters as well, so this isn't the problem

2. You misspelled 1 (or more) fieldnames, which then will be threaded as parameters as well, which you didn't specify off course

So I suggest you take a cloeser look in the Command, Do the fields yeard_id, giver_name and rec_name exist in the table tblmain?

Answer 2

User309206123 posted

Instead of passing variables, try passing default values for yearid, giver name and receiver name and see if the command works. If it works then issue with values being passed.

Answer 3

User-1188570427 posted

Both Answers helped. I guess it's too early to be coding. I took the code from a different table which had the columns different (since they are a different data types). I fixed that. Also instead of storing the name in the table I was trying to delete, I store their specific ID. So I was passing their name instead of their id. It's working now.

Thanks.

Answer 4

User-231977777 posted

hi ,

try to Replace your parameters with( ? ) mark .like this

"WHERE year_id = ? AND giver_name = ? AND rec_name = ? "

and when you need to put values , make them ordered 

cmd.Parameters.AddWithValue("?", HttpContext.Current.Session("year").ToString()) cmd.Parameters.AddWithValue("?", giver_name) cmd.Parameters.AddWithValue("?", rec_name)

let me know if it works. thanks

Nice