Solved

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

Posted on 2008-06-17
19
643 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VS.net 2010 11 34
How to sort a list of objects using an item in an enclosed list 5 46
ASP.NET MVC 2 31
vb.net winforms sizing/resolution? 4 31
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 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