Reputation: 615
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
Reputation: 6354
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.
Upvotes: 0
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