Error in the Stored Procedure

I am trying to swap two rows in a table .. I am stuck with this error since a long time.. can anyone guess where the problem is ? create procedure was working fine in query analyzer but when used it in the stored procedure. I am getting these .. can anyone help me out please ... Your help will be greatly appreciated.. UpdateRowsReorderUp is my storedprocedure ... and i am using MS Sql 2000 .. am I doing something really wrong which i'm not supposed to ???

Thanks friends..

Procedure 'UpdateRowsReorderUp' expects parameter '@.nextlowestsortID', which was not supplied.

CREATE PROCEDURE [dbo].[UpdateRowsReorderUp]

(

@.intsortID int,

@.nextlowestsortID int,

@.MemberID int

)

AS

Select @.nextlowestsortID=(Select Top 1 SortID from SelectedCredits where SortID<@.intsortID order by SortID DESC)

UPDATE SelectedCredits SET SortID= CASE

WHEN SortID = @.nextlowestsortID then @.intsortID

WHEN SortID = @.intsortID then @.nextlowestsortID ELSE SortID End

WHERE MemberID = @.MemberID

SELECT * FROM SelectedCredits WHERE MemberID= @.MemberID ORDER BY SortID

GO

**************

// this is my script on the page

void moveup(Object s, DataListCommandEventArgs e) {

objcmd= new SqlCommand("UpdateRowsReorderUp",objConn);

objcmd.CommandType = CommandType.StoredProcedure;

objcmd.Parameters.Add("@.intsortID",intsortID);

objcmd.Parameters.Add("@.MemberID",Session["MemberID"]);

objRdr= objcmd.ExecuteReader();

dlSelCredits.DataSource = objRdr;

dlSelCredits.DataBind();

objRdr.Close();

objConn.Close();

BindData();

}

You are missing the @.nextlowestsortid parameter in your code. Add it between the other two.
|||

Thanks for your reply . I'm storing a value in the @.nextlowestsortid using a SELECT statement.I am not assigning any value outside so that i can pass it into the parameter. for example @.intsortid i'm assiging intsortid through the code but what can i assign to this? Sorry to ask like this i am a newbie in this field..

Can i write the SELECT statement in different way so that there is no need add any parameter through the code ??

|||If the @.nextlowestsortid parameter is an internal parameter, remove itfrom the parameter list and declare it after the AS, like this:
DECLARE @.nextlowestsortid int
Then you stored procedure will only have two parameters that you supply, and the error will go away.
Sam
|||

Thank you very much Sam .. I am really grateful to you ..

You solved my problem .. that was the perfect solution ..

cheers mate

Error handling in SSIS

Hi!

I guess this is an easy one, but I haven't found a (nice) way to handle "error"-rows.

In my application I load a lot of data, in earlier version I used the Transformation task, which did deliver log and error-files. How do I make the same in SSIS? In the best of worlds I would like to log when a package/task i starting, ending and how many rows are OK and how many rows are errors. For the errors I want a Description what is wrong (FK, PK, NULL value ....) and the complete row in a file so it will be easy to reolad it.

I've tried the logging-feature but when I redirects the error-rows to a text-file, it also eliminates the error text in my log file. So it seems that I have to choose to have the description or the row-records?!

Best, Jocke

Hi,

You can include the error description in the text file using this simple technique: http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx

-Jamie

|||

Thanks for your reponse!

But, I've tried that, but it will only give "No status is available." in the errordescription :-(

// Jocke

|||

Unfortunately that's all you'll get for some errors. I wish it weren't the case but there you are!

-Jamie