Error with SQL Server Stored Procedure & C#

Question

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!

Grant Winney · Accepted Answer · 2014-04-25 23:52:32Z

1

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();

edited Apr 25, 2014 at 23:52

answered Apr 25, 2014 at 23:44

Grant Winney

66.6k13 gold badges120 silver badges167 bronze badges

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

1 Comment

Alejo_Blue Over a year ago

Maaaaaaan!!! you were so da** right! (sorry about that) but it's just that i've tried the whooole day to figure out that! Thanks to you my bro! And thanks to the rest of the pals that contribute to help but this is the solution that worked for me!!! (Y) i love you man!!

marc_s · Accepted Answer · 2014-04-26 06:38:59Z

Few things:

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

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.

Collectives™ on Stack Overflow

Error with SQL Server Stored Procedure & C#

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related