SQL Output Parameter

I have a questionnaire which stores answers for each user who does the test. I need to store the output in the database (which is the user doing the test.) I'm not quite sure what i am doing wrong. Currently my inputID returns 0. (InputAnswers inputs the test answers for that user to database)

CREATE PROCEDURE [dbo].[InputAnswers]

@QuestionID int,
@InputID int OUTPUT,
@Answer nchar,
@Remark nvarchar(50)

AS
BEGIN

SET NOCOUNT ON;

INSERT INTO Answers (InputID, QuestionID, Answer, Remark) VALUES (@InputID, @QuestionID, @Answer, @Remark)

END

The code below loads the answers from the list view which holds the questions:

        private void LoadAnswers(int inputID)
       {
        foreach (StepControl step in dckSteps.Children)
        {
            foreach (QuestionControl quest in step.listQuestions.Items)
            {
                int questionID = quest.questionID;
                string answer = quest.answer;
                string remark = quest.txtRemark.Text;

                SqlConnection conDatabase = new SqlConnection(String.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}", SQLSERVER_ID, SQLDatabaseName, SQLServerLoginName, SQLServerPassword));
                string query = "InputAnswers";

                SqlCommand cmd = new SqlCommand(query, conDatabase);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@InputID", SqlDbType.Int).Value = inputID;
                cmd.Parameters.Add("@QuestionID", SqlDbType.Int).Value = questionID;
                cmd.Parameters.Add("@Answer", SqlDbType.NChar).Value = answer;
                cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = remark;

                conDatabase.Open();
                cmd.ExecuteNonQuery();
                conDatabase.Close();
            }
        }
    }

I then load these answers into the button click method:

     private void btnSubmit_Click(object sender, RoutedEventArgs e)
        {
        string name = txtName.Text;
        string cno = txtCNO.Text;
        var date = datePicker.SelectedDate;
        int inputID = 0;

        SqlConnection conDatabase = new SqlConnection(String.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}", SQLSERVER_ID, SQLDatabaseName, SQLServerLoginName, SQLServerPassword));
        string query = "SaveInput";

        SqlCommand cmd = new SqlCommand(query, conDatabase);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@InputID", SqlDbType.Int).Value = ParameterDirection.Output;
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = name;
        cmd.Parameters.Add("@CNO", SqlDbType.NVarChar).Value = cno;
        cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = date;

        conDatabase.Open();
        cmd.ExecuteNonQuery();
        conDatabase.Close();

        LoadAnswers(inputID);
      }

This all works correctly apart from sending the OUTPUT back to the database. Any ideas?

EDIT: The procedure SaveInput looks like this: (SaveInput stores the user info)

CREATE PROCEDURE [dbo].[SaveInput]

@InputID int OUTPUT,
@Name nvarchar(50),
@CNO nvarchar(50),
@Date DATETIME

AS
BEGIN

SET NOCOUNT ON;

IF(@InputID = 0)
BEGIN

 INSERT INTO Input (Name, CNO, Date) VALUES (@Name, @CNO, @Date)
 SET @InputID = @@IDENTITY 
END
ELSE
BEGIN

UPDATE Input SET Name = @Name,
CNO = @CNO,
Date = @Date
WHERE InputID = @InputID

END
END

I have a feeling i don't need to use OUTPUT on both procedures.

Jon Skeet
people
quotationmark

This is at least part of the problem:

cmd.Parameters.Add("@InputID", SqlDbType.Int).Value = ParameterDirection.Output;

That's creating an input parameter - but giving it a value which is the enum value ParameterDirection.Output. You want:

cmd.Parameters.Add("@InputID", SqlDbType.Int).Direction = ParameterDirection.Output;

You then need to fetch the result from the parameter after executing the command:

cmd.ExecuteNonQuery();
// You might want to store the parameter reference in a local variable instead
// of fetching it again afterwards.
int inputId = (int) cmd.Parameters["@InputID"].Value;

If you want the parameter to be input/output though, you should be using ParameterDirection.InputOutput (and giving it a value before executing the command). For example:

int inputId = ...;

var inputIdParameter = cmd.Parameters.Add("@InputID", SqlDbType.Int);
inputIdParameter.Direction = ParameterDirection.InputOutput;
inputIdParameter.Value = inputId;

...
cmd.ExecuteNonQuery();
inputId = (int) inputIdParameter.Value;

people

See more on this question at Stackoverflow