Getting user choices from multi case switch statement

Question

I am using a wizard control to make the users go through multiple steps. They start at step 1 but sometimes skip some steps if they answer yes or no (radio buttons). For example, a question at the end of some steps would be: "Is the issue fixed?" and there is a yes or no radio buttons choices. If the answer is yes, take them to the last step to finish. If the answer is no, then they go to the next to step.

My issue is getting the results from all the steps to table test in the database.

        string constring = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ClientMonitorDevices"].ConnectionString;
        SqlConnection conn = new SqlConnection(constring);
        SqlCommand cmd = new SqlCommand();

        cmd = conn.CreateCommand();
        cmd.CommandText = @"INSERT INTO test (siteid, hotelname, step1, step2)
                            VALUES (@siteid, @hotelname, @step1, @step2)";

        cmd.Parameters.AddWithValue("@siteid", siteid);
        cmd.Parameters.AddWithValue("@hotelname", hotelname);

        int activeStep = Wizard1.ActiveStepIndex;

        switch (activeStep)
        {
            case 1:
                if (step1_yes.Checked)
                {
                    step1 = "Device Can Detect Network with a Signal Strength of 50% or Greater";
                    Wizard1.ActiveStepIndex = 2;
                }
                else if (step1_no.Checked)
                {
                    step1 = "Device Cannot Detect Network with a Signal Strength of 50% or Greater";
                    Wizard1.ActiveStepIndex = 16;
                }
                else
                {
                    e.Cancel = true;
                    gen.MessagBox(this.Page, "Please choose Yes or No", "Please choose Yes or No");
                }
                cmd.Parameters.AddWithValue("@step1", step1);
                break;
            case 2:
                if (step2_unable.Checked)
                {
                    step2 = "Unable to Join Network";
                    Wizard1.ActiveStepIndex = 3;
                }
                else if (step2_unidentified.Checked)
                {
                    step2 = "Connect to Unidentified Network";
                    Wizard1.ActiveStepIndex = 7;
                }
                else if (step2_internet.Checked)
                {
                    step2 = "Connected (Internet Access)";
                    Wizard1.ActiveStepIndex = 11;
                }
                else if (step2_local.Checked)
                {
                    step2 = "Connected Local Only (No Internet Access)";
                    Wizard1.ActiveStepIndex = 15;
                }
                else
                {
                    e.Cancel = true;
                    gen.MessagBox(this.Page, "Please Choose One of the Options", "Please Choose One of the Options");
                }
                cmd.Parameters.AddWithValue("@step2", step2);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                break;

The error I get is Must declare the scalar variable "@step1". because step1 is out of scope or that's what I think. I tried a stored procedure but that didn't work either.

Another attempt was to use update statement but the issue I ran into is getting the ID column (index) which is indexable and and get incremented by 1 with every insert. If anyone have any ideas then by all means let me know. I have been working on this for about 2 weeks now with no avail.

rory.ap · Answer 1 · 2016-11-16T17:25:23.560

0

The problem is that your query, which contains the @step1 parameter, is defined outside of any conditional structures (i.e. switch blocks):

cmd.CommandText = @"INSERT INTO test (siteid, hotelname, step1, step2)
                    VALUES (@siteid, @hotelname, @step1, @step2)";

However, you're only supplying the @step1 query param in one of the cases in the activeStep switch block and @step2 in the other one. The query itself expects both parameters.

You need to have both these lines in both of your outer-most case statements:

cmd.Parameters.AddWithValue("@step1", step1);
cmd.Parameters.AddWithValue("@step2", step2);

If, in either case, the other value is going to be null, you can just set the value to DBNull.Value.

edited Nov 16 '16 at 17:25

answered Nov 16 '16 at 17:18

rory.ap

34,009
10
83
174

Not sure I follow. Are you saying to put the two statements outside the switch statement like after the switch statement closing bracelet? If so, I did that and got this error The parameterized query '(@siteid nvarchar(6),@hotelname nvarchar(8),@step1 nvarchar(4000' expects the parameter '@step1', which was not supplied. all of my variables are varchar(max) on the database side – chattyhu Nov 16 '16 at 17:29
So you have the statement `cmd.Parameters.AddWithValue("@step1", step1);` followed by the statement `cmd.Parameters.AddWithValue("@step2", step2);` at the bottom now, outside the `switch` block? – rory.ap Nov 16 '16 at 17:33
Yes. `default: break; } cmd.Parameters.AddWithValue("@step1", step1); cmd.Parameters.AddWithValue("@step2", step2); conn.Open(); cmd.ExecuteNonQuery(); conn.Close();` – chattyhu Nov 16 '16 at 17:46
Not sure why that's not working then. It might be having trouble due to the fact that your db type is varchar(max) in the db. Try changing the statements to this: `cmd.Parameters.Add("@step1", SqlDbType.VarChar, -1).Value = step1;` and `cmd.Parameters.Add("@step2", SqlDbType.VarChar, -1).Value = step2;` – rory.ap Nov 16 '16 at 18:00
Thank you so much for all the suggestions. I did what you suggested and got a similar error `The parameterized query '(@siteid varchar(max) ,@hotelname varchar(max) ,@step1 varchar(m' expects the parameter '@step1', which was not supplied.` – chattyhu Nov 16 '16 at 19:34

Getting user choices from multi case switch statement

1 Answers1