Click here to Skip to main content
15,846,004 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello Friends,


I'm using Asp.net c# and MYSql as back-end.

I'm updating a table,but table is not updating.There are only 3 columns in the table.
There is no exception when I'm executing the command object. But this returns 0 value from cmd.ExecuteNonQuery().
I debugged this and found cmd.Parameters are full with values.
and if i manually run the update command in mysql it works fine.

the table is as follow
column -- Datatype
ShortText -- varchar
title -- varchar
id -- int

Please guide me...

C#
int retVal = 0;
        string shortText = ((TextBox)fmvwShortText.FindControl("txtShortText")).Text.Trim();
        try
        {
            int id = Convert.ToInt32(((Label)fmvwShortText.FindControl("lblShrtTextID")).Text);
            MySqlConnection con = new MySqlConnection(System.Configuration.ConfigurationManager.AppSettings["conn"]);
            cmd = new MySqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "UPDATE temp_posts SET ShortText=@shrtText WHERE id=@id AND Title=@title";
            cmd.Parameters.Add("@shrtText", MySqlDbType.VarChar).Value = shortText;
            cmd.Parameters.Add("@title", MySqlDbType.VarChar).Value =Session["EditTitle"].ToString();
            cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = id;
            con.Open();
            retVal = cmd.ExecuteNonQuery();
            con.Close();
        }
        catch (Exception e) { }
        return retVal;


Thanks.
Posted

Quote:
this returns 0 value from cmd.ExecuteNonQuery().


cmd.ExecuteNonQuery()

returns number of records affected after updating the table. As your output is 0 means 0 number of rows affected after execution of the query.

You must check your Update query in Where clause mainly
in
Session["EditTitle"].ToString();
because this is a varchar it may have spaces which may differ form your available records.
 
Share this answer
 
Comments
Manish Kumar Namdev 9-Jul-12 4:48am    
Dear Sanjay, Thanks for efforts. I have debugged many times all the values are in cmd.parameter. Still not updating.
Sanjay Kunjam 9-Jul-12 4:52am    
OK. Try this.

cmd.Parameters.AddWithValue("@shrtText", shortText);
cmd.Parameters.AddWithValue("@title", Session["EditTitle"].ToString());
cmd.Parameters.AddWithValue("@id",id);
Hi,
It seems that your font-end is not giving any error. The error is from sql table or database itself. Try tracing the query which you are passing from front-end using sql-profiler[^].

This will help you to find your solution.
All the best.
--Amit
 
Share this answer
 
The easiest approach for you here is to put a breakpoint on cmd.ExecuteNonQuery().
Pick up the query and paste it in SQL Management studio.

Put appropriate parameters and then run the query.
This would give you an idea of what could be going wrong with the query.
 
Share this answer
 
Comments
Manish Kumar Namdev 9-Jul-12 3:59am    
Thanks to join this thread
I have tried this many times it's not worthy.
Still not able to update the table
I thanks to everyone who put their best effort for this problem.

Finally got the solution.

In my code I used @ in cmd.CommandText and in parameters.
But when I replace this @ with ? both in cmd.CommandText and in parameters and used the cmd.ExecuteScalar(); this worked.

As one of my dear friend "Mohsin" helped me to make me understand why this (?) worked.
Actually Parameter names depend on the provider. When using the provider for
SQL Server, it should start with @ (e.g. @param1). For Oracle
provider, it should start with a colon (...for e.g. aram1. For
OleDb provider, just a question mark (?) would work



Thank you everyone to contribute your best... many thanks


But i'm still left with a question that ExecuteScalar() is updating the records in the database? I am with no answer... looking for this.
 
Share this answer
 
I thanks to everyone who put their best effort for this problem.

Finally got the solution.

In my code I used @ in cmd.CommandText and in parameters.
But when I replace this @ with ? both in cmd.CommandText and in parameters and used the cmd.ExecuteScalar(); this worked.

As one of my dear friend "Mohsin" helped me to make me understand why this (?) worked.
Actually Parameter names depend on the provider. When using the provider for
SQL Server, it should start with @ (e.g. @param1). For Oracle
provider, it should start with a colon (...for e.g. aram1. For
OleDb provider, just a question mark (?) would work



Thank you everyone to contribute your best... many thanks


But i'm still left with a question that ExecuteScalar() is updating the records in the database? I am with no answer... looking for this.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900