CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
Alejo_Blue
Alejo_Blue

Reputation: 615

Error with SQL Server Stored Procedure & C#

I've been developing a stored procedure in order to get a register from a table so I built the following query to achieve this:

ALTER procedure [dbo].[procedure_odd]
      @codSeccion int,
      @NomDoce varchar(500),
      @codMate varchar(500)
as
begin
     declare @hora varchar(50);

     set @hora = (
        select b.man_nomhor 
        from ra_hpl_horarios_planificacion a 
        inner join ra_man_grp_hor b on a.hpl_codman = b.man_codigo 
        where a.hpl_codcil = 100 
          and a.hpl_codemp = (select emp_codigo from pla_emp_empleado 
                              where emp_nombres_apellidos = @NomDoce) 
          and a.hpl_codmat = @codMate 
          and a.hpl_descripcion = @codSeccion)

     return @hora
end

I've tested this query (ONLY the query not the stored procedure with the query) in my SQL Server console and it works just fine. The problem is when I call it from C# it doesn't work no matter what I try! Also I tried to develop a stored procedure with output parameter but with no result.

Also I've tried this other way(which works so good and fine!):

select b.man_nomhor 
from ra_hpl_horarios_planificacion a 
      inner join ra_man_grp_hor b on a.hpl_codman = b.man_codigo 
where a.hpl_codcil = 100 
      and a.hpl_codemp = 
         (select emp_codigo from pla_emp_empleado 
          where emp_nombres_apellidos = 'julio escobar') 
      and a.hpl_codmat = 'FONO-I' 
      and a.hpl_descripcion = 1;

Here is my code on C# (My 11000 solution):

public String horarios(int Seccion, string NomDocent, string CodMate)
{
    cn.Open();
    cmd.Connection = cn;

    cmd.CommandText = "select b.man_nomhor from ra_hpl_horarios_planificacion 
    a inner join ra_man_grp_hor b on a.hpl_codman = b.man_codigo where 
    a.hpl_codcil = 100 and a.hpl_codemp =(select emp_codigo from 
    pla_emp_empleado where emp_nombres_apellidos = '" + NomDocent + 
    "') and a.hpl_codmat = '" + CodMate + "' and a.hpl_descripcion = '" + Seccion + "'";

    dr = cmd.ExecuteReader();

    if (dr.HasRows)
    {
        if (dr.Read())
        {
            msj = dr[0].ToString();
        }
    }

    cn.Close();

    return msj;
}

When I run my Visual Studio it doesn't show any error at all but in the variable MSJ it set an empty STRING like this MSJ = "";

This must be really easy but it just that (believe) I've tried so hard to get to the solution with bno results, please help!

Upvotes: 1

Views: 89

Answers (2)

Nathan Tregillus
Nathan Tregillus

Reputation: 6354

Few things:

  1. if you want this to be a stored procedure, be sure to set command.CommandType = CommandType.StoredProcedure.

  2. you do not need to call return or set a variable to return the text you have. instead, just have a select statement return the one field:

     ALTER procedure [dbo].[procedure_odd]
   @codSeccion int,
   @NomDoce varchar(500),
   @codMate varchar(500)
    as
   begin

    select top 1 b.man_nomhor from ra_hpl_horarios_planificacion a inner join
    ra_man_grp_hor b on a.hpl_codman = b.man_codigo where a.hpl_codcil = 100 
    and a.hpl_codemp = (select emp_codigo from pla_emp_empleado 
    where emp_nombres_apellidos = @NomDoce) and a.hpl_codmat = @codMate and
    a.hpl_descripcion = @codSeccion)
END

once this is done, just execute the stored procedure with a dataReader, that will return your value. if you need more information, I can edit my answer to clarify.

Upvotes: 0

Grant Winney
Grant Winney

Reputation: 66511

It looks like Seccion (and thus a.hpl_descripcion) are integers, but your query is placing apostrophes around it like a literal.

Try removing the apostrophes:

... + "' and a.hpl_descripcion = " + Seccion;

If that's indeed the issue, parameterizing your query can eliminate these kinds of mistakes:

cmd.CommandText = "... and a.hpl_codemp =(select emp_codigo from pla_emp_empleado where emp_nombres_apellidos = @NomDocent) and a.hpl_codmat = @CodMate and a.hpl_descripcion = @Seccion";

cmd.AddParameterWithValue("@NomDocent", NomDocent);
cmd.AddParameterWithValue("@CodMate", CodMate);
cmd.AddParameterWithValue("@Seccion, Seccion);

dr = cmd.ExecuteReader();

Upvotes: 1

Related Questions