General program help

[Kielo]

I have a program, for an opticians...

I need to make 4 working screens.. I can order spectacles, this works...

I choose a customer from CustomerTable
Adds a record to a SpecSalesTable, and an auto key is allocated to each sale. (SpecSalesID)


In another screen, I need to display all the frames which have been ordered.
These frames are in the FrameID - SpecSalesTable

When I click the frameID in the listbox, I need the SpecSalesID to be loaded corrosponding to the frameID... which is in a different table, so Inner join is used, but none of mine will work at the moment...

Some code:

Dim ConnectionString As String
        Dim SQLString As String
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim dr As System.Data.OleDb.OleDbDataReader
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
        ConnectionString += "Source=" & "Opticians.accdb "
        conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
        TextBox1.Text = FramesListBox.Text
        SQLString = "SELECT * FROM SpecSalesTable "
        SQLString += "INNER JOIN CustomerTable ON SpecSalesTable.CustomerID = CustomerTable.CustomerID "
        SQLString += "Where '" & TextBox1.Text & "'"
        SQLString += "= SpecSalesTable.FrameID"
 
        'frame id = stock id
        ' spec sales table
        ' stock table
        Try
            conn.Open()
            If ConnectionState.Open.ToString = "Open" Then
                cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
                dr = cmd.ExecuteReader()
                If dr.HasRows Then
                    dr.Read()
                    If Not IsDBNull(dr.Item("FrameID")) Then
                        TextBox1.Text = dr.Item("FrameID").ToString
                    End If
                    If Not IsDBNull(dr.Item("CustomerID")) Then
                        TextBox2.Text = dr.Item("CustomerID").ToString
                    End If
                    If Not IsDBNull(dr.Item("Surname")) Then
                        TextBox3.Text = dr.Item("Surname").ToString
                    End If
                    If Not IsDBNull(dr.Item("Firstname")) Then
                        TextBox3.Text = dr.Item("Firstname").ToString
                    End If
 
                End If
            End If
        Catch ex As Exception
        End Try

[cicatrix]

TextBox1.Text = FramesListBox.SelectedItem.ToString

But I don't really see any reason why using TextBox1 at all. This can be accomplished simply by:

SQLString = "SELECT * FROM SpecSalesTable "
SQLString += "INNER JOIN CustomerTable ON SpecSalesTable.CustomerID = CustomerTable.CustomerID "
SQLString += "Where '" & FramesListBox.SelectedItem.ToString & "'"
SQLString += "= SpecSalesTable.FrameID"

You only need to check whether your FramesListBox really has some selected item (check whether its .SelectedIndex <> -1)

[Kielo]

Thanks for the reply... doesn't seem to do anything... (not putting data into the textboxes...)

[cicatrix]

I suspect that your SQL query does not return anything. use this code before Try:

Debug.WriteLine(SQLString)

Does your SQLString appears what it should be?

You have to select something in the FramesListBox, by the way.

[Kielo]

Whereabouts should I put the debug thing in...

Yeah I'm selecting the items Sorry about the noob questions :P lol

[cicatrix]

Before Try keyword

Debug.WriteLine(SQLString) ' <- HERE
Stop
Try
            conn.Open()

Also, read this very carefully:
http://www.homeandlearn.co.uk/net/nets5p6.html