SQL results saved to C# Variable

I am trying the following code that is suppose to take the results of the query (on button click) and save it to a variable.  I'm not sure what I am doing wrong.   When I debug the code below, it's being caught by the catch statement.  

If someone could help guide me with any follow-up questions on this topic, I would appreciate it.  

    protected void Button1_Click(object sender, EventArgs e)
    {
       try
        {
            string BarcodeValue = Barcode.Text;
            SqlConnection connection = new SqlConnection("Data Source=my connectionstring");
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = "Select COUNT(*)  From Item where Barcode = '" + BarcodeValue + "'";
            int result = ((int)cmd.ExecuteScalar());
            
           if (String.ReferenceEquals(result, '0'))
//should return 1 from query,  but is going to catch statement
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('456 wrong');", true);
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('456 right');", true);
            }
            connection.Close();
        }
        catch
        {
            ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Error');", true);
        }
}
   

Open in new window

LVL 1
jjrr007Asked:
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.

Luis PérezSoftware Architect in .NetCommented:
What is the exception thrown?

Anyway, you can try this:
SqlConnection connection = new SqlConnection("Data Source=my connectionstring");
connection.Open() //<-- open the connection before execute the command

Hope that helps.
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
Randy PooleCommented:
replace your catch with:
catch(Exception ex)
        {
            ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('"+ex.message+"');", true);
        }

Open in new window


And let us know the error
0
jjrr007Author Commented:
Randy Poole,

Thanks. I do want to do more with error trapping.  Do I need "using System."what"- can't find which one? Here is the error after trying that code:

Error      2      'System.Exception' does not contain a definition for 'message' and no extension method 'message' accepting a first argument of type 'System.Exception' could be found (are you missing a using directive or an assembly reference?)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Randy PooleCommented:
Sorry should be a capitial Message
so
catch(Exception ex)
        {
            ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('"+ex.Message+"');", true);
        }

Open in new window

0
jjrr007Author Commented:
The error message is OK now- thanks.  

Here is the problem- when I tried:
connection.Open() ;

It didn't go to the catch statement this time, but query in debugging is now returning 0, not 1.  In other words, it should have went to the else part of the else statement.  I tried the following queries in SQL Server Management Studio:
Select COUNT(*)  From Item where Barcode = '456' Result was 1
Select COUNT(*)  From Item where Barcode = 456 Result was 1


Here is the code in c# run in SSMS (the problem now):
Select COUNT(*)  From Item where Barcode = ' " + 456 + " ' Result was 0
Above is  the same thing as in C# (that's- single quote, double quote, plus sign, 456, plus sign, double quote, single quote. I took out the wrap around " s when running in C# as that's part of C# code)

How do I adjust the query so that the result is the same?
0
jjrr007Author Commented:
When I debugged it, I see that Barcode in the query has a value of null- though I entered 456.  

Why did that happen?  If we find the answer to this, it should be all work.  Here is the asp.net code for Barcode:

        <asp:TextBox ID="Barcode" runat="server" 
            style="text-align: left; margin-bottom: 0px" Width="110px"></asp:TextBox>
            <asp:RegularExpressionValidator  Runat="server" ID="valNumbersOnly" ControlToValidate="Barcode" Display="Dynamic" ErrorMessage="Barcode Required" ForeColor="Red" Font-Size="X-Small" ValidationExpression="(^([0-9]*|\d*\d{1}?\d*)$)">
</asp:RegularExpressionValidator>
<asp:RequiredFieldValidator id="RequiredFieldValidator1" runat="server"
  ControlToValidate="Barcode"
  Display="Dynamic"
  ErrorMessage="Barcode Required"
  Font-Size="X-Small"
  ForeColor="Red">
</asp:RequiredFieldValidator>

Open in new window

0
jjrr007Author Commented:
My mistake.  The result of the query is 1 - so the query is working fine.

Here is he issue- It's going to the else statement though though the value returned is 1.  is there something not right here:

            if (String.ReferenceEquals(result, '1'))
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('456 wrong');", true);
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('456 right');", true);
            }
            connection.Close();
            Barcode.Text = "";

Open in new window


Below is the complete code:
public partial class _Default : System.Web.UI.Page
{



    protected void Page_Load(object sender, EventArgs e)
    {
         
        
    }










    protected void Button1_Click(object sender, EventArgs e)
    {


        try
        {

          string  BarcodeValue = Barcode.Text;

            SqlConnection connection = new SqlConnection("my connection string");
            connection.Open();
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = "Select COUNT(*)  From Item where Barcode = '" + BarcodeValue + "'";
            int result = ((int)cmd.ExecuteScalar());
            




            if (String.ReferenceEquals(result, '1'))
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('456 wrong');", true);
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('456 right');", true);
            }
            connection.Close();
            Barcode.Text = "";
            
        }
        catch (Exception ex)
        {
            ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Try Again!    " + ex.Message + "');", true);
            Barcode.Text = "";
        }
        
    }

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
}

Open in new window

0
Randy PooleCommented:
if (result==0)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('"+BarcodeValue +"' wrong');", true);
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('"+BarcodeValue +"' right');", true);
            }

Open in new window


Your return is an integer not string..
0
Randy PooleCommented:
also wasn't sure if you wanted a result of 0 to alert the first msg or larger then 0, just modify accordingly.
0
jjrr007Author Commented:
I will try what you said tonight.  I am away from that computer and let you know.

thx again.
0
tipsybroomCommented:
result is an integer
if (String.ReferenceEquals(result, '0'))

if (result.Equals(0))

Open in new window

or
if (result == 0)

Open in new window

0
jjrr007Author Commented:
Thanks to everyone.  I appreciate the quick response as well.  Below is another question I asked.  It should be easy for you experts!  Thanks again!

http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_28505331.html
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
C#

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.