Флажки для ввода данных для SQL-запроса в VBA (userform-excel)

Question

Я пытаюсь создать пользовательскую форму, которая будет иметь флажки для разных лет и командные кнопки для разных запросов. Так, если, например, пользователь выбирает три флажка -1990,1993, 1995, а затем пользователь нажимает на конкретный запрос. Затем этот запрос должен быть выполнен с указанием года при переходе в "часть запроса". Это мой код:

*

  Private Sub CommandButton1_Click()
    Dim connection As New ADODB.connection
    Dim rst As New Recordset
    Dim strConnectionStr As String
    Dim Qry As String
    strConnectionStr = "Provider=SQLOLEDB;" & "DATA SOURCE=" & _
                      "INITIAL CATALOG=;" & _
                      "UID=; PWD="
    Qry = "  SELECT d.Year,d.[University name],d.[School name], COUNT(distinct d.Title) 'Number of paper published'from [dbo].[Ashish$] d where [Business/Non-business]='1'group by d.Year,d.[University name],d.[School name]  order by d.[Year], [Number of paper published] desc;"

    ActiveSheet.Cells.ClearContents
    connection.Open strConnectionStr
    rst.Open Qry, connection
    For iCols = 0 To rst.Fields.Count - 1
        Sheets("Query1").Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
    Next
    Sheets("Query1").Range("A2").CopyFromRecordset rst*`


    rst.Close
    connection.Close

    End Sub


Above is the code for normal command buttons without check box . Below is the code for taking user inputs by checkboxes ...

    Private Sub CommandButton1_Click()
    Dim connection As New ADODB.connection
    Dim rst As New Recordset
    Dim strConnectionStr As String
    Dim Qry As String
    Dim ctl As Control
    Dim i As Integer
    i = 0
    strConnectionStr = "Provider=SQLOLEDB;" & "DATA SOURCE=;" & _
                      "INITIAL CATALOG=;" & _
                      "UID=; PWD="
    If CheckBox6.Value = True Then
       year1 = CheckBox6.Caption
       i = i + 1
    End If
    If CheckBox5.Value = True Then
       year2 = CheckBox5.Caption
       i = i + 1
    End If

    Qry = "  SELECT d.Year,d.[University name],d.[School name], COUNT(distinct d.Title) 'Number of paper published'from [dbo].[Ashish$] d where [Business/Non-business]='1'  and d.Year=CheckBox6.Caption  group by d.Year,d.[University name],d.[School name]  order by d.[Year], [Number of paper published] desc;"

    ActiveSheet.Cells.ClearContents

    connection.Open strConnectionStr

    rst.Open Qry, connection

    For iCols = 0 To rst.Fields.Count - 1
        Sheets("Query1").Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
    Next

    Sheets("Query1").Range("A2").CopyFromRecordset rst


    rst.Close
    connection.Close
    End Sub

UB

В основном я не могу взять значения из флажков и использовать их в запросе для этой цели.

Мне нужна помощь. Кто-нибудь может направить меня в этом?

---

изменить: следующее было первоначально опубликовано в качестве ответа:

If CheckBox1.Value = True Then
 y1 = CheckBox1.Caption
 i = i + 1
End If
If CheckBox2.Value = True Then
 y2 = CheckBox2.Caption
 i = i + 1
End If

If CheckBox3.Value = True Then
x1 = CheckBox3.Caption
j = j + 1
End If

If CheckBox4.Value = True Then
x2 = CheckBox4.Caption
j = j + 1
End If

If CheckBox5.Value = True Then
x3 = CheckBox5.Caption
j = j + 1
End If

If i = 0 Then
MsgBox "Select at least one year "
End If

If j = 0 Then
MsgBox "Select at least one journal "
End If


strConnectionStr = "Provider=SQLOLEDB;" & "DATA SOURCE=;" & _
                  "INITIAL CATALOG=;" & _
                  "UID=; PWD="
Qry = "  SELECT d.Year,d.[University name],d.[School name], COUNT(distinct d.Title) 'Number of paper published'from [dbo].[Ashish$] d where [Business/Non-business]='1' and "
Qry = Qry & "[Year] IN (" & y1 & "," & y2 & ") and [Name] IN  (" & x3 & "," & x4 & ") & vbCrLf"
Qry = Qry & "group by d.Year,d.[University name],d.[School name]  order by d.[Year], [Number of paper published] desc;"

ActiveSheet.Cells.ClearContents

connection.Open strConnectionStr

rst.Open Qry, connection

For iCols = 0 To rst.Fields.Count - 1
    Sheets("Query1").Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
Next

Sheets("Query1").Range("A2").CopyFromRecordset rst


rst.Close
connection.Close
End Sub

Выше мой код и вопрос опубликован в моем последнем комментарии

Answer 1

Это так же просто, как ...and d.Year=" & CheckBox6.Caption & " group by...

Если вы хотите использовать значение переменной, тогда оно должно быть вне двойных кавычек. Затем вы можете использовать & оператор, чтобы соединить части вместе.

НО: создание SQL-запроса из пользовательского ввода путем объединения строк вызывает проблемы. SQL-инъекция может быть очень реальной возможностью - смотрите здесь и здесь

Вы можете использовать объект ADODB Command вместе с коллекцией Parameters для безопасного выполнения параметризованного запроса. Смотрите этот ответ для демонстрации того, как это сделать

---

edit: если некоторые значения являются текстовыми строками, то они должны быть заключены в одинарные кавычки, например:

and [Name] IN ('" & x3 & "','" & x4 & "')

который будет производить вывод, как это:

and [Name] IN ('Academy of Science','Foo')

Если только некоторые переменные могут иметь значения, то вам нужно по-другому конструировать предложение IN:

Dim yearsChosen As String

If CheckBox1.Value = True Then
    yearsChosen = yearsChosen & "," & CheckBox1.Caption
    i = i + 1
End If
If CheckBox2.Value = True Then
    yearsChosen = yearsChosen & "," & CheckBox2.Caption
    i = i + 1
End If

' Check to see if anything was selected - if it was, remove the
' leading comma and add the AND [Year] IN part
If Len(yearsChosen <> 0) Then
    yearsChosen = " AND [Year] IN (" & Mid$(yearsChosen, 2) & ")"
End If

Часть Names будет работать аналогично, за исключением того, что вам нужны одинарные кавычки вокруг значений:

namesChosen = namesChosen & ",'" & CheckBox5.Caption & "'"

Построить SQL-запрос довольно просто:

Qry = "  SELECT d.Year,d.[University name],d.[School name], COUNT(distinct d.Title) 'Number of paper published'from [dbo].[Ashish$] d where [Business/Non-business]='1' "
Qry = Qry & yearsChosen & namesChosen & vbCrLf
Qry = Qry & "group by d.Year,d.[University name],d.[School name]  order by d.[Year], [Number of paper published] desc;"