error in deleting,updating and records in asp

method · May 2nd, 2005, 09:02 AM

Hi experts . I got an asp page that supposed to add,delete records from access 2000 mdb file. It loads all the records but when i try to delete or update i get these errrors. I be happy if some one help me resolve these problems and be able to add new records.Thanks
----------------------------------------------------------
Delete error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] The record cannot be deleted or
changed because table 'COMMITTEE_MEMBERS' includes related records.
/asp/asp/tenissdb_edit.asp, line 134

------------------------------------------------------------
update error:

Technical Information (for support personnel)

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'CInt'
/asp/asp/tenissdb_edit.asp, line 204

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)

Page:
POST 201 bytes to /asp/asp/tenissdb_edit.asp

POST Data:
playerno=44&NAME=Bakerck&INITIALS=E&BIRTH_DATE=1%2 F9%2F1963&************=M&JOINED=1980
&STREET=Lewis+Street&HOUSENO=23&POSTCODE=4444LJ&TO WN=Inglewood&PHONENO=070-368753
&LEAGUENO=1124&Update+Database=Submit+Quer .

-----------------------------------------------------------

Here is the code:
<%

%>

<%

' Our connection string... you can replace this with whatever
' connection string you want to use.
Dim CONN_STRING
CONN_STRING = "DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & Server.Mappath("/db/db.mdb") & ";"

' The name of this file so all the links and forms will still
' work if you rename it.
Dim SCRIPT_NAME
SCRIPT_NAME = Request.ServerVariables("SCRIPT_NAME")

' I use this link a lot so I threw it into a "pseudo-const"
' so I don't have to keep typing it.
Dim BACK_TO_LIST_TEXT
BACK_TO_LIST_TEXT = "<p>Click <a href=""" & SCRIPT_NAME & """>" _
& "here</a> to go back to record list.</p>"

' Declare our standard variables.
Dim cnnDBEdit, rstDBEdit ' ADO objects

Dim strSQL ' To hold various SQL Strings
Dim iRecordId ' Used to keep track of the record in play

' Choose what to do by looking at the action parameter
Select Case LCase(Trim(Request.QueryString("action")))
Case "add"
' Select an empty RS
strSQL = "SELECT * FROM players WHERE playerno=0;"

Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
rstDBEdit.Open strSQL, CONN_STRING, adOpenKeyset, adLockOptimistic, adCmdText

' Add our record and set it's values. You could bounce
' into an edit mode here to let people enter the initial
' values, but for simplicity I just add the record with
' some default values.
rstDBEdit.AddNew

' rstDBEdit.Fields("text_field").Value = CStr(WeekdayName(WeekDay(Date())))
' rstDBEdit.Fields("integer_field").Value = CInt(Day(Now()))
' rstDBEdit.Fields("date_time_field").Value = Now()

%>
<p>
i made this add new records part
</p>

<form action="<%= SCRIPT_NAME %>?action=editsave" method="post">
<input type="text" name="playerno" value="playerno"/><br />
<input type="text" name="NAME" value="NAME" /><br />
<input type="text" name="INITIALS" value="INITIALS" /><br />
<input type="text" name="BIRTH_DATE" value="BIRTH_DATE" /><br />
<input type="text" name="************" value="************" /><br />
<input type="text" name="JOINED" value="JOINED" /><br />
<input type="text" name="STREET" value="STREET" /><br />
<input type="text" name="HOUSENO" value="HOUSENO" /><br />
<input type="text" name="POSTCODE" value="POSTCODE" /><br />
<input type="text" name="TOWN" value="TOWN" /><br />
<input type="text" name="PHONENO" value="PHONENO" /><br />
<input type="text" name="LEAGUENO" value="LEAGUENO" /><br />

<input type="submit" name="add records to Database">
</form>
<%
' i had to comment this next line after adding the add record form
' rstDBEdit.Update

' Get the id of the record just added. This might cause
' problems with some DB providers, but it works with
' the SQL Server our sample runs off.
iRecordId = rstDBEdit.Fields("playerno").Value

' i had to comment this next line after adding the add record form

' rstDBEdit.Close
Set rstDBEdit = Nothing

Response.Write("<p>Record playerno #" & iRecordId & " added!</p>")
Response.Write(BACK_TO_LIST_TEXT)

' Here's the more efficient way, but since I want to get
' back the new record's id I'm not using it.
'
'strSQL = "INSERT INTO players " _
' & "(text_field, integer_field, date_time_field) " _
' & "VALUES (" _
' & "'" & CStr(WeekdayName(WeekDay(Date()))) & "', " _
' & CInt(Day(Now())) & ", " _
' & "'" & Now() & "'" _
' & ")"
'
''Response.Write strSQL
'
'Set cnnDBEdit = Server.CreateObject("ADODB.Connection")
'cnnDBEdit.Open CONN_STRING
'
'cnnDBEdit.Execute strSQL, adAffectAll, adCmdText Or adExecuteNoRecords
'
'cnnDBEdit.Close
'Set cnnDBEdit = Nothing
Case "delete"
' Get the id to delete
iRecordId = Request.QueryString("playerno")
If IsNumeric(iRecordId) Then
iRecordId = CLng(iRecordId)
Else
iRecordId = 0
End If

strSQL = "DELETE FROM PLAYERS WHERE playerno=" & iRecordId & ";"

Set cnnDBEdit = Server.CreateObject("ADODB.Connection")
cnnDBEdit.Open CONN_STRING

cnnDBEdit.Execute strSQL, adAffectAll, adExecuteNoRecords

cnnDBEdit.Close
Set cnnDBEdit = Nothing

' We assume all is fine. Notice that we really don't
' check that anything was done... we just assume
' so since no error was thrown. For example... if
' you enter an id that's not really in the DB, the
' script runs fine, but nothing gets deleted.
Response.Write("Record playerno #" & iRecordId & " deleted!")
Response.Write(BACK_TO_LIST_TEXT)
Case "edit"
' First of a 2 part process... build a form with the
' values from the db.
iRecordId = Request.QueryString("playerno")
If IsNumeric(iRecordId) Then
iRecordId = CLng(iRecordId)
Else
iRecordId = 0
End If

strSQL = "SELECT * FROM players WHERE playerno=" & iRecordId & ";"

Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
rstDBEdit.Open strSQL, CONN_STRING, adOpenKeyset, adLockOptimistic, adCmdText

If Not rstDBEdit.EOF Then
%>
<p>
Note: Watch your input... the text field is small and no error
handling is done to check for valid integers or dates. If
an error gets thrown when you submit simply hit back and fix
the offending entry before resubmitting.
</p>

<form action="<%= SCRIPT_NAME %>?action=editsave" method="post">
<input type="hidden" name="playerno" value="<%= rstDBEdit.Fields("playerno").Value %>"/><br />
<input type="text" name="NAME" value="<%= Server.HTMLEncode(rstDBEdit.Fields("NAME").Value) %>" /><br />
<input type="text" name="INITIALS" value="<%= Server.HTMLEncode(rstDBEdit.Fields("INITIALS").Val ue) %>" /><br />
<input type="text" name="BIRTH_DATE" value="<%= Server.HTMLEncode(rstDBEdit.Fields("BIRTH_DATE").V alue) %>" /><br />
<input type="text" name="************" value="<%= Server.HTMLEncode(rstDBEdit.Fields("************").Value) %>" /><br />
<input type="text" name="JOINED" value="<%= Server.HTMLEncode(rstDBEdit.Fields("JOINED").Value ) %>" /><br />
<input type="text" name="STREET" value="<%= Server.HTMLEncode(rstDBEdit.Fields("STREET").Value ) %>" /><br />
<input type="text" name="HOUSENO" value="<%= Server.HTMLEncode(rstDBEdit.Fields("HOUSENO").Valu e) %>" /><br />
<input type="text" name="POSTCODE" value="<%= Server.HTMLEncode(rstDBEdit.Fields("POSTCODE").Val ue) %>" /><br />
<input type="text" name="TOWN" value="<%= Server.HTMLEncode(rstDBEdit.Fields("TOWN").Value) %>" /><br />
<input type="text" name="PHONENO" value="<%= Server.HTMLEncode(rstDBEdit.Fields("PHONENO").Valu e) %>" /><br />
<input type="text" name="LEAGUENO" value="<%= Server.HTMLEncode(rstDBEdit.Fields("LEAGUENO").Val ue) %>" /><br />

<input type="submit" name="Update Database">
</form>
<%
Else
Response.Write "Record not found!"
End If

rstDBEdit.Close
Set rstDBEdit = Nothing

Response.Write(BACK_TO_LIST_TEXT)
Case "editsave"
' Part 2 of 2: Here's where we save the values that the
' user entered back to the DB. Again... no error
' handling or input checking so ' characters and invalid
' values will throw error messages.
iRecordId = Request.Form("PLAYERNO")
iRecordId = Replace(iRecordId, "'", "''")

' Date delimiter on this should be changed to # for Access
strSQL = "UPDATE PLAYERS SET " _
& "NAME = '" & CStr(Replace(Request.Form("NAME"), "'", "''")) & "', " _

& "INITIALS = " & CInt(Replace(Request.Form("INITIALS"), "'", "''")) & ", " _

& "BIRTH_DATE = '" & CStr(Replace(Request.Form("BIRTH_DATE"), "'", "''")) & "', " _

& "************ = '" & CStr(Replace(Request.Form("************"), "'", "''")) & "', " _

& "JOINED = '" & CStr(Replace(Request.Form("JOINED"), "'", "''")) & "', " _

& "STREET = '" & CStr(Replace(Request.Form("STREET"), "'", "''")) & "', " _

& "HOUSENO = '" & CStr(Replace(Request.Form("HOUSENO"), "'", "''")) & ", " _

& "POSTCODE = '" & CStr(Replace(Request.Form("POSTCODE"), "'", "''")) & ", " _

& "TOWN = '" & CStr(Replace(Request.Form("TOWN"), "'", "''")) & "', " _

& "PHONENO = '" & CStr(Replace(Request.Form("PHONENO"), "'", "''")) & ", " _

& "LEAGUENO = '" & CDate(Replace(Request.Form("LEAGUENO"), "'", "''")) & ", " _

& "WHERE (PLAYERNO = " & iRecordId & ")"

' If something does throw an error, checking this is
' actually a valid command often helps debug.
Response.Write strSQL

Set cnnDBEdit = Server.CreateObject("ADODB.Connection")
cnnDBEdit.Open CONN_STRING

cnnDBEdit.Execute strSQL, adAffectAll, adCmdText Or adExecuteNoRecords

cnnDBEdit.Close
Set cnnDBEdit = Nothing

Response.Write("<p>Record Id #" & iRecordId & " updated!</p>")
Response.Write(BACK_TO_LIST_TEXT)
Case Else ' view
' Our default action... just lists the records in the DB
strSQL = "SELECT * FROM players ORDER BY playerno;"

Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
rstDBEdit.Open strSQL, CONN_STRING, adOpenForwardOnly, adLockReadOnly, adCmdText
%>
<table border="1" cellspacing="2" cellpadding="2">
<thead>
<tr>
<th>PLAYERNO</th>
<th>NAME</th>
<th>INITIALS</th>
<th>BIRTH_DATE</th>

<th>************</th>
<th>JOINED</th>
<th>STREET</th>
<th>HOUSENO</th>
<th>POSTCODE</th>
<th>TOWN</th>
<th>PHONENO</th>
<th>LEAGUENO</th>

<th>Delete</th>
<th>Edit</th>
</tr>
</thead>
<tbody>
<%
Do While Not rstDBEdit.EOF
%>
<tr>
<td><%= rstDBEdit.Fields("PLAYERNO").Value %></td>
<td><%= rstDBEdit.Fields("NAME").Value %></td>
<td><%= rstDBEdit.Fields("INITIALS").Value %></td>
<td><%= rstDBEdit.Fields("BIRTH_DATE").Value %></td>
<td><%= rstDBEdit.Fields("************").Value %></td>
<td><%= rstDBEdit.Fields("JOINED").Value %></td>
<td><%= rstDBEdit.Fields("STREET").Value %></td>
<td><%= rstDBEdit.Fields("HOUSENO").Value %></td>
<td><%= rstDBEdit.Fields("POSTCODE").Value %></td>
<td><%= rstDBEdit.Fields("TOWN").Value %></td>
<td><%= rstDBEdit.Fields("PHONENO").Value %></td>
<td><%= rstDBEdit.Fields("LEAGUENO").Value %></td>

<td><a href="<%= SCRIPT_NAME %>?action=delete&playerno=<%= rstDBEdit.Fields("PLAYERNO").Value %>">Delete</a></td>
<td><a href="<%= SCRIPT_NAME %>?action=edit&playerno=<%= rstDBEdit.Fields("PLAYERNO").Value %>">Edit</a></td>
</tr>
<%
rstDBEdit.MoveNext
Loop
%>
</tbody>
<tfoot>
<tr>
<td colspan="6" align="right"><a href="<%= SCRIPT_NAME %>?action=add">Add a new record</a></td>
</tr>
</tfoot>
</table>
<%
rstDBEdit.Close
Set rstDBEdit = Nothing
End Select
%>

luckyfelix · May 6th, 2005, 10:35 AM

Method... It looks like you could have 1 of 2 problems. The error you got is typically one that shows up if you have a relational database instead of a flat databae. Basically if the tables, forms or queries in your access DB have "Relatioships" you can not delete info from one table without first removing the relationships to the other tables.
Honestly it looks like you have a lot more code you are using to delete a record from the DB than you need... It can be much more simple.
1. Call your database
2. Define your variables
3. identify the record you want deleted
4. Set up the string
5. error handling

Here is an example (the config file and stringfunctions are more or less irrelevant for you right now
Hope this helps!

<%
' Heather P 2001
' Details: Delete selected order
%>

<%
on error resume next

dim conntemp, rstemp, rstemp2, mySql
'Calls DNS Less connection from OpenDB.asp
call openDb()
'Defines Which Record is to be deleted from previous form
pIdOrder = getUserInput(request.querystring("idOrder"),10)
'Error Handling if IDOrder comes thru as nothing
if pIdOrder="" then
response.redirect "message.asp?message=You must select the order you want to delete."
end if

' update orders record !

mySql="DELETE RECORD FROM orders1 WHERE idorder=" &pIdorder
set rstemp=conntemp.execute(mySQL)
'Error handling if record does not exist or string is bad
if err.number <> 0 then
response.redirect "supporterror.asp?error="& Server.Urlencode("Error in DeleteOrderExec2: "&Err.Description)
end if
If it works you are redirected and shown a message!
response.redirect "message.asp?message=The order information has been deleted"
%>