moving calculations between asp pages 1

[jordan11]

Hi,

I need some advice on how to move calculations from one asp page to another.

Basically what I am doing is calcualtions on a few asp pages and once i get to the end of all the pages do one final calculation with all the totals from each page.

Thanks in advance

 

[DotNetGnat]

put the totals in each page in a session variable...

session("totalonpage1")=yourvalue1
session("totalonpage2")=yourvalue2

on page 3

GrandTotal= session("totalonpage1")+ session("totalonpage2")

-DNG

 

[Sheco]

You could use the QueryString if users need the ability to create bookmark for any of the middle steps.

 

[jordan11]

Thanks guys

 

[DotNetGnat]

no problem...glad to be of help...

-DNG

 

[jordan11]

sorry one more thing

this is my results
p.ID o.ID geog salary qualifi
----------- ------------- ----------- ----------- -----------
128 1 0 200 70
128 2 0 0 30
128 5 0 200 0
206 1 50 200 70
206 2 50 0 30
206 5 0 200 0


so can you tell me how I would pass the p.id, o.id with each catergory so for e.g P.id, o.id and salary (128,1,200)should pass together to the next asp until the final page so when i do my final calculaton on the last page and update then i update the database for each catergory with the final calculation and the o.id and p.id that belongs to that calculation.

Thanks

 

[DotNetGnat]

how did you get that results...can you show the code...then i can show how to create the string 128,1,200
that we can hold until the final page...

-DNG

 

[jordan11]

my code is below but when i put it on my asp page the p.id will be the request p.id and the o.id will be all that match the specialty ie 'AR' so the results should really look like this for each individual p.id

p.ID o.ID geog salary qualifi
----------- ------------- ----------- ----------- -----------
128 1 0 200 70
128 2 0 0 30
128 5 0 200 0

and when I get to the second asp page salary is calculated again using different results added to the page e.g
asp page1 results=128,1,200 and asp page2 = 128,1,400 and on the final page i need to find the total and then the weight for salary plus all the other catergries, is it best for me to add as i go long ie asp page 1 + asp page 2 therefore i get 200 +400 which i would like to be 128,1,600 and then pass that to the third page and then to the final page?

my code


SELECT distinct clientopportunity.opportunityID, physiciancontact.physicianID

,(CASE WHEN clientopportunity.CLocationState = Physicianqualifications.medstate THEN 50 ELSE 0 END) AS geography
,(case
when clientopportunity.salaryfrom = 0 then 0

when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 ='0' then 100
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 < 10 then 100
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 < 20 then 80
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 <30 then 60
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 >40 then 20
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 >60 then 0
else 0 end
+case
when clientopportunity.salaryto = 0 then 0
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 ='0' then 100
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 < 10 then 100
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 < 20 then 80
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 <30 then 60
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 >40 then 20
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 >60 then 0
else 0 end) AS salary
,(CASE WHEN Physicianqualifications.medstate IS NULL THEN 0
WHEN Physicianqualifications.medstate = 'int' then 0 else CONVERT(int,clientopportunity.AMG)*10 END
+CASE WHEN Physicianqualifications.medstate IS NULL THEN 0
WHEN Physicianqualifications.medstate = 'int' then CONVERT(int,clientopportunity.IMG)*10 else 0 END
+CASE WHEN Physicianqualifications.medstate IS NULL THEN 0 ELSE CONVERT(int,clientopportunity.RESGRAD)*10 END
+ CASE WHEN Physicianqualifications.medstate IS NULL THEN 0 ELSE CONVERT(int,clientopportunity.PRACPHY)* 10 end) AS qualifi
FROM clientopportunity
LEFT JOIN physiciancontact
ON physiciancontact.physpecialty = clientopportunity.specialty
AND UPPER(clientopportunity.specialty) = 'ar'
INNER JOIN physicianpreference
ON physicianpreference.physicianID = physiciancontact.physicianID
INNER JOIN Physicianqualifications
ON Physicianqualifications.physicianID =physiciancontact.physicianID


Thanks

 

[DotNetGnat]

ok let me put the lay out for you...let me know if you can carry on from this point..or else post back with any errors or problems that you are facing...

<%
myDSN="your connection string
mySQL="Your complete SQL statement"
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp=conntemp.execute(mySQL)

alldata=rstemp.getrows

if  IsArray(alldata) then
numcols=ubound(alldata,1)
numrows=ubound(alldata,2)

FOR rowcounter= 0 TO numrows
FOR colcounter=0 to numcols
mystring=alldata(colcounter,rowcounter)&","

response.write mystring

Next

response.write "<br>"
Next

end if

%>

see what it results...can you take from there...

-DNG

 

[jordan11]

Thanks but I not sure how to put it all together
This is one of my asp pages

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Physician Career</title>
<!--#include file="connect.asp" -->
</head>

<body>
<%




Dim rs

set rs=server.createobject("ADODB.recordset")
txtSQL= "SELECT * FROM States"
Set rs=conn.Execute(txtSQL)


set rp=server.createobject("ADODB.recordset")
txtSQL= "SELECT * FROM PhysicianContact where physicianID ='" & REQUEST("id") & "'"
Set rp=conn.Execute (txtSQL)


Session("physicianID") = Request("id")

SELECT CASE Request("Validate")
CASE "1"

Updatequalifications

CASE ELSE
DisplayForm
END SELECT



Sub DisplayForm()

%>
<table width="593" align="center" style="BORDER-RIGHT: #000000 2px solid; BORDER-TOP: black 2px solid; BORDER-LEFT: #000000 2px solid; BORDER-BOTTOM: black 2px solid">
<FORM action="physicianqualifications.asp" method="POST" name="Register" onSubmit="return submitform(document.Register)">
<input type="hidden" name="Validate" value="1">
<input type="hidden" name="PhysicianId" value="<%=request("ID")%>">

<p>
<tr><th colspan=5 style="BORDER-BOTTOM: black 2px solid"><strong>Qualifications</strong></th></tr>
<tr>
<th colspan="2"scope="row"><div align="left">What date will you be avaliable?</div></th>
<td colspan="3"><script>DateInput('orderdate', true, 'dd-MON-YYYY')</script></td>

</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<th height="36" colspan="5"scope="col"><div align="left">Medical School</div></th>
</tr>
<tr>
<th width="148"><div align="left">Name<span class="style1">%</span></div></th>
<th width="91"><div align="left">Year </div></th>
<th width="118"><div align="left">State<span class="style1">%</span></div></th>
<th width="155"><div align="left">City</div></th>
<td width="144"><strong>Country</strong></td>
</tr>
<tr>
<td scope="row"><div align="left">
<input name="txtmedname" type="text" value="<% =request("txtmedname")%>"> </div></td>
<td><input name="txtmedyear" type="text" size="10" value="<% =request("txtmedyear")%>"></td>
<td><select name="states" >
<option value="<% =request("states")%>"></option>
<option value="Int">International</option>
<%
while not rs.eof%>
<option value="<% =rs("StateCode")%>" <%if rs("StateCode") = Request("states") then%> selected <%end if%> ><%=rs("StateName")%></option>
<%
rs.movenext
wend
%>
</select>
</td>
<td> <input name="txtmedcity" type="text" value="<% =request("txtmedcity")%>"></td><td><input name="txtmedcountry" type="text" value="<% =request("txtmedcountry")%>"></td>
</tr>
<tr>
<th scope="row" colspan="2"><div align="left">What is your Board Status<span class="style1">%</span></div></th>
<td colspan="4">
<input name="pbs" type="radio" value="1">BC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input name="pbs" type="radio" value="2">BE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input name="pbs" type="radio" value="3">None
<strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As of year</strong>
<input name="txtpboardsyear" type="text" size="10" value="<% =request("txtpboardsyear")%>"></td>
</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<th colspan="3" scope="row"><div align="left">What is your Current Practice Status?<span class="style1">%</span></div></th>
<td colspan="2"><select name="selstatus">
<option></option>
<option value="Residents">Residents</option>
<option value="Fellow">Fellow</option>
<option value="Military">Military</option>
<option value="Referred">Referred</option>
<option value="Unspecified">Unspecified</option>
</select></td>
</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<td>
<select name="sel1" size="10" onChange="setOptions(document.qualifications.select1.options[document.qualifications.select1.selectedIndex].value);">
<option value="select State"></option>
<%
rs.movefirst
while not rs.eof%>
<option value="<% =rs("StateCode")%>" <%if rs("StateCode") = Request("sel1") then%> selected <%end if%> ><%=rs("StateName")%></option>
<%
rs.movenext
wend
%>
</select>
</td>
<td>
<table>
<tr>
<td align="center" valign="middle">
<input type="button" value=">>"
onClick="moveOptions(this.form.sel1, this.form.sel2);" /><br />
<input type="button" value="<<"
onClick="moveOptions(this.form.sel2, this.form.sel1);" />
</td>
</tr>
</table>
</td>
<td colspan="2">
<select name='sel2' multiple size=10></select>
</td>
<td>&nbsp;</td>
</tr>
<tr>
<td colspan="5">
<div align="center">
<input name="workstat" type="radio" value="Us Citizen">Us Citizen&nbsp;&nbsp;&nbsp;&nbsp;
<input name="workstat" type="radio" value="Permanent Resident">Permanent Resident&nbsp;&nbsp;&nbsp;&nbsp;
<input name="workstat" type="radio" value="JI">JI&nbsp;&nbsp;&nbsp;&nbsp;
<input name="workstat" type="radio" value="H1B">H1B&nbsp;&nbsp;&nbsp;&nbsp;
<input name="workstat" type="radio" value="Other">Other&nbsp;&nbsp;&nbsp;&nbsp;
</div>
</td>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<th scope="row" colspan="2"><div align="left">Salary Minimum<span class="style1">%</span></div></th>
<th colspan="2"><div align="left">Expected Income<span class="style1">%</span> <div></div></th>
<td>&nbsp;</td> </tr>
<tr>
<th scope="row" colspan="2"><div align="left">
<strong>$</strong>
<input name="txtpminsalary" type="text" value="<% =request("txtpminsalary")%>">
</div></th>

<td colspan="3"><strong>$</strong><input name="txtpexpsalary" type="text" value="<% =request("txtpexpsalary")%>">
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input name="saltype" type="radio" value="Hourly">
Hourly&nbsp;&nbsp;&nbsp;&nbsp;
<input name="saltype" type="radio" value="Annual">
Annual</td>
</tr>
<tr>
<td colspan="5" align="center"><input type="button" value="Previous" onClick="history.back()">&nbsp;&nbsp;&nbsp;<input type="submit" name="Submit" value="Next"></td>
</tr>

</table>
</form>


<%
end sub

Sub Updatequalifications()

strminsalary=request("txtpminsalary")
strexpsalary=request("txtpexpsalary")
if isnull(request("txtpminsalary")) or trim(Request("txtpminsalary")) = "" then strminsalary = "0" else strminsalary = request("txtpminsalary") end if
if isnull(request("txtpexpsalary")) or trim(Request("txtpexpsalary")) = "" then strexpsalary = "0" else strexpsalary = request("txtpexpsalary") end if



session("sel2")=split(trim(replace(request("sel2"),"'","''")),",")
'arrSelectedItems=Request.form("sel2")

set rspho = server.CreateObject("ADODB.Recordset")
txtsql="select * from physicianqualifications where physicianid = " & chr(39) & trim(request("physicianid")) & chr(39)


rspho.open txtsql, conn, 1, 2, adCmdText


if rspho.EOF then
rspho.Addnew

End if

rspho("PhysicianID") = Request.Form("PhysicianID")
rspho("PhyDateAvaliable") = Request.Form("orderdate")
rspho("MedicalSchool") = Request.Form("txtmedname")
rspho("MedYear") = Request.Form("txtMedYear")
rspho("MedState") = Request.Form("states")
rspho("MedCity") = Request.Form("txtmedcity")
rspho("medcountry") = Request.Form("txtmedcountry")
'rspho("LicenseStates") = arrSelectedItems
rspho("pBoardStatus") = Request.Form("pbs")
rspho("BoardStatusYear") = Request.Form("txtpboardsyear")
rspho("CurrentPraticeStatus") = Request.Form("selstatus")
rspho("SalaryMinimum") = strminsalary
rspho("SalaryExpected") = strexpsalary
rspho("PayType") = Request.Form("saltype")
rspho.update
rspho.close
set rspho = nothing
txtsql = "DELETE FROM statelookup WHERE physicianID = '" _
& trim(request("physicianid")) &"'"
conn.Execute txtsql

for r=0 to ubound(session("sel2"))
txtsql = "INSERT INTO statelookup (physicianID, LicenseStates) " _
& "VALUES ('" & trim(request("physicianid")) &"', '" _
& session("sel2")(r) & "')"

conn.Execute txtsql
Next


Session("physicianID") = Request.Form("PhysicianID")

Response.Redirect "questions.asp?ID=" & trim(session("PhysicianID"))


end sub

%>


</body>
<!--#include file="disconnect.asp" -->
</html>

and i would like to add the calculation below to the code above after update and then pass the information to the physicianid, values for each opportunityid to the next asp page so on each page there may be more than one opportunityid

p.ID o.ID geog salary qualifi
----------- ------------- ----------- ----------- -----------
128 1 0 200 70
128 2 0 0 30
128 5 0 200 0


the code that does the calaculation

SELECT distinct clientopportunity.opportunityID, physiciancontact.physicianID

,(CASE WHEN clientopportunity.CLocationState = Physicianqualifications.medstate THEN 50 ELSE 0 END) AS geography
,(case
when clientopportunity.salaryfrom = 0 then 0

when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 ='0' then 100
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 < 10 then 100
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 < 20 then 80
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 <30 then 60
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 >40 then 20
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 >60 then 0
else 0 end
+case
when clientopportunity.salaryto = 0 then 0
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 ='0' then 100
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 < 10 then 100
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 < 20 then 80
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 <30 then 60
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 >40 then 20
when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 >60 then 0
else 0 end) AS salary
,(CASE WHEN Physicianqualifications.medstate IS NULL THEN 0
WHEN Physicianqualifications.medstate = 'int' then 0 else CONVERT(int,clientopportunity.AMG)*10 END
+CASE WHEN Physicianqualifications.medstate IS NULL THEN 0
WHEN Physicianqualifications.medstate = 'int' then CONVERT(int,clientopportunity.IMG)*10 else 0 END
+CASE WHEN Physicianqualifications.medstate IS NULL THEN 0 ELSE CONVERT(int,clientopportunity.RESGRAD)*10 END
+ CASE WHEN Physicianqualifications.medstate IS NULL THEN 0 ELSE CONVERT(int,clientopportunity.PRACPHY)* 10 end) AS qualifi
FROM clientopportunity
LEFT JOIN physiciancontact
ON physiciancontact.physpecialty = clientopportunity.specialty
AND UPPER(clientopportunity.specialty) = 'ar'
INNER JOIN physicianpreference
ON physicianpreference.physicianID = physiciancontact.physicianID
INNER JOIN Physicianqualifications
ON Physicianqualifications.physicianID =physiciancontact.physicianID



Thanks

 

[jordan11]

I have managed to put something together and used an array to capture the information for each output but it is only returning the list of all the states instead of

128 1 0 200 70
128 2 0 0 30
128 5 0 200 0

this is my new code

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
</head>

<body>
<%




Dim rs

set rs=server.createobject("ADODB.recordset")
txtSQL= "SELECT * FROM States"
Set rs=conn.Execute(txtSQL)


set rp=server.createobject("ADODB.recordset")
txtSQL= "SELECT * FROM PhysicianContact where physicianID ='" & REQUEST("id") & "'"
Set rp=conn.Execute (txtSQL)

PhySpecialty= request("PhySpecialty")

SELECT CASE Request("Validate")
CASE "1"

Updatequalifications

CASE ELSE
DisplayForm
END SELECT



Sub DisplayForm()

%>
<table width="593" align="center" style="BORDER-RIGHT: #000000 2px solid; BORDER-TOP: black 2px solid; BORDER-LEFT: #000000 2px solid; BORDER-BOTTOM: black 2px solid">
<FORM action="physicianqualifications.asp" method="POST" name="Register" onSubmit="return submitform(document.Register)">
<input type="hidden" name="Validate" value="1">
<input type="hidden" name="PhysicianId" value="<%=request("ID")%>">
<input type="hidden" name="PhySpecialty" value="<%=request("PhySpecialty")%>">

<p>
<tr><th colspan=5 style="BORDER-BOTTOM: black 2px solid"><strong>Qualifications</strong></th></tr>
<tr>
<th colspan="2"scope="row"><div align="left">What date will you be avaliable?</div></th>
<td colspan="3"><script>DateInput('orderdate', true, 'dd-MON-YYYY')</script></td>

</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<th height="36" colspan="5"scope="col"><div align="left">Medical School</div></th>
</tr>
<tr>
<th width="148"><div align="left">Name<span class="style1">%</span></div></th>
<th width="91"><div align="left">Year </div></th>
<th width="118"><div align="left">State<span class="style1">%</span></div></th>
<th width="155"><div align="left">City</div></th>
<td width="144"><strong>Country</strong></td>
</tr>
<tr>
<td scope="row"><div align="left">
<input name="txtmedname" type="text" value="<% =request("txtmedname")%>"> </div></td>
<td><input name="txtmedyear" type="text" size="10" value="<% =request("txtmedyear")%>"></td>
<td><select name="states" >
<option value="<% =request("states")%>"></option>
<option value="Int">International</option>
<%
while not rs.eof%>
<option value="<% =rs("StateCode")%>" <%if rs("StateCode") = Request("states") then%> selected <%end if%> ><%=rs("StateName")%></option>
<%
rs.movenext
wend
%>
</select>
</td>
<td> <input name="txtmedcity" type="text" value="<% =request("txtmedcity")%>"></td><td><input name="txtmedcountry" type="text" value="<% =request("txtmedcountry")%>"></td>
</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<th height="36" colspan="5"scope="col"><div align="left">Internship</div></th>
</tr>
<tr>
<th width="148" scope="row"><div align="left">Name</div></th>
<th width="91"><div align="left">Year </div></th>
<th width="118"><div align="left">State</div></th>
<th width="155"><div align="left">City</div></th>
<td>&nbsp;</td>
</tr>
<tr>
<th scope="row"><div align="left">
<input name="txtinternname" type="text" value="<% =request("txtinternname")%>">
</div></th>
<td><input name="txtintrenyear" type="text" size="10" value="<% =request("txtintrenyear")%>"></td>
<td><select name="statei">
<option value="<% =request("subSpecialty")%>"></option>
<%
rs.movefirst
while not rs.eof%>
<option value="<% =rs("StateCode")%>" <%if rs("StateCode") = Request("statei") then%> selected <%end if%> ><%=rs("StateName")%></option>
<%
rs.movenext
wend
%>
</select>
</td>
<td><input name="txtinterncity" type="text" value="<% =request("txtinterncity")%>"></td>
<td>&nbsp;</td>
</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<th height="36" colspan="5"scope="col"><div align="left">Residency</div></th>
</tr>
<tr>
<th width="148" scope="row"><div align="left">Name</div></th>
<th width="91"><div align="left">Year </div></th>
<th width="118"><div align="left">State</div></th>
<th width="155"><div align="left">City</div></th>
<td>&nbsp;</td>
</tr>
<tr>
<th scope="row"><div align="left">
<input name="txtresiname" type="text" value="<% =request("txtresiname")%>">
</div></th>
<td><input name="txtresiyear" type="text" size="10" value="<% =request("txtresiyear")%>"></td>
<td><select name="stater">
<option value=""></option>
<%
rs.movefirst
while not rs.eof%>
<option value="<% =rs("StateCode")%>" <%if rs("StateCode") = Request("stater") then%> selected <%end if%> ><%=rs("StateName")%></option>
<%
rs.movenext
wend
%>
</select>
</td>
<td><input name="txtresicity" type="text" ></td>
<td>&nbsp;</td>
</tr>
<tr>
<th height="23" colspan="5"scope="col"><div align="left"></div></th>
</tr>
<tr>
<th scope="row" colspan="2"><div align="left">What is your Board Status<span class="style1">%</span></div></th>
<td colspan="4">
<input name="pbs" type="radio" value="1">BC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input name="pbs" type="radio" value="2">BE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input name="pbs" type="radio" value="3">None
<strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As of year</strong>
<input name="txtpboardsyear" type="text" size="10" value="<% =request("txtpboardsyear")%>"></td>
</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<th colspan="3" scope="row"><div align="left">What is your Current Practice Status?<span class="style1">%</span></div></th>
<td colspan="2"><select name="selstatus">
<option></option>
<option value="Residents">Residents</option>
<option value="Fellow">Fellow</option>
<option value="Military">Military</option>
<option value="Referred">Referred</option>
<option value="Unspecified">Unspecified</option>
</select></td>
</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<td>
<select name="sel1" size="10" onChange="setOptions(document.qualifications.select1.options[document.qualifications.select1.selectedIndex].value);">
<option value="select State"></option>
<%
rs.movefirst
while not rs.eof%>
<option value="<% =rs("StateCode")%>" <%if rs("StateCode") = Request("sel1") then%> selected <%end if%> ><%=rs("StateName")%></option>
<%
rs.movenext
wend
%>
</select>
</td>
<td>
<table>
<tr>
<td align="center" valign="middle">
<input type="button" value=">>"
onClick="moveOptions(this.form.sel1, this.form.sel2);" /><br />
<input type="button" value="<<"
onClick="moveOptions(this.form.sel2, this.form.sel1);" />
</td>
</tr>
</table>
</td>
<td colspan="2">
<select name='sel2' multiple size=10></select>
</td>
<td>&nbsp;</td>
</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<td colspan="5"><div align="left"><strong>Are you legally authorized to work in the United States?</strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input name="btnpleg" type="radio" value="1">
Yes &nbsp;&nbsp;&nbsp;
<input name="btnpleg" type="radio" value="2">
&nbsp;No</div></td>
</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<th colspan="5" scope="row"><div align="left">What is your work authorization status?</div></th>
</tr>
<tr>
<td colspan="5">
<div align="center">
<input name="workstat" type="radio" value="Us Citizen">Us Citizen&nbsp;&nbsp;&nbsp;&nbsp;
<input name="workstat" type="radio" value="Permanent Resident">Permanent Resident&nbsp;&nbsp;&nbsp;&nbsp;
<input name="workstat" type="radio" value="JI">JI&nbsp;&nbsp;&nbsp;&nbsp;
<input name="workstat" type="radio" value="H1B">H1B&nbsp;&nbsp;&nbsp;&nbsp;
<input name="workstat" type="radio" value="Other">Other&nbsp;&nbsp;&nbsp;&nbsp;
</div>
</td>
</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<th scope="row"><div align="left">Spoken Langauages </div></th>
<td colspan="2"><div align="center">
<input name="txtplang" type="text" value="<% =request("txtplang")%>">
</div></td>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<th scope="row" colspan="2"><div align="left">Salary Minimum<span class="style1">%</span></div></th>
<th colspan="2"><div align="left">Expected Income<span class="style1">%</span> <div></div></th>
<td>&nbsp;</td>


</tr>
<tr>
<th scope="row" colspan="2"><div align="left">
<strong>$</strong>
<input name="txtpminsalary" type="text" value="<% =request("txtpminsalary")%>">
</div></th>

<td colspan="3"><strong>$</strong><input name="txtpexpsalary" type="text" value="<% =request("txtpexpsalary")%>">
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input name="saltype" type="radio" value="Hourly">
Hourly&nbsp;&nbsp;&nbsp;&nbsp;
<input name="saltype" type="radio" value="Annual">
Annual</td>
</tr>
<tr>

<td><div align="left">
</div></td>

<td colspan="2">&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<th colspan="5"scope="row"><div align="center"></div></th>
</tr>
<tr>
<th colspan="2" scope="row"><div align="left">Are you currently interviewing? </div></th>
<td colspan="2"><input name="pinterview" type="CHECKBOX" value="1">
Yes</td>
<td>&nbsp;</td>
</tr>
<tr>
<td colspan="5" scope="row"><strong>Would like a email notifications of any new Opportunity?</strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input name="pemaillist" type="checkbox" value="1">&nbsp;&nbsp;Yes</td>
<td width="1">&nbsp;</td>
</tr>
<tr>
<th colspan="5"><div align="center"></div></th>
</tr>
<tr>
<th colspan="5" ><div align="left">What is your primary motivating factor for leaving your current position? </div></th>
</tr>
<tr>
<th colspan="5"><div align="left">
<textarea name="txtreasonleaving" cols="80"></textarea>
</div></th>
</tr>

<tr>
<td colspan="5" align="center"><input type="button" value="Previous" onClick="history.back()">&nbsp;&nbsp;&nbsp;<input type="submit" name="Submit" value="Next"></td>
</tr>
<tr>
<th colspan="5"><div align="center"></div></th>
</tr>
</table>
</form>


<%
end sub

Sub Updatequalifications()


strminsalary=request("txtpminsalary")
strexpsalary=request("txtpexpsalary")



if isnull(request("txtpminsalary")) or trim(Request("txtpminsalary")) = "" then strminsalary = "0" else strminsalary = request("txtpminsalary") end if
if isnull(request("txtpexpsalary")) or trim(Request("txtpexpsalary")) = "" then strexpsalary = "0" else strexpsalary = request("txtpexpsalary") end if



session("sel2")=split(trim(replace(request("sel2"),"'","''")),",")
'arrSelectedItems=Request.form("sel2")

set rspho = server.CreateObject("ADODB.Recordset")
txtsql="select * from physicianqualifications where physicianid = " & chr(39) & trim(request("physicianid")) & chr(39)


rspho.open txtsql, conn, 1, 2, adCmdText


if rspho.EOF then
rspho.Addnew

End if



rspho("PhysicianID") = Request.Form("PhysicianID")
rspho("PhyDateAvaliable") = Request.Form("orderdate")
rspho("MedicalSchool") = Request.Form("txtmedname")
rspho("MedYear") = Request.Form("txtMedYear")
rspho("MedState") = Request.Form("states")
rspho("MedCity") = Request.Form("txtmedcity")
rspho("medcountry") = Request.Form("txtmedcountry")
rspho("Internship") = Request.Form("txtinternname")
rspho("InternYear") = Request.Form("txtintrenyear")
rspho("InternState") = Request.Form("statei")
rspho("InternCity") = Request.Form("txtinterncity")
rspho("Residency") = Request.Form("txtresiname")
rspho("ResYear") = Request.Form("txtresiyear")
rspho("ResiState") = Request.Form("stater")
rspho("ResiCity") = Request.Form("txtresicity")
'rspho("LicenseStates") = arrSelectedItems
rspho("pBoardStatus") = Request.Form("pbs")
rspho("BoardStatusYear") = Request.Form("txtpboardsyear")
rspho("CurrentPraticeStatus") = Request.Form("selstatus")
rspho("LeagallyWork") = Request.Form("btnpleg")
rspho("SponsEmpVisaStatus") = Request.Form("workstat")
rspho("OtherLang") = Request.Form("txtplang")
rspho("SalaryMinimum") = strminsalary
rspho("SalaryExpected") = strexpsalary
rspho("PayType") = Request.Form("saltype")
rspho("PhyInterviewing") = Request.Form("pinterview")
rspho("ReasonLeaving") = Request.Form("txtreasonleaving")

rspho.update
rspho.close
set rspho = nothing
txtsql = "DELETE FROM statelookup WHERE physicianID = '" _
& trim(request("physicianid")) &"'"
conn.Execute txtsql

for r=0 to ubound(session("sel2"))
txtsql = "INSERT INTO statelookup (physicianID, LicenseStates) " _
& "VALUES ('" & trim(request("physicianid")) &"', '" _
& session("sel2")(r) & "')"

conn.Execute txtsql


'retrieve userID for the newly added user


physicianid = Request("physicianid")
specialty = Request("PhySpecialty")

set rs = server.CreateObject("ADODB.Recordset")

txtSQL = "SELECT distinct clientopportunity.opportunityID, physiciancontact.physicianID,"_

&"(CASE WHEN clientopportunity.CLocationState = Physicianqualifications.medstate THEN 50 ELSE 0 END) AS geography,"_
&"(case"_
&"when clientopportunity.salaryfrom = 0 then 0"_


&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 ='0' then 100"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 < 10 then 100"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 < 20 then 80"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 <30 then 60"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 >40 then 20"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 >60 then 0"_
&"else 0 end"_
&"+case"_
&"when clientopportunity.salaryto = 0 then 0"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 ='0' then 100"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 < 10 then 100"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 < 20 then 80"_
&"when((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 <30 then 60"_
&"when((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 >40 then 20"_
&"when((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 >60 then 0"_
&"else 0 end) AS salary"_
&",(CASE WHEN Physicianqualifications.medstate IS NULL THEN 0"_
&"WHEN Physicianqualifications.medstate = 'int' then 0 else CONVERT(int,clientopportunity.AMG)*10 END"_
&"+CASE WHEN Physicianqualifications.medstate IS NULL THEN 0"_
&"WHEN Physicianqualifications.medstate = 'int' then CONVERT(int,clientopportunity.IMG)*10 else 0 END"_
&"+CASE WHEN Physicianqualifications.medstate IS NULL THEN 0 ELSE CONVERT(int,clientopportunity.RESGRAD)*10 END"_
&"+ CASE WHEN Physicianqualifications.medstate IS NULL THEN 0 ELSE CONVERT(int,clientopportunity.PRACPHY)* 10 end) AS qualifi"_
&"FROM clientopportunity"_
&"LEFT JOIN physiciancontact"_
&"ON physiciancontact.physpecialty = clientopportunity.specialty"_
&"AND UPPER(physiciancontact.physicianid) = '128'"_
&"INNER JOIN Physicianqualifications "_
&"ON Physicianqualifications.physicianID =physiciancontact.physicianID;"

set rs = conn.Execute(txtsql)

next
alldata=rs.getrows

if IsArray(alldata) then
numcols=ubound(alldata,1)
numrows=ubound(alldata,2)

FOR rowcounter= 0 TO numrows
FOR colcounter=0 to numcols
mystring=alldata(colcounter,rowcounter)&","

response.write mystring

Next

response.write "<br>"
Next
response.end
end if


'Next


Session("physicianID") = Request.Form("PhysicianID")

Response.Redirect "questions.asp?ID=" & trim(session("PhysicianID"))


end sub

%>


</body>
<!--#include file="disconnect.asp" -->
</html>

thanks for all help given

 

[jordan11]

this is my new error message
Microsoft VBScript runtime error '800a01a8'

Object required: 'rsp'
once i changed rs to rsp in the code below

Set rsp=conn.Execute(txtSQL)

next
alldata=rsp.getrows

if IsArray(alldata) then
numcols=ubound(alldata,1)
numrows=ubound(alldata,2)

FOR rowcounter= 0 TO numrows
FOR colcounter=0 to numcols
mystring=alldata(colcounter,rowcounter)&","

response.write mystring

Next

response.write "<br>"
Next
response.end
end if

 

[DotNetGnat]

thats because, you also need to change....

Dim [red]rs[/red]

set [red]rs[/red]=server.createobject("ADODB.recordset")
txtSQL= "SELECT * FROM States"
Set [red]rs[/red]=conn.Execute(txtSQL)

-DNG

 

[jordan11]

ok I as since changed the next in the wrong position
I change the position of NEXT and no longe get that error, the data is now updating to the database but it is puting the same information in several times and the second problem it is not getting through the calaculation it keeps saying Incorrect syntax near 'clientopportunity'.
but looking at my code i can not see the problem.
Is there a better way for me to get the o.id, pid. and value for each catergory and pass them to the next page? would it be better if i broke up the query so that each catergory is calculated seperately for each o.id ?
this is the part of the code that is giving a problem

Sub Updatequalifications()


strminsalary=request("txtpminsalary")
strexpsalary=request("txtpexpsalary")



if isnull(request("txtpminsalary")) or trim(Request("txtpminsalary")) = "" then strminsalary = "0" else strminsalary = request("txtpminsalary") end if
if isnull(request("txtpexpsalary")) or trim(Request("txtpexpsalary")) = "" then strexpsalary = "0" else strexpsalary = request("txtpexpsalary") end if



session("sel2")=split(trim(replace(request("sel2"),"'","''")),",")
arrSelectedItems=Request.form("sel2")

set rspho = server.CreateObject("ADODB.Recordset")
txtsql="select * from physicianqualifications where physicianid = " & chr(39) & trim(request("physicianid")) & chr(39)


rspho.open txtsql, conn, 1, 2, adCmdText


if rspho.EOF then
rspho.Addnew

End if



rspho("PhysicianID") = Request.Form("PhysicianID")
rspho("PhyDateAvaliable") = Request.Form("orderdate")
rspho("MedicalSchool") = Request.Form("txtmedname")
rspho("MedYear") = Request.Form("txtMedYear")
rspho("MedState") = Request.Form("states")
rspho("MedCity") = Request.Form("txtmedcity")
rspho("medcountry") = Request.Form("txtmedcountry")
rspho("Internship") = Request.Form("txtinternname")
rspho("InternYear") = Request.Form("txtintrenyear")
rspho("InternState") = Request.Form("statei")
rspho("InternCity") = Request.Form("txtinterncity")
rspho("Residency") = Request.Form("txtresiname")
rspho("ResYear") = Request.Form("txtresiyear")
rspho("ResiState") = Request.Form("stater")
rspho("ResiCity") = Request.Form("txtresicity")
rspho("LicenseStates") = arrSelectedItems
rspho("pBoardStatus") = Request.Form("pbs")
rspho("BoardStatusYear") = Request.Form("txtpboardsyear")
rspho("CurrentPraticeStatus") = Request.Form("selstatus")
rspho("LeagallyWork") = Request.Form("btnpleg")
rspho("SponsEmpVisaStatus") = Request.Form("workstat")
rspho("OtherLang") = Request.Form("txtplang")
rspho("SalaryMinimum") = strminsalary
rspho("SalaryExpected") = strexpsalary
rspho("PayType") = Request.Form("saltype")
rspho("PhyInterviewing") = Request.Form("pinterview")
rspho("ReasonLeaving") = Request.Form("txtreasonleaving")

rspho.update
rspho.close
set rspho = nothing
txtsql = "DELETE FROM statelookup WHERE physicianID = '" _
& trim(request("physicianid")) &"'"
conn.Execute txtsql

for r=0 to ubound(session("sel2"))
txtsql = "INSERT INTO statelookup (physicianID, LicenseStates) " _
& "VALUES ('" & trim(request("physicianid")) &"', '" _
& session("sel2")(r) & "')"
next
conn.Execute txtsql


'retrieve userID for the newly added user


physicianid = Request("physicianid")
specialty = Request("PhySpecialty")

set rd=server.createobject("ADODB.recordset")

txtSQL = "SELECT distinct clientopportunity.opportunityID, physiciancontact.physicianID,"_

&"(CASE WHEN clientopportunity.CLocationState = Physicianqualifications.medstate THEN 50 ELSE 0 END) AS geography,"_
&"(case"_
&"when clientopportunity.salaryfrom = 0 then 0"_


&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 ='0' then 100"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 < 10 then 100"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 < 20 then 80"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 <30 then 60"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 >40 then 20"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryfrom) / clientopportunity.Salaryfrom) * 100.0 >60 then 0"_
&"else 0 end"_
&"+case"_
&"when clientopportunity.salaryto = 0 then 0"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 ='0' then 100"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 < 10 then 100"_
&"when ((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 < 20 then 80"_
&"when((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 <30 then 60"_
&"when((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 >40 then 20"_
&"when((Physicianqualifications.salaryminimum - clientopportunity.salaryto) / clientopportunity.Salaryto) * 100.0 >60 then 0"_
&"else 0 end) AS salary"_
&",(CASE WHEN Physicianqualifications.medstate IS NULL THEN 0"_
&"WHEN Physicianqualifications.medstate = 'int' then 0 else CONVERT(int,clientopportunity.AMG)*10 END"_
&"+CASE WHEN Physicianqualifications.medstate IS NULL THEN 0"_
&"WHEN Physicianqualifications.medstate = 'int' then CONVERT(int,clientopportunity.IMG)*10 else 0 END"_
&"+CASE WHEN Physicianqualifications.medstate IS NULL THEN 0 ELSE CONVERT(int,clientopportunity.RESGRAD)*10 END"_
&"+ CASE WHEN Physicianqualifications.medstate IS NULL THEN 0 ELSE CONVERT(int,clientopportunity.PRACPHY)* 10 end) AS qualifi"_
&"FROM clientopportunity"_
&"LEFT JOIN physiciancontact"_
&"ON physiciancontact.physpecialty = clientopportunity.specialty"_
&"AND UPPER(physiciancontact.physicianid) = '128'"_
&"INNER JOIN Physicianqualifications "_
&"ON Physicianqualifications.physicianID =physiciancontact.physicianID"_
&"INNER JOIN statelookup "_
&"ON statelookup.physicianID =Physicianqualifications.physicianID;"

Set rd=conn.Execute(txtSQL)
alldata=rd.getrows

if IsArray(alldata) then
numcols=ubound(alldata,1)
numrows=ubound(alldata,2)

FOR rowcounter= 0 TO numrows
FOR colcounter=0 to numcols
mystring=alldata(colcounter,rowcounter)&","

response.write mystring

Next

response.write "<br>"
Next
'response.end
end if
rd.close
set rd = nothing




Session("physicianID") = Request.Form("PhysicianID")

Response.Redirect "questions.asp?ID=" & trim(session("PhysicianID"))

'next
end sub

%>


</body>
<!--#include file="disconnect.asp" -->
</html>
Thanks

 

[jordan11]

I have broken up the query and displayed the data differntly so would this make it easier to pass the data to the next page
this is the results from my query which i have in my asp page.

p.ID o.ID geog
10 1 70
10 2 90
10 5 60



p.ID o.ID salary
10 1 70
10 2 60
10 5 50




p.ID o.ID qualifi
10 1 70
10 2 80
10 5 70

p.id I can do but i will like to pass the o.id with the correct value and catergory name.
e.g catergory salary i will like to pass 1,70 AS salary to the next page.

Thanks in advance

 

[DotNetGnat]

sorry for not getting back to this..but i havent forgot about it...was busy with meeting...
ok so now you have different recordsets...

is there a requirement for you to display these tables as intermediate steps or do you just want to grab 1,70 in the final page for calculations...

i will write up a sampple code as soon as i get some time...

-DNG

 

[jordan11]

thanks,
it has change slighty i know neede to do small queries for each catergory.
what i need to do is use the results to do some calculations and then update the database with the results,
so what i need to do first is get he total results for each catergorgy for a particular o.id and then first find the percentage weight for each catergory and then add each catergory together to get a total. this total will then be updated in the database with the p.id ,oid ,total.

Thanks for remembering me

 

[jordan11]

Hi Dng,
This is my new code

Hi,
this is my code below,
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Physician Career</title>
<!--#include file="connect.asp" -->
</head>
<body>


<%
set rp=server.createobject("ADODB.recordset")
txtSQL= "SELECT * FROM PhysicianContact where physicianID ='" & REQUEST("id") & "'"
Set rp=conn.Execute (txtSQL)


Session("physicianID") = Request("id")

Dim oRScp

set oRScp=server.createobject("ADODB.recordset")
txtSQL= "SELECT * FROM clientopportunity WHERE specialty = (SELECT physpecialty FROM physiciancontact where physicianID ='" & REQUEST("id") & "')"


Set oRScp=conn.Execute(txtSQL)


Errhandlerr Conn

SELECT CASE Request("Validate")
CASE "q"

Updatecalculations

CASE ELSE
calculation
END SELECT


Sub calculation()

%>

<form method="post" action="questions.asp">
<input type="hidden" name="Validate" value="q">
<input type="hidden" name="PhysicianId" value="<%=request("ID")%>">
<%
Do while not oRScp.EOF
StropportunityID = oRScp("opportunityId")
StrlocationState = oRScp("CLocationState")
txtSQL = "SELECT distinct physiciancontact.physicianID"_
&",clientopportunity.opportunityID"_
&",(CASE WHEN clientopportunity.CLocationState = physicianpreference.PreferredState THEN 20 ELSE 0 END"_
&"+CASE WHEN clientopportunity.CLocationState = Physicianqualifications.medstate THEN 50 ELSE 0 END"_
&"+CASE WHEN clientopportunity.CLocationState = physicianpreference.StateBirth THEN 10 ELSE 0 END) AS geography"_
&" FROM clientopportunity "_
&" LEFT JOIN physiciancontact"_
&" ON physiciancontact.physpecialty = clientopportunity.specialty"_
&" AND UPPER(physiciancontact.physicianid) ='Request.Form("PhysicianID")'"_
&" INNER JOIN physicianpreference"_
&" ON physicianpreference.physicianID = physiciancontact.physicianID"_
&" INNER JOIN Physicianqualifications"_
&" ON Physicianqualifications.physicianID =physiciancontact.physicianID"_
&" WHERE physicianqualifications.SalaryExpected IS NOT NULL OR physicianqualifications.SalaryMinimum IS NOT NULL OR physicianpreference.StateBirth IS NOT NULL;"



Set oRSc=conn.Execute(txtSQL)



txtSQL = "SELECT distinct physiciancontact.physicianID"_
&",clientopportunity.opportunityID"_
&",(CASE WHEN Physicianqualifications.medstate IS NULL THEN 0 WHEN Physicianqualifications.medstate = 'int' then 0 else CONVERT(int,clientopportunity.AMG)*10 END"_
&"+CASE WHEN Physicianqualifications.medstate IS NULL THEN 0 WHEN Physicianqualifications.medstate = 'int' then CONVERT(int,clientopportunity.IMG)*10 else 0 END"_
&"+CASE WHEN Physicianqualifications.medstate IS NULL THEN 0 ELSE CONVERT(int,clientopportunity.RESGRAD)*10 END"_
&"+CASE WHEN Physicianqualifications.medstate IS NULL THEN 0 ELSE CONVERT(int,clientopportunity.PRACPHY)*10 END) AS QUALIFICATIONS"_
&" FROM clientopportunity "_
&" LEFT JOIN physiciancontact"_
&" ON physiciancontact.physpecialty = clientopportunity.specialty"_
&" AND UPPER(physiciancontact.physicianid) ='Request.Form("PhysicianID")'"_
&" INNER JOIN Physicianqualifications"_
&" ON Physicianqualifications.physicianID =physiciancontact.physicianID"_
&" WHERE physicianqualifications.SalaryExpected IS NOT NULL OR physicianqualifications.SalaryMinimum IS NOT NULL;"



Set oRSc=conn.Execute(txtSQL)





txtSQL = "SELECT distinct physiciancontact.physicianID"_
&",clientopportunity.opportunityID"_
&",(case when clientopportunity.salaryto = 0 then 0"_
&"+case when paymenttype = 'hourly' then ((P.salaryminimum * 40 * 52 - c.Salaryfrom * 40 * 52) / c.Salaryfrom* 40 * 52) * 100.0 else ((P.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 end < 10 then 100"_
&"+case when paymenttype = 'hourly'then ((P.salaryminimum * 40 * 52 – c.Salaryfrom * 40 * 52) / c.Salaryfrom* 40 * 52) * 100.0 else ((P.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 end <20 then 80"_
&"+case when paymenttype = 'hourly'then ((P.salaryminimum * 40 * 52 – c.Salaryfrom * 40 * 52) / c.Salaryfrom* 40 * 52) * 100.0 else ((P.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 end <30 then 60"_
&"+case when paymenttype = 'hourly'then ((P.salaryminimum * 40 * 52 – c.Salaryfrom * 40 * 52) / c.Salaryfrom* 40 * 52) * 100.0 else ((P.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 end >60 then 20 else 0 end) AS salaryS"_
&" FROM clientopportunity "_
&" LEFT JOIN physiciancontact"_
&" ON physiciancontact.physpecialty = clientopportunity.specialty"_
&" AND UPPER(physiciancontact.physicianid) ='Request.Form("PhysicianID")'"_
&" INNER JOIN Physicianqualifications"_
&" ON Physicianqualifications.physicianID =physiciancontact.physicianID"_
&" WHERE physicianqualifications.SalaryExpected IS NOT NULL OR physicianqualifications.SalaryMinimum IS NOT NULL;"



Set oRSc=conn.Execute(txtSQL)

response.write (txtSQL)
response.end
Errhandlerr Conn

oRSc.Close
Set oRSc= nothing
Response.Write "</td></tr><tr><td>&nbsp;</td></tr>"
oRScp.Movenext
Loop
oRScp.Close
Set oRScp= nothing
%>

</form>

<%
end sub


sub Updatecalculations ()

txtsql = "DELETE FROM ratings WHERE physicianID = '" _
& trim(request("physicianid")) &"'"
conn.Execute txtsql


txtsql ="INSERT INTO ratings (opportunityid,physicianID,rating)"_
& "VALUES StropportunityID, &"_
& trim(request("physicianid"))&"'"_
& rating &"'"
conn.Execute txtsql


set Conn = nothing


'response.end


Session("physicianID") = Request.Form("PhysicianID")

Response.Redirect "physicianpreference.asp?ID=" & trim(session("PhysicianID"))


end sub
%>

</body>
<!--#include file="disconnect.asp" -->
</html>



I have calculation based on salary, geopgraphy and qualifications and what i need to do is first do a calaculation on each category and the add them together to get the total rating.

e.g cal1= salary*30/30*100
cal2= qualifiactions*50/50*100
cal3= geography*20/20*100


rating=cal1+cal2+cal3
then the databse should be upated with the physicianid,opportunityid and rating for that opportunityid
then it needs to loop to update all the opportunityid and ratings for the physicianid


thanks I hope can help me fix my code so it can loop through and then update the databese,
not sure if i need to use and if statement for each new calculation and then go through the loop and also i used the same recordset name for each calcualtion is this right?

thanks again

 

[DotNetGnat]

jordan,

i started writing the code but all i see your asp page getting messy and inefficient....if i were you..i would just create views out of the queries you have and then you a select statement on top of these views to the rating...i think it would be better and efficient...let me know what you think...

-DNG

 

[jordan11]

that sounds fine but not sure how to go about doing it
can you give me an example using my code.


thanks DNG