Solved

Query combo box from another combo box result in vb express

Posted on 2014-07-23
2
246 Views
Last Modified: 2014-07-25
Trying to query a combobox from another combobox result in vb express.  Getting an error near "=" on the select ProductCode query - makes no sense to me as i can't see any obvious error.  

 Private Sub GetMachine()
        'Query product code table
        If SQL.SQLDataSet IsNot Nothing Then
            SQL.SQLDataSet.Clear()
        End If
        cboMachine.Items.Clear() 'empty combo box
        SQL.RunQuery("SELECT Machine FROM tbl_MachineList")
        MsgBox(SQL.recordcount)
        If SQL.recordcount > 0 Then
            For Each r As DataRow In SQL.SQLDataSet.Tables(0).Rows
                cboMachine.Items.Add(r("Machine")) 'machine column
            Next
            '      cboMachine.SelectedIndex = 0 'set combo box on 1st record
        ElseIf SQL.Exception <> "" Then
            MsgBox(SQL.Exception)
        End If
    End Sub

    Private Sub GetProductCode(Machine As String)
        If SQL.SQLDataSet IsNot Nothing Then
            SQL.SQLDataSet.Clear()
        End If
        'Run query
        SQL.RunQuery("SELECT ProductCode FROM tbl_ProductStandards" & _
>>                               "WHERE Machine = '" & cboMachine.Text & "' ")                      << This line
        If SQL.recordcount > 0 Then
            For Each r As DataRow In SQL.SQLDataSet.Tables(0).Rows
                cboProductCode.Items.Add(r("ProductCode")) 'product code column
            Next
        ElseIf SQL.Exception <> "" Then
            MsgBox(SQL.Exception)
        End If
    End Sub

    Private Sub cboMachine_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cboMachine.SelectedIndexChanged
        GetProductCode(cboMachine.Text)
    End Sub
0
Comment
Question by:SweetingA
2 Comments
 

Author Comment

by:SweetingA
ID: 40215855
OK sorted it out somehow....

    Private Sub GetMachine()
        'Query product code table
        If SQL.SQLDataSet IsNot Nothing Then
            SQL.SQLDataSet.Clear()
        End If
        cboMachine.Items.Clear() 'empty combo box
        SQL.RunQuery("SELECT Machine FROM tbl_MachineList")
        MsgBox(SQL.recordcount)
        If SQL.recordcount > 0 Then
            For Each r As DataRow In SQL.SQLDataSet.Tables(0).Rows
                cboMachine.Items.Add(r("Machine")) 'machine column
            Next
            '      cboMachine.SelectedIndex = 0 'set combo box on 1st record
        ElseIf SQL.Exception <> "" Then
            MsgBox(SQL.Exception)
        End If
    End Sub

    Private Sub GetProductCode()
        If SQL.SQLDataSet IsNot Nothing Then
            SQL.SQLDataSet.Clear()
        End If
        cboProductCode.Items.Clear() 'empty combo box
        SQL.RunQuery("SELECT ProductCode FROM tbl_ProductStandards WHERE Machine ='" & cboMachine.Text & "' ")
        If SQL.recordcount > 0 Then
            For Each r As DataRow In SQL.SQLDataSet.Tables(0).Rows
                cboProductCode.Items.Add(r("ProductCode")) 'product code column
            Next
        ElseIf SQL.Exception <> "" Then
            MsgBox(SQL.Exception)
        End If
    End Sub

    Private Sub cboMachine_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cboMachine.SelectedIndexChanged
        GetProductCode()
    End Sub
0
 
LVL 15

Accepted Solution

by:
JimFive earned 500 total points
ID: 40217095
The issue was that you didn't have a space before the WHERE.  I always like to put my SQL statements into a string variable and then echo the variable to make sure that I'm getting the statement that I expect.  Missing spaces and quotes are usual culprits.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now