Reading from database...

[Kielo]

Trying to make a program, but in one of my forms...

I am writing to a table, one of the things in this is an auto number...

SpecSales Table , working fine, gets updated... and allocates an auto number.

I'm reading from Customer Table & Eye test Table (using inner join)

When the SpecSales Table is updated, I need information from Customer Table, Eye Test Table & SpecSales Table to be inserted into a text file, I currently have the information from the Customer Table & Eye Test Table working fine, but I'm lost on how to get the SpecSales Table information into it, all I need is the auto number from it (SpecSales ID)

Code is here to get an idea of what im doing...

Public Class SpectaclesSelection
    Private Sub SpectaclesSelection_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 CustomerTable" '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
                    ListBoxOutput.Items.Clear()
                    While dr.Read
                        If Not IsDBNull(dr.Item("Surname")) Then  'INSERTS INFORMATION INTO
                            TitleString = dr.Item("Surname")    '       LISTBOX
                            ListBoxOutput.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 ListBoxOutput_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBoxOutput.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)
        AddressTextBox.Text = ListBoxOutput.Text
        SQLString = "SELECT * FROM CustomerTable "
        SQLString += "INNER JOIN EyeTestTable ON CustomerTable.CustomerID = EyeTestTable.CustomerID "
        SQLString += "Where '" & AddressTextBox.Text & "'"
        SQLString += "= Surname"
        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("Street")) Then
                        AddressTextBox.Text = dr.Item("Street").ToString
                    End If
                    If Not IsDBNull(dr.Item("Town")) Then
                        Address2TextBox.Text = dr.Item("Town").ToString
                    End If
                    If Not IsDBNull(dr.Item("County")) Then
                        Address3TextBox.Text = dr.Item("County").ToString
                    End If
                    If Not IsDBNull(dr.Item("RightEyeMeasurement")) Then
                        RightEyeTextBox.Text = dr.Item("RightEyeMeasurement").ToString
                    End If
                    If Not IsDBNull(dr.Item("LeftEyeMeasurement")) Then
                        LeftEyeTextBox.Text = dr.Item("LeftEyeMeasurement").ToString
                    End If
                    If Not IsDBNull(dr.Item("DateOfTest")) Then
                        DateTextBox.Text = dr.Item("DateOfTest").ToString
                    End If
                End If
            End If
        Catch ex As Exception
        End Try
 
 
    End Sub
 
    Private Sub ListBoxOutput_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBoxOutput.Click
 
        Button2.Enabled = True
        GroupBox3.Enabled = False
 
 
    End Sub
 
    Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub
 
    Private Sub ExitToolStripMenuItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
        Me.Close()
 
    End Sub
 
    Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
 
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Close()
 
    End Sub
 
    Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter
 
    End Sub
 
    Private Sub GroupBox2_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox2.Enter
 
    End Sub
    Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Address2TextBox.TextChanged
 
    End Sub
 
    Private Sub OrderButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OrderButton.Click
        Dim myValue As Integer
        Dim myValue1 As Integer
        Dim myValue2 As Integer
        Dim myValue3 As Integer
        Dim totalCost As Integer
        Dim deposit As Integer
 
        myValue = 50
 
        Select Case GPTextBox.Text
            Case "Glass"
                myValue1 = 30
            Case "Plastic"
                myValue1 = 15
        End Select
 
        Select Case ScratchTextBox.Text
            Case "Yes"
                myValue2 = 10
            Case "No"
                myValue2 = 0
        End Select
 
        Select Case UVTextBox.Text
            Case "Yes"
                myValue3 = 15
            Case "No"
                myValue3 = 0
        End Select
 
 
 
        totalCost = myValue + myValue1 + myValue2 + myValue3
        deposit = totalCost * 0.2
 
        TextBox1.Text = totalCost
        TextBox2.Text = deposit
        TextBox3.Text = totalCost - deposit
 
        Button3.Enabled = True
        Button4.Enabled = True
        myAddNew(8) 'CALLS ADDNEW METHOD WHEN ADD BUTTON IS CLICKED
 
 
 
    End Sub
    Private Sub myAddNew(ByVal CusRef As Integer)
        Dim ConnectionString As String
        Dim SQLString As String
        Dim whichButtonDialogResult As DialogResult
        Dim dbCommand As System.Data.OleDb.OleDbCommand
        Dim Connection As System.Data.OleDb.OleDbConnection
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
        ConnectionString += "Source=" & "Opticians.accdb "
        Connection = New System.Data.OleDb.OleDbConnection(ConnectionString)
        SQLString = "INSERT INTO SpecSalesTable (FrameID, DateOfSale, GlassOrPlastic, ScratchCoating, UVFilter, TotalCost, DepositPaid) "
        SQLString += "Values ('" & StockTextBox.Text & "',#" & Date.Today & "#,'" & GPTextBox.Text & "','" & ScratchTextBox.Text & "','" & UVTextBox.Text & "','" & TextBox1.Text & "','" & TextBox2.Text & "')"
        whichButtonDialogResult = MessageBox.Show("Are You Sure You Want To Place This Order?", "Add Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If whichButtonDialogResult = DialogResult.Yes Then
            Connection.Open()
            If CBool(ConnectionState.Open) Then
                dbCommand = New System.Data.OleDb.OleDbCommand(SQLString, Connection)
                Try
                    dbCommand.ExecuteNonQuery()
                    MessageBox.Show("Order placed, SpecSalesTable Updated! ")
                Catch ex As Exception
                    MessageBox.Show(" Error placing order... ")    'IF ERROR DISPLAYS MESSAGE
                End Try
 
            End If
 
        End If
 
        Connection.Close()
    End Sub
 
    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
 
    End Sub
 
    Private Sub IDTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
 
    End Sub
 
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
 
        GroupBox3.Enabled = True
 
 
    End Sub
 
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim txtFile As String = IO.File.ReadAllText("template.txt")
        txtFile = txtFile.Replace("[date]", "07/03/2010")
        txtFile = txtFile.Replace("[left eye]", LeftEyeTextBox.Text)
        txtFile = txtFile.Replace("[right eye]", RightEyeTextBox.Text)
        txtFile = txtFile.Replace("[material]", GPTextBox.Text)
        txtFile = txtFile.Replace("[UVF]", UVTextBox.Text)
        txtFile = txtFile.Replace("[Scratch]", ScratchTextBox.Text)
        
        IO.File.WriteAllText("NewOrder.txt", txtFile)
        Process.Start("NewOrder.txt")
End Class

*had to leave some out

[Kielo]

Bump!

[GaryMazzone]

How are SpecSales and Customer tables realted (is there a field that is common in both) or does the relationship to SpecSales come from EyeTests?


What you do is add another join (probably a Left Outer) to get the information