Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 1193
Home > Forums > ASP.NET >

help in updation of table

Posted by Jammyhunt under ASP.NET on 8/4/2013 | Points: 10 | Views : 2723 | Status : [Member] | Replies : 25
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 


i have this table named general

i want to update recover amount column using ac no in where clause..

value of recover amount will be total of a grid view which is below..

table1(gridview)

sl.no name salary

1 raj 1000

2 aman 1200

3 khan 900

4 jammy 1100

total 4200

i have already calculated total of this gridview using rowdatabound event..

now i want to update recover amount column with total of this gridview in where clause field will be ac no

please tell the necessary steps.



Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Jammyhunt on: 8/4/2013 [Member] Starter | Points: 25

Up
0
Down
bro i understood and tried your code but still it doesnt work.. its really frustrating.. i am looking for this from 3 days over here..
Can you please join me using team viewer?

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/4/2013 [Member] Starter | Points: 25

Up
0
Down
ok download this file from below link.

http://www.mediafire.com/download/29djd37hxpm7mrg/tosend.rar
Run it and open default.apsx after that click on acount details column. which will redirect you to gend.aspx page.... here i calculated total of amount column, And i want to update general table with this total. general table get show on the page default.aspx

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/5/2013 [Member] Starter | Points: 25

Up
0
Down
Bro have you tested on your system?... because it still doesnt work for me

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Jammyhunt,
Which database you are using? 
"UPDATE general SET Quotename(recover amount) = " + total + " WHERE Quotename(ac no) = '"+ ac + "' ;"

In the above statement total and ac are of type string.
If the above is not working, please post us the exact error message which you got.

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/5/2013 [Member] Starter | Points: 25

Up
0
Down
I am using ms-access database..
And yes total is double datatype and ac is string.. which i am assingin using query string.

and error message is this from the begining 

 Syntax error in UPDATE statement.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.



Exception Details: System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.



Source Error:


Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Make total variable type as string and test it again.. Let us know the result...
Because query string is of String type. so we can't concatenate it with double variable

Another thing is you should use quotes for table name also.. because general may be reserved word in Ms-Access
Better to use

"UPDATE QUOTENAME(general) SET Quotename(recover amount) = " + total + " WHERE Quotename(ac no) = '"+ ac + "' ;"

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/5/2013 [Member] Starter | Points: 25

Up
0
Down
still the same error

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Another thing is you should use quotes for table name also.. because general may be reserved word in Ms-Access
Better to use 

"UPDATE QUOTENAME(general) SET Quotename(recover amount) = " + total + " WHERE Quotename(ac no) = '"+ ac + "' ;"


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/5/2013 [Member] Starter | Points: 25

Up
0
Down
its giving me error that ';' expected

will you please look at my code, rectify it and test on your system... please

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
I don't have Ms-Access to test your code....
May be you forgot to put semicolon for Query string.. try this...
"UPDATE QUOTENAME(general) SET Quotename(recover amount) = " + total + " WHERE Quotename(ac no) = '"+ ac + "';" ;

If you get error again post us the present code which you have now..

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/5/2013 [Member] Starter | Points: 25

Up
0
Down
still same error 

ac = Request.QueryString["ac_no"];



            double amt = Convert.ToDouble(total);



            OleDbCommand com = new OleDbCommand();

            OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|daksh.mdb;Persist Security Info=False;");

            com.Connection = con;

            com.Connection.Open();

            com.CommandText = "UPDATE QUOTENAME(general) SET Quotename(recover amount) = " + amt + " WHERE Quotename(ac no) = '" + ac + "';"; 

            com.ExecuteNonQuery();

            con.Close();


Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
ac = Request.QueryString["ac_no"]; means your column name is ac_no ?
The column names in the table are ac_no , recover_amount 

com.CommandText = "UPDATE QUOTENAME(general) SET Quotename(recover_amount) = " + total + " WHERE Quotename(ac_no) = '" + ac + "';"; 




ac = Request.QueryString["ac_no"];



            double amt = Convert.ToDouble(total);



            OleDbCommand com = new OleDbCommand();

            OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|daksh.mdb;Persist Security Info=False;");

            com.Connection = con;

            com.Connection.Open();

            com.CommandText = "UPDATE QUOTENAME(general) SET Quotename(recover amount) = " + total + " WHERE Quotename(ac no) = '" + ac + "';"; 

            com.ExecuteNonQuery();

            con.Close();


Note: otherwise use parameterized method
http://stackoverflow.com/questions/4589348/microsoft-access-update-command-using-c-sharp-oledbconnection-and-command-not-wo

modified update statement with ac no, recover amount.... so whats the problem here....
go through the following link for UPDATE statement
http://hyderabadtechies.info/index.php?option=com_content&view=article&id=1913:c-select-insert-update-delete-codes-with-ms-access&catid=39:articles
Follow the order as per the link



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/5/2013 [Member] Starter | Points: 25

Up
0
Down
in database column name ac no and recover amount but when it comes to gridview it is ac_no and recover_amount

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Where are you assigning/getting total value ? that is not in the above code... verify that also...
try to put a break point and copy the com.CommandText value at runtime. post us the same

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/5/2013 [Member] Starter | Points: 25

Up
0
Down
i am assigning total value to the column named recover amount which exist in general table

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
No... I'm talking about the total variable value?
SET Quotename(recover amount) = " + total +

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/5/2013 [Member] Starter | Points: 25

Up
0
Down
this is my exception message 

System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at gend.GridView2_RowDataBound(Object sender, GridViewRowEventArgs e) in c:\Users\jammy\Documents\Visual Studio 2010\WebSites\tosend\gend.aspx.cs:line 67


Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/5/2013 [Member] Starter | Points: 25

Up
0
Down
i am getting total value from the footer of gridview using rowdatabound event and assigning it to general table inside recover amount field

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
These links might help you
http://social.msdn.microsoft.com/Forums/vstudio/en-US/cf321d2b-339a-45f9-9bd5-8f543538771c/oledb-command-update-syntax-error
http://forums.asp.net/t/1858844.aspx/1?Grid+Update+System+Data+OleDb+OleDbException+0x80040E14+Syntax+error+in+UPDATE+statement+

Post us the runtime UPDATE query by putting break point at GridView2_RowDataBound() method and then copy com.CommandText value.

please look into sample application
http://www.codeproject.com/Articles/607868/Social-Club-Sample-application-using-WinForms-Csha

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/5/2013 [Member] Starter | Points: 25

Up
0
Down
nothing works :(

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Did you check the runtime UPDATE statement?
1) put break point at com.CommanfText
2) Click on F5
3) When the control goes across UPDATE statement you right click on com.CommandText and copy that query
post it back to us

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
try this code 

protected void GridView2_RowDataBound(object sender, GridViewRowEventArgs e)

{

      if (e.Row.RowType == DataControlRowType.DataRow)

      {

            string AccountNum = GridView2.DataKeys[e.Row.RowIndex].Values[0].ToString();  //Assumed that the index of ac_no in the grid is 0 

	    int total = 0;

	    

	string ConnString = Utils.GetConnString();

	string SqlString = "Update [general] Set [recover amount]= ? WHERE [ac no]= ?";

	using (OleDbConnection conn = new OleDbConnection(ConnString))

	{

		using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))

		  {

			cmd.CommandType = CommandType.Text;

			cmd.Parameters.AddWithValue("@RecAmount", total );  //Assign total variable value before use  

			cmd.Parameters.AddWithValue("@AccNum", Request.QueryString["ac_no"]);

			conn.Open();

			cmd.ExecuteNonQuery();

		  }

	}



     }

}


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/5/2013 [Member] Starter | Points: 25

Up
0
Down
com.CommandText = "UPDATE QUOTENAME(general) SET Quotename(recover_amount) = " + total + " WHERE Quotename(ac_no) = '" + ac + "';";

you asking for this?

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jammyhunt on: 8/5/2013 [Member] Starter | Points: 25

Up
0
Down
gridview3 doesnt exist in my corrent context...

will you please explain me this 

protected void GridView2_RowDataBound(object sender, GridViewRowEventArgs e)



{



      if (e.Row.RowType == DataControlRowType.DataRow)



      {



            string AccountNum = GridView3.DataKeys[e.Row.RowIndex].Values[0].ToString();



	    int total = 0;


Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
That was typo... refer my earlier reply ( Posted on: 8/5/2013 6:54:29 AM) and do not forget to get the value of "total" variable

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jammyhunt, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts