SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to Delete a record from an Access Database

    I'm making a foray into ASP.NET for my own edifiction, and so far it's been interesting but a little frustrating.

    I'm developing a simple add/edit/delete application for a list of "clients." I have written the code to display the list, and add new clients, but I'm stuck on deleting them. Here's the code for the delete page, which displays a confirmation, then should delete the client in question:

    Code:
    Partial Class Default2
        Inherits System.Web.UI.Page
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Label1.Text = "Are you sure you  want to Delete Client Number " & Request.QueryString("cnum").ToString & "?"
        End Sub
    
        Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
            Try
                Dim dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("books.mdb"))
                Dim sql = "DELETE FROM Client WHERE ClientNum = ?"
                Using dbconn
                    Dim command As New OleDbCommand()
                    command.Connection = dbconn
                    command.CommandText = sql
                    command.Parameters.AddWithValue("ClientNum", Request.QueryString("cnum").ToString)
                    command.Connection.Open()
                    command.ExecuteNonQuery()
                End Using
                Response.Redirect("Default.aspx")
            Catch ex As Exception
                Label1.Text = "Delete Failed! - " & ex.Message
                Button1.Text = "Try Again"
                Button2.Text = "Go Back to the List"
            End Try
        End Sub
    
        Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
            Response.Redirect("Default.aspx")
        End Sub
    End Class
    When I press the "yes" button, which is Button1, it returns the following error:

    No value given for one or more required parameters.

    I've seen references to this error around the net but haven't had any luck making the code work. Any help?
    Bring out our hope and reason, before we pine away.

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Try using @clientNum rather than ? in the query.

  3. #3
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That sounds extremely plausible -- I'll try it when I get to my development machine, thanks!
    Bring out our hope and reason, before we pine away.

  4. #4
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I finally was able to get back to the lab, and I made the change. The good news is that it deletes the record... the bad news is that it deletes EVERY record. Any idea why it would do that?
    Bring out our hope and reason, before we pine away.

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2007
    Location
    Almere, The Netherlands
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the WHERE clause of an Access action query you should always specify the table name. So the correct SQL statement is:

    "DELETE FROM Client WHERE Client.ClientNum = ClientNum"

    Furthermore I wouldn't recommend to use the @ in Access SQL, sometimes this will cause strange errors


Bookmarks

Posting Permissions

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