Lm th? no ? truy v?n v C?p Nh?t Excel d? li?u b?ng cch s? d?ng ADO t? ASP

D?ch tiu ? D?ch tiu ?
ID c?a bi: 195951 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

TM T?T

Bi vi?t ny ch?ng t? lm th? no ? truy v?n v C?p Nh?t thng tin trong m?t b?ng tnh Excel s? d?ng ActiveX Data Objects (ADO) t? m?t Active Server Pages (ASP) trang. Bi vi?t c?ng m t? nh?ng h?n ch? m c lin quan v?i lo?i ?ng d?ng.

Quan tr?ng: m?c d Cc ?ng d?ng ASP/ADO h? tr? a ng?i dng truy c?p, m?t b?ng tnh Excel khng. V? v?y, phng php ny c?a cu v c?p nh?t thng tin h? tr? nhi?u ng?i s? d?ng truy c?p ?ng th?i.

THNG TIN THM

? truy c?p d? li?u trong b?ng tnh Excel c?a b?n cho vi?c ny m?u, s? d?ng tr?nh i?u khi?n ODBC Microsoft cho Excel. T?o ra m?t b?ng ? truy c?p vo cc d? li?u b?ng cch t?o ra m?t lo?t cc Named trong b?ng tnh Excel c?a b?n.

Cc b?c ? t?o ?ng d?ng m?u

  • T?o t?p tin Excel ADOtest.xls v?i d? li?u sau trong sheet1:

    Thu g?n b?ng nyBung r?ng b?ng ny
    column1column2column3
    RR i?u ny15
    BBth? nghi?m20
    EEtc ph?m25

    Chu y N?u m?t c?t trong b?ng tnh Excel c?a b?n c ch?a c? hai vn b?n v s? i?n tho?i, tr?nh i?u khi?n Excel ODBC khng chnh xc gi?i thch ki?u d? li?u m cc c?t nn. H?y ?m b?o r?ng t?t c? cc trong m?t c?t c?a cc cng m?t ki?u d? li?u. Ba l?i sau y c th? x?y ra n?u m?i t? bo trong m?t c?t khng ph?i l c?a cng lo?i ho?c b?n ? lo?i h?n h?p gi?a "vn b?n" v "chung":
    1. Microsoft OLE DB Provider cho tr?nh i?u khi?n ODBC l?i '80040e21'
      Thu?c tnh yu c?u khng th? ?c h? tr? b?i tr?nh i?u khi?n ODBC.
    2. Microsoft OLE DB Provider cho tr?nh i?u khi?n ODBC l?i '80004005'
      Truy v?n khng ph?i l updateable v? n c khng c c?t t?m ki?m ?c s? d?ng nh m?t ch?a kha hy v?ng.
    3. Microsoft OLE DB Provider cho tr?nh i?u khi?n ODBC l?i '80004005'
      Truy v?n d?a C?p Nh?t khng thnh cng. D?ng C?p Nh?t khng t?m th?y.
  • T?o m?t ph?m vi Named, myRange1, trong b?ng tnh c?a b?n:

    1. Lm n?i b?t khu v?c row(s) v column(s) ni d? li?u c?a b?n c tr.
    2. Trn menu chn, tr? ?n tn, v nh?p vo Xc ?nh.
    3. Nh?p myRange1 tn cho d?y Named Tn.
    4. Bm OK.
    MyRange1 Named Range ch?a d? li?u sau:

    Thu g?n b?ng nyBung r?ng b?ng ny
    column1column2column3
    RRi?u ny15
    BBth? nghi?m20
    EEtc ph?m25


    Chu y ADO gi? ?nh r?ng hng ?u trong m?t truy v?n Excel c ch?a cc ? m?c c?t. Do , ph?m vi Named ph?i bao g?m cc ? m?c c?t. y l hnh vi khc nhau t? DAO.

    Chu y ? m?c c?t khng th? l m?t s?. Khng th? i?u khi?n Excel gi?i thch chng, v thay vo , tr? l?i m?t t? bo tham kh?o. V d?, m?t c?t tiu ? c?a "f1" s? ?c misinterpreted.
  • T?o m?t ODBC h? th?ng d? li?u ngu?n tn (DSN) tr? ?n cc t?p tin ADOTest.xls.
    1. T? b?ng i?u khi?n, m? ODBC Ng?i qu?n tr?.
    2. Trn tab DSN h? th?ng, b?m thm.
    3. Ch?n Microsoft Excel Driver (*.xls) v b?m hon t?t. N?u ty ch?n ny khng t?n t?i, b?n c?n ph?i ci ?t tr?nh i?u khi?n Microsoft ODBC cho Excel t? thi?t l?p Excel.
    4. Ch?n ADOExcel cho tn ngu?n d? li?u.
    5. ?m b?o r?ng cc phin b?n ?c thi?t l?p ? phin b?n ng c?a Excel.
    6. B?m vo "Ch?n Workbook...", duy?t ?n ADOTest.xls t?p tin, v nh?n OK.
    7. Nh?p vo cc "ty ch?n >>" nt v r? rng "?c Ch?"h?p ki?m.
    8. Nh?p OK v sau nh?p OK m?t l?n n?a.
  • Thi?t l?p quy?n truy c?p vo cc t?p tin ADOTest.xls.
Trang ch? ho?t ?ng c?a b?n ?c truy c?p n?c danh, b?n ph?i ?m b?o r?ng cc ti kho?n cha xc ?nh ng?i (IUSR_<machinename>) c t ?c/ghi (RW) quy?n truy c?p vo b?ng tnh. N?u b?n mu?n xa b? thng tin t? b?ng tnh, b?n c?n ? c?p quy?n truy c?p cho ph h?p.<b00></b00></machinename>

N?u b?n ang ch?ng th?c quy?n truy c?p vo trang ch? ho?t ?ng c?a b?n, b?n c?n ph?i ?m b?o r?ng t?t c? ng?i dng truy c?p vo ?ng d?ng c?a b?n c thch h?p c?p php.

Chu y N?u b?n khng thi?t l?p cho php thch h?p trn cc b?ng tnh, b?n s? c ?c m?t thng bo l?i tng t? nh sau:

Microsoft OLE DB Provider cho tr?nh i?u khi?n ODBC l?i '80004005'

[Microsoft][ODBC Excel Driver] My bay ph?n l?c Microsoft c s? d? li?u khng th? m? t?p '(khng bi?t)'. N ? ?c m? ra ?c quy?n b?i ng?i dng khc, ho?c b?n c?n s? cho php ? xem d? li?u c?a n.
  1. T?o m?t trang ASP m?i v dn trong o?n m? sau:
          <!-- 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. Ti?t ki?m v tn trang ch? ho?t ?ng c?a b?n v xem n trong cc tr?nh duy?t. B?n s? th?y nh?ng i?u sau y:
          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
    					
Chu y M?t b?n C?p Nh?t ?c th?c hi?n trn hng ?u tin c?a ph?m vi Named c?a b?n (sau cc ? m?c).

THAM KH?O

bit thm thng tin, bm vao s bai vit sau xem bai vit trong C s Kin thc Microsoft:
190195Lm th? no ? ExtractInformation t? Excel t? v?i DAO

Thu?c tnh

ID c?a bi: 195951 - L?n xem xt sau cng: 20 Thang Tam 2011 - Xem xt l?i: 2.0
p d?ng
  • Microsoft Active Server Pages 4.0
  • Microsoft Data Access Components 2.5
T? kha:
kbcode kbhowto kbmdacnosweep kbmt KB195951 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:195951

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com