fill combobox using sproc and variable

DimMeAsLost

Active member
Joined
Jan 31, 2010
Messages
29
Programming Experience
1-3
Hello all.

I have created a dialog form with a combo box, it has an OK and Cancel button. It is called frmEstSel.

I have a stored procedure that has 1 input parameter (@Branch). The data for the parameter is in a global variable at 'modUser.ThisAppUser.Branch'.

What I need is to connect to server, pass parameter, load the combo box. Second, I will need to pass the item selected in the combo box to a second stored procedure (@EstName is parameter for second sproc) and open a form to display that record.

90% of my application I am creating will use this method, so I am anxious to learn so it can be repeated.

Thanks in advance
 

DimMeAsLost

Active member
Joined
Jan 31, 2010
Messages
29
Programming Experience
1-3
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.


VB.NET:
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.
 

DimMeAsLost

Active member
Joined
Jan 31, 2010
Messages
29
Programming Experience
1-3
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.
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
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.
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
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:
VB.NET:
            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.
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
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
VB.NET:
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:
VB.NET:
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
 

DimMeAsLost

Active member
Joined
Jan 31, 2010
Messages
29
Programming Experience
1-3
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.
 

DimMeAsLost

Active member
Joined
Jan 31, 2010
Messages
29
Programming Experience
1-3
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.
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
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
 

DimMeAsLost

Active member
Joined
Jan 31, 2010
Messages
29
Programming Experience
1-3
I have created the form and dataset. I have one major problem.

1. I created the dataset from the stored procedure. When it created it and I dragged the dataset to the form (details mode) it added a tool bar called toolbarfilltextbox. It is basically a textbox in the toolbar for the user to input manually the parameter required and has a Fill button beside it.

I need to get the parameter from frmSelJob / cboJobs.SelectedValue to this form 'frmExistJob'

PS - I reviewed the links you provided and the first one shows the dataset designer and their are parameters listed. Mine does not show parameters. I did select the field when I went through the wizard, and of course, the toolbarfill textbox works, but manually.

Thanks in advance for your help.
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Oh OK, so it's two seperate forms?

There's a couple of options (I'm sure other people will chip in)

One is to have a "Global Variables" module. Anything declared in here is stored for the "life" of the running application, so can use a lot of memory if you use a lot of variables.

To do this, you would create a new module and use something like:
VB.NET:
Module modGlobalVar

    Friend vJob as Integer

End Module

Now, on frmSelJob, you would add code to assign a value to vJob. I assume you have a button on your form that opens frmExistJob.
VB.NET:
Private Sub btnOpenForm_Click(.........)

vJob = me.cboJobs.SelectedValue
Dim frm as new frmExistJob
frm.showdialog()

End Sub

Then on frmExistJob you would set your parameter to be vJob

It's that easy!!

But like I say, Global Variables are great, unless you have a lot, or are storing a great deal of data (like 1 String Variable but storing a lot of text)

The other option is to declare each variable on a form, and access that form from another.

I use both options in my applications, but give the Global Variables a go and see if that works.
 

DimMeAsLost

Active member
Joined
Jan 31, 2010
Messages
29
Programming Experience
1-3
I tried this yesterday and still having a few issues.

The global variable code works fine, except....

When I created the form, I created the dataset based on the sproc. When I dragged the dataset to the form it place a 'filltoolbar' on the form. The parameter has to be placed on the form, and then you have to click on the 'Fill' button. I am not wanting this.

I need to remove the filltoolbar and somehow have the dataset parameter before the form is loaded. I am calling the stored procedure and passing the variable from the prior form which has the dropdown box.

My issue is with the form filltollbar and the binding table adapter, correct?
 

DimMeAsLost

Active member
Joined
Jan 31, 2010
Messages
29
Programming Experience
1-3
VB.NET:
Private Sub FillToolStripButton_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillToolStripButton.Click
        Try
            Me.SpOpenEstimateTableAdapter.Fill(Me.MT3ds.spOpenEstimate, PrmJobNameToolStripTextBox.Text)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

    End Sub


This was generated from VS
 

DimMeAsLost

Active member
Joined
Jan 31, 2010
Messages
29
Programming Experience
1-3
If I copy this code to the form load event, pass the variable to the fill textbox, and then hide the toolstrip, It will work. I actually have it working. Now, is this the best practice?
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Delete the toolstrip if you don't need it. Remove all the underlaying code.

On your form load event
VB.NET:
Try
            Me.SpOpenEstimateTableAdapter.Fill(Me.MT3ds.spOpenEstimate, vJob)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
As long as vJob is the name of your global variable your global variable!

You could rename the query on the dataTable, so that it matches what you are doing. For instance:
.Fill - fills EVERY row
.FillByJobID - fills by a variable JobID
.FillByCustomerID - fills by a variable CustomerID
etc etc

You can have more than one query on a dataTable. By default it's call it .Fill . This can and will get confusing the more queries you put on the dataTable.

Personally, the way you've now got this working is the same way that I do things (except I don't really use stored procedures, just SQL queries)
 

DimMeAsLost

Active member
Joined
Jan 31, 2010
Messages
29
Programming Experience
1-3
Thanks for your help. Very much appreciated.

Stored Procedures is a must on my application. SQL server is going to be a hosted SQL Server. Trying to be super efficient on the data transfer.

Thanks again.
 

Latest posts

Top Bottom