dcsimg
Results 1 to 4 of 4

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

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    24

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

    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
    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 !!!

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,775

    Re: Getting from Access database info to put into combo box with filters

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3
    Hyperactive Member
    Join Date
    Nov 2017
    Posts
    398

    Re: Getting from Access database info to put into combo box with filters

    Code:
    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):

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

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    24

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width