Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    11

    Unanswered: Deleting with null value

    I have some rows in my access database that contain empty fields. But when i try to delete the rows because some of the fields are empty it will not delete the row. Is there any way around this problem?

    thanks in advance

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Deleting with null value

    Originally posted by slidj
    I have some rows in my access database that contain empty fields. But when i try to delete the rows because some of the fields are empty it will not delete the row. Is there any way around this problem?

    thanks in advance
    Can you be more specific? Example of data and query you're using, description of the table, etc.

  3. #3
    Join Date
    Mar 2004
    Posts
    11
    i am trying to delete entire rows from an access database, and some of these rows will contain NULL values, and as such is stopping the deletion process becasue of their presence. I am trying to delete all the data in the rows just by specifying the primary key. the code is below

    <html>
    <head>
    <title>Delete Event</title>
    </head>
    <body bgcolor="white" text="black">

    <%
    Dim strclubs

    ' Retrieve Input From Form
    strclubs = Request.Form("clubs")

    ' Open Database Connection
    Set conn = Server.CreateObject("ADODB.Connection")
    connStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("intelliGuide.mdb")
    Conn.Open connStr
    %>

    <form name="form" method="post" action="deleteevent.asp">

    <Select Name="clubs" size="1" style="position:absolute; z-index:1; left: 160; top: 150" ID="Select1" onchange=form.submit()>
    <option selected value="">Select a venue...
    <%
    strSQLQuery = "SELECT clubname FROM club"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQLQuery, conn, 3, 3

    While Not rs.EOF
    %>

    <option value="<%= rs("clubname") %>"><%= rs("clubname") %></option>

    <%
    rs.MoveNext
    Wend

    rs.close
    set rs = nothing
    %>

    </Select>

    <div><font face="Arial" style="position:absolute; z-index:1; left: 160; top: 200"><b>Club ID:</b></font></div>
    <input type="text" name="clubID" style="position:absolute; z-index:1; left: 260; top: 200" maxlength="5">

    <input type="Submit" name="Submit" value="Submit" style="position:absolute; z-index:1; left: 160; top: 250">

    <%
    if len(strclubs) > 0 then
    strSQLQuery = "SELECT clubID, clubname FROM club WHERE clubName like '%" & strclubs & "'"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQLQuery, conn, 3, 3

    Do While Not rs.Eof
    %>

    <div><font face="Arial" style="position:absolute; z-index:10; left: 360; top: 150"><b>
    <%
    Response.Write rs.Fields("clubID").Value
    %>
    </b></font></div>

    <%
    rs.MoveNext
    Loop
    rs.close

    end if

    set rs = Nothing
    %>

    </form>

    <%
    If request.form("Submit") = "Submit" Then

    strClubID = Request.Form("clubID")

    strSQLQuery = "SELECT clubname, street, postcode, tel, fax, picture, map FROM club WHERE clubID like '%" & strclubID & "'"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQLQuery, conn, 3, 3

    strClubname = rs.Fields("clubname").Value
    strstreet = rs.Fields("street").Value
    strpostcode = rs.Fields("postcode").Value
    strtel = rs.Fields("tel").Value
    strfax = rs.Fields("fax").Value
    strpicture = rs.Fields("picture").Value
    strmap = rs.Fields("map").Value

    rs.close
    set rs = nothing

    ' Assemble SQL Query String
    strSQLQuery = "DELETE Club ("
    strSQLQuery = strSQLQuery & "ClubID, clubname, street, postcode, "
    strSQLQuery = strSQLQuery & "tel, fax, picture, map"
    strSQLQuery = strSQLQuery & ") VALUES ('"
    strSQLQuery = strSQLQuery & strClubID & "', '" & strclubname & "', '"
    strSQLQuery = strSQLQuery & strstreet & "', '" & strpostcode & "', '"
    strSQLQuery = strSQLQuery & strtel & "', '" & strfax & "', '" & strpicture & "', '"
    strSQLQuery = strSQLQuery & strmap & "' )"

    'Execute Query String
    conn.execute(strSQLQuery)

    response.write("Venue Deleted Successfully.<p>")

    'Set Strings to Nothing
    strClubID = ""
    strclubname = ""
    street = ""
    strpostcode = ""
    strtel = ""
    strfax = ""
    strpicture = ""
    strmap = ""

    End If
    Set rs = Nothing
    Set conn = Nothing
    %>

    </body>
    </html>

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by slidj
    i am trying to delete entire rows from an access database, and some of these rows will contain NULL values, and as such is stopping the deletion process becasue of their presence. I am trying to delete all the data in the rows just by specifying the primary key. the code is below
    Look at the DELETE syntax... You specified DELETE...VALUES which makes no sense...

  5. #5
    Join Date
    Dec 2003
    Posts
    454
    You are using the syntax of INSERT statement for your DELETE statement.

    SQL = "DELETE MyTable WHERE MyTableID = " & myTableID

Posting Permissions

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