ASP.NET

Update Command

Please support our ASP.NET advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Mar 2008

Posts: 4

Reputation: Enjoy is an unknown quantity at this point

Solved Threads: 0

Enjoy

Offline

Newbie Poster

Update Command

Oct 8th, 2008

Hi all,
I am using asp and vb and back end is SQL .
I am getting syntax error in update command. Error is Incorrect Syntax near keyword "desc".
Can anyone debug this.
R can you tell me an alternative way to update record in database.
I have to check 3conditions in order to update a record.
This is the statement i have given.

com = New SqlCommand("Update prod_details Set price ='" & price.Text & "',qty='" & qty.Text & "',desc='" & desc.Text & "' where cname='" & cname.Text & "' and pname= '" & pname.Text & "' and mno= '" & mno.Text & "'", con)

•

Join Date: Oct 2007

Posts: 6

Reputation: zezo is an unknown quantity at this point

Solved Threads: 0

zezo

Offline

Newbie Poster

Re: Update Command

Oct 9th, 2008

com = New SqlCommand("Update prod_details Set price ='" & price.Text & "',qty='" & qty.Text & "',desc='" & desc.Text & "' where cname='" & cname.Text & "' and pname= '" & pname.Text & "' and mno= '" & mno.Text & "'", con)

try type new not New
and add ; at the end of statement

If you need another way:
on my i use stored procedure and pass values as a parameter and it is more accurate than execute and pass command from .net application.
here is the code:
database code:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
CREATE PROCEDURE ChangeProductDetails
@Price int  , "you specify the data types of parameters according to your fields in database"
@Qty int,
@Desc nvarchar(50),
@Cname nvarchar(50),
@Pname nvarchar(50),
@Mno nvarchar(50)
 AS
update  prod_details 
set        price = @Price,
            qty=@Qty,
            desc = @Desc
where   cname = @Cname and pname= @Pname and mno = @Mno
GO
CREATE PROCEDURE ChangeProductDetails
@Price int  , "you specify the data types of parameters according to your fields in database"
@Qty int,
@Desc nvarchar(50),
@Cname nvarchar(50),
@Pname nvarchar(50),
@Mno nvarchar(50)
 AS
update  prod_details 
set        price = @Price,
            qty=@Qty,
            desc = @Desc
where   cname = @Cname and pname= @Pname and mno = @Mno
GO

asp.net code

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
SqlCommand Cmd_UpdateProducts = new SqlCommand("ChangeProductDetails",con);
			Cmd_UpdateProducts.CommandType = CommandType.StoredProcedure;
			//passing parameters to command
			SqlParameter  Price= Cmd_UpdateProducts.Parameters.Add("@Price",SqlDbType.Int);
			Price.Value = txt_Price.Text;
			SqlParameter Qty = Cmd_UpdateProducts.Parameters.Add("@Qty",SqlDbType.Int);
			Qty.Value = txt_Qty.Text;
			SqlParameter desc = Cmd_UpdateProducts.Parameters.Add("@Desc",SqlDbType.NVarChar);
			desc.Value = txt_desc.Text;
SqlParameter Cname = Cmd_UpdateProducts.Parameters.Add("@Cname",SqlDbType.NVarChar);
			Cname.Value = txt_Cname.Text;
SqlParameter Pname = Cmd_UpdateProducts.Parameters.Add("@Pname",SqlDbType.NVarChar);
			Pname.Value = txt_Pname.Text;
SqlParameter Mno= Cmd_UpdateProducts.Parameters.Add("@Mno",SqlDbType.NVarChar);
			Mno.Value = txt_Mno.Text;
			con.Open();
			Cmd_UpdateProducts.ExecuteNonQuery();
			con.Close();
SqlCommand Cmd_UpdateProducts = new SqlCommand("ChangeProductDetails",con);
			Cmd_UpdateProducts.CommandType = CommandType.StoredProcedure;
			//passing parameters to command
			SqlParameter  Price= Cmd_UpdateProducts.Parameters.Add("@Price",SqlDbType.Int);
			Price.Value = txt_Price.Text;
			SqlParameter Qty = Cmd_UpdateProducts.Parameters.Add("@Qty",SqlDbType.Int);
			Qty.Value = txt_Qty.Text;
			SqlParameter desc = Cmd_UpdateProducts.Parameters.Add("@Desc",SqlDbType.NVarChar);
			desc.Value = txt_desc.Text;
SqlParameter Cname = Cmd_UpdateProducts.Parameters.Add("@Cname",SqlDbType.NVarChar);
			Cname.Value = txt_Cname.Text;
SqlParameter Pname = Cmd_UpdateProducts.Parameters.Add("@Pname",SqlDbType.NVarChar);
			Pname.Value = txt_Pname.Text;
SqlParameter Mno= Cmd_UpdateProducts.Parameters.Add("@Mno",SqlDbType.NVarChar);
			Mno.Value = txt_Mno.Text;
			con.Open();
			Cmd_UpdateProducts.ExecuteNonQuery();
			con.Close();

hope it usefull

•

Join Date: Oct 2008

Posts: 1

Reputation: ganesh_212005 is an unknown quantity at this point

Solved Threads: 0

ganesh_212005 ganesh_212005 is offline

Offline

Newbie Poster

Re: Update Command

Oct 10th, 2008

[QUOTE=Enjoy;707992]Hi all,
I am using asp and vb and back end is SQL .
I am getting syntax error in update command. Error is Incorrect Syntax near keyword "desc".
Can anyone debug this.
R can you tell me an alternative way to update record in database.
I have to check 3conditions in order to update a record.
This is the statement i have given.

[B]com = New SqlCommand("Update prod_details Set price ='" & price.Text & "',qty='" & qty.Text & "',desc='" & desc.Text & "' where cname='" & cname.Text & "' and pname= '" & pname.Text & "' and mno= '" & mno.Text & "'"), con

•

Join Date: May 2008

Posts: 144

Reputation: sierrainfo is an unknown quantity at this point

Solved Threads: 9

sierrainfo sierrainfo is offline

Offline

Junior Poster

Re: Update Command

Nov 18th, 2008

SierraInfo

•

Join Date: Aug 2007

Posts: 182

Reputation: Kusno is an unknown quantity at this point

Solved Threads: 15

Kusno

Offline

Junior Poster

Re: Update Command

Nov 19th, 2008

Hi,
coz desc is reserved keyword.

but you can still use it.
so change it to

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
com = New SqlCommand("Update prod_details Set price ='" & price.Text & "',qty='" & qty.Text & "',[desc]='" & desc.Text & "' where cname='" & cname.Text & "' and pname= '" & pname.Text & "' and mno= '" & mno.Text & "'", con)
com = New SqlCommand("Update prod_details Set price ='" & price.Text & "',qty='" & qty.Text & "',[desc]='" & desc.Text & "' where cname='" & cname.Text & "' and pname= '" & pname.Text & "' and mno= '" & mno.Text & "'", con)