Update statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update statement

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2006-08-11 : 03:56:52

I have one table test with following data

No1 No2 Amt
1 1 100
1 2 200
1 3 100
1 4 300

I want to increase or decrease the Amt field with percentage input.

suppose my input is increase by 10% then my table will be updated as follow

No1 No2 Amt
1 1 110
1 2 220
1 3 110
1 4 330

Please tell me how I can update the column.

I know the logic

declare @amt money
select @amt = (((100 * 10)/100))
select @amt = 100 + @amt
select @amt

but how i can implement this logic in update statement.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-11 : 04:00:51

[code]update table
set Amt = Amt * (100 + 10) / 100[/code]

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-11 : 12:04:30

khtan,

quote:

update table
set Amt = Amt * (100 + 10) / 100

I don't think that's exactly right. To increase by 10%, just multiply by 1.1:


update table
set Amt = Amt * 1.1

Ken

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-11 : 20:38:13

quote:
I don't think that's exactly right

Are you referring to result of int divide by int = int : (100 + 10) / 100 = 1 ?
Actully for the requirement, it would not make any different even if the data type for Amt is int as it will be multiply by 110 before divide by 100.


declare @tblint table
(
	Amt	int
)

declare @tblmoney table
(
	Amt	money
)

insert into @tblint
select	100 union all
select	200 union all
select	100 union all
select	300		

insert into @tblmoney
select	100 union all
select	200 union all
select	100 union all
select	300		

declare @markup int
select	@markup = 10	-- set to -10 for mark down by 10%

update @tblint set Amt = Amt * (100 + @markup) / 100

update @tblmoney set Amt = Amt * (100 + @markup) / 100

select * from @tblint

/* RESULT :
	Amt         
	----------- 
	110
	220
	110
	330
*/

select * from @tblmoney

/* RESULT :
	Amt                   
	--------------------- 
	110.0000
	220.0000
	110.0000
	330.0000
*/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-14 : 12:02:35

But if you have values like 435 then the datatype with int will result to truncation

Madhivanan

Failing to plan is Planning to fail

shybi
Starting Member

41 Posts

Posted - 2006-08-22 : 01:55:39

I want to update a datatable in the datagrid.the datagrid contains field from 5 tables.how can i write a update statement. the fields are UserID,FirstName,LastName,Dept,Location,Company,Groups.
the tables are Users,LocationDetail,DeptDetail,CompanyDetail,GroupDetail.when i click on the edit button on the datagrid,all the feilds in the row become editable means textboxes. when i click the update it should get updated? how will i fetch the values from the textboxes and then updated it?

can i write like this:
string UserID = e.Item.Cells(1).Text;

//Reference each TextBox
TextBox FirstName = e.Item.Cells(2).Controls(0);
TextBox LastName = e.Item.Cells(3).Controls(0);
TextBox DeptID = e.Item.Cells(4).Controls(0);
TextBox LocationID = e.Item.Cells(5).Controls(0);
TextBox CompanyID = e.Item.Cells(6).Controls(0);
TextBox GroupID = e.Item.Cells(7).Controls(0);

string sselect2 = "Update Users set UserID = '" + UserID + "',FirstName = '" + FirstName + "'," +
" LastName = '" + LastName + "',DeptID = '" + DeptID + "',CompanyID = '" + CompanyID + "'," +
" LocationID = '" + LocationID + "',GroupID = '" + GroupID + "' where UserID = '" + UserID + "' ";
SqlCommand cmd5 = new SqlCommand(sselect2, objConnect3);
cmd5.ExecuteNonQuery();

DataGrid1.EditItemIndex = -1;
string sselect3 = ViewState["sselect2"].ToString();
dt11 = GetDataTable(sselect3,objConnect3);
DataGrid1.DataSource = dt11;
DataGrid1.DataBind();

but its giving an error for cells that it denotes a property where method is expected??

shybi
shybi

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-22 : 03:59:40

I dont think the error which you are getting is due to the update statement.
Try this..


 string sselect2 = "Update Users set UserID = '" + UserID.Text().....

you are directly calling the TextBox datatype, you need to call its function call Text to retrive the values which are there in that text box.

Chirag

shybi
Starting Member

41 Posts

Posted - 2006-08-22 : 08:47:07

I made the above statement like these:

string strConnect3 = "user id=sa;password=sa;data source=data;persist security info=False;initial catalog=Northwind";
SqlConnection objConnect3 = new SqlConnection(strConnect3);
objConnect3.Open();

DataGrid1.EditItemIndex = e.Item.ItemIndex;
string sselect2 = ViewState["strSelect5"].ToString();
dt11 = GetDataTable(sselect2,objConnect3);
DataGrid1.DataSource = dt11;
DataGrid1.DataBind();

// System.Web.UI.WebControls.TextBox t = null;
TextBox UserID = (System.Web.UI.WebControls.TextBox) e.Item.Cells[1].Controls[0];
TextBox FirstName = (System.Web.UI.WebControls.TextBox) e.Item.Cells[2].Controls[0];
TextBox LastName= (System.Web.UI.WebControls.TextBox) e.Item.Cells[3].Controls[0];
TextBox DeptID = (System.Web.UI.WebControls.TextBox) e.Item.Cells[4].Controls[0];
TextBox CompanyID = (System.Web.UI.WebControls.TextBox) e.Item.Cells[5].Controls[0];
TextBox LocationID = (System.Web.UI.WebControls.TextBox) e.Item.Cells[6].Controls[0];
TextBox GroupID = (System.Web.UI.WebControls.TextBox) e.Item.Cells[7].Controls[0];

now what happens is when i click on a particular row for updating, dt11 contains all the columns in the table instead of one row which i selected as a result its throwing the error :
specified argument was out of range of valid values.
i know whats creating the problem. but i am not able to crack down the error.
when i click on the update button,there will be postback,and i am collecting the values in a datatable dt11,but instead of one row, its getting all the rows?can anyone tell me how can i get only one row value to get updated???

shybi

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-22 : 09:06:24

what are you doing in this function??
GetDataTable()

Chirag

shybi
Starting Member

41 Posts

Posted - 2006-08-23 : 01:11:58

private DataTable GetDataTable(string sql,SqlConnection conn)
{
DataTable oDT = new DataTable();
SqlDataAdapter dataadapter = null;

dataadapter = new SqlDataAdapter(sql,conn);
dataadapter.Fill(oDT);
return oDT;
}

shybi
Starting Member

41 Posts

Posted - 2006-08-24 : 09:42:26

how can i get the value from the datagrid textbox. when i click the update button,all the columns become textboxes.when i try to get the value from the textboxes,its not possible.how can i do it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-24 : 11:02:41

It is better to post your question at .NET related forum

Madhivanan

Failing to plan is Planning to fail

shybi
Starting Member

41 Posts

Posted - 2006-08-28 : 01:48:58

i have posted my question in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71023

shybi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources