Bài tập về ADO căn bản.

[Hoàng Trọng Nghĩa]

Chắc chắn là như thế, anh chọn cột nào thì phải select cột đó theo thứ tự anh đặt ra, còn muốn chọn hết là dấu sao (*)

Mình tưởng đâu chọn 7 to 10 được chứ!

 

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

Mình tưởng đâu chọn 7 to 10 được chứ!

Hihi, vậy thì anh cứ test thử nhé.

 

[Hoàng Trọng Nghĩa]

Hihi, vậy thì anh cứ test thử nhé.

Biết làm không được mới hỏi vậy mà.

 

[VetMini]

Nguyên văn bởi VetMini
Dùng hàm REPLACE hoặc SUBSTITUTE để đổi các chuỗi <cotN> theo đúng tên cột trước khi truy vấn.

Cho mình hỏi là mục đích chuyển đổi để làm gì vậy bạn?

2 lý do:

1. Trong đề bài có đặt điều kiện: tuỳ ý chọn cột. Vì vậy ta cứ đặt câu tổng quát rồi đến khi cần truy vấn thì chuyển đổi.

2. Thường thường dùng SQL động, viết câu tổng quát cho dễ nhìn. Khi thực sự truy vấn thì thay tham số vào.
Tôi dùng thuật này cho cả câu chuỗi kết nối.
Const CHUOIKN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<WBPathAndName>;Extended Properties=""Excel 8.0;HDR=No;"";"
.ConnectionString = Replace(CHUOIKN,"<WBPathAndName>",ThisWorkbook.FullName)

 

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

2 lý do:

1. Trong đề bài có đặt điều kiện: tuỳ ý chọn cột. Vì vậy ta cứ đặt câu tổng quát rồi đến khi cần truy vấn thì chuyển đổi.

2. Thường thường dùng SQL động, viết câu tổng quát cho dễ nhìn. Khi thực sự truy vấn thì thay tham số vào.
Tôi dùng thuật này cho cả câu chuỗi kết nối.
Const CHUOIKN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<WBPathAndName>;Extended Properties=""Excel 8.0;HDR=No;"";"
.ConnectionString = Replace(CHUOIKN,"<WBPathAndName>",ThisWorkbook.FullName)

Vậy bạn cho 1 ví dụ về việc mà bạn chọn cột tùy ý rồi replace nó xem thử nhé.

 

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

Sự khác biệt giữa inner join, left join và right join.

Ví dụ mình có đoạn code như bên dưới, các bạn cho hỏi là giữa inner join, left join và right join có sự khác biệt hay là không? Xin giải thích.

[GPECODE=sql]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then
Dim adoConn As Object, adoRS As Object
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")
With adoConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
.Open
End With
With adoRS
.ActiveConnection = adoConn
.Open "select T1.MS,T2.ten,T2.mau,T1.soluong " _
& "from [Data1$] T1 " _
& Sheet3.Range("C4").Value & " [Data2$] T2 " _
& "on T1.ms = T2.ms"
End With
With Sheet3
.[B8:E120].ClearContents
.[B8].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing

End If

End Sub

[/GPECODE]
P/s: Do các phần trên chưa nói đến phần căn bản này nên xin bổ sung thêm, mong các bạn hưởng ứng.

 

[hungpecc1]

Ví dụ mình có đoạn code như bên dưới, các bạn cho hỏi là giữa inner join, left join và right join có sự khác biệt hay là không? Xin giải thích.

P/s: Do các phần trên chưa nói đến phần căn bản này nên xin bổ sung thêm, mong các bạn hưởng ứng.

Vấn đề sử dụng các phép nối bảng trên SQL , em cũng từng ngâm cứu qua( nhưng mà vẫn lơ mơ lù mù ),cụ thể sự khác nhau giữa các phương thức có thể tham khảo ở trang web này :http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
***Nếu sử dụng các phép nối chuỗi, ta có thể áp dụng bài toán cập nhật dữ liệu mà không cần dùng vòng lặp !
ví dụ như bài test Ado của 1 bạn đã hỏi trên GPE ( trong file đính kèm )!
Nếu dùng vòng lặp thì em viết như sau :
[GPECODE=vb]
Sub Update_Data_ADO()
Dim lsSQL As String, cnn As Object, lrs As Object
Dim Tmparr(), Item, iR As Long, Val1, val2
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\DATA.xls" & _
";Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
Tmparr = Range("A2", [A65536].End(3)).Value
iR = 1
For Each Item In Tmparr
iR = iR + 1
If Len(Item) Then
Val1 = CDbl(Range("C" & iR & "")): val2 = CStr(Trim(Range("D" & iR & "")))
Debug.Print Val1; val2
lsSQL = "UPDATE [DATA$] " & _
"SET [Thanhtoan]= " & Val1 & ", [Phuong Thuc] = '" & val2 & "'" & _
"WHERE [Bill] Like '" & Item & "'"
lrs.Open lsSQL, cnn, 3, 1
End If
Next
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]
Còn nếu dùng Inner Joint thì em viết như sau :
[GPECODE=vb]
Sub Update_HLMT()
On Error GoTo Handle
Set Cn = CreateObject("ADODB.Connection")
Dim mySQL As String, strFile As Variant
strFile = Application.GetOpenFilename()
If strFile <> False Then
With Cn
mySQL = "UPDATE [Data$A2:F11] a " _
& "INNER JOIN " _
& "[Excel 8.0;HDR=No;IMEX=2;DATABASE=" _
& ThisWorkbook.FullName & "].[FORM$A2:E3] b " _
& "ON a.F1=b.F1 " _
& "SET a.F4=b.F3,a.F5=b.F4"
.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & _
";Extended Properties=""Excel 8.0;HDR=No;"";"
.Execute mySQL
.Close
End With
End If
Set Cn = Nothing
Exit Sub
Handle:
MsgBox Err.Description
End Sub
[/GPECODE]

 

[VetMini]

Bạn hỏi trên thực hành hay lý thuyết?

Theo lý thuyết, khi nối hai bảng với nhau, người ta dùng quy ước T1 là bên trái và T2 là bên phải.

Phép INNER JOIN là phép join có đối xứng, không phân biệt trái phải. (T1 INNER JOIN T2 = T2 INNER JOIN T1). Các dòng kết quả bắt buộc phải có cả bên T1 lẫn T2.

Phép LEFT JOIN là phép join không đối xứng, nó chú trọng lấy đủ hết dòng của T1, và bỏ trống phía phải nếu bên T2 không có dòng tương ứng.

Phép RIGHT JOIN ngược với phép left join, nó chú trọng lấy đủ hết dòng của T2, và bỏ trống phía trái nếu bên T1 không có dòng tương ứng

Phép FULL JOIN lấy tất cả T1 và T2, và bỏ trống bên trái hoặc bên phải nếu cần. Phép join này cũng đối xứng, tức là T1 FULL JOIN T2 = T2 FULL JOIN T1

 

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

Vấn đề sử dụng các phép nối bảng trên SQL , em cũng từng ngâm cứu qua( nhưng mà vẫn lơ mơ lù mù ),cụ thể sự khác nhau giữa các phương thức có thể tham khảo ở trang web này :http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
***Nếu sử dụng các phép nối chuỗi, ta có thể áp dụng bài toán cập nhật dữ liệu mà không cần dùng vòng lặp !
ví dụ như bài test Ado của 1 bạn đã hỏi trên GPE ( trong file đính kèm )!
Nếu dùng vòng lặp thì em viết như sau :
[GPECODE=vb]
Sub Update_Data_ADO()
Dim lsSQL As String, cnn As Object, lrs As Object
Dim Tmparr(), Item, iR As Long, Val1, val2
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\DATA.xls" & _
";Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
Tmparr = Range("A2", [A65536].End(3)).Value
iR = 1
For Each Item In Tmparr
iR = iR + 1
If Len(Item) Then
Val1 = CDbl(Range("C" & iR & "")): val2 = CStr(Trim(Range("D" & iR & "")))
Debug.Print Val1; val2
lsSQL = "UPDATE [DATA$] " & _
"SET [Thanhtoan]= " & Val1 & ", [Phuong Thuc] = '" & val2 & "'" & _
"WHERE [Bill] Like '" & Item & "'"
lrs.Open lsSQL, cnn, 3, 1
End If
Next
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]
Còn nếu dùng Inner Joint thì em viết như sau :
[GPECODE=vb]
Sub Update_HLMT()
On Error GoTo Handle
Set Cn = CreateObject("ADODB.Connection")
Dim mySQL As String, strFile As Variant
strFile = Application.GetOpenFilename()
If strFile <> False Then
With Cn
mySQL = "UPDATE [Data$A2:F11] a " _
& "INNER JOIN " _
& "[Excel 8.0;HDR=No;IMEX=2;DATABASE=" _
& ThisWorkbook.FullName & "].[FORM$A2:E3] b " _
& "ON a.F1=b.F1 " _
& "SET a.F4=b.F3,a.F5=b.F4"
.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & _
";Extended Properties=""Excel 8.0;HDR=No;"";"
.Execute mySQL
.Close
End With
End If
Set Cn = Nothing
Exit Sub
Handle:
MsgBox Err.Description
End Sub
[/GPECODE]

Cám ơn bạn trả lời, mình chỉ muốn biết ngắn gọn là 3 trường hợp trên có gì khác.

 

[hungpecc1]

Cám ơn bạn trả lời, mình chỉ muốn biết ngắn gọn là 3 trường hợp trên có gì khác.

Dạ , anh cho em hỏi nếu bây giờ em muốn inner Join lớn hơn 2 bảng , thì phải dùng câu lệnh nào tổng quát nhất ? Phép hợp Inner Jonin có hạn chế số lượng table không anh ?

 

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

Dạ , anh cho em hỏi nếu bây giờ em muốn inner Join lớn hơn 2 bảng , thì phải dùng câu lệnh nào tổng quát nhất ? Phép hợp Inner Jonin có hạn chế số lượng table không anh ?

Cũng còn tùy thuộc vào csdl của bạn nữa.

 

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

[TIP]Với dữ liệu bài #166, bạn hãy liệt kê những mặt hàng không xuất hiện trong sheet Data2[/TIP].

 

[hungpecc1]

[TIP]Với dữ liệu bài #166, bạn hãy liệt kê những mặt hàng không xuất hiện trong sheet Data2[/TIP].

Đầu tiên em xin chân thành cảm ơn anh Hai Lúa Miền Tây đã xây dựng topic này, nhờ topic này mà em đã biết thêm về ADO và em đang cố gắng học tập để áp dụng ADO vào công việc của mình !
Em xin post cách làm của em với bài tập này như sau :
[GPECODE=vb]
Sub BT_ADO()
Dim adoConn As Object, adoRS As Object
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")
With adoConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
.Open
End With
With adoRS
.ActiveConnection = adoConn
.Open "select T1.MS,T1.soluong " _
& "from [Data1$] T1 LEFT OUTER JOIN " _
& " [Data2$] T2 " _
& "on T1.ms = T2.ms" _
& " WHERE T2.ms is null"
End With
With Sheet3
.[B8:E120].ClearContents
.[B8].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing
End Sub
[/GPECODE]

 

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

Không làm khó được bạn hungpecc1: Câu trả lời của bạn hoàn toàn chính xác.
Tiếp theo nhé:

[TIP]Bạn hãy liệt kê (Ms, Ten, Mau) ở sheet Data2 mà không xuất hiện ở sheet Data1[/TIP]

 

[hungpecc1]

[TIP]Bạn hãy liệt kê (Ms, Ten, Mau) ở sheet Data2 mà không xuất hiện ở sheet Data1[/TIP][/QUOTE]

Chết rồi , em có vẻ nghiền ADO rồi ( càng vọc càng thấy hay), kiểu này lại thích ADO hơn Advanced Filter mất,^^
bài dự thi của em như sau :
[GPECODE=vb]
Sub BT_ADO()
Dim adoConn As Object, adoRS As Object
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")
With adoConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
.Open
End With
With adoRS
.ActiveConnection = adoConn
.Open "select T2.MS,T2.ten,T2.mau " _
& "from [Data1$] T1 RIGHT OUTER JOIN " _
& " [Data2$] T2 " _
& "on T1.ms = T2.ms" _
& " WHERE T1.ms is null"
End With
With Sheet3
.[B8:E120].ClearContents
.[B8].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing
End Sub
[/GPECODE]

 

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

Hihi, vậy là phần join xem như kết thúc, xin cảm ơn mọi người, cảm ơn bạn hungpecc1 đã tích cực hưởng ứng phần join này.

 

[viehoai]

Hihi, vậy là phần join xem như kết thúc, xin cảm ơn mọi người, cảm ơn bạn hungpecc1 đã tích cực hưởng ứng phần join này.

Anh Hai Lúa Miền Tây à, vì số đông chưa biết cả lý thuyết nên xin anh trình qua cơ bản lý thuyết, hoặc anh gợi ý một tí, như bài vừa rồi thì em như botay.com, không biến lần theo đường này. Như những bài trước đây vậy
Em hỏi thêm: Dạng bài toán này so sánh với cách dùng Array thì tốc độ hơn không?
Cảm ơn Anh rất nhiều

 

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

Anh Hai Lúa Miền Tây à, vì số đông chưa biết cả lý thuyết nên xin anh trình qua cơ bản lý thuyết, hoặc anh gợi ý một tí, như bài vừa rồi thì em như botay.com, không biến lần theo đường này. Như những bài trước đây vậy
Cảm ơn Anh rất nhiều

Đúng là cách trình bày đợt này có khác với đợt trước, em muốn đưa ra kết quả để tự mọi người tự tìm hiểu, sau đó có bài tập sẽ giúp cho mọi người nhớ dai hơn. Cảm ơn anh đã góp ý.
Em ít dùng Arr nhưng theo như so sánh thì Arr sẽ có có tốc độ xử lý cao hơn ado, mỗi cái đều có thế mạnh riêng anh à. Nếu kết hợp giữa ado và arr sẽ cho tốc độ rất tuyệt.

 

[VetMini]

[TIP]Với dữ liệu bài #166, bạn hãy liệt kê những mặt hàng không xuất hiện trong sheet Data2[/TIP].

Trong phạm vi của câu hỏi về cách sử dụng LEFT JOIN thì câu trả lời của hungpecc1 là đúng.

Trong phạm vi mở rộng của ngôn ngữ SQL thì đề này nếu giải bằng lệnh WHERE NOT EXISTS thì hợp lô gic hơn - và dễ lồng trong câu truy vấn phụ hơn.

Select trường1, trường2, vv
From Bảng1
Where Not Exists (Select * From Bảng2 Where Khoá = Bảng1.Khoá)

Về vấn đề tốc độ truy vấn thì vì ở đây bảng là worksheet nên hiệu quả cũng gần như nhau, không đáng kể. Nếu bảng nằm trên CSDL nghiêm túc (như SQL Server,...) thì hiệu quả có thể khác.

 

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

Trong phạm vi của câu hỏi về cách sử dụng LEFT JOIN thì câu trả lời của hungpecc1 là đúng.

Trong phạm vi mở rộng của ngôn ngữ SQL thì đề này nếu giải bằng lệnh WHERE NOT EXISTS thì hợp lô gic hơn - và dễ lồng trong câu truy vấn phụ hơn.

Select trường1, trường2, vv
From Bảng1
Where Not Exists (Select * From Bảng2 Where Khoá = Bảng1.Khoá)

Về vấn đề tốc độ truy vấn thì vì ở đây bảng là worksheet nên hiệu quả cũng gần như nhau, không đáng kể. Nếu bảng nằm trên CSDL nghiêm túc (như SQL Server,...) thì hiệu quả có thể khác.

Ở đây mình đang muốn nói đến join, nếu dùng như bạn thì nó thuộc về vấn đề khác.