Home > DeveloperSection > Forums > Updating query with comparison between string and varchar(max)
Kate Smith
Kate Smith

Total Post:24

Points:168
Posted on    June-19-2013 3:36 AM

 ADO.Net ADO.Net 
Ratings:


 1 Reply(s)
 886  View(s)
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?



Sumit Kesarwani

Total Post:378

Points:2694
Posted on    June-19-2013 8:39 AM

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.


Don't want to miss updates? Please click the below button!

Follow MindStick