Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2008-06-17
19
644 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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
 

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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