Displaying database information problem...

[Kielo]

Public Class ReceiptSpectacles
 
    Private Sub ReceiptSpectacles_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DisplayList(5) 'LOADS DISPLAYLIST METHOD
    End Sub
 
    Private Sub DisplayList(ByVal CusRef As Integer)
        Dim ConnectionString As String
        Dim SQLString As String
        Dim TitleString As String
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim dr As System.Data.OleDb.OleDbDataReader
        Dim cmd As System.Data.OleDb.OleDbCommand
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data "
        ConnectionString += "Source=" & "Opticians.accdb "
        conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
        SQLString = "SELECT * FROM SpecSalesTable" 'SELECTS ALL INFORMATION
        Try                                     '    FROM DATABASE
            conn.Open()
            If ConnectionState.Open Then
                cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
                dr = cmd.ExecuteReader()
                If dr.HasRows Then
                    FramesListBox.Items.Clear()
                    While dr.Read
                        If Not IsDBNull(dr.Item("FrameID")) Then  'INSERTS INFORMATION INTO
                            TitleString = dr.Item("FrameID")      '       LISTBOX
                            FramesListBox.Items.Add(TitleString)
                        End If
                    End While
                End If
                dr.Close()
            End If
        Catch
            MessageBox.Show("Error accessing database")     'IF ERROR ENCOUNTERED SHOWS MESSAGE
        End Try
        conn.Close()
    End Sub
 
    Private Sub FramesListBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FramesListBox.SelectedIndexChanged
        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 += "= FrameID"
        Try
            conn.Open()
            If ConnectionState.Open.ToString = "Open" Then
                cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
                dr = cmd.ExecuteReader()
                [I]If dr.HasRows Then[/I]
                    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
    End Sub

Right, there's my code...

A listbox, 3 text boxes.

The listbox should display the FrameID which is located in the SpecSalesTable

Then I want, the surname, firstname from the Customer table to be displayed, provided the customer table. customerID = specsalestable.customerID

Any ideas why this isn't working?

I italic'd a part
"If dr.HasRows Then"

Thats showing up as not having rows, so it's not even trying to do the add to textbox part...


Thanks

[GaryMazzone]

I see a test to see if the connection is Open. If open you do something.... OK where is it opened?

[Kielo]

Sorry I'm confused... where is the connection opened?

I'm lost

[HongKongCV]

Okay, consider this section of your code:

Try                          '    FROM DATABASE
conn.Open()
If ConnectionState.Open Then

You're opening the connection, okay, but the if-statement will always evaluate to true. Try this instead:

If conn.State = ConnectionState.Open Then

I would, however, expect a failed connection to raise an exception...

Also, in your FramesListBox_SelectedIndexChanged handler, I don't see where you close the connection.

[Kielo]

Alright, changed that... still didn't fix anything... (well make it work)

I just need surname, firstname from the customer table but not getting anything =/

[GaryMazzone]

How about adding a messagebox to the catch statement here and see if an error message pops out?

Try
            conn.Open()
            If ConnectionState.Open.ToString = "Open" Then
                cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
                dr = cmd.ExecuteReader()
                [i]If dr.HasRows Then[/i]
                    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
             MessageBox.Show(ex.Message)
        End Try

[Kielo]

Type mismatch in expression

[Kielo]

Strange, I can't see the mismatch... there just names... is it looking for integers I wonder?

How do I check that?

[GaryMazzone]

The problem is in you SQL Statement.

Is FrameId a string or Number?

[Kielo]

FrameID = Text field in Access...


There is a number in there, but im not saving it as integer or anything, just as text.

SQLString += "INNER JOIN CustomerTable ON SpecSalesTable.CustomerID = CustomerTable.CustomerID "

CustomerTable.CustomerID = Primary key in that table...

How do I resolve that? Assuming thats the problem?

[GaryMazzone]

Do you know how to set degubing on? Can you get the actual SQL statement that is generated and passed to the database and post it here. Also the table structure of all the tables involved (espically the columns in the Joins and the Where clasuse)

[Kielo]

I don't know how to set debugging on... how do I got about doing that?

Thanks dude

[GaryMazzone]

Place a break point on a line. The process will stop when the break point is reached and you can step though the code. You can see the value of different variables.

You need to read and understand debuging before you get much futher in programing.


Start here:
http://msdn.microsoft.com/en-us/library/sc65sadd.aspx

[Kielo]

Ah yeah, I've done that quite a few times... Just wan't fully sure if thats what you meant...

Thanks