1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

how to retrieve and update data into excel sheets using asp only

Discussion in 'C#' started by astarter, Jun 13, 2007.

  1. #1
    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 13, 2007 IP
  2. ccoonen

    ccoonen Well-Known Member

    Messages:
    1,606
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    160
    #2
    make sure to call actual Excel methods... the main methods generally don't deprecate for later versions. Set permissions? not sure if classic asp's connection to FSO can do it but I know asp.net can do anything ;)
     
    ccoonen, Jun 14, 2007 IP
  3. ccoonen

    ccoonen Well-Known Member

    Messages:
    1,606
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    160
    #3
    now that i think about it - just give us the URL and Code... and we'll tell you wan'ts wrong, you are a pushing excel with html table. you are having trouble with queries that generate the table? or you are having trouble generating the table itself? Remember, you can always modify generate html tables, and set the content-type to an excel type, and it will push out your html as EXCEL data ;)
     
    ccoonen, Jun 14, 2007 IP
  4. astarter

    astarter Guest

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    hi thanks for replying. Finally, after so much hardwork, i have learnt asp.net to become more powerful. Still I am having problems. Could anyone please help me out this time. Here's the code, I am using it on a test file.

    Dim conn As New OleDbConnection
    conn.ConnectionString = m_sConn1
    conn.Open()
    Dim cmd1 As New OleDbCommand
    cmd1.Connection = conn
    Dim reader As OleDbDataReader
    cmd1.CommandText = "select name from [EmployeeData$] where name='Ajay'"
    reader = cmd1.ExecuteReader
    DataGrid1.DataSource = reader
    DataGrid1.DataBind()

    Till this point it is working fine. But when I run this to insert a row, it does not work
    'Dim cmd2 As New OleDbCommand
    'cmd2.Connection = conn
    'cmd1.CommandText = "Update [EmployeeData$] set name='Jitesh' where name='Komal'"
    'cmd1.ExecuteNonQuery()
    'conn.Close()

    Similiarly,update command is not working. Kindly help me. Waiting for some helping hands.
     
    astarter, Jun 19, 2007 IP
  5. RodDev

    RodDev Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Guys,

    I'm having issues with something like this as well.... Although I'm trying to grab Updated Data from Excel and post it on ASP (classic) site.

    the following code works only for Static Range but not for Dynamic Range... any ideas??

    
    
    <%
    ' Selected constants from adovbs.inc
    Const adOpenStatic = 3
    Const adLockPessimistic = 2
    
    Dim cnnExcel
    Dim rstExcel
    Dim I
    Dim iCols
    
    ' This is all standard ADO except for the connection string.
    ' You can also use a DSN instead, but so it'll run out of the
    ' box on your machine I'm using the string instead.
    Set cnnExcel = Server.CreateObject("ADODB.Connection")
    cnnExcel.Open "DBQ=" & Server.MapPath("processorgrouplist.xls") & ";" & _
    	"DRIVER={Microsoft Excel Driver (*.xls)};"
    
    ' Same as any other data source.
    ' FYI: TestData is my named range in the Excel file
    Set rstExcel = Server.CreateObject("ADODB.Recordset")
    rstExcel.Open "SELECT * FROM TestData;", cnnExcel, _
    	adOpenStatic, adLockPessimistic
    
    ' Get a count of the fields and subtract one since we start
    ' counting from 0.
    iCols = rstExcel.Fields.Count
    %>
    <table border="1">
    	<thead>
    		<%
    		' Show the names that are contained in the first row
    		' of the named range.  Make sure you include them in
    		' your range when you create it.
    		For I = 0 To iCols - 1
    			Response.Write "<th>"
    			Response.Write rstExcel.Fields.Item(I).SYSTEM
    			Response.Write "</th>" & vbCrLf
    		Next 'I
    		%>
    	</thead>
    	<%
    	rstExcel.MoveFirst
    
    	' Loop through the data rows showing data in an HTML table.
    	Do While Not rstExcel.EOF
    		Response.Write "<tr>" & vbCrLf
    		For I = 0 To iCols - 1
    			Response.Write "<td>"
    			Response.Write rstExcel.Fields.Item(I).Value
    			Response.Write "</td>" & vbCrLf
    		Next 'I
    		Response.Write "</tr>" & vbCrLf
    
    		rstExcel.MoveNext
    	Loop
    	%>
    </table>
    
    <%
    rstExcel.Close
    Set rstExcel = Nothing
    
    cnnExcel.Close
    Set cnnExcel = Nothing
    %>
    
    
    Code (markup):
    Does anyone know how to get Dynamic Range going for the update to reflect into asp. Thanks!!
     
    RodDev, Dec 17, 2009 IP
  6. elizas

    elizas Peon

    Messages:
    16
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    XML is a simple and flexible system for defining data formats. This is completely platform independent and adopted everywhere for representing complex documents and data structures. For data transmission on web, its having significant contribution.
    Now days it's been the major use of XML to store both rowset (single table) and hierarchical (multiple-table) data in it.
     
    elizas, May 3, 2010 IP