Visual Basic ACCDB File Read for Cascading Combo Box

Question

So I've got an access database with three columns in: Manufacture, Fixture and Mode

I'm using the code below to create cascading combo boxes in my form.

Imports System.Data.OleDb


Public Class Add_Fixtures
   Dim con As New OleDbConnection
   Dim constring As String
   Dim cmd As New OleDbCommand
   Dim dr As OleDbDataReader
   Dim cmd1 As New OleDbCommand
   Dim dr1 As OleDbDataReader
   Dim cmd2 As New OleDbCommand
   Dim dr2 As OleDbDataReader

Private Sub Add_Fixtures_Load(sender As Object, e As EventArgs) Handles Me.Load
    constring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|/FixtureLibrary.accdb"
    With con
        .ConnectionString = constring
        .Open()
    End With

    With cmd
        .Connection = con
        .CommandType = CommandType.Text
        .CommandText = "SELECT DISTINCT Fixtures.Manufacture from Fixtures;"
    End With

    dr = cmd.ExecuteReader()

    While dr.Read
        cbManufacture.Items.Add(dr("Manufacture"))
    End While

    With cmd1
        .Connection = con
        .CommandType = CommandType.Text
        .CommandText = "SELECT  from Fixtures where [Manufacture]='" & Me.cbManufacture.Text & "';"
    End With

    dr1 = cmd1.ExecuteReader()

    While dr1.Read
        cbFixture.Items.Add(dr1("Fixture"))
    End While

    With cmd2
        .Connection = con
        .CommandType = CommandType.Text
        .CommandText = "SELECT DISTINCT Fixtures.Mode from Fixtures where [Fixture] ='" & Me.cbFixture.Text & "';"
    End With

    dr2 = cmd2.ExecuteReader()

    While dr2.Read
        cbMode.Items.Add(dr2("Mode"))
    End While
End Sub
End Class

But on this line: dr1 = cmd1.ExecuteReader()

I get this error

System.Data.OleDb.OleDbException: 'The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.'

Anyone have any ideas why or what is causing it?

Cheers

You have a typo. Running the debugger should tell you exactly what line it's on. — fbueckert, Feb 13 '19 at 17:42
The code is VB.NET but you tagged this as VBA. Are you sure about the tags? VB.NET and VBA are two wildly different languages that shares the first two letters, no more. — this, Feb 13 '19 at 17:53
Fixed the typo, but now not getting anything in combobox cbFixture or cbMode — Harry Bilney, Feb 13 '19 at 18:18
You should ask a new question for your new problem. If this one is solved, then you can either answer it yourself, or delete it if you feel it won't help others in the future. — fbueckert, Feb 13 '19 at 18:30

score 0 · Answer 1 · answered Feb 13 '19 at 19:19

0

You don't select any field(s), and property Text is only valid for the control having focus, so:

   .CommandText = "SELECT * from Fixtures where [Manufacture]='" & Me.cbManufacture.Value & "';"

answered Feb 13 '19 at 19:19

Gustav

53,498
7
29
55

That should be a parameter. – LarsTech Feb 20 '19 at 16:30

Visual Basic ACCDB File Read for Cascading Combo Box

1 Answers1