Question Unable to display Item from Combo Box and List it in the ListBox

[maneeshmassey]

Dear All,

I have a very simple (???) application in which a user selects an item from a Combox, which loads with the names of districts at run time. The user is expected to select a district of his/her choice and click the "SELECT" button. Upon clicking the "Select" button, the PastorID's related to all pastors working in that district appear in the adjacent ListBox from where the user can select a particular PastorID with the help of the keyboard key and details such as the name of the Pastor, Sex, Phone, Address and E-mail will get displayed in 5 text boxes below the ListBox.

This seems to be a very simple question for all you genius's out there, but since I am just a beginner, this seems daunting to me. I have been working on this for the past 2 months yet without any luck.

I am using Visual Basic 2008 Express Edition as my front end and Microsoft Access 2007 as my back end. Here I use 2 Tables: DistrictINFO table and PastorINFO table from the database. The DistrictINFO table contains a list of all districts along with their DistrictID's and the PastorINFO table contains a list of all pastors, sex, phone number, address and email.

Given below is the code I am using for the Pastorinformatioform:

Imports System
Imports System.Data
Imports System.Data.OleDb


Public Class PastorInformationForm


Public accConnection As New OleDbConnection
Public strConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = C:\Users\Joy\Documents\DB2020.accdb"
Private PastorTextBox(5) As TextBox
Private PastorID() As String




Private Sub PastorInformationForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


accConnection = New OleDbConnection(strConnectionString)


Try
accConnection.Open()


Catch OleDbExceptionErr As OleDbException
MessageBox.Show(OleDbExceptionErr.Message, "Access Error")
Catch InvalidOperationExceptionErr As InvalidOperationException
MessageBox.Show(InvalidOperationExceptionErr.Message, "Access Error")
End Try




If accConnection.State <> ConnectionState.Open Then
MessageBox.Show("Database connection failed to open!")
Exit Sub
End If




txtFName.Clear()
txtLName.Clear()
txtSex.Clear()
txtPhone.Clear()
txtAddress.Clear()
txtEmail.Clear()


ComboDistrict.Items.Add("Select a District")
ComboDistrict.Items.Add("Agra")
ComboDistrict.Items.Add("Aligarh")
ComboDistrict.Items.Add("Allahabad")
ComboDistrict.Items.Add("Ambedkarnagar")
ComboDistrict.Items.Add("Auraiya")
ComboDistrict.Items.Add("Azamgarh")
ComboDistrict.Items.Add("Badaun")
ComboDistrict.Items.Add("Baghpat")
ComboDistrict.Items.Add("Bahraich")
ComboDistrict.Items.Add("Ballia")
ComboDistrict.Items.Add("Balrampur")
ComboDistrict.Items.Add("Banda")
ComboDistrict.Items.Add("Barabanki")
ComboDistrict.Items.Add("Bareilly")
ComboDistrict.Items.Add("Basti")
ComboDistrict.Items.Add("Bijnore")
ComboDistrict.Items.Add("Bulandshahr")
ComboDistrict.Items.Add("Chandauli")
ComboDistrict.Items.Add("Chitrakoot")
ComboDistrict.Items.Add("Deoria")
ComboDistrict.Items.Add("Etah")
ComboDistrict.Items.Add("Etawah")
ComboDistrict.Items.Add("Faizabad")
ComboDistrict.Items.Add("Farrukhabad")
ComboDistrict.Items.Add("Fatehpur")
ComboDistrict.Items.Add("Firozabad")
ComboDistrict.Items.Add("Gautam Budh Nagar")
ComboDistrict.Items.Add("Ghaziabad")
ComboDistrict.Items.Add("Ghazipur")
ComboDistrict.Items.Add("Gonda")
ComboDistrict.Items.Add("Gorakhpur")
ComboDistrict.Items.Add("Hamirpur")
ComboDistrict.Items.Add("Hardoi")
ComboDistrict.Items.Add("Hathras(Mahamayanagar)")
ComboDistrict.Items.Add("Jalaun")
ComboDistrict.Items.Add("Jaunpur(Orai)")
ComboDistrict.Items.Add("Jhansi")
ComboDistrict.Items.Add("Jyotiba Phule Nagar")
ComboDistrict.Items.Add("Kannauj")
ComboDistrict.Items.Add("Kanpur Nagar")
ComboDistrict.Items.Add("Kanpur Dehat")
ComboDistrict.Items.Add("Kanshi Ram Nagar")
ComboDistrict.Items.Add("Kaushambi")
ComboDistrict.Items.Add("Kushinagar")
ComboDistrict.Items.Add("Lakhimpur Kheri")
ComboDistrict.Items.Add("Lalitpur")
ComboDistrict.Items.Add("Lucknow")
ComboDistrict.Items.Add("Maharajganj")
ComboDistrict.Items.Add("Mahoba")
ComboDistrict.Items.Add("Mainpuri")
ComboDistrict.Items.Add("Mathura")
ComboDistrict.Items.Add("Mau")
ComboDistrict.Items.Add("Meerut")
ComboDistrict.Items.Add("Mirzapur")
ComboDistrict.Items.Add("Moradabad")
ComboDistrict.Items.Add("Muzaffarnagar")
ComboDistrict.Items.Add("Pilibhit")
ComboDistrict.Items.Add("Pratapgarh")
ComboDistrict.Items.Add("Rai Bareli")
ComboDistrict.Items.Add("Rampur")
ComboDistrict.Items.Add("Saharanpur")
ComboDistrict.Items.Add("Shahjahanpur")
ComboDistrict.Items.Add("Shravasti")
ComboDistrict.Items.Add("Sidhharthnagar")
ComboDistrict.Items.Add("Sitapur")
ComboDistrict.Items.Add("Sonbhadra")
ComboDistrict.Items.Add("Sant Kabir Nagar")
ComboDistrict.Items.Add("Sant Ravi Das Nagar(Bhadohi)")
ComboDistrict.Items.Add("Sultanpur")
ComboDistrict.Items.Add("Unnao")
ComboDistrict.Items.Add("Varanasi")


ComboDistrict.SelectedIndex = 0


End Sub


Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click




cmdModify.Enabled = False
cmdDelete.Enabled = False
cmdSelect.Enabled = False
cmdBack.Text = "Save"
cmdAdd.Enabled = False
ComboDistrict.Enabled = False
cmdSelect.Enabled = False
txtFName.ReadOnly = False
txtLName.ReadOnly = False
txtSex.ReadOnly = False
txtPhone.ReadOnly = False
txtAddress.ReadOnly = False
txtEmail.ReadOnly = False
End Sub


Private Sub cmdSelect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSelect.Click


Dim strConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Joy\Documents\DB2020.accdb"
Dim accConnecton As New OleDbConnection


accConnection = New OleDbConnection(strConnectionString)


Try
accConnection.Open()




Catch OleDbExceptionErr As OleDbException
MessageBox.Show(OleDbExceptionErr.Message, "Access Error")
Catch InvalidOperationExceptionErr As InvalidOperationException
MessageBox.Show(InvalidOperationExceptionErr.Message, "Access Error")
End Try


If accConnection.State <> ConnectionState.Open Then
MessageBox.Show("Database connection failed to open!")
Exit Sub
End If




Dim strDistrict As String = "SELECT PastorID FROM DistrictINFO WHERE DistrictName = @DistrictName"
Dim strPastor As String = "SELECT PastorID, FName, LName, Sex, Phone, Address, E-Mail FROM PastorINFO WHERE PastorID = @pID"
Dim DistrictINFOTableAdapter, PastorINFOTableAdapter As New OleDbDataAdapter
Dim accDistrictCommand, accPastorCommand As New OleDbCommand
Dim accDistrictDataTable, accPastorDataTable As New DataTable
Dim strDistrictID As String
Dim rowDistrictID As DataRow






accDistrictCommand.Connection = accConnection
accDistrictCommand.CommandType = CommandType.Text
accDistrictCommand.CommandText = strDistrict
accDistrictCommand.Parameters.Add("@DistrictName", OleDbType.Char).Value = ComboDistrict.Text


DistrictINFOTableAdapter.SelectCommand = accDistrictCommand
DistrictINFOTableAdapter.Fill(accDistrictDataTable)





If accDistrictDataTable.Rows.Count = 0 Then


MessageBox.Show("No matched PastorID found!")


Exit Sub


End If


rowDistrictID = accDistrictDataTable.Rows.Item(0)
strDistrictID = rowDistrictID(0)




accPastorCommand.Connection = accConnection
accPastorCommand.CommandType = CommandType.Text
accPastorCommand.CommandText = strPastor


accPastorCommand.Parameters.Add("@pID", OleDbType.Char).Value = strDistrictID
PastorINFOTableAdapter.SelectCommand = accPastorCommand
PastorINFOTableAdapter.Fill(accPastorDataTable)







If accPastorDataTable.Rows.Count > 0 Then
Call FillPastorINFO(accPastorDataTable)
Else
MessageBox.Show("No matched PastorID found!")
End If




accDistrictDataTable.Dispose()
accDistrictDataTable = Nothing


accPastorDataTable.Dispose()
accPastorDataTable = Nothing


DistrictINFOTableAdapter.Dispose()
DistrictINFOTableAdapter = Nothing
PastorINFOTableAdapter.Dispose()
PastorINFOTableAdapter = Nothing


accDistrictCommand.Dispose()
accDistrictCommand = Nothing


accPastorCommand.Dispose()
accDistrictCommand = Nothing


PastorIDList.SelectedIndex = 0


End Sub


Private Sub FillPastorINFO(ByVal PastorINFO As DataTable)
Dim row As DataRow
Dim pos As Integer
ReDim PastorID(PastorINFO.Rows.Count)


PastorIDList.Items.Clear()


For Each row In PastorINFO.Rows
PastorIDList.Items.Add(row(0))
PastorID(pos) = row(0)
pos = pos + 1
Next
End Sub


Private Sub cmdBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdBack.Click
Me.Close()
End Sub


End Class

The problem is I am receiving an "ERROR: OleDbException was unhandled: No value given for one or more required parameters" at the line highlighted in red above in the code. I do not understand why I am getting this error which is quite annoying as I cannot proceed in the application developing development.

Can some wise guy help to throw light on this simple question ???
Appreciate all help !!!
Regards,
Maneesh

 

[Estuardo]

G'd morning maneeshmassey,
Your parameter and your field must be the same type. looks like your field is numeric, must of the times ID's are numbers, mainly in MS Access:

"WHERE PastorID = @pID"

and your parameter is Char type
"Parameters.Add("@pID", OleDbType.Char)

Also a few things i think you may want change:

Public accConnection As New OleDbConnection
Public strConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = C:\Users\Joy\Documents\DB2020.accdb"
Private PastorTextBox(5) As TextBox
Private PastorID() As String

Declare your variables Private, I don't see (in your code) any need to Declare them as public.
Take off the New keyword from your Connection if you don't need to initialize it.
Use the New keyword only once if you do not plan to destroy your connection. This expression New OleDbConnection is repeated at least 3 times, and the worst part is that is declared at module level and Procedure Level. You don't need to do that. Open and close the same connection as many times as you need but don't destroy or recreate it. You loose performance creating the same connection over and over again and let ALL of them open. You'd been very careful to check for the connection state when open, but never to close it.
If you don't close your connections, remember that Access will not close the db automatically and will let hanging a ldb file.
Store your districts in a table. If you ever need to add or delete a district you will have to go to your code and change it... You know what that means don't you?
Consider to get rid of the listbox and replace it with listview. You won't need to build arrays to keep track of indexes.

Here are some changes in your code

    Private accConnection As OleDbConnection
    Private strConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = C:\Users\Joy\Documents\DB2020.accdb"
    Private PastorTextBox(5) As TextBox
    Private PastorID() As String


  Private Sub PastorInformationForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
        accConnection = New OleDbConnection(strConnectionString) 'The only one time you need to do this
          '
          '
          '
          '     Your code here
          '
          '
          '

  End Sub

 Private Sub cmdSelect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelect.Click
        Dim DistrictINFOTableAdapter As New OleDbDataAdapter
        Dim PastorINFOTableAdapter As New OleDbDataAdapter
        Dim accDistrictCommand As New OleDbCommand
        Dim accPastorCommand As New OleDbCommand
        Dim accDistrictDataTable As New DataTable
        Dim accPastorDataTable As New DataTable
        Dim strDistrictID As String
        Dim rowDistrictID As DataRow
        Dim strSqlDistrict As String = "SELECT PastorID FROM DistrictINFO WHERE DistrictName=@DistrictName"
        Dim strSqlPastor As String = "SELECT PastorID, FName, LName, Sex, Phone, Address, E-Mail FROM PastorINFO WHERE PastorID=@pID"

        Try
            If Not accConnection.State = ConnectionState.Open Then accConnection.Open()

        Catch OleDbExceptionErr As OleDbException
            MessageBox.Show(OleDbExceptionErr.Message, "Access Error")
        Catch InvalidOperationExceptionErr As InvalidOperationException
            MessageBox.Show(InvalidOperationExceptionErr.Message, "Access Error")
        End Try

        With accDistrictCommand
            .Connection = accConnection
            .CommandType = CommandType.Text
            .CommandText = strSqlDistrict
            .Parameters.Add("@DistrictName", OleDbType.VarWChar).Value = Me.ComboDistrict.Text
        End With

        DistrictINFOTableAdapter.SelectCommand = accPastorCommand
        DistrictINFOTableAdapter.Fill(accDistrictDataTable)

        Try
            If accDistrictDataTable.Rows.Count = 0 Then
                MessageBox.Show("No matched PastorID found!")
                Exit Sub
            End If


            rowDistrictID = accDistrictDataTable.Rows.Item(0)
            strDistrictID = rowDistrictID.Item(0).ToString

            With accPastorCommand
                .Connection = accConnection
                .CommandType = CommandType.Text
                .CommandText = strSqlPastor
                .Parameters.Add("@pID", OleDbType.Integer).Value = CInt(strDistrictID) 'You must verify the parameter and field type
            End With



            PastorINFOTableAdapter.SelectCommand = accPastorCommand
            PastorINFOTableAdapter.Fill(accPastorDataTable)

            PastorIDList.SelectedIndex = 0

            If accPastorDataTable.Rows.Count > 0 Then
                Call FillPastorINFO(accPastorDataTable)
            Else
                MessageBox.Show("No matched PastorID found!")
            End If

        Catch ex1 As OleDbException
            MsgBox(ex1.Message)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            accDistrictDataTable.Dispose()
            accDistrictDataTable = Nothing
            accPastorDataTable.Dispose()
            accPastorDataTable = Nothing
            DistrictINFOTableAdapter.Dispose()
            DistrictINFOTableAdapter = Nothing
            PastorINFOTableAdapter.Dispose()
            PastorINFOTableAdapter = Nothing
            accDistrictCommand.Dispose()
            accDistrictCommand = Nothing
            accPastorCommand.Dispose()
            accDistrictCommand = Nothing
        End Try

    End Sub

G'd luck