i am tring to call stored procedure from asp net
Hi all,
I am trying to call from asp.net a stored procedure using c#. some reason, my stored procedure is not being executed. Please help me as soon as possible. I am struggling with it last three weeks. HERE IS MY CODE FROM asp.net page:
SqlConnection conn;
SqlCommand comm;
SqlParameter myParameter;
Response.Write("1");
conn = new SqlConnection( my connection string);
comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "sp_insertShoe";
comm.CommandType = CommandType.StoredProcedure;
myParameter = comm.CreateParameter();
myParameter.ParameterName = "@tmpUserName";
myParameter.Direction = ParameterDirection.Input;
myParameter.SqlDbType = SqlDbType.VarChar;
myParameter.Size= 15;
myParameter.Value = (Session["userName"]);
comm.Parameters.Add(myParameter);
myParameter = comm.CreateParameter();
myParameter.ParameterName = "@tmpCondition";
myParameter.Direction = ParameterDirection.Input;
myParameter.SqlDbType = SqlDbType.VarChar;
myParameter.Size= 4;
myParameter.Value = (ddlCondition.SelectedValue).Trim();
comm.Parameters.Add(myParameter);
myParameter = comm.CreateParameter();
myParameter.ParameterName = "@tmpSalePrice";
myParameter.Direction = ParameterDirection.Input;
myParameter.SqlDbType = SqlDbType.Money;
myParameter.Value = (txtSalesPrice.Text).Trim();
comm.Parameters.Add(myParameter);
myParameter = comm.CreateParameter();
myParameter.ParameterName = "@tmpInformation";
myParameter.Direction = ParameterDirection.Input;
myParameter.SqlDbType = SqlDbType.Text;
myParameter.Value = (txtInformation.Text).Trim();
comm.Parameters.Add(myParameter);
myParameter = comm.CreateParameter();
myParameter.ParameterName = "@tmpQuantity";
myParameter.Direction = ParameterDirection.Input;
myParameter.SqlDbType = SqlDbType.SmallInt;
myParameter.Value = (txtQuantity.Text).Trim();
comm.Parameters.Add(myParameter);
myParameter = comm.CreateParameter();
myParameter.ParameterName = "@tmpUPC";
myParameter.Direction = ParameterDirection.Input;
myParameter.SqlDbType = SqlDbType.VarChar;
myParameter.Size= 20;
myParameter.Value = (txtUPC.Text).Trim();
comm.Parameters.Add(myParameter);
myParameter = comm.CreateParameter();
myParameter.ParameterName = "@tmpBrandName";
myParameter.Direction = ParameterDirection.Input;
myParameter.SqlDbType = SqlDbType.VarChar;
myParameter.Size= 25;
myParameter.Value = (txtBrandName.Text).Trim();
comm.Parameters.Add(myParameter);
myParameter = comm.CreateParameter();
myParameter.ParameterName = "@tmpModelNumber";
myParameter.Direction = ParameterDirection.Input;
myParameter.SqlDbType = SqlDbType.VarChar;
myParameter.Size= 20;
myParameter.Value = (txtModelNumber.Text).Trim();
comm.Parameters.Add(myParameter);
myParameter = comm.CreateParameter();
myParameter.ParameterName = "@tmpColour";
myParameter.Direction = ParameterDirection.Input;
myParameter.SqlDbType = SqlDbType.VarChar;
myParameter.Size= 20;
myParameter.Value = (txtColour.Text).Trim();
comm.Parameters.Add(myParameter);
myParameter = comm.CreateParameter();
myParameter.ParameterName = "@tmpSizeShoe";
myParameter.Direction = ParameterDirection.Input;
myParameter.SqlDbType = SqlDbType.VarChar;
myParameter.Size= 15;
myParameter.Value = (ddlSize.DataTextField).Trim();
comm.Parameters.Add(myParameter);
myParameter = comm.CreateParameter();
myParameter.ParameterName = "@tmpShoeType";
myParameter.Direction = ParameterDirection.Input;
myParameter.SqlDbType = SqlDbType.VarChar;
myParameter.Size= 20;
myParameter.Value = (ddlShoeType.DataTextField).Trim();
comm.Parameters.Add(myParameter);
myParameter = comm.CreateParameter();
myParameter.ParameterName = "@tmpID";
myParameter.Direction = ParameterDirection.Output;
myParameter.SqlDbType = SqlDbType.Int;
myParameter.Size= 4;
comm.Parameters.Add(myParameter);
conn.Open();
comm.ExecuteNonQuery();
conn.Close();
HERE IS MY CODE FROM my STORED PROCEDURE:
alter procedure sp_insertShoe
@tmpUserName varchar(15),
@tmpCondition varchar(4),
@tmpSalePrice smallmoney,
@tmpInformation text,
@tmpQuantity smallint,
@tmpUPC varchar(20),
@tmpBrandName varchar(25),
@tmpModelNumber varchar(20),
@tmpColour varchar(20),
@tmpSizeShoe varchar(15),
@tmpShoeType varchar(20),
@tmpID bigint
AS
declare @tmpDate smalldatetime;
declare @tmpShoeID bigint;
declare @tmpProductID bigint;
Begin
insert into shoe ( BrandName, ModelNumber, Colour, SizeShoe, upcCode, ShoeType) Values ( @tmpBrandName, @tmpModelNumber, @tmpColour, @tmpSizeShoe, @tmpUPC, @tmpShoeType)
SET @tmpShoeID = @@IDENTITY
SET @tmpID = @tmpShoeID
SET @tmpDate = getdate()
insert into Product(seller, condition, advertiseDate, additionalInformation, SalePrice, quantity) Values (@tmpUserName, @tmpCondition, @tmpDate, @tmpInformation, @tmpSalePrice, @tmpQuantity)
SET @tmpProductID = @@IDENTITY
insert into linkProduct(itemID, productID, tableName) Values( @tmpShoeID, @tmpProductID, 'Shoe')
end