SQL Output Parameter

Question

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.

Change IF(@InputID = 0) to IF(@InputID is NULL) else your @InputID will never be assigned with identity value — Pரதீப்
– Pரதீப், Commented Jul 20, 2015 at 6:08

Jon Skeet · Accepted Answer · 2015-07-20 06:15:52Z

5

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;

edited Jul 20, 2015 at 6:15

answered Jul 20, 2015 at 6:10

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Zohar Peled Over a year ago

Also, I would recommend using SCOPE_IDENTITY() instead of @@IDENTITY, since the first will return the last identity value inserted by the stored procedure and the second will return the last identity regardless of how it was inserted or to what table. For more information, read this article

Jon Skeet Over a year ago

@ZoharPeled: I'll take your word it but leave it there as a comment rather than trying to put it into my answer. Basically I only tend to understand the client side of questions like this :)

zen_1991 Over a year ago

The ParameterDirection.Output didn't work as it returned an InvalidCast error but the InputOut method worked perfectly! :) Thank you!

Jon Skeet Over a year ago

@zen_1991: If the cast failed, that presumably just meant you needed to cast to something else - maybe long, for example.

freefaller Over a year ago

It's worth noting that if you're using cmd.ExecuteQuery() you need to close the query for the parameter to be updated... discovered via this answer

Collectives™ on Stack Overflow

SQL Output Parameter

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related