Use Stored Procedure as UpdateCommand

nickoy · March 31st, 2009, 02:00 PM

Hi All,
I have created a stored procedure to check concurrency before allowing updates or deletions on a table in my sql dbase.
The problem is that i am not able to pass the parameters from the Grid View to the stored procedure. Please see blelow for my stored procedure and c# code where i am attempting to pass the values form the row being edited to the stored procedure. It's not working though.

Please reply as to the solution for this problem.

Stored Procedure

Code:


ALTERPROCEDURE [dbo].[updateSupplier]
@SupplierId smallint,
@SupplierName nvarchar(100),
@Address nvarchar(100),
@ContactPerson nvarchar(50),
@Phone char(10),
@Fax char(10),
@Email nvarchar(50),
@Concurrency smalldatetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SETNOCOUNTON;
Update Supplier
Set SupplierName=@SupplierName, Address=@Address, ContactPerson=@ContactPerson,
Phone=@Phone, Fax=@Fax, Email=@Email, Concurrency=GetDate()
Where SupplierID=@SupplierID AND Concurrency=@Concurrency
IF@@ROWCOUNT= 0
BEGIN
IFEXISTS(SELECT SupplierID FROM Supplier 
WHERE SupplierID = @SupplierID )
RETURN 2 -- Concurrency conflict
ELSE
RETURN 1 -- The record has been deleted
END
ELSE
RETURN 0 -- The record could be updated
 
END

C# Code

Code:

protectedvoid Supplier_Updating(object sender, EventArgs e)
{
SqlCommand cmd = newSqlCommand("updateSupplier", sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(newSqlParameter("@SupplierID", SqlDbType.SmallInt,"SupplierID"));
 
cmd.Parameters.Add(newSqlParameter("@SupplierName", SqlDbType.NVarChar, 100,"SupplierName"));
 
cmd.Parameters.Add(newSqlParameter("@SupplierAddress", SqlDbType.NVarChar,100, "Address"));
 
cmd.Parameters.Add(newSqlParameter("@ContactPerson", SqlDbType.NVarChar,100, "ContactPerson"));
 
cmd.Parameters.Add(newSqlParameter("@Phone", SqlDbType.Char,10,"Phone"));
 
cmd.Parameters.Add(newSqlParameter("@Fax", SqlDbType.Char,10,"Fax"));
 
cmd.Parameters.Add(newSqlParameter("@Email", SqlDbType.NVarChar,50,"Email"));
 
cmd.Parameters.Add(newSqlParameter("@Concurrency", SqlDbType.SmallDateTime,"Concurrency"));
 
 
try
{
 
sqlcon.Open();
cmd.ExecuteNonQuery();
 
}
catch
{
thrownewApplicationException("Data Error");
}
finally
{
sqlcon.Close();
}
 
}

gbianchi · March 31st, 2009, 02:17 PM

Hello. do you receive any kind of error?? why you say that you can't pass the parameters???

nickoy · March 31st, 2009, 03:07 PM

Hi,
The error is:
Procedure or Function 'updateSupplier' expects parameter '@SupplierId', which was not supplied.

Please reply as to how i could correct this.

gbianchi · March 31st, 2009, 11:03 PM

I can see two things but I don't know where is the error.

First the parameter @SupplierId is defined in the code as @SupplierID.

second the parameter @SupplierAddress has a different name in the SP.

nickoy · April 1st, 2009, 10:24 AM

Thank You. I did the corrections; but i'm still getting the error re: the SupplierID not being supplied to the stored procedure. I've added another line of code:

cmd.Parameters["@SupplierID"].SourceVersion = DataRowVersion.Original;

But the error still remains.

gbianchi · April 1st, 2009, 11:10 AM

Mmmm.. I will take a shoot, but the parameter name is "@SupplierId" I don't know if that can cause and error. But if you have any doubt rename the parameter to another silly name just to try it...

nickoy · April 1st, 2009, 11:51 AM

I've been trying to get the parameters another way; please see below for snippet, now i'm getting the error Object reference not set to an instance of the object. I've highlighted the line with the error. Please check it and reply with suggestion(s).

Code:

 
private string supid;
 
protectedvoid Supplier_Updating(object sender, GridViewUpdateEventArgs e)
{
SqlCommand cmd = newSqlCommand("updateSupplier", sqlcon);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(newSqlParameter("@SupplierID", SqlDbType.SmallInt, 5, "SupplierID"));
TextBox id = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("SupplierID"));
supid = id.Text;
cmd.Parameters["@SupplierID"].Value = supid;

gbianchi · April 1st, 2009, 12:15 PM

Try renaming the parameter to see what is happening!!

Also, which object is empty?? (debug and check it).

nickoy · April 1st, 2009, 02:05 PM

It's Solved

. Thank you for your help.The problem was with the parameters; in the source file i had listed several parameters within the <updateParameters> tag. These were actually creating a fresh set of parameters which were not connected to the Grid View. I would have had to map them to the grid View in the code from behind. So that's why i was getting the error '@SupplierID not supplied'.

So what i've done is delete the entire <UpdateParameters> tag in the source code and specify that my update command is a stored procedure also i specified that
onUpdating="SuppliersUpdating" on the SqlDataSource.

In the code from behind i used the following snippet:

Code:

protected void Suppliers_Updating(object sender, EventArgs e)
{
GridView1.DataBind();
}