UPDATING QUERY WITH COMPARISON BETWEEN STRING AND VARCHAR(MAX)

Kate Smith

Total Post:24

Points:168
Posted by  Kate Smith
 1242  View(s)
Ratings:
Rate this:
Hi Developers,

I have an updating query like this:

 public void Update_Download(string _url){
            Data.Connect();
            using (Data.connexion)
            {
                string queryString = "update Fichier set Last_download=@last , Downloads_count= Downloads_count + 1  where Url = @url ";
                SqlCommand command = new SqlCommand(queryString, Data.connexion);
                command.Parameters.AddWithValue("@last", DateTime.Now);
                command.Parameters.AddWithValue("@url", _url);
                try
                {
                  SqlDataReader reader = command.ExecuteReader();
                 }
                catch { }
                }
         }
    }
}

the string connection :

public static SqlConnection connexion;
        public static bool  Connect()
        {
                System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
                builder["Initial Catalog"] = "Upload";
                builder["Data Source"] = "bd";
                builder["integrated Security"] = true;
                string connexionString = builder.ConnectionString;
                connexion = new SqlConnection(connexionString);
                try { connexion.Open(); return true; }
                catch { return false; }
        }
        public static  void Disconnect()
        {
            if (connexion != null) connexion.Close();
            connexion = null;
        }

the query is executing without exception but nothing is changed in table. the type of the attribute Url is varchar(Max) and the DBMS is Sql Server.

So what is the problem? how can i correct my query?

  1. Sumit Kesarwani

    Post:378

    Points:2694
    Re: Updating query with comparison between string and varchar(max)

    Hi,


    The problem is that you're using an ExecuteReader command. Change that to this:

    command.ExecuteNonQuery();
    Now, on top of that, I want to offer some advice. Consider the following modification of your current code:

    public void Update_Download(string _url)
    {
        try
        {
            using (SqlConnection connexion = Data.Connect())
            {
                string queryString = "update Fichier set Last_download=@last , Downloads_count= Downloads_count + 1  where Url = @url ";

                using (SqlCommand command = new SqlCommand(queryString, connexion))
                {
                    command.Parameters.AddWithValue("@last", DateTime.Now);
                    command.Parameters.AddWithValue("@url", _url);
                    command.ExecuteNonQuery();
                }
            }
        }
        catch (Exception ex)
        {
        }
    }
    and consider having Data.Connect() return a new SqlConnection(...) because you want to follow that procedure. When you need a new connection, get one using the new operation, and let SQL Server manage the pooling. Never share connections.

    I'm not sure you are sharing connection, this is just an observation.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!