CODEWITHSUNDEEP
c#sql-server
Mandinga
Mandinga

Reputation: 123

Stored procedure doesn't return dataset

I need to execute a stored procedure from C# and get a DataSet. The stored procedure using a SELECT statement works very well, but when using INSERT INTO command, the dataset returned is empty but the data is, in fact, inserted into the database. Some suggestions?

This is the stored procedure;

ALTER PROCEDURE sp_add_Indicator
    @Rut AS NCHAR(10),
    @Escuela NVARCHAR(50),
    @Indicador NVARCHAR(50),
    @Fecha NVARCHAR(50),
    @Comuna NVARCHAR(50),
    @Codigo NVARCHAR(50)
AS
    INSERT INTO IndicadoresCaso (RutAlumno, Escuela, Indicador, FechaIngresoCaso, Comuna, Codigo) 
    VALUES (@Rut, @Escuela, @Indicador, @Fecha, @Comuna, @Codigo)

This is my C# code:

DataSet dsDataSet = new DataSet();

try
{
    using (SqlConnection conn = new SqlConnection(_stringConnection))
    {
        conn.Open();

        SqlCommand sqlComm = new SqlCommand("sp_add_Indicator", conn);
        sqlComm.CommandType = CommandType.StoredProcedure;

        sqlComm.Parameters.AddWithValue("@Rut", "999999999");
        sqlComm.Parameters.AddWithValue("@Escuela", "Any school");
        sqlComm.Parameters.AddWithValue("Indicador", "Any Indicator");
        sqlComm.Parameters.AddWithValue("@Fecha", "Any date");
        sqlComm.Parameters.AddWithValue("@Comuna", "Any Comuna");
        sqlComm.Parameters.AddWithValue("@Codigo", "Any Code");

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = sqlComm;
        da.Fill(dsDataSet);
        conn.Close();
    }
}

Upvotes: 2

Views: 1580

Answers (5)

Steve Ford
Steve Ford

Reputation: 7753

You can use the OUTPUT clause of the INSERT statement:

INSERT INTO IndicadoresCaso 
    (RutAlumno, Escuela, Indicador, FechaIngresoCaso, Comuna, Codigo) 
    OUTPUT 
    Inserted.RutAlumno, Inserted.Escuela, Inserted.Indicador, 
    Inserted.FechaIngresoCaso,Inserted.Comuna, Inserted.Codugo    
VALUES (@Rut, @Escuela, @Indicador, @Fecha, @Comuna, @Codigo);

Upvotes: 0

AlwaysLearning
AlwaysLearning

Reputation: 8819

If you are wanting to return the row you just inserted and the IndicadoresCaso table has an identity column called IndicadoresCasoID then your stored procedure could do the following:

ALTER PROCEDURE sp_add_Indicator
    @Rut AS NCHAR(10),
    @Escuela NVARCHAR(50),
    @Indicador NVARCHAR(50),
    @Fecha NVARCHAR(50),
    @Comuna NVARCHAR(50),
    @Codigo NVARCHAR(50)
AS
    INSERT INTO IndicadoresCaso (RutAlumno, Escuela, Indicador, FechaIngresoCaso, Comuna, Codigo) 
    VALUES (@Rut, @Escuela, @Indicador, @Fecha, @Comuna, @Codigo);

    SELECT RutAlumno, Escuela, Indicador, FechaIngresoCaso, Comuna, Codigo
    FROM IndicadoresCaso
    WHERE IndicadoresCasoID = scope_identity();

Upvotes: 0

Olivier Depriester
Olivier Depriester

Reputation: 1625

What do you expect to retrieve from you dataset? The values you have just inserted? You already have them, so why get them again from the dataset?

Anyway for your SP to return a dataset, it must contains a SELECT statement. So add statement like:

SELECT RutAlumno,Escuela,Indicador,FechaIngresoCaso,Comuna,Codigo
FROM IndicadoresCaso
WHERE RutAlumno = @Rut -- AND Escuela = @Escuela ??

after your insert. Adapt the WHERE clause to retrieve the row(s) you need.

Upvotes: 2

Baljeetsingh Sucharia
Baljeetsingh Sucharia

Reputation: 2079

Your stored procedure is made to do only INSERT statement.

If you are looking for the inserted data to be returned then after the insert you need to write appropriate select statement (in stored procedure) to select data.

Upvotes: 0

Eriawan Kusumawardhono
Eriawan Kusumawardhono

Reputation: 4906

Your stored procedure does not contain any SELECT statement, only contains INSERT statement. This is why there's no data is returned after executing the stored procedure.

Upvotes: 0

Related Questions