CODEWITHSUNDEEP
c#sql-server
Jorge
Jorge

Reputation: 18237

output parameter does not have a value

I have simple SQL insert into command which does not work using the ExecuteNonQuery. Here's my command 

INSERT INTO [dbo].[et_personhasdevice] 
            ([id_naturalperson], 
             [id_device], 
             [pd_macaddress], 
             [pd_recordate], 
             [pd_status], 
             [created_at], 
             [created_by]) 
SELECT id_person, 
       1, 
       @macAddress, 
       CURRENT_TIMESTAMP, 
       'A', 
       CURRENT_TIMESTAMP, 
       'ETC' 
FROM   [dbo].et_person 
WHERE  pe_address = @personAddress

SET @ID_PERSONHASDEVICE = Scope_identity()

This is my block of code 

using (SqlCommand command = helper.GetSqlQueryCommand(query))
{
    //In parameters
    foreach (string key in parameters.Keys)
        command.Parameters.AddWithValue(key, parameters[key]);

    //out parameters
    foreach(string key in outParameters.Keys)
    {
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = key;
        parameter.Direction = ParameterDirection.Output;         
        parameter.SqlDbType = SqlDbType.Int;
        command.Parameters.Add(parameter);
    }

    command.ExecuteNonQuery();
    command.Parameters["ID_PERSONHASDEVICE"].Value //it does not have a value

}

The execution does not throw any exception but also does not inserted the record in the database, also I already check the command and works well in the management studio

Upvotes: 0

Views: 186

Answers (4)

HLGEM
HLGEM

Reputation: 96552

I am going to talk a bit about how to approach solving an issue like this.

First go to SSMS (or whatever interface you use to directly query a database for folks not using SQL Server) and run the select statment with the appropriate variable value. If it returns no records then you know why the insert is not working.

If it returns records then run the full insert. You may get an error that will tell you that you are missing a value for a required field or some other error. If there is no error then check the table to see if the data was inserted. If it was not, check for triggers on the table that could be rolling back the insert.

Once you know the statement works, then turn on profiler and try to run from the application using application code. Profiler will tell you what was sent, check it for differnces betweeen the SQL you have that works and what is sent. Often a variable won't get initialized and that is why there is no insert.

If you get no SQL from the Profiler, then look at the code in C# or whatever language you are writing in. At least now you know exactly where the problem has to lie.

Upvotes: 2

Pranav Singh
Pranav Singh

Reputation: 20091

Try 

 command.Parameters["@ID_PERSONHASDEVICE"].Value=...

I have added @ before ID_PERSONHASDEVICE, which I think was missing.

Upvotes: 0

Steve
Steve

Reputation: 216273

You don't need an output parameter at all. Use ExecuteScalar and it returns the value from your last statement SELECT SCOPE_IDENTITY()

Of course the command text should be changed in this way

string query   = @"INSERT INTO [dbo].[ET_PERSONHASDEVICE] (
                  [ID_NATURALPERSON],[ID_DEVICE],[PD_MACADDRESS],
                  [PD_RECORDATE],[PD_STATUS],[CREATED_AT],[CREATED_BY])
                  SELECT ID_PERSON,1,@macAddress,CURRENT_TIMESTAMP,'A',
                         CURRENT_TIMESTAMP,'ETC'
                  FROM [dbo].ET_PERSON WHERE PE_ADDRESS = @personAddress;
                  SELECT SCOPE_IDENTITY()";

You could pass two statements to a single SqlCommand and receive the value of the last one executed, you need only to divide the two commands with a semicolon

Upvotes: 1

Efran Cobisi
Efran Cobisi

Reputation: 6454

I believe ExecuteReader() does not "execute" its command until its result is enumerated. Instead, I would try with:

command.ExecuteNonQuery();

Upvotes: 1

Related Questions