Link to home
Start Free TrialLog in
Avatar of ExpressMan1
ExpressMan1Flag for Canada

asked on

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;
Avatar of Mlanda T
Mlanda T
Flag of South Africa image

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
Avatar of ExpressMan1

ASKER

Tried this but still no result showing.
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

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.
How about we try to access the value using:
cmd.Parameters["@ShowCity"].Value

Open in new window

Like this?

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

string ShowCity = cmd.Parameters["@ShowCity"].Value
Label1.Text = "Result = " + ShowCity;
Did that work?
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;
ASKER CERTIFIED SOLUTION
Avatar of Mlanda T
Mlanda T
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.