CODEWITHSUNDEEP
.netsql-serversql-updatereader
manu sharma
manu sharma

Reputation: 140

how to update and read the value of sql column on click of the same button

what i am doing is on click of a button, i am updating a sql column by adding +1 to it. I faced no problem doing that. Now what i want is that for the same button click event, i want to read the value of that column which i have updated and send it in a query string to another page. here is the code-

int i=0;
protected void btnSubmit_Click(object sender, EventArgs e)
{
    sql.Open();
    string r = "update counter set m_id=m_id+1 where id=1";
    SqlCommand com = new SqlCommand(r, sql);
    com.ExecuteNonQuery();
    sql.Close();
    {
        SendHTMLMail();
    }





void SendHTMLMail()
{
    StreamReader reader = new StreamReader(dd1.SelectedItem.Value);
    string readFile = reader.ReadToEnd();
    Regex regx = new Regex("(?<!src=\")http(s)?://([\\w+?\\.\\w+])+([a-zA-Z0-9\\~\\!\\@\\#\\$\\%\\^\\&amp;\\*\\(\\)_\\-\\=\\+\\\\\\/\\?\\.\\:\\;\\'\\,]*([a-zA-Z0-9\\?\\#\\=\\/]){1})?", RegexOptions.IgnoreCase);
    string output = regx.ToString();    
    string count = 0.ToString();
    output = readFile;
    string username = Server.UrlEncode(this.txtUsername.Text);

    //here i want to read the value of m_id that i have updated as shown above and setting it to 'i' and passing this i  to the query string//

    sql.Open();
    string re = "select m_id from counter where id=1";
    SqlCommand ccmd = new SqlCommand(re, sql);
    com.ExecuteNonQuery();
    SqlDataReader read = ccmd.ExecuteReader();
    {
       while (read.Read())
          i =int.Parse(read["m_id"].ToString());

    }
    sql.Close();

    output = regx.Replace(output, new MatchEvaluator((match) =>
    {   
      var url = Uri.EscapeDataString(match.Value.ToString());
      return $"http://localhost:61187/two?sender={username}&link={url}&count={count}&mailer_id={i}";
    }));

now, the problem is instead of getting updated say from 5 to 6, m_id is getting updated to 7

Upvotes: 0

Views: 77

Answers (1)

Steve
Steve

Reputation: 216293

You could batch commands together and execute just one command instead of calling two times to the database

protected void btnSubmit_Click(object sender, EventArgs e)
{
    sql.Open();
    string r = @"update counter set m_id=m_id+1 where id=1;
                 select m_id from counter where id=1;";

    SqlCommand com = new SqlCommand(r, sql);
    int result = (int)com.ExecuteScalar();
    sql.Close();
    {
        SendHTMLMail(result);
    }
}

At this point the SendHTMLMail method (nested or not) can receive the result variable and avoid any call to the database engine. Note that in this case I have changed the ExecuteNonQuery to ExecuteScalar to get back the only value returned by the select.

However, if want still keep the things separated, then you need to change the code inside your SendHTMLMail to something like this

// just change the CommandText to avoid reexecuting the INSERT
com.CommandText = "select m_id from counter where id=1";

// No more needed
// SqlCommand ccmd = new SqlCommand(re, sql);

// Get the reader from the SqlCommand
SqlDataReader read = com.ExecuteReader();
{
    .... go on with reading

Upvotes: 1

Related Questions