Populating listbox from dataset problem...

[qaisqais]

I'm having a bit of problems with my listbox..

Based on the item selected in the listbox,the corresponding in stock number and price number are meant to appear in the textboxes, this functions fine when it was databinded with VB's inbuilt databinding (clicking the listbox and using databound items etc). However I had to remake the project and doing this does not work now as the databinding built in doesn't let me change the connection string from |DataDirectory|.

Loading the listbox manually using a code to populate a dataset with the ProductName column from my ProductTbl and setting that as the datasource and the ProductName as listbox DisplayMember and Value member does make them appear in thelsitbox however clicking them doesn't trigger the corresponding quantity etc... (I was opening the connection and loading the dataset/listbox then closing connection on the form load event, deleted the code so no example)

Here's my code corresponding to get the quantity and price...any ideas/help please? using the selectedvalue worked for it when it was bound with vb's inbuilt function however neither selectedvalue,valuememebr or displaymember work to get the corresponding values to show when populating it myself with an sql statement, any ideas/help?

Private Sub listProducts_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles listProducts.SelectedIndexChanged
        Try
            con.Open()
            'sql statement selecting product price and quantity for the selected productname from listProducts(listbox)
            sql = "SELECT ProductPrice,ProductQuantity FROM ProductTbl WHERE ProductName='" & listProducts.SelectedValue & "'"
            da = New OleDb.OleDbDataAdapter(sql, con)
            'fil "PRODUCT" dataset using data adapter
            da.Fill(ds, "PRODUCT")
            'fill appropriate textboxes with table data
            txtCurrentQuantity.Text = ds.Tables("PRODUCT").Rows(0).Item(1)
            txtPrice.Text = ds.Tables("PRODUCT").Rows(0).Item(0)
            ds.Clear()
            con.Close()
        Catch ex As Exception
        End Try
    End Sub

 

[cjard]

However I had to remake the project and doing this does not work now as the databinding built in doesn't let me change the connection string from |DataDirectory|.

Why would a connection string cause inconsistent behaviour in forms code?
And why would you want to change it from |DataDirectory| ?

Loading the listbox manually using a code to populate a dataset with the ProductName column from my ProductTbl and setting that as the datasource and the ProductName as listbox DisplayMember and Value member does make them appear in thelsitbox however clicking them doesn't trigger the corresponding quantity etc... (I was opening the connection and loading the dataset/listbox then closing connection on the form load event, deleted the code so no example)

Indeed, it wouldnt. If you add the items manually into the listbox it uses its own internal data model, not the list offered by the bindingsource which is what the other controls are using. Delete your listbox and instead use a DataGridView, with hidden column and row headers and only one column of data visible

Here's my code corresponding to get the quantity and price...any ideas/help please?

No no no! Do NOT run a database query every time the user changes the list item that is selected! Download the data into a datatable (read the relevant DWx link in my signature, where x is the major version number of your .net framework, read the section "creating a simple data app"), bind everything through the same bindingsource and it will all work just fine

 

[qaisqais]

Thanks for your response, I couldn't use the |DataDirectory| connection string because when I'm debugging I don't have a copy of the database in the debug folders and I'm just generally confused if my program would work once it's finished with that connection string, don't understand how it works!

I've made a datagridview hiding the column and row headers, and having the productname column visible, i don't really understand how/what to apply from creating a simple data app to my situation, i'm not very good with programming unfortunately and have very little understanding of VB! could you explain further please? thanks for the help

 

[cjard]

Thanks for your response, I couldn't use the |DataDirectory| connection string because when I'm debugging I don't have a copy of the database in the debug folders and I'm just generally confused if my program would work once it's finished with that connection string, don't understand how it works!

If you have the db added to your project, and VS asked "do you want to copy the db into your solution?" and you say yes, the DB will go in as "Copy Always" (in its properties) and the connection string goes as the |dataDirectory| one.. You should perhaps set the copy mode of the db to Copy If Newer
This way when yo ustar tthe app youll have a debug db in with your app. The datadirectory macro is expanded to mean "the app folder" in this case, and it will all work fine

Creating a Simple Data App will introduce you to tableadapters, datatables, bound data, you'll have a datagridview on your form and from there you'll be able to see what happens if you edit its columns property to have only one column, remove the headers etc. Note that following the tutorial is not intended to solve your problem directly but provide a background understand about creating data driven applications