So I have a program that is supposed to support a small business. I have the work completed and the professor said it was correct but I wanted to go do a little extra.
Basically, on the Order screen, you begin to type the name of a customer/supplier/employee into a text box and a list of names that fit the criteria pops up in a list box (lbMatchingAccounts). When you select it, you can hit enter new order and be transported to the Order Details screen to enter the new order.
What I wanted to do was to create a new list box (ListBoxOrderId). What I want is to be able to select a person from lbMatchingAccounts and have all their Orders populate in ListBoxOrderId.
The Order information is in an Orders table.
Can anyone help me with where to start with this?
Here's the code I have so far (I took out my attempts with the above problem since none of them worked and the program would crash). Also I'm using Visual Basic 2010.
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Configuration
Imports System.Data.SqlServerCe
Public Class frmNewOrder
Dim Cnxn As SqlCeConnection
Dim TheCommand As SqlCeCommand
Dim SQLStmt As String
Private Sub frmNewOrder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
lblAdvice.Text = "Choose the type of order and the date, then click Choose Account..."
lbMatchingAccounts.DisplayMember = "DisplayValue"
lbMatchingAccounts.ValueMember = "IdValue"
txtAccountSearch.Text = ""
Me.AcceptButton = btnChooseAccount
gbWho.Enabled = False
If (Not LoggedInUser.Purview = "Owner" And Not LoggedInUser.Purview = "Manager") Then
rbPurchase.Enabled = False
rbWork.Enabled = False
End If
End Sub
Private Sub rbSales_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbSales.CheckedChanged
If rbSales.Checked Then btnChooseAccount.Text = "Choose Customer"
End Sub
Private Sub rbPurchase_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbPurchase.CheckedChanged
If rbPurchase.Checked Then btnChooseAccount.Text = "Choose Supplier"
End Sub
Private Sub rbWork_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbWork.CheckedChanged
If rbWork.Checked Then btnChooseAccount.Text = "Choose Employee"
End Sub
Private Sub btnChooseAccount_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChooseAccount.Click
gbOrderType.Enabled = False
dpDate.Enabled = False
btnChooseAccount.Enabled = False
gbWho.Enabled = True
btnStartNewOrder.Enabled = False
Me.AcceptButton = btnStartNewOrder
txtAccountSearch.Focus()
End Sub
Private Sub txtAccountSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAccountSearch.TextChanged
If txtAccountSearch.Text = "" Then
lblAdvice.Text = "Enter characters from the last name to search for a record. Click the New button to add a new account record."
Return
End If
Dim SQLStmt, WhereNoun, FirstName, AccountName As String
Dim DisplayedInList As Integer
Cnxn = New SqlCeConnection(My.Settings.Database1ConnectionString)
'Debug.Write(vbCrLf & "Before Cnxn.Open CnxnString=" & CnxnString & vbCrLf)
Cnxn.Open()
If Cnxn.State <> ConnectionState.Open Then
MsgBox("Unable to connect to the database...", MsgBoxStyle.Critical)
End
End If
If rbSales.Checked Then
SQLStmt = String.Format( _
"SELECT LastName, FirstName, Id as AccountId from BEs WHERE LastName like '{0}%' ORDER BY LastName, FirstName", _
txtAccountSearch.Text)
Else
If rbPurchase.Checked Then
WhereNoun = "Supplier"
ElseIf rbWork.Checked Then
WhereNoun = "Employee"
Else
MsgBox("Somehow we got here without a radio button clicked")
End
End If
SQLStmt = String.Format( _
"SELECT LastName, FirstName, Id as AccountId from BEs WHERE LastName like '{0}%' ORDER BY LastName, FirstName", _
txtAccountSearch.Text)
End If
Dim cmdSearch As New SqlCeCommand(SQLStmt, Cnxn)
Dim Matches As SqlCeDataReader = cmdSearch.ExecuteReader
If Not Matches.Read Then
txtAccountSearch.Focus()
lblAdvice.Text = "No matches found for '" & txtAccountSearch.Text & "'..."
btnStartNewOrder.Enabled = False
Else
Dim MatchingRecords As New ArrayList
DisplayedInList = 0
Do
FirstName = Matches.Item("FirstName") & ""
AccountName = Matches.Item("LastName")
If FirstName <> "" Then AccountName &= ", " & FirstName
MatchingRecords.Add(New ListItem(AccountName, Str(Matches.Item("AccountId"))))
DisplayedInList += 1
Loop Until (DisplayedInList > 50) Or Not Matches.Read
lbMatchingAccounts.DataSource = MatchingRecords
lblAdvice.Text = "Enter a few characters from a word in the title to search. Use the tab and arrow keys to hilite the desired item..."
btnStartNewOrder.Enabled = True
lbMatchingAccounts.Focus()
End If
Cnxn.Close()
txtAccountSearch.Focus()
End Sub
Private Sub lbMatchingAccounts_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbMatchingAccounts.SelectedIndexChanged
Dim SQLStmt As String
SQLStmt = "select * from BEs where Id=" & lbMatchingAccounts.SelectedItem.IdValue
Debug.Write("SQLStmt=" & SQLStmt & vbCrLf)
Cnxn = New SqlCeConnection(My.Settings.Database1ConnectionString)
Cnxn.Open()
Dim cmdSelectedRecord As New SqlCeCommand(SQLStmt, Cnxn)
Dim SelectedRecord As SqlCeDataReader = cmdSelectedRecord.ExecuteReader
If Not SelectedRecord.Read Then
lblAdvice.Text = "No record found for '" & lbMatchingAccounts.SelectedValue & "'..."
Else
lblAdvice.Text = "Hit enter to use the highlighted account, or start another search..."
End If
SelectedRecord.Close()
Cnxn.Close()
End Sub
Private Sub btnStartNewOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStartNewOrder.Click
Dim AccountId, OrderType, OrderId As String
Dim CrDr As Integer
AccountId = lbMatchingAccounts.SelectedItem.IdValue
Dim SQLStmt As String
Dim UpdateOK As Integer
Cnxn = New SqlCeConnection(My.Settings.Database1ConnectionString)
Cnxn.Open()
OrderType = ""
If rbSales.Checked Then
OrderType = "Sales"
CrDr = -1
ElseIf rbPurchase.Checked Then
OrderType = "Purchase"
CrDr = 1
ElseIf rbWork.Checked Then
OrderType = "Work"
CrDr = 1
End If
SQLStmt = String.Format("insert into Orders (Date, BEId, Type, DebitCredit, EnteredBy) values ('{0}', {1}, '{2}', {3}, {4})", _
dpDate.Value, AccountId, OrderType, CrDr, LoggedInUser.AccountId)
Dim UpdateCommand As New SqlCeCommand(SQLStmt, Cnxn)
Debug.Write("UpdateSQLStmt=" & SQLStmt & vbCrLf)
Try
UpdateOK = UpdateCommand.ExecuteNonQuery()
Catch ExMsg As Exception
MsgBox("Exception at Insert:" & ExMsg.Message)
End
End Try
SQLStmt = "select max(Id) as Id from Orders"
Dim cmdCurrentOrderId As New SqlCeCommand(SQLStmt, Cnxn)
Dim CurrentOrderId As SqlCeDataReader = cmdCurrentOrderId.ExecuteReader
If Not CurrentOrderId.Read Then
MsgBox("No record found for '" & lbMatchingAccounts.SelectedValue & "'...")
End
End If
OrderId = CurrentOrderId.Item("Id")
LoggedInUser.CurrentOrderId = OrderId
LoggedInUser.CurrentOrderType = OrderType
LoggedInUser.CurrentOrderCrDr = CrDr
Cnxn.Close()
Dim frmDetails As frmDetails
frmDetails = New frmDetails
frmDetails.Show()
Me.Hide()
End Sub
Private Sub btnOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Private Sub lbOrderId_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
End Class