Hi all! Okay i've been attempting to create this segment of code which is supposed to read from the database; based on search criteria entered, then load them into a form for viewing/editing.

I am able to read the concessionID from the database; however i am then unable to select the name after to insert it into the form.

Could someone point out where i am going wrong in my code and how i may go about fixing my error?

Private Sub btnCSViewCompany_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCSViewCompany.Click

Dim varCompanyID As String
        Dim da As SqlDataReader

        objCommand.Connection = objConnection

        If lstCSCompanyList.SelectedIndex = -1 Then
            MsgBox("Please select a company to view.")
        ElseIf lstCSCompanyList.SelectedIndex >= 0 Then
            Try
                objConnection.Open()
                Select Case companyType
                    Case "Concession"

                        objCommand.CommandText = "SELECT ConcessionID FROM tblConcessions WHERE ConcessionName='" & lstCSCompanyList.SelectedItem & "';"
                        da = objCommand.ExecuteReader
                        da.Read()
                        varCompanyID = (da("ConcessionID"))
                        MsgBox(varCompanyID)
                        'Using ID take column detail and place in relevant space
                        da.Close()
                        objCommand.CommandText = "SELECT ConcessionName FROM tblConcessions WHERE ConcessionID='" & varCompanyID & "';"
                        frmConcessions.txtPCompanyName.Text = objCommand.ExecuteNonQuery

                        frmConcessions.Show()
                        Me.Dispose()

                    Case "Garden Centre"
                        frmGardenCentre.Show()
                        'will perform similar to case "concessions"

                    Case "Third Party Company"
                        frmThirdPartyCompanies.Show()
                        'will perform similar to case "concessions"

                End Select
            Finally
                objConnection.Close()

            End Try
        End If

    End Sub

The error message that i am getting is:
IndexOutOfRangeException was unhandled
ConcessionName

i have tried an alternative method also by attempting to close and re-open the DataReader; however to no avail :(

ExecuteNonQuery is only for deleting, inserting, and updating tables.

Ahh okay; that makes much more sense! I have tried an alternative method (amazing what a break can do). Heres what i came up with and it works!:

Public Function DetailLookup(ByVal varColumnIn As String, ByVal varTableIn As String, ByVal varIDTypeIn As String, ByVal varCompanyIDIn As String)

        objCommand.Connection = objConnection

        Dim varCommand As String = "SELECT " & varColumnIn & " FROM " & varTableIn & " WHERE " & varIDTypeIn & "='" & varCompanyIDIn & "';"
        Dim da As SqlDataReader
        Dim Reading As String

        objConnection.Open()
        objCommand.CommandText = varCommand
        da = objCommand.ExecuteReader
        da.Read()
        Reading = (da(varColumnIn))
        objConnection.Close()

        Return Reading

    End Function
If lstCSCompanyList.SelectedIndex = -1 Then
            MsgBox("Please select a company to view.")
        ElseIf lstCSCompanyList.SelectedIndex >= 0 Then
            Try
                Select Case companyType
                    Case "Concession"

                        varCompanyKey = DetailLookup("ConcessionID", "tblConcessions", "ConcessionName", lstCSCompanyList.SelectedItem)

                        frmConcessions.txtPCompanyName.Text = DetailLookup("ConcessionName", "tblConcessions", "ConcessionID", varCompanyKey)

                        frmConcessions.txtPCompanyNumber.Text = DetailLookup("CompanyNumber", "tblConcessions", "ConcessionID", varCompanyKey)

Thanks very much for your reply yorro!
:)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.