Newbie Q: displaying a specific value (from sql table) in combobox and datagrid

[mannyc]

Hello
I only dabble as needed to create useful small "tools" to make my work easier so please understand I am not well versed in vb.net.

I am pulling data from a sql table and wish to display a particular security name (in my case) in the combobox display member
and the corresponding pricing info etc in a datagrid. Everything works but when the form loads what appears is blank on both combo box and datagrid. I'd like to force a particular security with it's associated pricing info to appear.
FYI - I don't know much about databinding - I'v only been learning slowly over time from diferent sources on net

For now what I'd like to know if the simplest technique i can use to do that in my simple form

Thanks

[wes4dbt]

Everything works but when the form loads what appears is blank on both combo box and datagrid. I'd like to force a particular security with it's associated pricing info to appear.

Will this always be the same security fdisplayed on form load? Is the security and pricing info in the same table?

There is more than one way to do this, you should post your current code that's relevant to this question.

[jmcilhinney]

It's not clear from your post what "a security" is. Please explain what the data actually represents and how exactly it is stored in the database, i.e. the schema and how tables relate to each other. As suggested, you should also post the relevant code. If the code works but doesn't show the expected data when the form loads, that suggests that you aren't executing the code when the form loads.

[mannyc]

I apologize for my lack of clarity - I just wanted to get a quick question going.
I am using a sql 2016 db. My form loads and the combo box (which shows a list of held stocks/securities) displays blank.
Below it I have a datagrid - it also displays blank but I am able to drop down on CB and see all my held stocks and when I click on one of them, my DG populates with required price history for that stock - all fine
I just would like to have the form load and display a particular security with its associated price history by default.
I will post code as soon as I get a minute

[jmcilhinney]

When you bind data, the first item in that data will be selected. If no item is selected in your case then that means that you mustn't be binding the data. The solution is to bind the data.

If you want some item other than the first to be selected then, whether the data is bound or not, the solution is to explicitly select the item you want selected.

[mannyc]

Hello
Thx for replying
I don't think I am binding data - not familiar with binding
but that is exactly what I need help with - how to programmatically select a specific item to display on form load

Do I need to do a arrow down for example??

[wes4dbt]

One way is to set the SelectedIndex, ComboBox1.SelectedIndex = 2

But until you answer the previous question and post the relevant code I'd just be guessing. no need for that.

[mannyc]

Code:

Public Class Form1


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGrid1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DataGrid1.MultiSelect = False
        DataGrid1.RowsDefaultCellStyle.SelectionForeColor = Color.Yellow

        Dim strCn As String = "Server=PortServ12;Database=Port12;Trusted_Connection=True;"
        Dim cn As New SqlConnection(strCn)
        cn.Open()


        Dim SQLsel1 As String = "Select id, security from smf  order by security"
        Dim cmdCB1 As New SqlCommand(SQLsel1, cn)
        Dim daCB1 As New SqlDataAdapter(cmdCB1)
        Dim dsCB1 As New DataSet()
        daCB1.Fill(dsCB1, "smf")


        '---Display list of securities from SMF Table

        With ComboBox1
            .DataSource = dsCB1
            .DisplayMember = "smf.security"
        End With


        cn.Close()

    End Sub

    '----POPULATE PRICE HISTORY (HPX TABLE)

    Private Sub ComboBox1_SelectedIndexChanged_1(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged


        Dim strCn As String = "Server=PortServ12;Database=Port12;Trusted_Connection=True;"
        Dim cn As New SqlConnection(strCn)
        cn.Open()



        '---Using an INNER JOIN

        Dim SQLsel2 As String = "Select smf.id, smf.security, hpx.price_date, hpx.price_xrate from SMF INNER JOIN HPX ON SMF.ID = HPX.record_# where smf.security = @CBS Order by hpx.price_date Desc"

        Dim cmdDGV1 As New SqlCommand(SQLsel2, cn)

        cmdDGV1.Parameters.AddWithValue("@CBS", CBSelection)


        Dim daDGV1 As New SqlDataAdapter(cmdDGV1)
        Dim dsDGV1 As New DataSet()
        daDGV1.Fill(dsDGV1, "HPX")
        DataGrid1.DataSource = dsDGV1.Tables(0)


        MsgBox(DataGrid1.Rows.Count & " Values")



        cn.Close()


    End Sub



End Class

[jmcilhinney]

Please don't post unformatted code snippets. They are unpleasant to read. You should do all you can to help us help you, which includes formatting you code for readability.

Public Class Form1
 
 
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGrid1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DataGrid1.MultiSelect = False
        DataGrid1.RowsDefaultCellStyle.SelectionForeColor = Color.Yellow
 
        Dim strCn As String = "Server=PortServ12;Database=Port12;Trusted_Connection=True;"
        Dim cn As New SqlConnection(strCn)
        cn.Open()
 
 
        Dim SQLsel1 As String = "Select id, security from smf  order by security"
        Dim cmdCB1 As New SqlCommand(SQLsel1, cn)
        Dim daCB1 As New SqlDataAdapter(cmdCB1)
        Dim dsCB1 As New DataSet()
        daCB1.Fill(dsCB1, "smf")
 
 
        '---Display list of securities from SMF Table
 
        With ComboBox1
            .DataSource = dsCB1
            .DisplayMember = "smf.security"
        End With
 
 
        cn.Close()
 
    End Sub
 
    '----POPULATE PRICE HISTORY (HPX TABLE)
 
    Private Sub ComboBox1_SelectedIndexChanged_1(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
 
 
        Dim strCn As String = "Server=PortServ12;Database=Port12;Trusted_Connection=True;"
        Dim cn As New SqlConnection(strCn)
        cn.Open()
 
 
 
        '---Using an INNER JOIN
 
        Dim SQLsel2 As String = "Select smf.id, smf.security, hpx.price_date, hpx.price_xrate from SMF INNER JOIN HPX ON SMF.ID = HPX.record_# where smf.security = @CBS Order by hpx.price_date Desc"
 
        Dim cmdDGV1 As New SqlCommand(SQLsel2, cn)
 
        cmdDGV1.Parameters.AddWithValue("@CBS", CBSelection)
 
 
        Dim daDGV1 As New SqlDataAdapter(cmdDGV1)
        Dim dsDGV1 As New DataSet()
        daDGV1.Fill(dsDGV1, "HPX")
        DataGrid1.DataSource = dsDGV1.Tables(0)
 
 
        MsgBox(DataGrid1.Rows.Count & " Values")
 
 
 
        cn.Close()
 
 
    End Sub
 
 
 
End Class

[mannyc]

Hello

1. I understand but last time i posted on here was probably years ago and I've never posted code. On the other hand it's not much and i'm sure you've seen it all with your experience.
2. But is the numbering done via the editing tools here? I ask because when I tried numbering a section (to test) it did not show numbers it showed asterisks and "List=1" etc. (on the screen as I did it). I see that once you post it the numbers appear ok - Lilke I said - don't use this much

[jmcilhinney]

I understand but last time i posted on here was probably years ago and I've never posted code. On the other hand it's not much and i'm sure you've seen it all with your experience.

Why you didn't do it doesn't really matter. People make mistakes. People don't know things. That's fine. I don't really care. The right thing to do is to format your code. I explained that to you. All you have to is make sure you do so in future, now that you know.

But is the numbering done via the editing tools here? I ask because when I tried numbering a section (to test) it did not show numbers it showed asterisks and "List=1" etc. (on the screen as I did it). I see that once you post it the numbers appear ok - Lilke I said - don't use this much

If you don't know how the editor works then spend some time experimenting with the editor. This site even has a forum dedicated to testing posts, so you can experiment as much as you like to see what does what.

Not surprisingly, when you hover over a button on the editor toolbar, it tells you what that button does. If you had taken the minimal amount of time required to do that then you'd have seen that the # button wraps CODE tags around the selected text. That seems likely to be relevant when posting code snippets. The CODE tags will use a fixed-width font and maintain indenting, which are two of the most important ways to improve readability. They also allow you to apply other formatting, e.g. bold or coloured text.

That's not what I used though. The VB button (looks like VE as it is cut off) will apply HIGHLIGHT tags. They used to be more useful as they would apply syntax highlighting for various languages as well. That was broken by a site software update a while back though, but I keep using them in case that ever gets fixed, in which case they will all start working again. You need to specify the language when prompted for an option. If you quote my post, you'll be able to see the result. You can type the same thing manually if you don't want to use the editor toolbar. HIGHLIGHT tags will add the line numbers, which can be useful to direct us to a specific place in your code, but they do not support additional formatting within the code.

[Shaggy Hiker]

EDIT: Well, I was too late, but I talked about more than the tags. One thing to take from that, though, is that I wasn't aware that the HIGHLIGHT tags were working, again.


I went back and edited your post to add [CODE][/CODE] tags, which is the # button. What you are seeing in JMC's post is what you get with the [HIGHLIGHT=vb.net]HIGHLIGHT [/HIGHLIGHT] tags, which is the VB button (though the B is chopped, so it looks like VE on most browsers). I didn't realize that button was working again. I prefer the # button, but now you can compare the two.

As for the code, you don't need a dataset. A dataset is essentially just a collection of datatables. You only have one, so you can just use a datatable. The difference is really minor, but you would find it VERY slightly easier.

You also want to set the displaymember before you set the datasource. I didn't realize it mattered until JMC told me, but he's right. If you only have a few items, it probably doesn't matter, but if's considerably faster if you set the displaymember first (and the valuemember, if you set that).

What I haven't done is bind a datasource that way. That may be the problem, and if it isn't I'm sure somebody will correct me. I have always bound a single datatable, in which case you could do this:

With ComboBox1
    .DisplayMember = "security"
    .DataSource = dsCB1.Tables(0)
End With

You could make it a bit tighter still if you used just a datatable rather than a dataset.

[jmcilhinney]

As for the issue, you are binding your data. That's what setting the DataSource property does. I would tend to change a few things in your code.

Firstly, don't use a DataSet if all you need is one DataTable. A data adapter can populate a DataTable as well so just create a DataTable.

Secondly, when binding data, always set the DataSource last. Properties like DataMember, DisplayMember, ValueMember, etc, should be set first. That's because a lot of work actually gets done when you set the DataSource and if you then set the DisplayMember or the like afterwards, a lot of that work has to be redone. Set the DisplayMember first and then all the work gets done just once when you set the DataSource.

If the amount of data is not too large, I would suggest that you retrieve all of it right at the start. You could then set up parent/child data-binding and the data displayed in the grid would be automatically filtered when the user made a selection in the ComboBox without any additional code.

Even with all that though, I'm not sure why you're not seeing an item selected in ComboBox by default. I'll take a closer look and get back to you.

[mannyc]

Thank you all for your replies/help
I looked around and sure enough it's an easy thing when you know VB well - As I said, that's not me

i added this and it now does what I need it to do

Dim indexW As Integer = ComboBox1.FindString("wandisco")
'MsgBox("Index is " & indexW)
ComboBox1.SelectedIndex = indexW

[Shaggy Hiker]

Yeah? Well, I didn't know about FindString, either, so one thing about programming: There is ALWAYS more to learn.