Convert Command.ExecuteScalar() To Int

Question

I need to insert a line in my question table and retrieve the inserted id. I initialize my sql command and execute it with ExecuteScalar(). I'm trying to convert the result of this method to int but I can not do it.

I tried to do that:

int result = Convert.ToInt32(Command.ExecuteScalar));

or

int result = (int)Command.ExecuteScalar();

but nothing work

here is my function

public int AddQuestionOrientation(Question questionForAdd)
    {
        try
        {
            con = new SqlConnection(connectionString);
            con.Open();
            SqlCommand command;
            String sql = "";
            sql = "INSERT INTO QUESTION VALUES(@Libelle, 
            @Bareme,2,@Filliere)";

            SqlParameter param = new SqlParameter();
            param.ParameterName = "@Libelle";
            param.Value = questionForAdd.Libelle;

            SqlParameter param2 = new SqlParameter();
            param2.ParameterName = "@Bareme";
            param2.Value = questionForAdd.Bareme;

            SqlParameter param3 = new SqlParameter();
            param3.ParameterName = "@Filliere";
            param3.Value = questionForAdd.IdFiliere;

            command = new SqlCommand(sql, con);
            command.Parameters.Add(param);
            command.Parameters.Add(param2);
            command.Parameters.Add(param3);
            int idQuestionInserted = (int)command.ExecuteScalar();
            command.Dispose();
            con.Close();
            return idQuestionInserted;
        }
        catch(Exception ex)
        {
            return 0;
        }
    }

If I try with the cast (int), I have the message error:

Object reference not set to an instance of an object

If I try with the Convert.ToInt32, my variable "IdQuestionInserted" is equal to 0.

_nothing work_ is not descriptive enough for us to help, please be specific about what exactly is not working for you. Have you debugged and checked exactly what runtime type is returned from the query? — JSteward, Jun 18 '19 at 21:21
You need to perform a select after your insert, as per the example. https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar?view=netframework-4.8 — Derrick Moeller, Jun 18 '19 at 21:21
I am guessing that you want the identity value after you insert? I would suggest you stop writing sql in your code. Move that to a procedure. And add select @MyOutput = SCOPE_IDENTITY(). And then make that be an output variable. — Sean Lange, Jun 18 '19 at 21:24
@JSteward Sorry for that, I updated my post with the error messages I get in catch block — ChillAndCode, Jun 18 '19 at 21:33
@SeanLange I do not understand, where should I put this select? — ChillAndCode, Jun 18 '19 at 21:36
Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Ňɏssa Pøngjǣrdenlarp, Jun 18 '19 at 21:59

score 1 · Accepted Answer · answered Jun 18 '19 at 21:47

This is a big departure from where you started. But you have several issue going on there. You should use the USING statement around objects with the IDisposable interface (connections, commands, etc...).

This code is all untested but should be really close.

Start with creating a stored procedure so you can start creating layers in your application.

create Procedure Question_Insert
(
    @Libelle varchar(50)
    , @Bareme varchar(50)
    , @Filliere varchar(50)
    , @QuestionID int output
) as
    set nocount on;

    INSERT INTO QUESTION
    (
        Libelle
        , Bareme
        , Filliere
    )
    values
    (
        @Libelle
        , @Bareme
        , @Filliere
    )

    select @QuestionID = SCOPE_IDENTITY()

Then in your dotnet code you need to change up a few things to make it cleaner and more robust. Ideally you should do something better than simply return 0 when there is an error. It will be really tough to debug when something goes wrong if you simply return a 0. This is like an error message that says, "An error occurred". Pretty useless. Do something with the error. Capture the message to enable you to fix it.

public int AddQuestionOrientation(Question questionForAdd)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    con.Open();
                    using (SqlCommand command = new SqlCommand("Question_Insert"))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        command.Parameters.Add("@Libelle", SqlDbType.VarChar, 50).Value = questionForAdd.Libelle;
                        command.Parameters.Add("@Bareme", SqlDbType.VarChar, 50).Value = questionForAdd.Bareme;
                        command.Parameters.Add("@Filliere", SqlDbType.VarChar, 50).Value = questionForAdd.IdFiliere;
                        command.Parameters.Add("@QuestionID", SqlDbType.Int).Direction = ParameterDirection.Output;
                        command.ExecuteNonQuery();
                        return int.Parse(command.Parameters["@QuestionID"].Value.ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

Thanks for that, I finally have my ID ! I still have to improve my way of coding because it's true that it's very disorderly but I start — ChillAndCode, Jun 18 '19 at 21:57

score 1 · Answer 2 · edited Jun 19 '19 at 05:47

1

To get inserted id use SCOPE_IDENTITY() add SELECT CAST(scope_identity() AS int to command query to do

INSERT INTO QUESTION
  VALUES(@Libelle, @Bareme, 2, @Filliere);

SELECT CAST(scope_identity() AS int;

this query will return inserted id for you.

edited Jun 19 '19 at 05:47

Dale K

25,246
15
42
71

answered Jun 19 '19 at 05:29

Mostafa

21
4

Convert Command.ExecuteScalar() To Int

2 Answers2