Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: excel & ASP

  1. #1
    Join Date
    Sep 2008
    Posts
    13

    Exclamation Unanswered: excel & ASP

    Anyone here know how to set the connection to read the figures on microsoft excel?i am able to set the and see the graph only when i hardcode in the figure...help !

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Without more information I can only point you to this link: http://connectionstrings.com/?carrier=excel2007

    Your problem (and therefore question) is not clear.
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2008
    Posts
    13
    hey georgev, thanks for the link i will try it out. and get back to you thanks !

  4. #4
    Join Date
    Sep 2008
    Posts
    13
    hi georgev,i currently have these codes
    --------------------------------------------------------------------------
    <!doctype html public "-//w3c//dtd html 4.0 transitional//en">

    <%@ Language=VBScript %>
    <%
    ' Set Connection Params
    Set oConn = Server.CreateObject("ADODB.connection")
    oConn.Open "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790;" &_
    "DBQ=C:\Inetpub\wwwroot\InfoWeb\14Apr08_NUS_hr_Dat a.xls;" &_
    "DefaultDir = C:\Inetpub\wwwroot\InfoWeb\"

    Set RS=Server.CreateObject("ADODB.recordset")

    ' Write the SQL Query
    RS.open "SELECT * FROM my_range", oConn


    do until RS.EOF
    Response.Write ( RS("Item No") & " -- " & RS("Item Description") & "<br>")
    RS.movenext
    Loop

    'Close the recordset/connection

    RS.Close
    oConn.Close
    Set RS = Nothing
    %>




    <html>
    <head>
    <title>Pressure Chart</title>
    </head>
    <body>


    <applet codebase="new" code="linegraph.class" height=220 width=360>
    <param name="title" value="Sales">
    <param name="NumberOfVals" value="<%=sqlQuery.RecordCount%>">
    <param name="NumberOfLabs" value="<%=sqlQuery.RecordCount%>">
    <PARAM NAME=ymax VALUE=<%=(Fix(sqlQuery("M")/10000)+1)*10000%>>
    <PARAM NAME=ymin VALUE=0>
    <PARAM NAME=mode VALUE=0>
    <PARAM NAME=border VALUE="20"> <PARAM NAME=Grid VALUE="true">
    <PARAM NAME=LineColor_R_L1 VALUE=53>
    <PARAM NAME=LineColor_G_L1 VALUE=153>
    <PARAM NAME=LineColor_B_L1 VALUE=51>
    <%
    i = 1
    while (not sqlQuery.EOF) %>
    <param name="VAL<%=i%>_L1" value="<%=sqlQuery("SalesTotal")%>">
    <param name="LAB<%=i%>" value="<%=MonthName(Month(sqlQuery("Date")))%>">
    <%
    i = i + 1
    sqlQuery.MoveNext
    wend %>

    </APPLET><p>

    </body>
    </html>
    --------------------------------------------------------------------------
    So how do i set my range which i underline? there are error in the codes could you point out to me ?

    this is the error:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

    [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'my_range'. Make sure the object exists and that you spell its name and the path name correctly.

    /Infoweb/pressure.asp, line 15
    Last edited by Ugene-; 09-18-08 at 06:43.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies mate, completely missed your reply - will try take a look today
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sounds like you've not defined your range in the Excel spreadsheet correctly; have a look at the following KB article (specifically the bit about creating MyRange1) http://support.microsoft.com/kb/195951 and see if it helps
    George
    Home | Blog

  7. #7
    Join Date
    Sep 2008
    Posts
    13
    No worries mate...Now i have got the connection correct..and now its able to display the data in the excel file on a browser in a table form..so now i have to plot it i alraedy got the applet working but now is how to get the connect between the applet with the data ?

    below is the correct data for the connection between excel and ASP..so if any 1 need it can play around with it..
    -------------------------------------------------------------------------
    <% Option Explicit %>
    <html>
    <body>

    <%
    Dim objConn, objRS, strSQL
    Dim x, curValue

    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
    "DBQ=C:\Inetpub\wwwroot\InfoWeb\14Apr08_NUS_hr_Dat a.xls;"

    strSQL = "SELECT * FROM D1170"
    Set objRS=objConn.Execute(strSQL)
    Response.Write("<table border=""1"">")
    Response.Write("<tr>")
    For x=0 To objRS.Fields.Count-1
    Response.Write("<th>" & objRS.Fields(x).Name & "</th>")
    Next
    Response.Write("</tr>")
    Do Until objRS.EOF
    Response.Write("<tr>")
    For x=0 To objRS.Fields.Count-1
    curValue = objRS.Fields(x).Value
    If IsNull(curValue) Then
    curValue="N/A"
    End If
    curValue = CStr(curValue)
    Response.Write("<td>" & curValue & "</td>")
    Next
    Response.Write("</tr>")
    objRS.MoveNext
    Loop
    objRS.Close
    Response.Write("</table>")

    objConn.Close
    Set objRS=Nothing
    Set objConn=Nothing
    %>

    </body>
    </html>
    --------------------------------------------------------------------------

    Below is the applet to plot the graph how to connect it?
    -------------------------------------------------------------------------
    <APPLET CODE="linegraph.class" HEIGHT=220 WIDTH=360>
    <PARAM NAME=NumberOfVals VALUE=36>
    <PARAM NAME=NumberOfLabs VALUE=4>
    <PARAM NAME=KeyWidth VALUE=70>

    <PARAM NAME=LineColor_R_L1 VALUE=0>
    <PARAM NAME=LineColor_G_L1 VALUE=0>
    <PARAM NAME=LineColor_B_L1 VALUE=200>

    <PARAM NAME=LineColor_R_L2 VALUE=200>
    <PARAM NAME=LineColor_G_L2 VALUE=0>
    <PARAM NAME=LineColor_B_L2 VALUE=0>

    <PARAM NAME=yMax VALUE=30>
    <PARAM NAME=yMin VALUE=-10>
    <PARAM NAME=Mode VALUE=0>
    <PARAM NAME=Lines VALUE=2>
    <PARAM NAME=Title VALUE="NET PROFIT (K$)">
    <PARAM NAME=Border VALUE="30">
    <PARAM NAME=Grid VALUE="true">

    <PARAM NAME=VAL1_L1 VALUE=10.3>
    <PARAM NAME=VAL2_L1 VALUE=11.0>
    <PARAM NAME=VAL3_L1 VALUE=14.5>
    <PARAM NAME=VAL4_L1 VALUE=12.20>
    <PARAM NAME=VAL5_L1 VALUE=15.0>
    <PARAM NAME=VAL6_L1 VALUE=12.5>
    <PARAM NAME=VAL7_L1 VALUE=20.5>
    <PARAM NAME=VAL8_L1 VALUE=22.5>
    <PARAM NAME=VAL9_L1 VALUE=18.8>
    <PARAM NAME=VAL10_L1 VALUE=24>
    <PARAM NAME=VAL11_L1 VALUE=27.8>
    <PARAM NAME=VAL12_L1 VALUE=29>
    <PARAM NAME=VAL13_L1 VALUE=26.9>
    <PARAM NAME=VAL14_L1 VALUE=22.4>
    <PARAM NAME=VAL15_L1 VALUE=18.0>
    <PARAM NAME=VAL16_L1 VALUE=14.8>
    <PARAM NAME=VAL17_L1 VALUE=7.5>
    <PARAM NAME=VAL18_L1 VALUE=3.2>
    <PARAM NAME=VAL19_L1 VALUE=-4.6>
    <PARAM NAME=VAL20_L1 VALUE=-2.0>
    <PARAM NAME=VAL21_L1 VALUE=1.8>
    <PARAM NAME=VAL22_L1 VALUE=2.5>
    <PARAM NAME=VAL23_L1 VALUE=-1.1>
    <PARAM NAME=VAL24_L1 VALUE=2.5>
    <PARAM NAME=VAL25_L1 VALUE=11.9>
    <PARAM NAME=VAL26_L1 VALUE=16.6>
    <PARAM NAME=VAL27_L1 VALUE=12.6>
    <PARAM NAME=VAL28_L1 VALUE=18.7>
    <PARAM NAME=VAL29_L1 VALUE=23.9>
    <PARAM NAME=VAL30_L1 VALUE=19.9>
    <PARAM NAME=VAL31_L1 VALUE=19.5>
    <PARAM NAME=VAL32_L1 VALUE=23.5>
    <PARAM NAME=VAL33_L1 VALUE=19.0>
    <PARAM NAME=VAL34_L1 VALUE=22.5>
    <PARAM NAME=VAL35_L1 VALUE=22.9>
    <PARAM NAME=VAL36_L1 VALUE=24.6>

    <PARAM NAME=VAL1_L2 VALUE=14.3>
    <PARAM NAME=VAL2_L2 VALUE=12.0>
    <PARAM NAME=VAL3_L2 VALUE=11.5>
    <PARAM NAME=VAL4_L2 VALUE=17.20>
    <PARAM NAME=VAL5_L2 VALUE=12.0>
    <PARAM NAME=VAL6_L2 VALUE=13.5>
    <PARAM NAME=VAL7_L2 VALUE=25.5>
    <PARAM NAME=VAL8_L2 VALUE=21.5>
    <PARAM NAME=VAL9_L2 VALUE=11.8>
    <PARAM NAME=VAL10_L2 VALUE=14>
    <PARAM NAME=VAL11_L2 VALUE=17.8>
    <PARAM NAME=VAL12_L2 VALUE=19>
    <PARAM NAME=VAL13_L2 VALUE=16.9>
    <PARAM NAME=VAL14_L2 VALUE=22.4>
    <PARAM NAME=VAL15_L2 VALUE=28.0>
    <PARAM NAME=VAL16_L2 VALUE=24.8>
    <PARAM NAME=VAL17_L2 VALUE=9.5>
    <PARAM NAME=VAL18_L2 VALUE=6.2>
    <PARAM NAME=VAL19_L2 VALUE=4.6>
    <PARAM NAME=VAL20_L2 VALUE=12.0>
    <PARAM NAME=VAL21_L2 VALUE=8.8>
    <PARAM NAME=VAL22_L2 VALUE=9.5>
    <PARAM NAME=VAL23_L2 VALUE=11.1>
    <PARAM NAME=VAL24_L2 VALUE=7.5>
    <PARAM NAME=VAL25_L2 VALUE=14.9>
    <PARAM NAME=VAL26_L2 VALUE=15.6>
    <PARAM NAME=VAL27_L2 VALUE=16.6>
    <PARAM NAME=VAL28_L2 VALUE=11.7>
    <PARAM NAME=VAL29_L2 VALUE=21.9>
    <PARAM NAME=VAL30_L2 VALUE=16.9>
    <PARAM NAME=VAL31_L2 VALUE=13.5>
    <PARAM NAME=VAL32_L2 VALUE=25.5>
    <PARAM NAME=VAL33_L2 VALUE=19.9>
    <PARAM NAME=VAL34_L2 VALUE=22.9>
    <PARAM NAME=VAL35_L2 VALUE=22.1>
    <PARAM NAME=VAL36_L2 VALUE=24.1>

    <PARAM NAME=LAB1 VALUE="1999">
    <PARAM NAME=LAB2 VALUE="2000">
    <PARAM NAME=LAB3 VALUE="2001">
    <PARAM NAME=LAB4 VALUE="2002">

    <PARAM NAME=Key_L1 VALUE="ABC Corp">
    <PARAM NAME=Key_L2 VALUE="XYZ Corp">

    </APPLET>
    -----------------------------------------------------------------------

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So you got it writing the data out to page? Superb

    I'm assuming that by "connect it", you mean, use the data from the excel spreadsheet as parameter values for your <applet>, right?
    George
    Home | Blog

  9. #9
    Join Date
    Sep 2008
    Posts
    13
    Yes mate. now its displaying it in a table form.so now i want to use the data to plot it on a graph. the applet is to a graph ..so i want to insert in for me instand of me hardcoding it in myself. its there any method ?

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yessir... Apply the same theory, using the .Write method to populate the applet values
    Code:
    Start loop
    
        Response.Write("<PARAM NAME=SomeName VALUE='")
        Response.Write(objRS.Fields(1).Value)
        Response.Write("'>")
    
    Next in loop
    George
    Home | Blog

  11. #11
    Join Date
    Sep 2008
    Posts
    13
    Quote Originally Posted by georgev
    Yessir... Apply the same theory, using the .Write method to populate the applet values
    Code:
    Start loop
    
        Response.Write("<PARAM NAME=SomeName VALUE='")
        Response.Write(objRS.Fields(1).Value)
        Response.Write("'>")
    
    Next in loop
    hey georgev i can't seem to get it...

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's some pseduo code giving an example of how to use the recordset to populate the VALUE of the PARAM.

    Ok, let's step back a mo.

    What fields from your recordset are you wanting to use to populate your applet?
    George
    Home | Blog

  13. #13
    Join Date
    Sep 2008
    Posts
    13
    Hey george, this is the values that i want to populate. but all the value below is hardcoded inside, thats why i want to read the data from my excel and display it on the graph. i don't konw how to put the retrieved data inside the applet - VALUE="retrieved data from excel".

    <PARAM NAME=LAB1 VALUE="1999">
    <PARAM NAME=VAL36_L1 VALUE=24.6>
    <PARAM NAME=VAL1_L2 VALUE=14.3>
    ----------------------------------------------------------------------


    and this is the actual applet without hardcoding inside..all the values inside like title=sales is not what i want as i copy from the actual code..hope u understand what i meant..

    <applet codebase="new" code="linegraph.class" height=220 width=360>
    <param name="title" value="Sales">
    <param name="NumberOfVals" value="<%=sqlQuery.RecordCount%>">
    <param name="NumberOfLabs" value="<%=sqlQuery.RecordCount%>">
    <PARAM NAME=ymax VALUE=<%=(Fix(sqlQuery("M")/10000)+1)*10000%>>
    <PARAM NAME=ymin VALUE=0>
    <PARAM NAME=mode VALUE=0>
    <PARAM NAME=border VALUE="20">
    <PARAM NAME=Grid VALUE="true">
    <PARAM NAME=LineColor_R_L1 VALUE=53>
    <PARAM NAME=LineColor_G_L1 VALUE=153>
    <PARAM NAME=LineColor_B_L1 VALUE=51>
    <%
    i = 1
    while (not sqlQuery.EOF) %>
    <param name="VAL<%=i%>_L1" value="<%=sqlQuery("SalesTotal")%>">
    <param name="LAB<%=i%>" value="<%=MonthName(Month(sqlQuery("Date")))%>">
    <%
    i = i + 1
    sqlQuery.MoveNext
    wend %>


    ------------------------------------------------------------------

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It looks like you've got it right in your code above... Is there still a problem?
    apolgies, having a slow morning
    George
    Home | Blog

  15. #15
    Join Date
    Sep 2008
    Posts
    13
    i am unable to place the retieved data into the applet ...can you teach me how do i pass the value inside ?and where should i place the applet ?after the connection ? or within the connection ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •