Kết nối ADO để lấy dữ liệu

[ishikawangocthuy]

Xin chào các anh chị trên diễn đàn ah!
-Em xem trên diễn đàn về bài@Tìm kiếm Tên nhân viên thông qua ID từ file excel khac. Do thầy @Hai Lua mien tay hướng dẫn và em có làm theo, nhưng vẫn bị lỗi ở đoạn này ah.

Rst.Open ("Select [Ten],[Bo_Phan] From [Sheet1$] Where [ID] = " & TextBox1.Text), cnn, 1, 3

-Xin các anh chị trên diễn đàn huớng dẫn ah.Em xin cảm ơn

 

[thnghiachau]

Xin chào các anh chị trên diễn đàn ah!
-Em xem trên diễn đàn về bài@Tìm kiếm Tên nhân viên thông qua ID từ file excel khac. Do thầy @Hai Lua mien tay hướng dẫn và em có làm theo, nhưng vẫn bị lỗi ở đoạn này ah.

Rst.Open ("Select [Ten],[Bo_Phan] From [Sheet1$] Where [ID] = " & TextBox1.Text), cnn, 1, 3

-Xin các anh chị trên diễn đàn huớng dẫn ah.Em xin cảm ơn

bạn chỉnh lại code theo sau:

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim oConn As Object, oRst As Object
Dim sConn As String, sQuery As String
Dim arrName
    Set oConn = CreateObject("ADODB.Connection")
    Set oRst = CreateObject("ADODB.Recordset")

    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & ThisWorkbook.Path & "\Ds nhan vien.xlsx" & ";" & _
            "Extended Properties=""Excel 12.0;HDR=Yes"";"
    oConn.Open sConn
    
    sQuery = "SELECT [Ten],[Bo_Phan] FROM [Sheet1$] WHERE [ID]='" & TextBox1.Text & "'"
    oRst.Open sQuery, oConn, 1, 3
                
    If oRst.RecordCount > 0 Then
        arrName = oRst.GetRows
        'arrtuoi = Rst.GetRows
        'MsgBox arrName(0, 0)
    Else
        MsgBox "Khong co du lieu!"
        Exit Sub
    End If
    
    TextBox2.Text = arrName(0, 0)
    TextBox3.Text = arrName(1, 0)
    
End Sub

 

[Hai Lúa Miền Tây]

Xin chào các anh chị trên diễn đàn ah!
-Em xem trên diễn đàn về bài@Tìm kiếm Tên nhân viên thông qua ID từ file excel khac. Do thầy @Hai Lua mien tay hướng dẫn và em có làm theo, nhưng vẫn bị lỗi ở đoạn này ah.

Rst.Open ("Select [Ten],[Bo_Phan] From [Sheet1$] Where [ID] = " & TextBox1.Text), cnn, 1, 3

-Xin các anh chị trên diễn đàn huớng dẫn ah.Em xin cảm ơn

Sao không phải là After mà là Before? Do kiểu dữ liệu ID là dạng chuỗi nên bạn chuyển sang như sau nhé:

Private Sub TextBox1_AfterUpdate()
    Dim cnn As Object, Rst As Object
    Set cnn = CreateObject("ADODB.Connection")
    Set Rst = CreateObject("ADODB.Recordset")
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Ds nhan vien.xlsx;Extended Properties=Excel 12.0;"
    Rst.Open ("Select [Ten],[Bo_Phan] From [Sheet1$] Where [ID] like '" & TextBox1.Text & "'"), cnn, 1, 3
             
    If Rst.RecordCount > 0 Then
        arrName = Rst.GetRows
    Else
        MsgBox "Khong co du lieu!"
        Exit Sub
    End If
    TextBox2.Text = arrName(0, 0)
    TextBox3.Text = arrName(1, 0)
End Sub

 

[ishikawangocthuy]

bạn chỉnh lại code theo sau:

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim oConn As Object, oRst As Object
Dim sConn As String, sQuery As String
Dim arrName
    Set oConn = CreateObject("ADODB.Connection")
[QUOTE="Hai Lúa Miền Tây, post: 990229, member: 42738"]
Sao không phải là After mà là Before? Do kiểu dữ liệu ID là dạng chuỗi nên bạn chuyển sang như sau nhé:

[CODE]Private Sub TextBox1_AfterUpdate()
    Dim cnn As Object, Rst As Object
    Set cnn = CreateObject("ADODB.Connection")
    Set Rst = CreateObject("ADODB.Recordset")
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Ds nhan vien.xlsx;Extended Properties=Excel 12.0;"
    Rst.Open ("Select [Ten],[Bo_Phan] From [Sheet1$] Where [ID] like '" & TextBox1.Text & "'"), cnn, 1, 3
            
    If Rst.RecordCount > 0 Then
        arrName = Rst.GetRows
    Else
        MsgBox "Khong co du lieu!"
        Exit Sub
    End If
    TextBox2.Text = arrName(0, 0)
    TextBox3.Text = arrName(1, 0)
End Sub

Set oRst = CreateObject("ADODB.Recordset")

sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & "\Ds nhan vien.xlsx" & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
oConn.Open sConn

sQuery = "SELECT [Ten],[Bo_Phan] FROM [Sheet1$] WHERE [ID]='" & TextBox1.Text & "'"
oRst.Open sQuery, oConn, 1, 3

If oRst.RecordCount > 0 Then
arrName = oRst.GetRows
'arrtuoi = Rst.GetRows
'MsgBox arrName(0, 0)
Else
MsgBox "Khong co du lieu!"
Exit Sub
End If

TextBox2.Text = arrName(0, 0)
TextBox3.Text = arrName(1, 0)

End Sub[/CODE]
[/QUOTE]
Cảm ơn @thnghiachau nhiều ah!

Bài đã được tự động gộp: 9/9/20

Sao không phải là After mà là Before? Do kiểu dữ liệu ID là dạng chuỗi nên bạn chuyển sang như sau nhé:

Private Sub TextBox1_AfterUpdate()
    Dim cnn As Object, Rst As Object
    Set cnn = CreateObject("ADODB.Connection")
    Set Rst = CreateObject("ADODB.Recordset")
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Ds nhan vien.xlsx;Extended Properties=Excel 12.0;"
    Rst.Open ("Select [Ten],[Bo_Phan] From [Sheet1$] Where [ID] like '" & TextBox1.Text & "'"), cnn, 1, 3
            
    If Rst.RecordCount > 0 Then
        arrName = Rst.GetRows
    Else
        MsgBox "Khong co du lieu!"
        Exit Sub
    End If
    TextBox2.Text = arrName(0, 0)
    TextBox3.Text = arrName(1, 0)
End Sub

Cảm ơn thầy@Hai Lúa Miền Tây nhiều ah!

 

[VetMini]

Sao không phải là After mà là Before? Do kiểu dữ liệu ID là dạng chuỗi nên bạn chuyển sang như sau nhé:
...

Loại công việc này người ta phải phân tích trước xem mỗi lần làm việc thì phải "tìm kiếm" (Update TextBox1) bao nhiêu lần.
Nếu chỉ tìm kiếm 1 vài lần thì code như vậy được rồi.
Nhưng nếu phải tìm kiếm liên tục thì cứ đóng mở kết nối mãi sẽ có khả năng bị hở bộ nhớ. Theo lý thuyết thì connection đóng lại khi object ra khỏi phạm vi (out of scope); trên thực tế ADO kết nối với file Excel có thể chứa bug hở bộ nhớ. Vả lại, một lần kết nối cũng khá tốn thời gian.

Nếu phải truy vấn nhiều lần thì dùng connection là biến toàn cục. Lần đầu tiên cần thì mở kết nối và giữ đó. Khi unload Form thì đóng lại.

 

[Hai Lúa Miền Tây]

Loại công việc này người ta phải phân tích trước xem mỗi lần làm việc thì phải "tìm kiếm" (Update TextBox1) bao nhiêu lần.
Nếu chỉ tìm kiếm 1 vài lần thì code như vậy được rồi.
Nhưng nếu phải tìm kiếm liên tục thì cứ đóng mở kết nối mãi sẽ có khả năng bị hở bộ nhớ. Theo lý thuyết thì connection đóng lại khi object ra khỏi phạm vi (out of scope); trên thực tế ADO kết nối với file Excel có thể chứa bug hở bộ nhớ.

Nếu phải truy vấn nhiều lần thì dùng connection là biến toàn cục. Lần đầu tiên cần thì mở kết nối và giữ đó. Khi unload Form thì đóng lại.

Nếu là 1 ứng dụng rõ ràng thì sẽ phải tạo kết nối khi mở form, ta dùng kết nối này để thực hiện các truy vấn. Tuy nhiên nó cũng có nhược điểm của nó.

 

[syquyen1987]

Xin chào các anh chị trên diễn đàn ah!
-Em xem trên diễn đàn về bài@Tìm kiếm Tên nhân viên thông qua ID từ file excel khac. Do thầy @Hai Lua mien tay hướng dẫn và em có làm theo, nhưng vẫn bị lỗi ở đoạn này ah.

Rst.Open ("Select [Ten],[Bo_Phan] From [Sheet1$] Where [ID] = " & TextBox1.Text), cnn, 1, 3

-Xin các anh chị trên diễn đàn huớng dẫn ah.Em xin cảm ơn

Truy vấn SQL, nếu tìm kiếm dạng text phải để vào trong dấu nháy " ' ". Nếu là số thì để nguyên, nếu là dạng ngày tháng thì phải format "mm/dd/yyyy" (tháng đứng trước ngày)

 

[Vô danh Tiểu tốt]

Tôi chả biết lập một cái kết nối với dữ liệu có tốn đến vài phần trăm giây (gõ nhầm thành vài trăm giây mà cũng làm một bác tưởng thiệt ) nữa không mà sao có người quan trọng hóa thế nhỉ? Trong khi việc duy trì một kết nối lại đòi hỏi cứ mỗi khoảng thời gian ngắn, hệ thống lại làm vài mới thủ tục nhàm chán lắm đi lặp lại theo kiểu

Ê! Mày còn nối với tao không???

Dạ! Còn nhưng em chả có việc quái gì để tương tác với bác

Ê! Mày còn nối với tao không???

Dạ! Còn nhưng em chả có việc quái gì để tương tác với bác

.............%#(&#%$#%#$%*%*&^*&^*^..................................................

.............%#$%*%*&^*&^*^..................................................

Cái vòng lặp duy trì kết nối ấy cứ kéo dài tưởng như vô tận có khi còn tốn thời gian gấp vài ngàn lần so với thời gian mở một cái kết nối. Thực tế thời gian có tương tác giữa một cái ứng dụng điển hình với dữ liệu chả mấy khi chiếm quá .... 0.1% thời gian chạy của nó. Các bác cứ tính tổng thời gian cái nút save, update...hoạt động rồi so với 8 tiếng trung bình một cái ứng dụng chạy rồi xem thế nào nhé. Cái ứng dụng nào có nhiều tính năng phức tạp thì giỏi lắm có thời gian tương tác dữ liệu chiếm được trên dưới 1% runtime. Còn nếu mà nhiều hơn số 1% này chắc chỉ có mấy chương trình thời gian thực thì may ra .

 

[VetMini]

Lập một kết nối có thời gian giới hạn nó đợi handshake. Quá thời gian ấy, tự động kết nối không đạt. Theo kinh nghiệm thì khoảng chục giây, khỏi nói chuyện trăm giây cho mệt.

Ở trên có nói, protocols để VBA sử dụng object ADO không hoàn toàn bug free. Lý do tránh mở và huỷ kết nối nhiều lần là vì có những lúc object (connection) huỷ rồi nhưng hệ thống chưa kịp thu hồi resources, hoặc bị VBA nghĩ là truy vấn vòng gì đó cho nên không huỷ object, Application (Excel) có thể bị crash.

 

[Kiều Mạnh]

Mình kết nối xong lấy dữ liệu linh tinh xong ... Close xong set = nothing xong .... quậy bấm chuột liên hồi một lúc Excel nó Ngu luôn
cũng trả biết nó là sao nữa

 

[Vô danh Tiểu tốt]

Lập một kết nối có thời gian giới hạn nó đợi handshake. Quá thời gian ấy, tự động kết nối không đạt. Theo kinh nghiệm thì khoảng chục giây, khỏi nói chuyện trăm giây cho mệt.

Theo kinh nghiệm chục giây (>=10s) của bác thì chắc cái máy bác chạy cũng phải cỡ trên dưới 28 năm tuổi (nếu còn tồn tại tới giờ) hoặc là chuyện xẩy ra cách đây cũng phải trên dưới 28 năm hoặc bác nói phét dọa mấy cháu chưa trải sự đời Năm 2002 tôi học C++ trên máy cái máy cổ lổ (so với chính máy thời 2002), với cấu hình Ram tầm 4MB chạy 2 vòng for lồng nhau, mỗi vòng for chạy không quá 30 lần với vài phép tính ở giữa mà cũng tốn cỡ hơn 3-5 giây cơ mà. Còn bây giờ trên một cái máy cũng loại cũ mèm (sản xuất cách đây 8 năm) Ram 4GB (gấp 1024 lần), chưa kịp chớp mắt thì cái đoạn mã for trên xong đời nào rồi, tôi cam đoan nói thật chứ không nói phét nhé.

Nếu mà bác còn cái máy tính nào trên 28 tuổi mà vẫn hoạt động thì ngay lập tức kiếm két sắt bỏ vào nhé. Bảo đảm 10 năm, nếu không phải bác thì con cháu bác phải đút túi vài trăm triệu nếu nó còn hoạt động được đấy. Còn nếu bác nói phét góp tý không khí, thì có người cười rồi đấy

 

[VetMini]

Truy vấn SQL, nếu tìm kiếm dạng text phải để vào trong dấu nháy " ' ". Nếu là số thì để nguyên, nếu là dạng ngày tháng thì phải format "mm/dd/yyyy" (tháng đứng trước ngày)

Với hầu hết các phiên bản của SQL, ngày tháng viết theo dạng hằng (literal) thì dùng tiêu chuẩn Nhật chứ: format 'yyyymmdd'

 

[syquyen1987]

Với hầu hết các phiên bản của SQL, ngày tháng viết theo dạng hằng (literal) thì dùng tiêu chuẩn Nhật chứ: format 'yyyymmdd'

Mình không hiểu lắm, như bạn bảo viết theo dạng hằng thì phải chuyển về hằng số như code DateSerial(Year(..), Month(..), Day(..)) chứ ạ. Còn dùng tiêu chuẩn Nhật format 'yyyymmdd' là sao ạ. Bạn có thể giải thích rõ hơn được không ạ. Mình xin cảm ơn

 

[VetMini]

Hầu hết các phiên bản SQL đều có thể ép kiểu chuỗi sang ngày khi cần. Và hầu hết đều hiểu dạng '20200910' có nghĩa là ngày 10 tháng 9 năm 2020.

 

[ongke0711]

Mình không hiểu lắm, như bạn bảo viết theo dạng hằng thì phải chuyển về hằng số như code DateSerial(Year(..), Month(..), Day(..)) chứ ạ. Còn dùng tiêu chuẩn Nhật format 'yyyymmdd' là sao ạ. Bạn có thể giải thích rõ hơn được không ạ. Mình xin cảm ơn

Trong SQL Server, tốt nhất là tryền tham số ngày dạng chuỗi ký tự (literal): [NgaySX] = 'yyyymmdd', đây là dạng chuẩn của nó để tính toán ngày tháng chính xác.

(Tôi nhầm bạn đề cập câu lênh SQL trong VBA sang SQL Server )

 

[syquyen1987]

Hầu hết các phiên bản SQL đều có thể ép kiểu chuỗi sang ngày khi cần. Và hầu hết đều hiểu dạng '20200910' có nghĩa là ngày 10 tháng 9 năm 2020.

Mình vừa thử như ý bạn thì câu lệnh truy vấn không ra kết quả ah, ví dụ mình muốn lọc dữ liệu từ 1/1/2020 đến 1/3/2020, như bình thường mình viết câu lệnh truy vấn là SELECT * FROM [SHEET1$A2:E10000 WHERE F2 BETWEEN #01/01/2020# AND #03/01/2020#" là ra kết quả. Nếu như theo ý bạn thì mình phải viết thế nào để có kết quả ạ. Mình cảm ơn

Bài đã được tự động gộp: 10/9/20

Trong SQL Server, tốt nhất là tryền tham số ngày dạng chuỗi ký tự (literal): [NgaySX] = 'yyyymmdd', đây là dạng chuẩn của nó để tính toán ngày tháng chính xác.

Mình chưa từng tiếp xúc SQL Server, mình mới chỉ từng tiếp xúc VBA thuần túy thôi ạ, nên mình không hiểu hết ý vấn đề này (chuyển ngày thành chuỗi ký tự). Mình vừa thử VBA ADO thuần túy thì không ra kết quả cho chuyển ngày thành chuỗi ký tự.

 

[Hai Lúa Miền Tây]

Mình vừa thử như ý bạn thì câu lệnh truy vấn không ra kết quả ah, ví dụ mình muốn lọc dữ liệu từ 1/1/2020 đến 1/3/2020, như bình thường mình viết câu lệnh truy vấn là SELECT * FROM [SHEET1$A2:E10000 WHERE F2 BETWEEN #01/01/2020# AND #03/01/2020#" là ra kết quả. Nếu như theo ý bạn thì mình phải viết thế nào để có kết quả ạ. Mình cảm ơn

Nó ra là hên nhé bạn, như trường hợp trên thì xét ngày và tháng 01/01 thì không cần xét, vì ngày và tháng giống nhau, còn 03/01 là ngày 03 hay là ngày 01 hoặc tháng 03 hay là tháng 01. Bạn có chắc là nó lọc ra dữ liệu theo ý với nhiều máy khác nhau? Do vậy ta phải đưa nó về định dạng chuẩn là yyyy-MM-dd cho chắc nhé.