[RESOLVED] Getting from Access database info to put into combo box with filters

[MikeDelke]

OK sorry to be a pest but fairly new to Access. Rewriting a Visual Basic 6 program in Visual Studio 2017 and can not seem to figure out the correct syntax for the select statement. I need to fill a combo box from the database where there are (2) combo box's that I use the text from to filter the search. I show no error's in the VB code but when I run it I get the error message.....

"Syntax Error( Missing Operator) in Query expression 'clState='AK and WHERE CountyLine = 'First District."'

I am trying to fill a combo box that shows the connecting counties to the First District in AK. They are in a field called clCounty
I need both filters because in the lower 48 there a multiple states having counties with the same name, as in other States.

Here is my Code

  Private Sub GetMCLCounty()


        ' Dim Access As New DbControl
        'Dim mcall = lblCall.Text
        Dim ds As New DataSet
        Dim dt As New DataTable
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        '------------ FROM HERE THE NEXT LINES SET UP THE CONNECTION STRING FOR USER'S DATABASE------------------

        Dim strFilePrefix = "NetControl"
        Dim strFileSuffix = ".accdb"
        Dim databaseFile As String = "C:\RRLogger Data\" & strFilePrefix & strFileSuffix
        Dim conString = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source= " & databaseFile

        '--------------------------------------------------------------------------------------------------------------
        con.ConnectionString = conString
        cmbHCounty.Items.Clear()
        con.Open()

        da = New OleDbDataAdapter("SELECT clCounty FROM CountyLine WHERE clState = '" & cmbMState.Text & " And Where CountyLine = '" & cmbMCounty.Text & "'", con)


        da.Fill(dt)

        con.Close()
        'CLEar combobox
        cmbxMCLine.Items.Clear()

        For Each R As DataRow In dt.Rows
            cmbxMCLine.Items.Add(R("County"))
        Next

        'DISPLAY FIRST RECORD

        cmbxMCLine.Text = CType(dt.Rows(0).Item(0), String)


    End Sub

The (2) combo box's I am using for the filters do contain the correct info. This syntax for Access is a little confusing to me, PLEASE HELP !!!

[jmcilhinney]

You don't use the WHERE keyword more than once in a single query. If you have multiple criteria then you use AND and/or OR operators between them but you only use WHERE once. Get rid of that second WHERE keyword and you should be fine. That said, you really should learn to use parameters when inserting values into SQL code. You can follow the Blog link in my signature below and check out my post on Parameters In ADO.NET.

[OptionBase1]

da = New OleDbDataAdapter("SELECT clCounty FROM CountyLine WHERE clState = '" & cmbMState.Text & " And Where CountyLine = '" & cmbMCounty.Text & "'", con)

Aside from what jmcilhinney posted above, you are missing a closing single quote after the value of cmbMState.Text is inserted (bolded and excessively sized below):

da = New OleDbDataAdapter("SELECT clCounty FROM CountyLine WHERE clState = '" & cmbMState.Text & "' And CountyLine = '" & cmbMCounty.Text & "'", con)

[MikeDelke]

Thank you to both for the help. It works as needed now.

The Blog was interesting and I will study it some more to better understand Parameters.