• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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

0
jjrr007
Asked:
jjrr007
3 Solutions
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now