Redirect on error page 1

[Bkster]

I have a table that users are inserting data into when they sign up for a job. The fields are scehdule_id and employee_number. The table is in access. I have the schedule_id set as the primary key. If two people try and sign up for the same job at the same time one person is going to get the job and the other is going to get an error message about not being able to enter the data because of not being able to enter a duplicate index key, primary key.

What I want to know is how can I redirect the person who is getting the error to a page advising them that the job has already been filled.

Thanks

Brian

 

[schase]

Couple of thoughts off the top of my head.

First - am I correct in assuming that on a different page - someone enters the jobs that need to be filled? And then on the page you mention an employee enters their info to take the job?

Which if so - you could do a quick check on the database to see if the field is empty - if it is than insert/update, if not then response.redirect to "sorry your slow" type of page.

Is this what you are looking for? "Damn the torpedoes, full speed ahead!"

-Adm. James Farragut

Stuart

 

[Bkster]

Actually I have a session variable declared from the login in page. I then have the insert behavior set up with two hidden fields. The schedule_id and the employee_number which is the variable. When the user hits the sign up button the appropriate schedule_id along with the employee_number are inserted into the tblschedule. This is the table where the schedule_id field is set as the primary key. What your describing is what I need. Can I just add the response.redirect in since the insert function is already set up? If so can you give some general guidlines as to where I would insert this code.

Thanks

Brian

Brian

 

[Bkster]

Here is the code from the page that inserts the data into the table.

Thanks

Brian
<%@LANGUAGE=&quot;VBSCRIPT&quot; CODEPAGE=&quot;1252&quot;%>

<!--#include file=&quot;Connections/airport.asp&quot; -->
<%
' *** 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(&quot;SCRIPT_NAME&quot)
If (Request.QueryString <> &quot;&quot Then
MM_editAction = MM_editAction & &quot;?&quot; & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = &quot;&quot;
%>
<%
' *** Insert Record: set variables

If (CStr(Request(&quot;MM_insert&quot) = &quot;signup&quot Then

MM_editConnection = MM_airport_STRING
MM_editTable = &quot;tblsch_signup&quot;
MM_editRedirectUrl = &quot;schedule.asp&quot;
MM_fieldsStr = &quot;schedule_id|value|employee_number|value&quot;
MM_columnsStr = &quot;schedule_id|none,none,NULL|employee_number|',none,''&quot;

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, &quot;|&quot
MM_columns = Split(MM_columnsStr, &quot;|&quot

' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> &quot;&quot; And Request.QueryString <> &quot;&quot Then
If (InStr(1, MM_editRedirectUrl, &quot;?&quot;, vbTextCompare) = 0 And Request.QueryString <> &quot;&quot Then
MM_editRedirectUrl = MM_editRedirectUrl & &quot;?&quot; & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & &quot;&&quot; & Request.QueryString
End If
End If

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

Dim MM_tableValues
Dim MM_dbValues

If (CStr(Request(&quot;MM_insert&quot) <> &quot;&quot Then

' create the sql insert statement
MM_tableValues = &quot;&quot;
MM_dbValues = &quot;&quot;
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),&quot;,&quot
MM_delim = MM_typeArray(0)
If (MM_delim = &quot;none&quot Then MM_delim = &quot;&quot;
MM_altVal = MM_typeArray(1)
If (MM_altVal = &quot;none&quot Then MM_altVal = &quot;&quot;
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = &quot;none&quot Then MM_emptyVal = &quot;&quot;
If (MM_formVal = &quot;&quot Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> &quot;&quot Then
MM_formVal = MM_altVal
ElseIf (MM_delim = &quot;'&quot Then ' escape quotes
MM_formVal = &quot;'&quot; & Replace(MM_formVal,&quot;'&quot;,&quot;''&quot & &quot;'&quot;
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & &quot;,&quot;
MM_dbValues = MM_dbValues & &quot;,&quot;
End If
MM_tableValues = MM_tableValues & MM_columns(MM_i)
MM_dbValues = MM_dbValues & MM_formVal
Next
MM_editQuery = &quot;insert into &quot; & MM_editTable & &quot; (&quot; & MM_tableValues & &quot values (&quot; & MM_dbValues & &quot&quot;

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject(&quot;ADODB.Command&quot
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> &quot;&quot Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>
<%
Dim signup
Dim signup_numRows

Set signup = Server.CreateObject(&quot;ADODB.Recordset&quot
signup.ActiveConnection = MM_airport_STRING
signup.Source = &quot;SELECT * FROM qryavailable&quot;
signup.CursorType = 0
signup.CursorLocation = 2
signup.LockType = 1
signup.Open()

signup_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
signup_numRows = signup_numRows + Repeat1__numRows
%>
<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>
function DoDateTime(str, nNamedFormat, nLCID)
dim strRet
dim nOldLCID

strRet = str
If (nLCID > -1) Then
oldLCID = Session.LCID
End If

On Error Resume Next

If (nLCID > -1) Then
Session.LCID = nLCID
End If

If ((nLCID < 0) Or (Session.LCID = nLCID)) Then
strRet = FormatDateTime(str, nNamedFormat)
End If

If (nLCID > -1) Then
Session.LCID = oldLCID
End If

DoDateTime = strRet
End Function
</SCRIPT>

 

[schase]

ok try this - and note we're gonna be doing a bunch of trouble shooting here. I may need a test database and the page emailed to me. - you WILL have to go through the below code and change to match your variables, fields, etc.

right before this line <%
' *** Insert Record: construct a sql insert statement and execute it


put this

<%
Dim rs__MMColParam
rs__MMColParam = &quot;1&quot;
if (Session(&quot;svScheduleID&quot <> &quot;&quot then rs__MMColParam = Session(&quot;svScheduleID&quot
%>
<%
set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.ActiveConnection = MM_airport_STRING
rs.Source = &quot;SELECT * FROM tblsch_signup WHERE scheduleID = &quot; + Replace(rs__MMColParam, &quot;'&quot;, &quot;''&quot + &quot;&quot;
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()
rs_numRows = 0
%>
<% Dim strSignupCheck
strSignupCheck = rs(&quot;scheduleID)
%>
<% IF strSignupCheck = &quot;&quot; THEN
response.Redirect(&quot;alreadytaken.asp&quot
End IF
%>


&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Bkster]

Thanks for the help.

I pasted the code in and changed the neccesary information. The record set binding was showing a red ! next to it until I changed the variables and fields. However it still is not working. I am still getting the same error message. (see below) In this bit of code I am suppose to put a value between &quot;&quot;. I want the redirect to occur if the field has any value in it (not Null)

<% IF strSignupCheck = &quot;&quot; THEN
response.Redirect(&quot;alreadytaken.asp&quot
End IF
%>



Also this code should go in the page that is sending the information to the tblsch_signup right?

Thanks again.

<% IF strSignupCheck = &quot;&quot; THEN
response.Redirect(&quot;alreadytaken.asp&quot
End IF
%>

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
/airport/signup.asp, line 140


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Crayon Crawler)

Page:
POST 69 bytes to /airport/signup.asp

POST Data:
Sign+Up=Sign+Up&schedule_id=10&employee_number=06272&MM_insert=signup

Time:
Wednesday, July 24, 2002, 6:39:46 PM


More information:
Microsoft Support

 

[Bkster]

Couple more thoughts on this. Keeping in mind I have limited knowledge in the coding world. Does the code you sent me run before I attempt to insert the record. More than likely when the page loads there would not be a conflict since I have a qryavailable that is filtering if the employee_number is null.If there is no employee_number associated with the schedule_id then the job displays. The problem is when I fire the insert command by clicking the Sign Up Button, if someone has fired the insert command for the same job and I have not refreshed my page I would be sending a duplicate entry for that schedule_id.
I think what I need to happen is that when the user clicks on the sign up button to insert into the tblsch_signup table it needs to check to make sure that the schedule_id is not already in the table.

What do you think?

Thanks

Brian

 

[Bkster]

Here is the rest of the code I dont know if this will help or not.

Thanks
Brian

<html>
<head>
<title>Available Jobs</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
</head>

<body leftmargin=&quot;0&quot; topmargin=&quot;0&quot; marginwidth=&quot;0&quot; marginheight=&quot;0&quot;>
<p><font size=&quot;3&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><strong>Available Jobs</strong></font></p>
<table width=&quot;562&quot; border=&quot;0&quot;>
<tr>
<td width=&quot;13%&quot;> <div align=&quot;center&quot;><strong><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>Post</font></strong></div></td>
<td width=&quot;36%&quot;> <div align=&quot;center&quot;><strong><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>Date</font></strong></div></td>
<td width=&quot;18%&quot;> <div align=&quot;center&quot;><strong><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>Time</font></strong></div></td>
<td width=&quot;33%&quot;> <div align=&quot;center&quot;><font size=&quot;2&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot;></font></font></div></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT signup.EOF))
%>
<tr>
<td height=&quot;40&quot; bgcolor=&quot;cccc66&quot;>
<div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(signup.Fields.Item(&quot;post&quot.Value)%></font></div></td>
<td bgcolor=&quot;cccc66&quot;>
<div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%= DoDateTime((signup.Fields.Item(&quot;date&quot.Value), 1, 1033) %></font></div></td>
<td bgcolor=&quot;cccc66&quot;>
<div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(signup.Fields.Item(&quot;time&quot.Value)%></font></div></td>
<td valign=&quot;middle&quot; bgcolor=&quot;#666666&quot;>
<div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><br>
</font>
<form action=&quot;<%=MM_editAction%>&quot; method=&quot;POST&quot; name=&quot;signup&quot; id=&quot;signup&quot;>
<div align=&quot;left&quot;> <font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>
<input name=&quot;Sign Up&quot; type=&quot;submit&quot; id=&quot;Sign Up&quot; value=&quot;Sign Up&quot;>
<input name=&quot;schedule_id&quot; type=&quot;hidden&quot; id=&quot;schedule_id&quot; value=&quot;<%=(signup.Fields.Item(&quot;schedule_id&quot.Value)%>&quot;>
<input name=&quot;employee_number&quot; type=&quot;hidden&quot; id=&quot;employee_number&quot; value=&quot;<%= Session(&quot;MM_Username&quot %>&quot;>
</font></div>
<font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>
<input type=&quot;hidden&quot; name=&quot;MM_insert&quot; value=&quot;signup&quot;>
</font>
</form>
</div></td>
</tr>

<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
signup.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
signup.Close()
Set signup = Nothing
%>

 

[schase]

oops sorry my bad.

ok try this

change
<% IF strSignupCheck = &quot;&quot; THEN
response.Redirect(&quot;alreadytaken.asp&quot
End IF
%>

to

<% IF strSignupCheck <> &quot;&quot; THEN
response.Redirect(&quot;alreadytaken.asp&quot
End IF
%>
&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Bkster]

I got it Thanks for the help. I ended up taking the session variable out and that did the trick. This is the code I used just in case anyone else reads this. I was already passing the schedule_id in a hidden field on submit.

Thanks again very helpful as always.
Brian

<%
set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.ActiveConnection = MM_airport_STRING
rs.Source = &quot;SELECT * FROM tblsch_signup WHERE schedule_id = &quot; + Replace(rs__MMColParam, &quot;'&quot;, &quot;''&quot + &quot;&quot;
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()
rs_numRows = 0
%>
<% Dim strSignupCheck
strSignupCheck = rs(&quot;schedule_id&quot
%>
<% IF strSignupCheck <> &quot;&quot; THEN
response.Redirect(&quot;alreadytaken.asp&quot
End IF
%>

 

[schase]

ahhh very cool, usually it's the small things that cause the biggest headaches &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Bkster]

hold the presses I started to celebrate a little early.

It is now redirecting me when I click on my sign up page. Which leads me back to the theory that I need something that checks the schedule_id variable that I am about to insert against the table when I click the sign up button. Maybe VBscript on the Input Button. It is now checking when the page loads.

This sign_up page has an additional record set on it that filters out avaiable jobs which list all available jobs thus there are many schedule_ids on the page.
I just have two fields insering into the tblsch_signup table. schedule_id and employee_number both are in hiddenfields

Darn I thought we had it.

Thanks

Brian

 

[schase]

ok, back to basics.

Go over the structure for me.

I.e. user logs in - this page logs the user in & checks the database for available records.

Once logged in this takes them to xxxxxxx.asp this page does, and has.....

etc.

&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[schase]

ok, back to basics.

Go over the structure for me.

I.e. user logs in - this page logs the user in & checks the database for available records.

Once logged in this takes them to xxxxxxx.asp this page does, and has.....

etc.

&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Bkster]

Ok

User logins (login.asp)when they pass the login they go to main_frame.asp Top frame is logo and hyper links (main.asp) main frame is schedule.asp shows which jobs they are signed up for.

All hyper links from top frame point to main frame.
There is a hyper link that points to available jobs-
when a user clicks on this link it opens signup.asp it displays all jobs that are avaiable. It is looking at a query that combines tblscedule and tblsch_signup if the employee number is null in the tblsch_signup table the record is displayed. Meaning no one has yet signed up for the job.
The signup.asp page has a table that displays the avaiable jobs. in the last cell is a form with a submit button. within the form are two hidden fields and the insert behavior. The two hidden fields are employee_number and schedule_id which insert into the tblsch_signup table. So now the early mentioned query will not display this job because the employee_number field is no longer null.

I have the table row set up to repeat the region so I will have up to 100 jobs listed each with there own sign up button.

If a user wants a job they simply click the sign up button and the insert behavior redirect is set to take them to schedule.asp which displays the jobs they are currently assigned to work. When they do this the job they signed up for is listed and is no longer available when the signup.asp is accessed.

This all works great. Maybe a little history on what this is for. I am a police officer and we work off duty jobs. We have a job that is paying very well and alot of people want to do it. When the system is opened up alot of people will be accessing it at the same time. So here is the scenario

Iam officer #1 I sign in at 0900hrs at the same time officer #2 signs in. We both access the available jobs page (signup.asp) I decide I want to sign up for job 1 I click the sign up button and get the job. Officer #2 decides he wants job#1 also. The job is still displaying because he has not refreshed the page. If he would the job would not display. Anyway he hits the sign up button and gets the access error (see earlier post). Because job 1 schedule_id is already in the tblsch_signup and the field is set as the primary key with no duplicates. Because only one person can sign up for each slot. Instead of getting the error he needs to get a message that the job was just filled.

Is it any clearer now.

Thanks again for your time
Brian

 

[schase]

ok, yes thanks, a roadmap is sometimes very much needed.

Ok, so correct me if I am wrong - the signup.asp page has an update behavior not an insert.

ok, so Officer A looks at the signup.asp page, and they are looking at a list of jobs to do. Click the signup button to the right of the job will update the record with the Officer's ID, and redirect them to their schedule.asp.

If the job is taken, then it simply reloads the signup.asp page.

If you do not mind, I will alter this a bit. Make it a little easier.
can you email me your signup.asp page? schase@elpasohonda.com

&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Bkster]

Actually it is not an update procedure it is an insert procedure. The insert behavior inserts the employee_number and the schedule_id into the tblsch_sign table. This table only has two fields and is empty until the someone signs up for a job. All the information about the job is stored in the tblschedule. The qryavailable ties these two together so when you open signup.asp it looks at the employee_number field from tblsch_signup if it is null it will display the job. In the query these are the fields that iam displaying post from tblschedule, times from tblschedule, dates from tblschedule, The employee_number field from tblsch_sign up is not displayed and used to filter the query criteria is set to Is Null. The schdule_id from tblschedule is in the query and not displayed but assigned to a hidden field in the form and is inserted along with the session variable employee_number, which is also in a hidden field, into tblsch_signup with the insert behavior. Is it getting any clearer

I will send you the file now

 

[schase]

well we got it sorted out. Ended up being a misconception on the qry inside access.

&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart