Thread: fill combobox using sproc and variable

Ok, been tinkering around. I have this code below, that when the dialog form with the combo box (frmSelEst2) loads then the combobox is populated from the stored proc with the parameter being sent. This works great, but I need someone to check if anything is bad or needs to be changed to make it more efficient. I also need to add error handling. I have not come to grips with 'Try...Finally' yet. Still learning.

Code:

Private Sub frmSelEst2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con1 As New SqlClient.SqlConnection("Data Source=CompDEV;Initial Catalog=XXXX;Persist Security Info=True;User ID=sa;Password=XXXX")
        Dim dtSelEst As New DataTable("Jobs")
        If Not con1.State = ConnectionState.Open Then con1.Open()

        Dim mjsqlcmd As New SqlClient.SqlCommand("spSelEst1", con1)
        mjsqlcmd.CommandType = CommandType.StoredProcedure

        mjsqlcmd.Parameters.Add("@prmBidOff", SqlDbType.NVarChar, 50)
        mjsqlcmd.Parameters("@prmBidOff").Value = modUser.ThisAppUser.AceBranch



        dtSelEst.Load(mjsqlcmd.ExecuteReader())

        With cboJobs
            .DataSource = dtSelEst
            .ValueMember = "JobName"
            .DisplayMember = "JobName"

        End With

        con1.Close()

    End Sub

Thanks again for any suggestions.

The above code works for what is needed. I am working on adding error handling right now.

My next part of this puzzle is opening a form to display the returned datarow in all of the textboxes. Then if a user makes any changes I will need to update that record.

I have the stored procedures built to use the parameter (@JobName) from the combo box the user selects the name.

I have the code to return the datarow just like above, just need to know how to map each column to the textbox.

Thanks in advance.

When you say map columns to textboxes do you mean the dataTable columns?

With my SP I return all results. I then create the tableAdapter in code, and use this to fill my UI generated DataSet dataTable.

I've then got all my "textboxes" and other fields databound to that dataTable's columns.

Yes, that is what I am trying to do.

Thanks.

I don't run .executereader, instead I create an SQLDataAdapter from my SQLCommand (i.e. the Stored Procedure), then fill it to my UI created DataSet table. I don't create the DataSet in code - it's already on my form.

Here's a snippet of my code, it may help:

Code:

            cn.Open()
            Dim cmd As New SqlCommand("spSearch", cn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@CoProducerID", SqlDbType.Int)
            cmd.Parameters("@CoProducerID").Value = me.cboSearchCoProducer.SelectedValue
            
            Dim da = New SqlDataAdapter(cmd)
            Me.DsBreadcrumb.DC.Clear() 'this clears the dataTable, otherwise everytime the query is run it will add more rows to the end.
            da.Fill(Me.DsBreadcrumb.DC) 'use the code created SQLDataAdapter to fill my DataTable
            da = Nothing
            cn.Close()

On my form, I have all of the fields dataBound to the column in the "DC" table of my DsBreadcrumb dataSet.

Regarding Try...Catch...

If there's an error in a section of your code (usually when loading data, e.g. can't convert a string to an integer) then you will get an "Unhandled Exception". By using Try...Catch you can "trap" these errors and allow the program to carry on and give you a better understanding of the error in a messagebox.

For instance

Code:

Try
   me.tableAdapter.fill(me.dataSet.dataTable)
   me.textbox1.text = "HELLO"
Catch ex as system.exception
   messagebox.show(ex.message)
End Try

if there's an issue with filling the dataTable, a messagebox will show the exact error. Because there's been a failure, anything else will not get run. So the textbox won't say HELLO.

If the code looked like this:

Code:

Try
   me.tableAdapter.fill(me.dataSet.dataTable)
Catch ex as system.exception
   messagebox.show(ex.message)
End Try
   me.textbox1.text = "HELLO"

Then even if the dataTable errors during fill, the textbox will still say HELLO as it's now outside of the "Try - End Try" code.

That's quite a basic example, but more explained here: How to: Use the Try/Catch Block to Catch Exceptions
and regarding "...finally" How to: Use Finally Blocks

Sorry I have not replied. We have moved our office and I have been the main coordinator of it. New T3 connection is incredible!

Anyway, back to the post. I have read your replies and started this morning on trying it out. I have a few questions:

1. Can you show me how you declared the DS (dataset) and the DSBreadcrumb (dataset name)? I have tried using with my names I created and want to verify my code.

2. How did you bind the datatable to the form? Can you supply me some information on how you went about setting this up?

Thanks again for all of your help.

From the above post, I have shown that my combo box will pull the data. Now I am stuck on passing the value selected to the stored proc and open the next form with a single row using the parameter.

My problem is, if I run the procedure from the combobox form, the next form can not access the dataset and vise versa. I need to pass the string in the .SelectedValue of the combo box to the next form.

I am trying to search the internet, but everything I am looking at is in ASP.

Thanks in advance.

1. Can you show me how you declared the DS (dataset) and the DSBreadcrumb (dataset name)? I have tried using with my names I created and want to verify my code.

I used the GUI to create databound dataset, datatables and all queries on those datatables.
Connecting to Data in Visual Studio Overview

2. How did you bind the datatable to the form? Can you supply me some information on how you went about setting this up?

Again, because it's all databound, you simply bind to the controls you drag onto the form
How to: Bind Data to Existing Controls