how to update a table in sql 2008r2

Posted by Shanky11 under Sql Server on 3/4/2013 | Points: 10 | Views : 1813 | Status : [Member] | Replies : 24
how to udate a table in sql server 2008
i have four column to update ............................i have a form in vb.net
four filed to update

username,lastname.mobile emailid
how can i update???????????????????????




Responses

Posted by: Santhi on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
Hi

Write update query.
Example
Update tablename set username= value,lastname=value,mobile=value,emailid=value.
Value refers to the value you need to update.
You create sqlconnection and in the sqlcommand pass this query and execute it through VB.NET

Thanks & Regards,
Santhi .V

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

Posted by: Shanky11 on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
hello santhi.................
the pblm is how can i define where condition
update table reg set uname-''shankar ,name =''mishra, mob=5565656,emaiid='abc@gmailcom'
and where conditoin???????????????????/

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

Posted by: Santhi on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
Hi

There would have been some identity column right.
From where you are Fetching the records?
You will display records on the textboxes by any identity column right.
Just pass that value in where condition.
Can you answer to my question.
You wanted to update the record which is already saved in the database right.


Thanks & Regards,
Santhi .V

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

Posted by: Shanky11 on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
hello santhi
yes column is already is there in database
userid column i define it uniqueidentifier
.........................................on a form user retrieve his/her info after that they want to update it....how can they perform this action??????????????


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

Posted by: Santhi on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
Hi

No probs, if you dont have identity column also.
Actually you are updating the column values which is already saved in the database.
So fetch those values and save it in some variables.
Pass those variables in the where condition.
Example:
Update tablename set uname=value,lname=value where mobilenumber='already saved value'
If the mobilenumber is unique you can do like this

Thanks & Regards,
Santhi .V

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

Posted by: Shanky11 on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
see
i have five column
userid as uniqueidentifier
username
lastname
mobile
emailid there is a edit btn
on click button of edit
user can edit theh value already shows in textbox and he/she can update this

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

Posted by: Santhi on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
Hi

Fetch that userid column while you are retrieving it.
Pass that column value in where condition.



Thanks & Regards,
Santhi .V

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

Posted by: Santhi on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
Hi

For Example:

Fetch all the columns what you need along with the userid.
Select * from tablename
You will display the records in any controls.
string userid = string.empty;(if it is string);

if(dt.rows.count > 0)
{
userid = dt[0]["userid"].ToString();
}
On the edit button click event.
Now you pass this userid in the where condition.
if you have any queries tell me

Thanks & Regards,
Santhi .V

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

Posted by: Shanky11 on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down

See my code
now tell me what i have to do??????????????

Public Sub updatedetails()
sqlcon = New SqlConnection("data source=I3-001\SQL2008R2; initial catalog=SmsDb; user id=sa; password=bss123")
sqlcon.Open()
cmd1 = New SqlCommand("update registration set username='" + txtuserNameEdit.Text + "',lastname='" + txtLastName.Text + "',Mobile=" + txtMobile.Text + ",emailID='" + txtEmailID.Text + "' where mobile =" + txtMobile.Text + " ", sqlcon)
cmd1.ExecuteNonQuery()
sqlcon.Close()
End Sub

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

Posted by: Santhi on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
Hi

Can you tell me where you are showing the data.
It has been retrieved from the database.
Retrieve the userid along with that.
Tell me your scenario clearly.
This is more than enough.
You are done already.
Now you need to pass the userid.

Thanks & Regards,
Santhi .V

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

Posted by: Shanky11 on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
In a frm
now i want to update these four filds
1 more column userid but i m not showing it........i hav to show only 4

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

Posted by: Shanky11 on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
See i m pasting my code below.....................................but i can't do like this beacz if user gonna to havechange the field in where condition also
then it will show error

Public Sub updatedetails()
sqlcon = New SqlConnection("data source=I3-001\SQL2008R2; initial catalog=SmsDb; user id=sa; password=bss123")
sqlcon.Open()
cmd1 = New SqlCommand("update registration set username='" + txtuserNameEdit.Text + "',lastname='" + txtLastName.Text + "',Mobile=" + txtMobile.Text + ",emailID='" + txtEmailID.Text + "' where mobile =" + txtMobile.Text + " ", sqlcon)
cmd1.ExecuteNonQuery()
sqlcon.Close()
End Sub

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

Posted by: Oswaldlily on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
userid is your uniqueidentifier .
so use userid in wherecondition.......
where userid=txtUserid.text ..

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

Posted by: Shanky11 on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
i m not displaying userid in that form .
there is no need to show it.....
so i m not showing it......
then how can i overcome ths pblm???????????/

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

Posted by: Santhi on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
Hi

You no need to show that...
Save it in some variable and then use that on the where condition.


Thanks & Regards,
Santhi .V

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

Posted by: Oswaldlily on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
whether userid values and username values from database are same?

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

Posted by: Shanky11 on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
userid and username both are dffferent columns name
database is same

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

Posted by: Oswaldlily on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
yes different column names...whether their values are same or different?

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

Posted by: Shanky11 on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
how can they same??it is differen
because one is userid uniqueidentifier nad another username

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

Posted by: Oswaldlily on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
display userid in design page and make it invisible......

u have to bring like if(txtUsername.Text="username 4m table")
{
_userid= "userid of that username";

}

Now get those values and use in where condiiton

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

Posted by: Santhi on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down
Hi

This link contains an example for what you need.
Refer this link
http://www.aspdotnet-suresh.com/2011/02/how-to-inserteditupdate-and-delete-data.html

Thanks & Regards,
Santhi .V

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

Posted by: Oswaldlily on: 3/4/2013 [Member] Starter | Points: 25

Up
0
Down


Create procedure spname
(
@username varchar(10),
@pwd varchar(10)
)
as

begin
set nocount on

declare @userid varchar(10)
set @userid=(Select UD_UserId from User_Details where UD_User_Name=@username )

update tablename set column1=@username where Ud_User_Id=@userid

set nocount off
end

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

Posted by: Muhsinathk on: 3/7/2013 [Member] Bronze | Points: 25

Up
0
Down
Hi Shanky11 ,

Please execute the following SQL script;
Use 'userid' in where condion because 'userid' is primary key.so its unique.

DECLARE @UserID AS UNIQUEIDENTIFIER;
DECLARE @mobile AS VARCHAR(20);
DECLARE @username AS VARCHAR(20);
DECLARE @lastname AS VARCHAR(20);
DECLARE @emailID AS VARCHAR(20);

SET @UserID= ''; --Example :- @UserID='3A2BBBE8-B153-46EE-BE20-A0D100C91542';
SET @mobile= ''; --Example :- @MobileNo=txtMobile.Text;
SET @username=''; --Example :- @Username=txtuserNameEdit.Text;
SET @lastname=''; --Example :- @Lastname=txtLastName.Text ;
SET @emailID= ''; --Example :- @EmailID=txtMobile.Text

BEGIN
UPDATE registration set username=@username,lastname=@lastname,Mobile=@mobile,emailID=@emailID WHERE userid=@UserID;
END

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

Posted by: Muhsinathk on: 3/7/2013 [Member] Bronze | Points: 25

Up
0
Down

Please mark as answer if it helpful to you..That helps others who search the same..

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

Login to post response