Trouble building an SQL Query from VB.NET checklistbox options

Question

I am trying to create an sql query from options selected in a checkListBox. The user will select all of the modules they want (in the checklistbox) to include data from, it will then build the query to collect this data. They will also enter a range for a rating value that will be included in the query. I am very new to using sql so I am struggling to understand what operator is missing from the final query.

This is what I have at the moment:

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim h As Integer
    Dim queryString As String
    Dim moduleArray(6) As String
    Dim counter As Integer = 0
    Dim provider As String
    Dim database As String
    Dim connString As String
    Dim moduleLen As Integer = 0
    Dim moduleString As String = ""
    Dim sqlquery As New OleDb.OleDbCommand

    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
    Change the following to your access database location
    database = "C:\Users\mello_000\OneDrive\Google Drive\Computing\Exampapergenerator\users.accdb"
    connString = provider & database
    Dim myConnection As OleDbConnection = New OleDbConnection(connString)

    myConnection.Open()
    sqlquery.Connection = myConnection
    For h = 0 To h = 6
        For Each item As String In Me.CheckedListBox1.CheckedItems
            moduleArray(moduleLen) = item
            If moduleArray(moduleLen) = "" Then
            Else
                moduleLen = moduleLen + 1
            End If
        Next
    Next

    For i = 0 To moduleLen
        If i = 0 Then
            moduleString = "'" & moduleArray(i) & "'"
        ElseIf i > 0 Then
            moduleString = moduleString & "'OR'" + "'" & moduleArray(i) & "'"
        End If
    Next


    queryString = ("SELECT QText FROM Question WHERE QModule = '" & moduleString & "' AND QRating BETWEEN '" & TextBox1.Text() & "'AND'" & TextBox2.Text())
    sqlquery.CommandText = queryString
    sqlquery.ExecuteNonQuery()



End Sub

However I am getting the output to be: "SELECT QText FROM Question WHERE QModule = ''C1''OR''C2'' AND QRating BETWEEN '1'AND'2" and an error:

Syntax error (missing operator) in query expression 'QModule = ''C1''OR''C2'' AND QRating BETWEEN '1'AND'2'.

Also, what would be the best way of outputting all of the returned data in a numbered list, in a form that would be printable?

Chase Rocker · Accepted Answer · 2016-05-08 04:12:46Z

2

Why are you doing this For h = 0 To h = 6 instead of just For h = 0 To 6?

You don't need single quotes around "'OR'" just use " OR ".

And your SQL syntax is wrong. This QModule = ''C1''OR''C2'' either needs to be QModule = 'C1' OR QModule = 'C2' or a better way QModule IN ('C1','C2')

Assuming QRating is numeric, you don't need single quotes. This QRating BETWEEN '1'AND'2' should be QRating BETWEEN 1 AND 2.

Also you should look into using SQL parameters so you don't have to worry about quotes or escaping them if you have quotes in your data.

answered May 8, 2016 at 4:12

Chase Rocker

1,9082 gold badges13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trouble building an SQL Query from VB.NET checklistbox options

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related