Show result in label after SelectedIndexChanged

I created the following stored procedure.
ALTER PROCEDURE [dbo].[spShowCity]
@OriginCountryID int,
@ShowCity Bit OUTPUT
AS
SELECT ShowCity
FROM
CountryOrigin
WHERE (OriginCountryID = @OriginCountryID)

And the aspx.cs code below:    I am trying to get the True or False bit (ShowCity) to show in the label (Label1) after selecting a Country from the DropDownList ddlOrigin. No errors but no result showing. I plan on using the showcity bit value to set a city field to visible or not. Just using the label for testing for now.

 protected void ddlOrigin_SelectedIndexChanged(object sender, EventArgs e)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("spShowCity", con);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@OriginCountryID", ddlOrigin.SelectedValue);

                SqlParameter outputParameter = new SqlParameter();
                outputParameter.ParameterName = "@ShowCity";
                outputParameter.SqlDbType = System.Data.SqlDbType.Bit;

                outputParameter.Direction = System.Data.ParameterDirection.Output;
                cmd.Parameters.Add(outputParameter);

                con.Open();
                cmd.ExecuteNonQuery();

                string ShowCity = outputParameter.Value.ToString();
                Label1.Text = "Result = " + ShowCity;
ExpressMan1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
ALTER PROCEDURE [dbo].[spShowCity]
@OriginCountryID int,
@ShowCity Bit OUTPUT
AS
SELECT @ShowCity  = ShowCity
FROM
CountryOrigin
WHERE (OriginCountryID = @OriginCountryID)


1 - You are not setting the value for @ShowCity in your SQL
2 - Be sure to check for a NULL return value, just in case @OriginCountryID does not exist in CountryOrigin. In this case, @ShowCity will be NULL
0
ExpressMan1Author Commented:
Tried this but still no result showing.
0
MlandaTCommented:
Try running this SQL to see whether the stored procedure is working as expected
declare @OriginCountryID int, @ShowCity bit
 
set @OriginCountryID = 123 --put valid Country ID here

exec spShowCity @OriginCountryID, @ShowCity out

select @ShowCity

Open in new window

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

ExpressMan1Author Commented:
MlandaT.....Yes that SQL works and the stored procedure is working, returning either a 0 or 1 for True of False.  

The problem seems to be in the cs code. The DataValueField for ddlOrigin is set to OriginCountryID if it helps.
0
MlandaTCommented:
How about we try to access the value using:
cmd.Parameters["@ShowCity"].Value

Open in new window

0
ExpressMan1Author Commented:
Like this?

cmd.Parameters.AddWithValue("@OriginCountryID", ddlOrigin.SelectedValue);
cmd.Parameters["@ShowCity"].Value
0
MlandaTCommented:
Ooops! Sorry... meant when reading the value.. instead of outputParameter.Value.ToString();
0
ExpressMan1Author Commented:
Like this?

string ShowCity = cmd.Parameters["@ShowCity"].Value
Label1.Text = "Result = " + ShowCity;
0
MlandaTCommented:
Did that work?
0
ExpressMan1Author Commented:
No.

Tried this as below and there is no error but label still shows nothing after selecting country form dropdownlist.

string ShowCity = cmd.Parameters["@ShowCity"].Value.ToString();
                Label1.Text = "Result = " + ShowCity;
0
MlandaTCommented:
I have created a quick test of your code and the result is getting passed back after calling the stored procedure. Of course my stored procedure just returns a 1 or 0 depending on whether OriginalCountryID is greater than 0 or not. Here is a screenshot of my LinqPad showing the results. You will see that I could access the return value correctly from either outputParamer.Value or cmd.Parameters["@ShowCity"].ValueSample resultsGoing back to basics, the questions that come to my mind are:
1 - Are you sure that the label is visible?
2 - If you put a breakpoint on the string ShowCity = cmd.Parameters["@ShowCity"].Value; line, does the ShowCity string variable get a value?
3 - Are you sure you have correctly wired the ddlOrigin_SelectedIndexChanged event, and when you change the value, this function is actually being called?

I'm sorry, I think we are going to have to wait for other experts to come and help on this one. I think we have done everything I can think of here.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ExpressMan1Author Commented:
Thanks for you help and patience MlandaT.  

Got it to work by setting the AutoPostBack of ddlOrigin to true and that along with your code "    string ShowCity = cmd.Parameters["@ShowCity"].Value.ToString();  "

Label now returning True of False.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

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.