Delete Record Question

Bkster · Sep 1, 2002

I have an application that allows a user to cancel a job. I used the delete record behavior. It is working90% of the time. The table that the record is being deleted from is only two columns. Job_ID and Employee_ID. It looks like the problem occurs because only the employee_id is being deleted and not the job_id. This causes a problem when someone goes to sign up for a job because the job_id is the primary key and the user gets an error because duplicate entries are not allowed for the job. Like I said it works fine 90% of the time. I am just having to check the database table daily because the job shows up as available but the end user gets the error when they try and sign up for it. Any ideas?

Thanks

Brian

mlawson · Sep 2, 2002

Hi Brian,

Post your code up and I'll see how it fairs against some standard VB code (it is VB and not Java?)

M "There are 3 kinds of people; those that can count and those that can't"

Bkster · Sep 2, 2002

Here is the code. Thanks for the help.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME&quot

)
If (Request.QueryString <> "&quot

Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Delete Record: declare variables

if (CStr(Request("MM_delete&quot

) = "form1" And CStr(Request("MM_recordId&quot

) <> "&quot

Then

MM_editConnection = MM_airport_STRING
MM_editTable = "tblsch_signup"
MM_editColumn = "schedule_id"
MM_recordId = "" + Request.Form("MM_recordId&quot

+ ""
MM_editRedirectUrl = "admin_home.asp"

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "&quot

Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "&quot

Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%>
<%
' *** Delete Record: construct a sql delete statement and execute it

If (CStr(Request("MM_delete&quot

) <> "" And CStr(Request("MM_recordId&quot

) <> "&quot

Then

' create the sql delete statement
MM_editQuery = "delete from " & MM_editTable & " where " & MM_editColumn & " = " & MM_recordId

If (Not MM_abortEdit) Then
' execute the delete
Set MM_editCmd = Server.CreateObject("ADODB.Command&quot

MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "&quot

Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>
<%
Dim conf__MMColParam
conf__MMColParam = "1"
If (Request.QueryString("schedule_id&quot

<> "&quot

Then
conf__MMColParam = Request.QueryString("schedule_id&quot

End If
%>
<%
Dim conf
Dim conf_numRows

Set conf = Server.CreateObject("ADODB.Recordset&quot

conf.ActiveConnection = MM_airport_STRING
conf.Source = "SELECT * FROM qryassignments WHERE schedule_id = " + Replace(conf__MMColParam, "'", "''&quot

+ ""
conf.CursorType = 0
conf.CursorLocation = 2
conf.LockType = 1
conf.Open()

conf_numRows = 0
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body leftmargin="0" topmargin="0">
<p><strong><font size="3" face="Arial, Helvetica, sans-serif">Confirm Cancellation
</font></strong></p>
<form name="form1" method="POST" action="<%=MM_editAction%>">
<font size="2" face="Arial, Helvetica, sans-serif">This is to confirm that you
are canceling Officer <%=(conf.Fields.Item("fname&quot

.Value)%> <%=(conf.Fields.Item("lname&quot

.Value)%> from Post <%=(conf.Fields.Item("post&quot

.Value)%> on <%=(conf.Fields.Item("dates&quot

.Value)%> from <%=(conf.Fields.Item("times&quot

.Value)%> . If this is correct click the Cancel button.
<input type="submit" name="Submit" value="Cancel">
</font>
<input type="hidden" name="MM_delete" value="form1">
<input type="hidden" name="MM_recordId" value="<%= conf.Fields.Item("schedule_id&quot

.Value %>">
</form>
<p> </p>
<p>  </p>
</body>
</html>
<%
conf.Close()
Set conf = Nothing
%>

CTekMedia · Sep 18, 2002

Is your database auto-generating the primary key? (JobID)? What database are you using?

Cheers Peace

http://www.bluetone.biz

BT

Bkster · Sep 18, 2002

Thanks for the reply. I forgot to come and close this out. I had forgot to restrict a page that was behind a password protected page. They were bookmarking that page and the available jobs were showing but no session variable had been establish so it was inserting a blank value into the database. Thanks

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Delete Record Question

Bkster

Technical User

mlawson

Technical User

Bkster

Technical User

CTekMedia

Programmer

Bkster

Technical User

Similar threads

Part and Inventory Search

Sponsor