Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Code Support: ASP (Non-Forum Related)
 Reloading lawyers dropdown listbox		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

samflex
Starting Member

USA
4 Posts
Posted - 08 June 2010 :  09:16:30  Show Profile  Reply with Quote
Dearest Experts,

I am a first timer here and I ask for your patience and heavenly assistance.

I have one serious issue.

The history:

I have 12 attorneys. This number doesn't change.

Each attorney is assigned a case, no more than one case a time.

This is very critical and a bit confusing and I will explain it a bit more.

The cases are assigned randomly.

First, a case is created, then each attorney is selected from the dropdown and assigned a case.

The dropdown is also known as a wheel.

Once a attorney is assigned a case, the attorney disappears from the dropdown (wheel).

Once each attorney has been assigned a case, and no attorney is left on the dropdown, any attempt to assign another case to a attorney results to the following message I created:

"No attorneys available to handle case " & caseid (whatever the case number is)"

This is simply because ALL attorneys on the dropdown has been assigned a case.


These scenarios work great so far.

The problem I have now is that once all attorneys have each been assigned a case, and a new case is created, rather than this code below:

       If RS.EOF Then
           Response.Write "FATAL ERROR:  No attorneys available to handle case " & caseid
           RS.Close
           Conn.Close
           Response.End
       End If

we would like to reload the dropdown with SAME attorneys and start assigning new cases to each attorney all over again.


I indicated that each attorney is assigned a case, no more than one case a time in one WHEEL or dropdown.

I also indicated that is a bit confusing.

Here is why. Even though each attorney is assigned one case a time, this is within one wheel or dropdown.

Once all attorneys in this wheel or dropdown have each been assigned a case, the dropdown or wheel is now empty and will need
to be reloaded so we can start assigning cases to each attorney all over again.

Next time a case is created, the wheel or dropdown is reloaded with same attorneys and cases are assigned to each attorney as they come.

This process goes on until all attorneys in the dropdown or wheel is assigned a case.


I tried this:


       If RS.EOF Then
           Response.Write "Reloading attorneys wheel"
       SQL = "SELECT j.AttorneyCode, j.AttorneyName " & _
             "FROM Attorneys j " & _
             "LEFT JOIN Cases c ON c.AttorneyCode = j.AttorneyCode " & _
             "WHERE c.AttorneyCode IS NULL " & _
             "ORDER BY j.AttorneyName"
           RS.Close
           Conn.Close
           Response.End
       End If

But it isn't working.

I am really stumped as to how to handle this.

I would really, really appreciate your assistance with handling this portion ofthe problem.

3 tables are involved so far:

Attorney table (lookup table)
AttorneyCode nvarchar(50),
AttorneyName nvarchar(50)

Clients table (lookup table)
ClientID int pk identity key,
ClientName nvarchar(50)

Cases table
caseId int pk identity key,
attorneyCode nvarchar(50),
ClientId int,
CaseName nvarchar(50),
CaseType nvarchar(50


Do I need to make any changes to these tables to help solve this problem?

Please let me know and please, please help

Below is the entire working code.

Many thanks in advance for your assistance.


 <html>
<head>
<script>
function clearText(thefield){
    if (thefield.defaultValue==thefield.value) {
        thefield.value = "";
    }
}
function restoreText(thefield){
    if (thefield.value=="") {
        thefield.value = thefield.defaultValue;
    }
}
</script>
</head>
<body background="images/fcweb_bg.jpg">
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};" & _
 "Server=wgc;" & _
 "Address=wgc,1433;" & _
 "Network=DBMSSOCN;" & _
 "Database=ATTORNEYS" & _
 "Uid=***;" & _
 "Pwd=***"

If Request("POSTBACK") = "NEWCASE" Then

  ' new case requested
   client = Trim(Request("clientid"))
   casename = Trim(Request("casename"))
   casetype = Trim(Request("casetype"))

 If client <= 0 Then
    ' only generate a new client id if we *NEED* to!!!!!
    SQL = "Set Nocount on; " _
        & " Insert INTO Clients (ClientName) VALUES ('" & Replace(Request("newclientid"),"'","''") & "');" _
        & " Select @@identity; " _
        & " set nocount off"
    'response.write SQL
    'response.end
    Set RS = Conn.Execute(SQL)
    client = RS(0)
    RS.close
End If

   SQL = "INSERT INTO Cases (clientid,casename,casetype) " _
       & " VALUES(" & client & ",'" & casename & "','" & casetype & "')"
      ' response.write SQL & "<br>"
      ' response.end

   conn.Execute SQL
   SQL = "SELECT @@IDENTITY"
   Set RS = conn.Execute(SQL)
   caseid = RS(0)
    ' response.write caseid& "<br>"
    ' response.end

   RS.Close
End If

If Request("POSTBACK") = "ASSIGNCASE" Then
   caseid = Trim(Request("caseid"))
   AttorneyCode = Trim(Request("Attorney"))
   SQL = "UPDATE Cases SET AttorneyCode = '" & AttorneyCode & "' WHERE caseid = " & caseid
   conn.Execute( SQL )
End If

' see if there are any cases pending that need a Attorney assigned to them:
SQL = "SELECT TOP 1 CaseID, ClientID, casename, casetype FROM Cases WHERE Coalesce(AttorneyCode,'0') = '0' ORDER BY CaseID "
'Response.write sql & "<br>"
'Response.end
Set RS = conn.Execute(SQL)
If Not RS.EOF Then
   ' aha!  have a case that needs assignment
   caseid = RS("caseID")
   clientid = RS("clientID")
   casename = RS("casename")
   casetype = RS("casetype")

   ' now, does this client have any cases assigned to any Attorney?
   SQL = "SELECT TOP 1 AttorneyCode FROM Cases WHERE clientID = " & clientid & " AND Coalesce(AttorneyCode,'0') <> '0' "
  'Response.write sql & "<br>"
  'Response.end

  Set RS = conn.Execute(SQL)
   If Not RS.EOF Then
       lcode = RS(0) ' yes!  already has this code assigned
       SQL = "UPDATE Cases SET AttorneyCode = '" & lcode & "' WHERE caseid = " & caseid
       conn.Execute SQL
       Response.Write "<h3>Assigned " & lcode & " to case " & caseid & " for client " & clientid & "</h3>" _
                    & "<br/>Case name: " & casename & "<br/>Case type: " & casetype _
                    & "<br/>(same client as a prior case)</br></hr>" & vbNewLine
   Else
       ' if here, this is first case for this client...so find an available attorney:
      ' SQL = "SELECT AttorneyCode, AttorneyName FROM Attorneys " _
      '     & " WHERE AttorneyCode NOT IN (SELECT AttorneyCode FROM Cases) " _
      '     & " ORDER BY AttorneyName "
       'Response.write sql & "<br>"
       'Response.end

     '    SQL = "SELECT Attorneys.AttorneyCode, AttorneyName " & _
    '         "FROM Attorneys " & _
    '         "LEFT JOIN Cases ON Cases.AttorneyCode = Attorneys.AttorneyCode " & _
    '         "WHERE Cases.CaseId IS NULL " & _
    '         "ORDER BY AttorneyName "

       SQL = "SELECT j.AttorneyCode, j.AttorneyName " & _
             "FROM Attorneys j " & _
             "LEFT JOIN Cases c ON c.AttorneyCode = j.AttorneyCode " & _
             "WHERE c.AttorneyCode IS NULL " & _
             "ORDER BY j.AttorneyName"

       Set RS = Conn.Execute( SQL )
       If RS.EOF Then
           Response.Write "FATAL ERROR:  No attorneys available to handle case " & caseid
           RS.Close
           Conn.Close
           Response.End
       End If
       ' so let the clerk choose a attorney:
%>
       <hr>
       <br/>
       <h2>Choose Attorney for Case</h2>
       <br/>
       Case id: <%=caseid%><br/>
       Client id: <%=clientid%><br/>
       Case Name: <%=casename%><br/>
       Case Type: <%=casetype%><br/>
       <form name="attorneyForm">
           <input type="hidden" name="POSTBACK" value="ASSIGNCASE" />
           <input type="hidden" name="caseid" value="<%=caseid%>" />
           Choose an available attorney:
           <select name="attorney">
<%
       Do Until RS.EOF
%>
         <option value="<%=RS("AttorneyCode")%>"><%=RS("AttorneyCode")%></option>
<%
           RS.MoveNext
       Loop
       RS.Close
%>
          </select>
          <br/>
          <input type="submit" value="Assign Attorney"/>
       </form>
       </body></html>
<%
        conn.Close
        Response.End ' don't display more...must choose a attorney
    End If
End If
%>
<img src="images/casewheel.gif">
<h2>Create a new Case</h2>
<table width="74%" style="border:1 solid #F3EFE0;" cellspacing=0>
<tr style="background: #F3EFE0;color:#003;vertical-align:top;margin-top:0;height: 25px;">
<td>
<form name="caseForm">
<input type="hidden" name="POSTBACK" value="NEWCASE" />

    <select name="clientid">
    <option value="0">Choose client</option>
<%
Set RS = Conn.Execute("SELECT ClientID, ClientName FROM Clients ORDER BY ClientName")
Do Until RS.EOF %>
    <option value="<%=rs(0)%>"><%=rs(1)%></option>
<%
RS.MoveNext
Loop
RS.Close
%>
</select>
<INPUT Name="newclientid" size="32" onFocus="this.form.clientid.selectedIndex=0;"> <--Type in client name if not in the dropdown list<br>
<br/>
<input name="casename" onfocus="clearText(this)" onblur="restoreText(this)" size="50" value="Enter case name" />
<br/><br>
<input name="casetype"  onfocus="clearText(this)" onblur="restoreText(this)" size="50" value="Enter case type" /> 
<input style="font-size: x-small; font-weight: bold; color: #003;" type=submit value="Create Case" name=case>
</form>
</td>
</tr>
</table>
</body>
</html>


May you be rewarded ten fold for your assistance.

cripto9t
Average Member

USA
881 Posts
Posted - 08 June 2010 :  10:41:38  Show Profile  Reply with Quote
Just a suggestion .

Add another column to your attorney table default 0 and set it to 1 when they have been assigned a case. When all Rows are set to 1, Reset the column to 0. 

Sub GetAvailableAttorneys()
    Dim SQL
    Dim RS
    
    SQL = Select AttorneyCode From Attorney
          Where New_Column = 0
          Set RS = Conn.Execute( SQL )

    if RS.EOF then   'No Attorneys Available, time to start over
       SQL = Update Attorney
             Set New_Column = 0
             Conn.Execute(SQL)

       'call this sub again
       GetAvailableAttorneys
    else
       <select name="attorney"><%
       Do Until RS.EOF  %>
         <option value="<%=RS("AttorneyCode")%>"><%=RS("AttorneyCode")%></option>  
       <% RS.MoveNext
       Loop
       RS.Close%>
       </select>
    end if
end Sub
    _-/Cripto9t\-_
Go to Top of Page

AnonJr
Moderator

United States
5768 Posts
Posted - 08 June 2010 :  11:02:57  Show Profile  Visit AnonJr's Homepage  Reply with Quote
You may also want to try asking at a programming site like http://stackoverflow.com - this is the support site for a specific project, and while we do try to help, we have a more narrowly targeted audience.
Go to Top of Page

samflex
Starting Member

USA
4 Posts
Posted - 08 June 2010 :  13:03:59  Show Profile  Reply with Quote
Cripto9t,

Thank you so very much for your kindness.

I integrated the code but not sure if I used it in the right spot because I am getting a dropdown with no values.

Can you please see how I used it?

I am posting latest code and I will only highlight the new code for easier read.

AnnonJr, I will post it there as well.

I posted here since it is an ASP question and this forum based on everything I have browsed so far is very savvy in figuring out ASP-related issues and much more.

Thank you two.

Hopefully, Cripto9t will take me home on this.

 <html>
<head>
<script>
function clearText(thefield){
    if (thefield.defaultValue==thefield.value) {
        thefield.value = "";
    }
}
function restoreText(thefield){
    if (thefield.value=="") {
        thefield.value = thefield.defaultValue;
    }
}
</script>
</head>
<body background="images/fcweb_bg.jpg">
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};" & _
 "Server=wgc;" & _
 "Address=wgc,1433;" & _
 "Network=DBMSSOCN;" & _
 "Database=ATTORNEYS" & _
 "Uid=***;" & _
 "Pwd=***"

If Request("POSTBACK") = "NEWCASE" Then

  ' new case requested
   client = Trim(Request("clientid"))
   casename = Trim(Request("casename"))
   casetype = Trim(Request("casetype"))

 If client <= 0 Then
    ' only generate a new client id if we *NEED* to!!!!!
    SQL = "Set Nocount on; " _
        & " Insert INTO Clients (ClientName) VALUES ('" & Replace(Request("newclientid"),"'","''") & "');" _
        & " Select @@identity; " _
        & " set nocount off"
    'response.write SQL
    'response.end
    Set RS = Conn.Execute(SQL)
    client = RS(0)
    RS.close
End If

   SQL = "INSERT INTO Cases (clientid,casename,casetype) " _
       & " VALUES(" & client & ",'" & casename & "','" & casetype & "')"
      ' response.write SQL & "<br>"
      ' response.end

   conn.Execute SQL
   SQL = "SELECT @@IDENTITY"
   Set RS = conn.Execute(SQL)
   caseid = RS(0)
    ' response.write caseid& "<br>"
    ' response.end

   RS.Close
End If

If Request("POSTBACK") = "ASSIGNCASE" Then
   caseid = Trim(Request("caseid"))
   AttorneyCode = Trim(Request("Attorney"))
   SQL = "UPDATE Cases SET AttorneyCode = '" & AttorneyCode & "' WHERE caseid = " & caseid
   conn.Execute( SQL )
End If

' see if there are any cases pending that need a Attorney assigned to them:
SQL = "SELECT TOP 1 CaseID, ClientID, casename, casetype FROM Cases WHERE Coalesce(AttorneyCode,'0') = '0' ORDER BY CaseID "
'Response.write sql & "<br>"
'Response.end
Set RS = conn.Execute(SQL)
If Not RS.EOF Then
   ' aha!  have a case that needs assignment
   caseid = RS("caseID")
   clientid = RS("clientID")
   casename = RS("casename")
   casetype = RS("casetype")

   ' now, does this client have any cases assigned to any Attorney?
   SQL = "SELECT TOP 1 AttorneyCode FROM Cases WHERE clientID = " & clientid & " AND Coalesce(AttorneyCode,'0') <> '0' "
  'Response.write sql & "<br>"
  'Response.end

  Set RS = conn.Execute(SQL)
   If Not RS.EOF Then
       lcode = RS(0) ' yes!  already has this code assigned
       SQL = "UPDATE Cases SET AttorneyCode = '" & lcode & "' WHERE caseid = " & caseid
       conn.Execute SQL
       Response.Write "<h3>Assigned " & lcode & " to case " & caseid & " for client " & clientid & "</h3>" _
                    & "<br/>Case name: " & casename & "<br/>Case type: " & casetype _
                    & "<br/>(same client as a prior case)</br></hr>" & vbNewLine
   Else
       ' if here, this is first case for this client...so find an available attorney:
      ' SQL = "SELECT AttorneyCode, AttorneyName FROM Attorneys " _
      '     & " WHERE AttorneyCode NOT IN (SELECT AttorneyCode FROM Cases) " _
      '     & " ORDER BY AttorneyName "
       'Response.write sql & "<br>"
       'Response.end

     '    SQL = "SELECT Attorneys.AttorneyCode, AttorneyName " & _
    '         "FROM Attorneys " & _
    '         "LEFT JOIN Cases ON Cases.AttorneyCode = Attorneys.AttorneyCode " & _
    '         "WHERE Cases.CaseId IS NULL " & _
    '         "ORDER BY AttorneyName "

       SQL = "SELECT j.AttorneyCode, j.AttorneyName " & _
             "FROM Attorneys j " & _
             "LEFT JOIN Cases c ON c.AttorneyCode = j.AttorneyCode " & _
             "WHERE c.AttorneyCode IS NULL " & _
             "ORDER BY j.AttorneyName"

       Set RS = Conn.Execute( SQL )

Sub GetAvailableAttorneys()
    Dim SQL
    Dim RS
    
    SQL = "Select AttorneyCode From Cases " & _
          "Where IsNull(AssignedCases) or AssignedCases = 0 " & _
          Set RS = Conn.Execute( SQL )

    if RS.EOF then   'No Attorney Available, time to start over

       SQL = "Update Cases " & _
             "Set AssignedCases = 0"
             Conn.Execute(SQL)

       'call this sub again
       GetAvailableAttorney
    else
    %>
       <select name="attorney">
       <%
       Do Until RS.EOF  %>
         <option value="<%=RS("AttorneyCode")%>"><%=RS("AttorneyCode")%></option> 
       <% RS.MoveNext
       Loop
       RS.Close%>
       </select>
    end if
end Sub
       ' so let the clerk choose a attorney:
%>


       <hr>
       <br/>
       <h2>Choose Attorney for Case</h2>
       <br/>
       Case id: <%=caseid%><br/>
       Client id: <%=clientid%><br/>
       Case Name: <%=casename%><br/>
       Case Type: <%=casetype%><br/>
       <form name="attorneyForm">
           <input type="hidden" name="POSTBACK" value="ASSIGNCASE" />
           <input type="hidden" name="caseid" value="<%=caseid%>" />
           Choose an available attorney:
           <select name="attorney">
<%
       Do Until RS.EOF
%>
         <option value="<%=RS("AttorneyCode")%>"><%=RS("AttorneyCode")%></option>
<%
           RS.MoveNext
       Loop
       RS.Close
%>
          </select>
          <br/>
          <input type="submit" value="Assign Attorney"/>
       </form>
       </body></html>
<%
        conn.Close
        Response.End ' don't display more...must choose a attorney
    End If
End If
%>
<img src="images/casewheel.gif">
<h2>Create a new Case</h2>
<table width="74%" style="border:1 solid #F3EFE0;" cellspacing=0>
<tr style="background: #F3EFE0;color:#003;vertical-align:top;margin-top:0;height: 25px;">
<td>
<form name="caseForm">
<input type="hidden" name="POSTBACK" value="NEWCASE" />

    <select name="clientid">
    <option value="0">Choose client</option>
<%
Set RS = Conn.Execute("SELECT ClientID, ClientName FROM Clients ORDER BY ClientName")
Do Until RS.EOF %>
    <option value="<%=rs(0)%>"><%=rs(1)%></option>
<%
RS.MoveNext
Loop
RS.Close
%>
</select>
<INPUT Name="newclientid" size="32" onFocus="this.form.clientid.selectedIndex=0;"> <--Type in client name if not in the dropdown list<br>
<br/>
<input name="casename" onfocus="clearText(this)" onblur="restoreText(this)" size="50" value="Enter case name" />
<br/><br>
<input name="casetype"  onfocus="clearText(this)" onblur="restoreText(this)" size="50" value="Enter case type" /> 
<input style="font-size: x-small; font-weight: bold; color: #003;" type=submit value="Create Case" name=case>
</form>
</td>
</tr>
</table>
</body>
</html>
Go to Top of Page

cripto9t
Average Member

USA
881 Posts
Posted - 08 June 2010 :  14:34:27  Show Profile  Reply with Quote
I'm no code wiz so don't count on me to much . 2 things -

1. Put the AssignedCases column in the Attorney table not the Cases table. Then every time You assign a new case (If Request("POSTBACK") = "NEWCASE"), Update the column to 1 for that attorney.

2. You're not calling the sub

Replace your code
<select name="attorney">
<%
Do Until RS.EOF
%>
<option value="<%=RS("AttorneyCode")%>"><%=RS("AttorneyCode")%></option>
<%
RS.MoveNext
Loop
RS.Close
%>
</select>

with this
'Call Sub GetAvailableAttorneys to display attorney select box

GetAvailableAttorneys
    _-/Cripto9t\-_
Go to Top of Page

samflex
Starting Member

USA
4 Posts
Posted - 09 June 2010 :  11:01:09  Show Profile  Reply with Quote
Thanks a lot Cripto9t for your help.

I am not real sure that your approach will work as far as updating the Attorney table.

However, your approach has helped me in another way.

What I have decided to do, using your approach, is to create a Staging table, save Attorney case assignments to that table until all 12 attorneys have been assigned a case each.

Then I dump the cases from staging table to cases table, thereby resetting the attorney dropdown list.

I am not real sure if that is the most efficient way but it seems to hold up.

What I need now is rather than have it reload automatically, I want to put a button there that says, "Reload attorney wheel" so that the user can click that button to reload the dropdownlist.

I am working on that.

Meanwhile, if you have any suggestions please share with me.

That would be greatly appreciated.

meanwhile, I will try the link that AnonJr provided.

Again, many thanks for your smart idea.
Go to Top of Page

samflex
Starting Member

USA
4 Posts
Posted - 09 June 2010 :  15:39:46  Show Profile  Reply with Quote
Sorry guys, even my own solution didn't work.

It works as far as reloading Attorneys' dropdown list but it doesn't capture when an attorney has been assigned a case already.

Soooo back to square one.

AnonJr, since you suggested that stackflow link, do you know how it works?

I spent more than 20 minutes (yes, shameful), trying to figure out how to post a thread.

It kept telling me to "choose a tag".

I have no clue what that is or how to go about it.

This is getting to be way too much fun.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.21 seconds. Powered By: Snitz Forums 2000 Version 3.4.07