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>
dBforums
>
Deleting with null value
03-11-04, 11:40
Linear Mode
