Solved

Fill a combobox with a parameterized query with the parameter selected from another combobox

Posted on 2008-06-17
19
637 Views
Last Modified: 2008-09-12
 I want to fill a combobox with a paramaterized sql query.  The paramater will be chosen by a different combobox.  I've created a query using the combobox and tried a stored procedure... problem is, I dont understand how I set the paramater to the selected value.  
0
Comment
Question by:logoncom
  • 10
  • 9
19 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21805980
You can use combobox.SelectedValue to get the selected value.

Pass it to the stored procedure just like any other value:
		Dim dt As New DataTable

		Using conn As New SqlConnection("your connection string")

			Using cmd As New SqlCommand("sproc name", conn)

				cmd.CommandType = CommandType.StoredProcedure

				cmd.Parameters.Add("paramName", SqlDbType.VarChar, 20).Value = combobox1.SelectedValue

				Using da As New SqlDataAdapter(cmd)

					da.Fill(dt)

				End Using

			End Using

		End Using

Open in new window

0
 

Author Comment

by:logoncom
ID: 21806770
That seems like it would work but now I am having a heck of a time getting the value in the combobox I am trying to send to the sp.  Im using the event when selectedvaluechanged in the combox to run my sp and fill my second combobox.

Im using the following:
cmd.Parameters.Add("@item", SqlDbType.VarChar, 20).Value = combobox.SelectedValue.ToString

I keep getting an "Object reference not set to an instance of an object"  So I put a trycatch in and now It gives me 3 different vules on each change... 1st one is correct, 2nd and 3rd are wrong.  Then I can get the 1st value again.

Does that make sense?                      
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21806850
I'm not sure what's going on with the events.

What object is set to nothing when you get that exception?
0
 

Author Comment

by:logoncom
ID: 21807063
it is the value in combobox 1.  When the page first loads it does not have a value yet
I have an if then to go around the query but it ignores it... do I have it written wrong?

 If Not cbbCategory.SelectedValue.ToString Is Nothing Then
<<DB & SP code here>>
else
endif
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21807291
hmmm... is cbbCategory the filtering combo box then? What is the filtered combo box name?
0
 

Author Comment

by:logoncom
ID: 21807328
"cbbcategory" is the main combobox.  Whatever is select there determines what will be displayed in the second combobox "cbbtype"


0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21807373
not sure what you mean by "it ignores it" then...

I'd check
If Not cbbCategory.SelectedValue IsNot Nothing AndAlso cbbCategory.SelectedValue.ToString Is Nothing Then
End If
0
 

Author Comment

by:logoncom
ID: 21814088
This is just not working for me.  I've used the code given above and the correct value is being sent to the SP, but the 2nd combobox is not being populated with the results.  
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21814141
Have you put a breakpoint after the datatable is loaded to see if t he correct data is coming back from the database?

You also need to bind the returned data to the combobox.

Other problems? Please post code.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:logoncom
ID: 21814542
it does not seem to be returning any data.  Not even executing the SP i dont think... I must be missing something.  Here is the query I am trying to execute and below that is my code.

SELECT     Item,code
FROM         MaterialType
WHERE     LEN(code) = 5 and code LIKE @ITEM





        If Not ComboBox1.SelectedValue.ToString Is Nothing Then

            Dim ct As String = ComboBox1.SelectedValue
            Dim dt As New DataTable
            Using conn As New SqlConnection("data connection here is correct")
                conn.Open()
                Using cmd As New SqlCommand("getcategorytype", conn)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.Add("@item", SqlDbType.VarChar).Value = "(" & ct & "%)"
                    Using da As New SqlDataAdapter(cmd)
                        da.Fill(dt)




                        With ComboBox2

                            .DataSource = dt
                            .DisplayMember = "item"


                        End With

                        conn.Close()

                    End Using
                End Using
            End Using
        Else
        End If
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21815234
As a side note, you don't need conn.Open or conn.Close with a dataAdapter.

Are you sure you want code to be like item?

In the past, I want to say I've done this differently... something like this, I think:
SELECT     Item,code
FROM         MaterialType
WHERE     LEN(code) = 5 and code LIKE (@item + '%')

0
 

Author Comment

by:logoncom
ID: 21816586
not working still.  I have put a stop point and my parameter is correct.  If I put my parameter in a query and run it, i get the correct result so somewhere in my code I am not getting the data into my combo box.  Do I need to do a "for each item in dt..... add ..... next ?

How do I know if my SP is even getting executed?  I keep getting blank combo boxes so something is missing
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21816983
You need to check to see if anything is coming back from the query to the database -- not if the query works on the server.

Put a breakpoint on the line after da.Fill(dt)

Check that dt.Rows.Count > 0 (that is, add a watch on dt.rows.count, or use the datatable visualizer (hover over dt, and click on the magnifying glass that shows up). This will let you know you got data back.
0
 

Author Comment

by:logoncom
ID: 21817664
Ok, so I am not getting any data back.  The parameter is right and the query works... so what am I missing?
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21817878
I suspect that passing back the parameter the way you're trying isn't working.

Try leaving the parentheses in the query, not the parameter.
0
 

Author Comment

by:logoncom
ID: 21822451
Im getting closer.  The problem I am facing now is the paramater I am passing as a string is coming out as "A01" and when I pass that to the SP the quotes mess up the sql.

On the vb.net side ( sql side) , how can I get the parameter to drop the quotes?
0
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 500 total points
ID: 21822657
That's actually a "feature" of using SQL parameters. The goal is, in part, to prevent SQL injection attacks.

Change the SQL to be
SELECT     Item,code
FROM         MaterialType
WHERE     LEN(code) = 5 and code LIKE (@item + '%')

and
cmd.Parameters.Add("@item", SqlDbType.VarChar).Value = "(" & ct & "%)"

becomes
cmd.Parameters.Add("@item", SqlDbType.VarChar).Value = ct
0
 

Author Comment

by:logoncom
ID: 21822747
There must be something I am doing wrong in regards to Stored Procedures.  With the SP I can get data back but it does not filter out the data based on the parameter.  So,  I gave up on the SP and used a sql statement instead with the same query I had in the SP and it works.  Any Idea what thats about?
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21823241
Well.. the sproc code I posted earlier, I tested... it worked just fine. So not sure what's going on there.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now