Before beigninning with my problem, let me first explain what the code does..First page is an option page wherein the user has to select whether he wants to go for company specific search or employee specific...Accordin to the category selected, i want to display the results...I am postting the code below...I dont know where the problem is..As m new to ASP ...m not even clear with using arrays in ASP...Can anyone please help...? Its urgent...n i got stuck with the problem :(
<%@Language=VBScript%>
<%Option Explicit%>
<% '-----------------------------------------------------------------------------------
' NAME : Report_Details.asp
' PURPOSE : This ASP file will generate reports according to the search type and
' criteria defined by the user.
' AUTHOR : Shuchi Khandelwal
' DATE : 14 September 2007
'------------------------------------------------------------------------------------
%>
<HTML>
<BODY>
<FORM method="post" name="result_form">
<input type="hidden" name="search_criteria">
<input type="hidden" name="searchType">
<input type="hidden" name="str_advanced">
<%
'DECLARE VARIBLES
Dim objConn,strSQL,Rs,SQLstmt,RefRs,RefStmt,re_str,Emp loyee_Name,EmpStr,CompanyArray()
Dim strCat,order_string,CompanyName,refId,refKey,compa nyId,adv_str,temp,count
Dim Company_Name,Company_Location,Company_Function,Com pany_Business,Company_CEO,Company_Description
Dim NewStr,NewStmt,empStmt,empRs,emprefid,emprefkey,Ne wRs,EmployeeArray
Dim Employee_Designation,Employee_Mobile,Employee_Emai l,Employee_Project,Employee_Description
ReDim CompanyArray(1000,1000)
ReDim EmployeeArray(1000,1000)
'ESTABLISH CONNECTION WITH DATABASE
set objConn=Server.createObject("ADODB.Connection")
objConn.ConnectionString="Provider=SQLOLEDB;Data Source=(local);Initial Catalog=company_information;" &_
"User Id = sa; Password=sa"
objConn.open
'SET VALUES FROM PREVIOUS FORM
order_string=request.Form("searchType")
strCat=request.Form("search_criteria")
adv_str=request.Form("str_advanced")
re_str=Replace(adv_str,"-","'")
response.write strCat
'response.write "aaaa"
%>
<CENTER>
<TABLE border="1" cellpadding="5" cellspacing="2">
<TR BGCOLOR=#3163A0>
<TH colspan="8" scope="colgroup" bgcolor=#3163A2>
Report Results
</TH>
</TR>
<%
'CHECK IF THE CATEGORY = COMPANY SPECIFIC
If (strCat="1") Then
%>
<TR>
<TH scope="col" bgcolor=#CFCFCF>Company Name</TH>
<TH scope="col" bgcolor=#CFCFCF>Location</TH>
<TH scope="col" bgcolor=#CFCFCF>Function</TH>
<TH scope="col" bgcolor=#CFCFCF>Core Business</TH>
<TH scope="col" bgcolor=#CFCFCF>CEO</TH>
<TH scope="col" bgcolor=#CFCFCF>Industry Type</TH>
<TH scope="col" bgcolor=#CFCFCF>Employee Details</TH>
</TR>
<%
Else
%>
<TR>
<TH scope="col" bgcolor=#CFCFCF>Company Name</TH>
<TH scope="col" bgcolor=#CFCFCF>Employee Name</TH>
<TH scope="col" bgcolor=#CFCFCF>Employee Designation</TH>
<TH scope="col" bgcolor=#CFCFCF>Employee Mobile</TH>
<TH scope="col" bgcolor=#CFCFCF>Employee Email</TH>
<TH scope="col" bgcolor=#CFCFCF>Employee Project</TH>
<TH scope="col" bgcolor=#CFCFCF>Functional Area</TH>
</TR>
<% End if%>
</TABLE>
</CENTER>
<%
'CHECK IF THE CATEGORY = COMPANY SPECIFIC
If (strCat="1") Then
count=0
'EXECUTE THE STANDARD QUERY FOR MASTER
strSQL="SELECT * FROM MASTER M,Reference_company R"
strSQL=strSQL & " WHERE M.CompanyRefKey= R.ReferenceKey"
'response.write strSQL
'CHECK IF ADVANCED STRING IS NULL
'IF NOT, THEN ATTACH REPLACED STRING TO IT
If Not(adv_str=Null) Then
strSQL= strSQL & "AND "& re_str &""
'IF IT IS NULL THEN THE STANDARD QUERY WILL REMAIN UNCHANGED
Else
strSQL=strSQL
End If
'CHECK IF ORDER STRING IS NULL
'IF NOT, THEN ATTACH ORDER STRING TO STANDARD QUERY
If Not(order_string=Null) Then
strSQL=strSQL & " ORDER BY "& order_string &""
'ELSE STANDARD QUERY WILL REMAIN UNCHANGED
Else
strSQL=strSQL
End If
'EXECUTE THE QUERY
Set Rs=objConn.execute(strSQL)
' MOVE TO THE FIRST RECORD
Rs.movefirst
' START A LOOP THAT WILL END WITH THE LAST RECORD
Do while not Rs.eof
'COLLECT COMPANY-ID AND REFERENCE-KEY IN VARIABLES
companyId=rs("CompanyId")
refKey= rs("CompanyRefKey")
'RETRIEVE THE RECORDS FROM REFERENCE TABLE WHERE COMPANY-ID IS THE COMPANY-ID WE'VE GOT ABOVE
RefStmt="SELECT * FROM Reference_Company"
RefStmt=RefStmt & " WHERE ReferenceId=1 AND ReferenceKey='" & refKey &"'"
Set RefRs=objConn.execute(RefStmt)
'ASSIGN ALL THE REQUIRED RECORDSET FIELDS TO VARIABLES
Company_Name=Rs("CompanyName")
Company_Location=Rs("CompanyLocation")
Company_Function=Rs("CompanyFunction")
Company_Business=Rs("CompanyBusiness")
Company_CEO=Rs("CompanyCEO")
Company_Description=RefRs("Description")
response.write Company_Name
'COLLECT EMPLOYEE INFORMATION RESPECTIVE TO THE COMPANY
NewStr="SELECT * FROM EMPLOYEE WHERE CompanyId='"& companyId &"'"
'EXECUTE THE QUERY
Set Newstmt=objConn.execute(NewStr)
If (NewStmt.eof And NewStmt.bof) Then
response.write "<center>"
response.write "<pre><b>"
response.write " No Employee Record Exist.</b>"
response.write "</pre></center>"
Else
' MOVE TO THE FIRST RECORD
NewStmt.movefirst
' START A LOOP THAT WILL END WITH THE LAST RECORD
Do while not NewStmt.eof
emprefkey=NewStmt("EmpRefKey")
'FIND THE FUNCTIONAL AREA OF THE EMPLOYEE
empStmt="SELECT * FROM Reference_Company"
empStmt=empStmt & " WHERE ReferenceId=0"
empStmt=empStmt & " AND ReferenceKey='"& emprefkey &"'"
Set empRs=objConn.execute(empStmt)
'ASSIGN ALL THE REQUIRED RECORDSET FIELDS TO VARIABLES
Employee_Name= Newstmt("EmpName")
Employee_Designation= NewStmt("EmpDesignation")
Employee_Mobile= NewStmt("EmpMobile")
Employee_Email= NewStmt("EmpEmail")
Employee_Project= NewStmt("EmpProject")
Employee_Description=empRs("Description")
response.write Employee_Name
'MERGE ALL THE VALUES IN A SINGLE STRING
If (EmpStr="") Then
EmpStr = Employee_Name & "~" & Employee_Designation
EmpStr = EmpStr & "~" & Employee_Mobile
EmpStr = EmpStr & "~" & Employee_Email
EmpStr = EmpStr & "~" & Employee_Project
EmpStr = EmpStr & "~" & Employee_Description
Else
EmpStr=EmpStr & "|" & Employee_Name
EmpStr=EmpStr & "~" & Employee_Designation
EmpStr= EmpStr & "~" & Employee_Mobile
EmpStr=EmpStr & "~" & Employee_Email
EmpStr= EmpStr & "~" & Employee_Project
EmpStr=EmpStr & "~" & Employee_Description
End If
' MOVE TO THE NEXT RECORD
NewStmt.movenext
' LOOP BACK TO THE DO STATEMENT
loop
End If
LINE 205(where the error is)--- CompanyArray=ArrayCompany_Name,Company_Location,Co mpany_Function,Company_Business,Company_CEO,Compan y_Description,EmpStr)
count=count+1
' MOVE TO THE NEXT RECORD
rs.movenext
' LOOP BACK TO THE DO STATEMENT
loop
'ELSE CHECK IF CATEGORY = EMPLOYEE SPECIFIC
Else
strSQL="SELECT * FROM EMPLOYEE E,Reference_company R"
strSQL=strSQL & " WHERE E.EmpRefKey= R.ReferenceKey"
'response.write strSQL
'CHECK IF ADVANCED STRING IS NULL
'IF NOT, THEN ATTACH REPLACED STRING TO IT
If Not(adv_str=Null) Then
strSQL= strSQL & "AND "& re_str &""
'IF IT IS NULL THEN THE STANDARD QUERY WILL REMAIN UNCHANGED
Else
strSQL=strSQL
End If
'CHECK IF ORDER STRING IS NULL
'IF NOT, THEN ATTACH ORDER STRING TO STANDARD QUERY
If Not(order_string=Null) Then
strSQL=strSQL & " ORDER BY "& order_string &""
'ELSE STANDARD QUERY WILL REMAIN UNCHANGED
Else
strSQL=strSQL
End If
'EXECUTE THE QUERY
Set Rs=objConn.execute(strSQL)
'MOVE TO THE FIRST RECORD
Rs.movefirst
' START A LOOP THAT WILL END WITH THE LAST RECORD
do while not Rs.eof
'MATCH THE COMPANY-ID OF EMPLOYEE FROM COMPANY TABLE AND RETRIEVE THE COMPANY NAME
temp=rs("CompanyId")
refKey= rs("EmpRefKey")
SQLstmt="SELECT * FROM MASTER WHERE CompanyId='"& temp &"'"
Set NewRs=objconn.execute(SQLstmt)
RefStmt="SELECT * FROM Reference_Company"
RefStmt=RefStmt & " WHERE ReferenceId=0 AND ReferenceKey='" & refKey &"'"
Set RefRs=objConn.execute(RefStmt)
' RETRIEVE RECORDSET INFORMATION FROM THE DATABASE
Company_Name=NewRs("CompanyName")
Employee_Name= rs("EmpName")
Employee_Designation= rs("EmpDesignation")
Employee_Mobile=rs("EmpMobile")
Employee_Email= rs("EmpEmail")
Employee_Project= rs("EmpProject")
Employee_Description= RefRs("Description")
EmployeeArray=Array(Company_Name,Employee_Name,Emp loyee_Designation,Employee_Mobile,Employee_Email,E mployee_Project,Employee_Description)
'MOVE TO THE NEXT RECORD
rs.movenext
' LOOP BACK TO THE DO STATEMENT
loop
End If
'CLOSE ALL THE RECORDSETS AND THE DATABASE CONNECTION
Rs.close
Set Rs=Nothing
RefRs.close
Set RefRs=Nothing
NewRs.close
Set NewRs=Nothing
'EmpRs.close
'Set EmpRs=Nothing
'NewStmt.close
'Set NewStmt=Nothing
objConn.close
Set objConn=Nothing
%>
<TABLE>
<%
'DECLARE VARIABLES
Dim i,CompName,CompLocation,CompFunction,CompCEO,CompB usiness,CompDescription,CompanyEmployee,CompEmpDet ail
Dim EmpName,EmpDesignation,EmpMobile,EmpEmail,EmpProje ct,EmpDescription,item,CompEmployee
If (count>0) Then
For i=0 To UBound(CompanyArray,2)
CompName= CompanyArray(0,i)
CompLocation=CompanyArray(1,i)
CompFunction= CompanyArray(2,i)
CompBusiness= CompanyArray(3,i)
CompCEO= CompanyArray(4,i)
CompDescription= CompanyArray(5,i)
CompEmployee=Split(CompanyArray(6,i),"|")
Response.Write "<TR><TD>" & CompName & "</TD>"
Response.Write "<TD>" & CompLocation & "</TD>"
Response.Write "<TD>" & CompFunction & "</TD>"
Response.Write "<TD>" & CompBusiness & "</TD>"
Response.Write "<TD>" & CompCEO & "</TD>"
Response.Write "<TD>" & CompDescription & "</TD>"
Response.Write "<TD>"
%>
<CENTER>
<DIV style="border:1px solid #000000; background-color: #FFFFFF;width:100%; height:100; overflow-x: scroll; overflow-y:scroll;">
<TABLE border="1" cellpadding="5" cellspacing="2">
<TR>
<TH scope="col" bgcolor=#CFCFCF>Employee Name</TH>
<TH scope="col" bgcolor=#CFCFCF>Designation</TH>
<TH scope="col" bgcolor=#CFCFCF>Mobile</TH>
<TH scope="col" bgcolor=#CFCFCF>Email</TH>
<TH scope="col" bgcolor=#CFCFCF>Project</TH>
<TH scope="col" bgcolor=#CFCFCF>Functional Area</TH>
</TR>
<TR>
<% For Each item In CompEmployee
CompEmpDetail=Split(CompEmployee,"~")
Response.Write "<TD>" & CompEmpDetail & "</TD>"
Next
%>
</TR>
</TABLE>
</DIV>
</CENTER>
<%
Response.write "</TD>"
Response.Write "</TR>"
Next
Else If(count<>0) Then
For i=0 To UBound(EmployeeArray,2)
EmpName= EmployeeArray(0,i)
EmpDesignation=EmployeeArray(1,i)
EmpMobile= EmployeeArray(2,i)
EmpEmail= EmployeeArray(3,i)
EmpProject= EmployeeArray(4,i)
EmpDescription= EmployeeArray(5,i)
Response.Write "<TR><TD>" & EmpName & "</TD>"
Response.Write "<TD>" & EmpDesignation & "</TD>"
Response.Write "<TD>" & EmpMobile & "</TD>"
Response.Write "<TD>" & EmpMobile & "</TD>"
Response.Write "<TD>" & EmpEmail & "</TD>"
Response.Write "<TD>" & EmpProject & "</TD>"
Response.Write "<TD>" & EmpDescription & "</TD>"
Response.Write "</TR>"
Next
End If
End If
%>
</TABLE>
</FORM>
</BODY>
</HTML>
Now the error it gives me is--
Type mismatch
/company/report_details.asp, line 205
Array Problem...Can anyone help?? :(
