[ASP.NET and SQL] Stored Procedure and gridview update

[Artamir]

Hello,

I have a table called t_data containing the following columns:

id, iYear, iMonth, iValue, fk_t_relation_id

The fk_t_relation_id column is linked to the id of a project table so for a single year, 12 rows in t_data have the same fk_t_relation_id. In my gridview I use a stored procedure to show the data from each project on a single row with all the months in that row instead of 12 rows for a single year. The problem comes when I try to edit the values in my grid.

I use the following stored procedure:

ALTER PROCEDURE [dbo].[p_updateData]

@iYear int,
@id int,
@iJan int,
@iFeb int,
@iMar int,
@iApr int,
@iMay int,
@June int,
@July int,
@Aug int,
@Sept int,
@Oct int,
@Nov int,
@Dec int,

AS
BEGIN

UPDATE t_data
SET iValue = @iJan
WHERE fk_t_relation_id = @id AND
iYear = @iYear AND
iMonth = 1

UPDATE t_data
SET iValue = @iFeb
WHERE fk_t_relation_id = @id AND
iYear = @iYear AND
iMonth = 2

And so on for each month.

@id in my gridview corresponds to the id of the project in t_relation, or the value of fk_t_relation_id in t_data. The procedure works fine when testing it in SQL Server 2005 but I do get problems when testing it in my application. I get no error message but the update simply doesn't go through.

When running the application in debug mode and editing entries, it seems to only check the ItemTemplates and not the EditTemplates. The Datasource seems correct the and UpdateCommand points to the right stored procedure. I get no errors or exceptions either.

I have tried adding the values iJan, iFeb, etc. to the DataKeyNames in the <asp:gridview> tag but that didn't help.

I really have no clue as to how to fix this. Any help will be appreciated. Please do tell if you need more information as I am new to the subject.

[GR7]

I would recommend to use the SQL Profiler to figure out the exact statement that SQL is receiving to execute.

Since you said its working when testing it in SQL, probably you're not filling the values correctly from the ASP.NET app. First use the sql profiler to determine exactly what SQL is being sent.

you just start the profiler, then do the update in your application and stop the profiler, so you can find the specific row in the profiler that was sent by the ASP.NET app.

hope this helps.

[Artamir]

Hey, thanks for the reply.

I tried using the Profiler but I did not have required the permissions since I am not the db owner. I did find another way to see what parameters were passed and how they were passed. It turns out the @iYear parameter wasn't passed correctly, it always returned a null value. Everything works now. :)