Cách thoát chuỗi trích dẫn cho câu lệnh SQL được sử dụng trong cơ sở dữ liệu thông qua Excel VBA

Giải pháp Excel 3 Tháng Mười
Chia sẻ ngay bây giờ:

SQL Server Sử dụng các cặp dấu ngoặc đơn để xác định điểm bắt đầu và kết thúc của một chuỗi. Việc chèn 'Mrs Brown's Boys' vào bảng cơ sở dữ liệu sẽ thất bại vì ba dấu ngoặc đơn ngụ ý hai chuỗi, một trong số đó chưa hoàn chỉnh. Cần có ký tự thoát cho dấu nháy đơn sau chữ "Brown". Bài viết này sẽ khám phá việc sử dụng một hàm VBA tùy chỉnh để giải quyết sự bất thường này.

Bài viết này giả định rằng người đọc đã hiển thị dải băng Nhà phát triển và quen thuộc với Trình soạn thảo VBA. Nếu không, hãy Google “Excel Developer Tab” hoặc “Excel Code Window”.

Thuật ngữ “cơ sở dữ liệu” ở đây áp dụng cho cơ sở dữ liệu “sức mạnh công nghiệp” như SQL Server và Oracle.

Một ví dụ về sổ làm việc được sử dụng trong bài tập này có thể được tìm thấy đây.

Chuỗi SQL

Việc chèn dấu nháy đơn (hoặc dấu ngoặc kép) vào bên trong câu lệnh SQL sẽ trả về lỗi sau từ trình quản lý cơ sở dữ liệu (trong trường hợp này là với tên O'Dowd):Lỗi được trả về từ Trình quản lý cơ sở dữ liệu

Cần có ký tự thoát, đó là dấu nháy kép thay vì dấu nháy đơn. Vì vậy, O”Dowd được chấp nhận trong cơ sở dữ liệu. O'Dowd thì không.

Chức năng

Trong trường hợp các trường bắt giữ có thể chứa dấu nháy đơn, có thể xây dựng một hàm tùy chỉnh để thực thi trước khi cập nhật, thay thế dấu nháy đơn bằng dấu nháy kép.

  1. Mở một sổ làm việc mới;
  1. Đặt tên cho trang đầu tiên là “Cập nhật” và hoàn thành như sau, sử dụng tên cơ sở dữ liệu của riêng bạn, v.v. Các trường này sẽ được sử dụng để xây dựng chuỗi kết nối tới SQL Server.Đặt tên cho bảng đầu tiên là "Cập nhật" và hoàn thành như thế này
  2. Mở cửa sổ mã và chèn một mô-đun. Sử dụng các mục menu >Công cụ >Tham chiếu để tham chiếu các thư viện ADO.Mở cửa sổ mã và chèn một mô-đun

Sao chép mã bên dưới vào mô-đun. Điều này kết nối với cơ sở dữ liệu.

ConnDB công khai dưới dạng ADODB.Connection công khai rs Như mới ADODB.Recordset Public strSQL dưới dạng chuỗi Public strCriteria dưới dạng chuỗi Sub ConnectDatabase() Nếu connDB.State = 1 Thì connDB.Close On Error GoTo ErrConnect Dim strServer, strDBase, strUser, strPWD As String strServer = Trang tính("Cập nhật").Range("B2") strDBase = Trang tính("Cập nhật").Range("B3") strUser = Trang tính("Cập nhật").Range("B4") strPWD = Trang tính(" Cập nhật").Range("B5") Nếu strPWD > "" Thì strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & ";Hết thời gian kết nối=30;" Khác strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" & strDBase 'Kết thúc xác thực Windows Nếu connDB.ConnectionTimeout = 30 connDB.Open strConnectionstring Thoát Sub ErrConnect: MsgBox Err.Description End Sub
  1. Thêm chức năng vào mô-đun:
Hàm fRemoveApostrophe(strWord As String) Khai báo n là số nguyên Khai báo x là số nguyên x = 0 Đối với n = 0 đến 100 x = InStr(x + 2, strWord, "'") 'Tìm vị trí của dấu nháy đơn Nếu x = 0 Thì Thoát Đối với Nếu x > 0 Thì strWord = Left(strWord, x - 1) & Chr(39) & Chr(39) & Right(strWord, Len(strWord) - (x)) Kết thúc Nếu Tiếp theo n fRemoveApostrophe = strWord Kết thúc Hàm
  1. Bỏ qua chức năng.
Sub IgnoreFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B10") strSQL = "Insert into tblCrewMember (LastName) values ​​('" & strCriteria & "')" MsgBox strSQL & ". Lệnh SQL này sẽ thất bại; lưu ý ba dấu nháy đơn." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
  1. Sử dụng chức năng
Sub UseFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B15") strCriteria = fRemoveApostrophe(strCriteria) strSQL = "Insert into tblCrewMember (LastName) values ​​('" & strCriteria & "')" MsgBox strSQL & ". This SQL entry will succeed, and appear in the datatable as O'Dowd." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
  1. Hoàn thành mẫu đơn Cập nhật bảng tính như sau, bắt đầu từ ô A8:Hoàn thành bảng tính cập nhật
  1. Gán các nút cho macro Bỏ qua chức năng và Sử dụngChức năng lần lượt

Kết quả

Một hộp thông báo sẽ hiển thị kết quả; không có cơ sở dữ liệu nào được cập nhật về mặt vật lý trong bài tập này nhưng nếu bạn muốn làm như vậy, hãy đảm bảo tên trường tương thích với cơ sở dữ liệu của bạn và sử dụng câu lệnh VBA conndb.execute(strSQL)

Khôi phục từ sự cố Excel

Excel dễ bị treo khi máy tính của bạn sắp hết tài nguyên. Trong quá trình làm bài tập này, bảng tính Excel, chưa được lưu, đã bị đóng băng. Cửa sổ Mã lệnh chỉ phản hồi một phần, cho phép đóng toàn bộ sổ làm việc. Hóa ra, sổ làm việc đã mở lại bình thường với nội dung và mã lệnh hoàn chỉnh. Nếu các tệp tạm thời và tệp nguồn bị hỏng (điều này khá thường xuyên xảy ra), công việc sẽ phải làm lại từ đầu nếu không có công cụ nào để giải quyết. thiệt hại xlsx. Nó ít có ý nghĩa trong trường hợp này, nhưng có thể là một thảm họa tiềm ẩn đối với các sổ làm việc lớn hơn.

Giới thiệu tác giả:

Felix Hooker là một chuyên gia phục hồi dữ liệu trong DataNumen, Inc., công ty hàng đầu thế giới về công nghệ khôi phục dữ liệu, bao gồm khôi phục rar và các sản phẩm phần mềm phục hồi sql. Để biết thêm thông tin, hãy truy cập www.datanumennăm

Chia sẻ ngay bây giờ:

Được đóng lại.