Problem with my combo box and access database + i'm a newbie

[steplar]

Hello,

I'm a newbie in vb :D however with all the information and help this forum had provided i reach this far with my code BUT i can't proceed anymore.

I'm trying to make a small software which will help me in my work. I can't get it to work and with so little experience in this field i'm so lost. could you please tell me what i did wrong here.

Imports System.Data.OleDb
Public Class Form2
    '+++++++++++++++++++++++++++++++++++++
    Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:/mydata.mdb")

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT EmployeeID,FirstName FROM Employees ORDER BY FirstName", con)
        Dim cmd2 As OleDbCommand = New OleDbCommand("SELECT ProductID, ProductName FROM Products", con)

        Dim ds As New DataSet
        Dim da As New OleDbDataAdapter

        Dim ds2 As New DataSet
        Dim da2 As New OleDbDataAdapter

        con.Open()

        ds = New DataSet()
        da = New OleDbDataAdapter()

        da.SelectCommand = cmd

        da.Fill(ds, "employee")

        With cmbdscName
            .DataSource = ds.Tables("employee")
            .DisplayMember = "FirstName"
            .ValueMember = "EmployeeID"
            .SelectedIndex = 0
        End With

        ds2 = New DataSet()
        da2 = New OleDbDataAdapter()

        da2.SelectCommand = cmd2

        da2.Fill(ds2, "Product")

        With cmbProduct
            .DataSource = ds2.Tables("product")
            .DisplayMember = "ProductName"
            .ValueMember = "ProductID"
            .SelectedIndex = 0

        End With

        con.Close()
        con = Nothing

    End Sub

'now what i want to do is to filter the next combo box (cmbsubp) by the value selected in the first combo box (cmbProduct)
    Private Sub cmbProduct_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbProduct.SelectedIndexChanged

        Dim cmd As OleDbCommand = New OleDbCommand("SELECT subp.SubItem " & _
"FROM subp INNER JOIN Products ON subp.ProductID = Products.ProductID " & _
"WHERE (((Product.ProductID) = '" & cmbProduct.SelectedItem.ToString & "'))", con)

'products table have a productID which is related to the ProductID field in subp table.

        Dim ds As New DataSet
        Dim da As New OleDbDataAdapter

        con.Open() ' I have error here telling me that connection has not been closed

        ds = New DataSet()
        da = New OleDbDataAdapter()

        da.SelectCommand = cmd

        da.Fill(ds, "products") 'when i comment out con.open() i have an error here (No value given for one or more required parameters.)

        With cmbSubP
            .DataSource = ds.Tables("products")
            .DisplayMember = "ProductName"
            .ValueMember = "ProductID"
            .SelectedIndex = 0
        End With

        con.Close()

    End Sub
End Class

well as i have said, i tried to fix it but with the little understanding i have .. i can't :(

The product have a Sub product, so when they select one product from the first combo i want the related sub-product to fill the combo.

if my approach is wrong i would appreciate it if someone can tell me a better approach.

[adam_k]

Ole is not my strong suit - far from it, but I believe that you've destroyed con by con = Nothing and then you try to use it again.

Try commenting out this line of code and try again or declare con all over again for the cmbProduct_SelectedIndexChanged sub.

[Reverend Jim]

I believe adam_k is correct. When you connect to a database on a network the standard is to keep the connection open for as short a time as possible because there are a limited number of connections available and you want to free them up for others. In your case you are connecting to a local resource so that is not a consideration. Even so, as adam points out, as long as you don't set con = Nothing you can leave the connection open, or you can open it and close it as many times as you want.

As for the error on con.Open(), if you replace it with

If con.State = ConnectionState.Closed Then
    con.Open()
End If

Then the connection will be opened if it wasn't already open.

[steplar]

Thanks for the reply,
i tried commenting it out, but i still get the error. however aw you both have suggested, i just make the connection private and reopen it on cmbProduct_SelectedIndexChanged

da.Fill(ds, "products")

this line has 'No value given for one or more required parameters' error still...

i really dont know what to check and how to proceed :(

[adam_k]

Just above da.Fill(ds, "products") type debug.print cmd.commandtext . It might be helpfull to put a breakpoint on fill.

When you debug your program you'll get the query in the immediate pane (the breakpoint helps you not to loose it with all the info about the exception).

Copy the query and run it in access (in a query with SQL view). If it runs OK, we need to figure out something else. If it doesn't, you should check your syntax (table names, field names,etc).

[steplar]

thanks for your replies,

After many trials and errors, I have fixed the connection problem taking all your suggestions into consideration. What i did is i opened a new module to open the connection and calls it whenever i need it.

Now i have a bigger problem as i advanced with my combo. im not pasting the entire code, since it will be too messy :P

I have 3 combo and 3 different table in my database. all this are related ... i want the first combo to display the Product i publish the combo on form load

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 Dim cmd2 As OleDbCommand = New OleDbCommand("SELECT ProductID, ProductName FROM Products", con)
' some code follows
' and after all the dataset 

 da2.Fill(ds2, "Product")

        With cmbProduct
            .DataSource = ds2.Tables("product")
            .DisplayMember = "ProductName"
            .ValueMember = "ProductID"
            .SelectedIndex = 0
        End With

End Sub

Now this Product have a sub product i want to filter the next combo by productID i.e i select the product i want the corresponding sub-product to be shown in the combo2

Private Sub cmbProduct_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbProduct.SelectedIndexChanged

qry = "SELECT * FROM subp WHERE ProductID = " & cmbProduct.SelectedValue.GetHashCode & " order by subitem"

da.Fill(ds, "product")

        With cmbSubP
            .DataSource = ds.Tables("product")
            .DisplayMember = "SubItem"
            .ValueMember = "ProductID"
            '.SelectedIndex = -1
        End With
end sub

Everything works normal till here.
My headach starts from the next code where i have to filter combo3 using the items from combo2

Private Sub cmbSubP_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbSubP.SelectedIndexChanged
qry1 = "SELECT * FROM subtype WHERE psubID =  " & cmbSubP.SelectedValue.GetHashCode & " "

da1.Fill(ds1, "subtype")
        With cmbType
            .DataSource = ds1.Tables("subtype")
            .DisplayMember = "type"
            .ValueMember = "psubID"
            '.SelectedIndex = 0
        End With
end sub

The codes always return the value of the first combobox, i'm totally lost, the last combo cmbType wouldn't update corresponding to the second combo(cmpSubP), instead it always return the value of combo1 (cmbProduct)

Please tell me if theres a better approach to this, i've been tryin and browsing the net for two straigh nights now :P can't get it to work

[adam_k]

Why are you using .GetHashCode?
My guess is that the hashcode accidentally matched the productID and that's why you are getting results in the second combo. Try removing it and if that doesn't work use .ToString

PS: Read here what gethascode is about : http://msdn.microsoft.com/en-us/library/system.object.gethashcode.aspx

[poojavb]

U can use ComboBox.SelectedItem or ComboBox.SelectedValue or ComboBox.Text

Try using this...