Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 17th, 2007, 02:56 AM
Authorized User
 
Join Date: Aug 2007
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default Array Problem...Can anyone help?? :(

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
 
Old September 25th, 2007, 05:21 AM
Authorized User
 
Join Date: Dec 2006
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Guys,

In your page u r using two arrays one is Company and another is Employee.

For Company u r using code :

CompanyArray=ArrayCompany_Name,Company_Location,Co mpany_Function,Company_Business,Company_CEO,Compan y_Description,EmpStr)

And For Employee u r using code :

EmployeeArray=Array(Company_Name,Employee_Name,Emp loyee_Designation,Employee_Mobile,Employee_Email,E mployee_Project,Employee_Description)


In CompanyArray Case u r not using Opening's Brack '(' --

Correct Syntex will be : --

CompanyArray=Array(Company_Name,Company_Location,C ompany_Function,Company_Business,Company_CEO,Compa ny_Description,EmpStr)

Try This.







Similar Threads
Thread Thread Starter Forum Replies Last Post
Array problem Ivanchan Excel VBA 2 July 24th, 2007 08:28 PM
problem in getting the value of an array knightneo Beginning PHP 0 December 4th, 2006 10:23 PM
Array Problem monika.vasvani ASP.NET 1.0 and 1.1 Professional 3 September 13th, 2006 05:56 AM
Array problem NEO1976 Javascript 0 August 3rd, 2006 03:55 AM
Array Problem hayley Classic ASP Basics 4 February 2nd, 2005 12:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.