?
Solved

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

Posted on 2008-06-17
19
Medium Priority
?
673 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Native ability to set a user account password via AD GPO was removed because the passwords can be easily decrypted by any authenticated user in the domain. Microsoft recommends LAPS as a replacement and I have written an article that does something …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…

589 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