retrieving and updating excel sheets using asp only? Please help this time at least.

Discussion in '.NET' started by astarter, Jun 14, 2007.

  1. astarter

    astarter

    Joined:
    Jun 11, 2007
    Messages:
    4
    hi to all, i have to retrieve and update data into excel sheets using ado. I found following link. http://support.microsoft.com/kb/195951#top. I am copying it here for your convenience.

    • Create the Excel file ADOtest.xls with the following data in sheet1:

    column1 column2 column3
    rr this 15
    bb test 20
    ee works 25

    Note If a column in your Excel spreadsheet contains both text and numbers, the Excel ODBC driver cannot correctly interpret which data type the column should be. Please make sure that all the cells in a column are of the same data type. The following three errors can occur if each cell in a column is not of the same type or you have the types mixed between "text" and "general": a. Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
    The request properties can not be supported by this ODBC Driver.
    b. Microsoft OLE DB Provider for ODBC Drivers error '80004005'
    The query is not updateable because it contains no searchable columns to use as a hopeful key.
    c. Microsoft OLE DB Provider for ODBC Drivers error '80004005'
    Query based update failed. The row to update could not be found.

    • Create a Named Range, myRange1, in your spreadsheet:

    a. Highlight the row(s) and column(s) area where your data resides.
    b. On the Insert menu, point to Name, and click Define.
    c. Enter the name myRange1 for the Named Range name.
    d. Click OK.
    The Named Range myRange1 contains the following data:

    column1 column2 column3
    rr this 15
    bb test 20
    ee works 25


    Note ADO assumes that the first row in an Excel query contains the column headings. Therefore, the Named Range must include the column headings. This is different behavior from DAO.

    Note Column headings cannot be a number. The Excel driver cannot interpret them and, instead, returns a cell reference. For example, a column heading of "F1" would be misinterpreted.
    • Create an ODBC System Data Source Name (DSN) pointing to the ADOTest.xls file. a. From the Control Panel, open the ODBC Administrator.
    b. On the System DSN tab, click Add.
    c. Select Microsoft Excel Driver (*.xls) and click Finish. If this option does not exist, you need to install the Microsoft ODBC driver for Excel from Excel setup.
    d. Choose ADOExcel for the Data Source Name.
    e. Make sure the Version is set to the correct version of Excel.
    f. Click "Select Workbook...", browse to the ADOTest.xls file, and click OK.
    g. Click the "Options>>" button and clear the "Read Only" check box.
    h. Click OK and then click OK again.

    • Set permissions on the ADOTest.xls file.
    If your Active Server Page is accessed anonymously, you need to make sure that the Anonymous Account (IUSR_<MachineName>) has at least Read/Write (RW) access to the spreadsheet. If you want to delete information from the spreadsheet, you need to grant the permissions accordingly.

    If you are authenticating access to your Active Server Page, you need to ensure that all users accessing your application have the appropriate permissions.

    Note If you do not set the appropriate permissions on the spreadsheet, you get an error message similar to the following:

    Microsoft OLE DB Provider for ODBC Drivers error '80004005'


    [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.
    1. Create a new ASP page and paste in the following code: <!-- Begin ASP Source Code -->
    <%@ LANGUAGE="VBSCRIPT" %>
    <%
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "ADOExcel"

    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.ActiveConnection = objConn
    objRS.CursorType = 3 'Static cursor.
    objRS.LockType = 2 'Pessimistic Lock.
    objRS.Source = "Select * from myRange1"
    objRS.Open
    %>
    <br>
    <%
    Response.Write("Original Data")

    'Printing out original spreadsheet headings and values.

    'Note that the first recordset does not have a "value" property
    'just a "name" property. This will spit out the column headings.

    Response.Write("<TABLE><TR>")
    For X = 0 To objRS.Fields.Count - 1
    Response.Write("<TD>" & objRS.Fields.Item(X).Name & "</TD>")
    Next
    Response.Write("</TR>")
    objRS.MoveFirst

    While Not objRS.EOF
    Response.Write("<TR>")
    For X = 0 To objRS.Fields.Count - 1
    Response.write("<TD>" & objRS.Fields.Item(X).Value)
    Next
    objRS.MoveNext
    Response.Write("</TR>")
    Wend
    Response.Write("</TABLE>")

    'The update is made here

    objRS.MoveFirst
    objRS.Fields(0).Value = "change"
    objRS.Fields(1).Value = "look"
    objRS.Fields(2).Value = "30"
    objRS.Update

    'Printing out spreadsheet headings and values after update.

    Response.Write("<br>Data after the update")
    Response.Write("<TABLE><TR>")
    For X = 0 To objRS.Fields.Count - 1
    Response.Write("<TD>" & objRS.Fields.Item(X).Name & "</TD>")
    Next
    Response.Write("</TR>")
    objRS.MoveFirst

    While Not objRS.EOF
    Response.Write("<TR>")
    For X = 0 To objRS.Fields.Count - 1
    Response.write("<TD>" & objRS.Fields.Item(X).Value)
    Next
    objRS.MoveNext
    Response.Write("</TR>")
    Wend
    Response.Write("</TABLE>")

    'ADO Object clean up.

    objRS.Close
    Set objRS = Nothing

    objConn.Close
    Set objConn = Nothing
    %>
    <!-- End ASP Source Code -->


    2. Save and name your Active Server Page and view it in the browser. You will see the following: Original Data:

    column1 column2 column3
    -----------------------------

    rr this 30
    bb test 20
    tt works 25


    Data after the update:

    column1 column2 column3
    -----------------------------

    change look 30
    bb test 20
    tt works 25


    Note An update was performed on the first row of your Named Range (after the headings



    --------------------------------------------------------------------------
    --------------------------------------------------------------------------
    Now i have these doubts in this.
    1. How to make sure that version is correctly chosen. I mean the options it is showing there has only version 3, version 4 , version 5 and version 97-2000. But my excel has version 11.6560.6568.
    2.How to set permission on adotest.xls file?

    when i chose version 97-2000 and did not set permission on adotest.xls file(as i dont know how to do so), I got an error :HTTP 500 - Internal server error
    Internet Explorer


    Please advice what to do ? I am really stuck here.
     
    astarter, Jun 14, 2007
    #1
    1. Advertising

  2. astarter

    astarter

    Joined:
    Jun 11, 2007
    Messages:
    4
    please yar, help me this time at least. I never got any reply here..::dong: -?
     
    astarter, Jun 15, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,395
    Carl Prothman [MVP]
    Jan 12, 2005
  2. Mr Newbie

    Style Sheets - Designer - Not Updating

    Mr Newbie, Nov 10, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    335
    Mr Newbie
    Nov 10, 2005
  3. AAaron123
    Replies:
    0
    Views:
    597
    AAaron123
    Oct 3, 2008
  4. Carl Corcoran
    Replies:
    1
    Views:
    272
    Bob Barrows
    Nov 12, 2003
  5. Dennis Jensen

    Generating Excel like sheets using XML and XSD

    Dennis Jensen, Jan 13, 2004, in forum: ASP General
    Replies:
    0
    Views:
    296
    Dennis Jensen
    Jan 13, 2004
Loading...

Share This Page