locked
Delete SQL statement Issues with Access db RRS feed

  • 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()
    Wednesday, December 21, 2011 5:46 AM

Answers

  • 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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 21, 2011 5:52 AM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 21, 2011 5:55 AM

All replies

  • 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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 21, 2011 5:52 AM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 21, 2011 5:55 AM
  • 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.

    Wednesday, December 21, 2011 6:10 AM
  • 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
    Wednesday, December 21, 2011 6:37 AM