CODEWITHSUNDEEP
c#sql
drac
drac

Reputation: 357

c# update statement with where clause

I have a database with ordernumber & trackingnumber. The ordernumber is already completed.

I have the following code to update a trackingnumber column in my database, but it must only update where the ordernumber matches what the user types in. (The user enters an order number and a tracking number in default.aspx)

At the moment the code seems to work ok. but i have noticed a problem im not sure how to fix: the ordernumber column only goes up to number 3578, so i would expect that if the user typed in 3579 or higher, or indeed anything that doesn't match an ordernumber, then an exception will be thrown, but instead it says "updated". However there is no ordernumber of 3579, it just doesn't exist. so is it my SQL query that is not working properly? as i have the "Where" clause in. or is there something else i have missed?

Code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;

    namespace tracking
    {
        public partial class _Default : Page
        {
    protected void Page_Load(object sender, EventArgs e)
    {
        errorLabel.Visible = false;
        SuccessLabel.Visible = false;
    }

    protected void submitBtn_Click(object sender, EventArgs e)
    {
        int _orderID = Convert.ToInt32(orderID.Text);
        string _trackingID = trackingNumber.Text;

        SqlConnection myConnection = new SqlConnection("Data Source=MYDATABASESERVER;Initial Catalog=DATABASENAME;User ID=USERNAME;Password=PASSWORD");
        SqlCommand myCommand = new SqlCommand("UPDATE Shipment SET TrackingNumber = @tracking WHERE OrderId = @order", myConnection);

        try
        {
            myConnection.Open();
            myCommand.Parameters.AddWithValue("@order", _orderID);
            myCommand.Parameters.AddWithValue("@tracking", _trackingID);
            myCommand.ExecuteNonQuery();
            myConnection.Close();
            SuccessLabel.Text = "Thank you, tracking details have been updated";
            SuccessLabel.Visible = true;
            orderID.Text = "";
            trackingNumber.Text = "";
        }
        catch (Exception f)
        {
            errorLabel.Text =  f.Message.ToString();
            errorLabel.Visible = true;
            return;

        }






    }
}

}

Upvotes: 1

Views: 4135

Answers (3)

evhen14
evhen14

Reputation: 1927

You script doesn't affect any rows.

The ExecuteNonQuery method returns a number of rows affected. 

if (myCommand.ExecuteNonQuery() == 0)
{
    errorLabel.Text = "Tracking number is not found";
    errorLabel.Visible = true;
}

This will give you have many were updated

Upvotes: 1

SynerCoder
SynerCoder

Reputation: 12766

The ExecuteNonQuery method returns the number of rows affected. Your query runs fine, but no rows are updated. Therefor no exception.

I catch the number of updated rows, then check that:

myConnection.Open();
myCommand.Parameters.AddWithValue("@order", _orderID);
myCommand.Parameters.AddWithValue("@tracking", _trackingID);
int rowsUpdated = myCommand.ExecuteNonQuery();
myConnection.Close();
if(rowsUpdated > 0)
{
    SuccessLabel.Text = "Thank you, tracking details have been updated";
    SuccessLabel.Visible = true;
}
orderID.Text = "";
trackingNumber.Text = "";

Upvotes: 4

Muhammad Hani
Muhammad Hani

Reputation: 8664

because the command completed successfully but with no rows update ExecuteNonQuery() returns number of rows updated, check it first to determine what've been done with your script.

int res = myCommand.ExecuteNonQuery();
    if(res > 0)
    {
        Console.WriteLine("Worked");
    }

Dont forget to add finally and Close your connection.

Upvotes: 0

Related Questions