June 12, 2006 at 7:05 pm
I have a dinamic query in a SP that execute with the exec(@SQLVAR).
I send from Vb6.0 the next line, to concatenate with my query:
whereString = "'and tbEmpleado.noTarjeta = ' + char(39) + " & noTarjeta & " + char(39) + ' '"
But I got the next error:
Run-time error '-2147217900(80040e14')
Line1: Incorrect syntax near '+'
How can I send the chain from VB, with out receoving that error?
Thanks
June 14, 2006 at 5:39 am
You are mixing up your quotes. You want to use double quotes for the VB syntax strings, and single quotes for the SQL quote delimters.
whereString = "and tbEmpleado.noTarjeta = '" & noTarjeta & "'"
Hope this helps
Mark
June 14, 2006 at 9:40 am
This is the SP
CREATE PROCEDURE spRepxRangoFecha
@fechaIni varchar (10),
@fechaFin varchar (10),
@where varchar(150)
AS
set nocount on
Set quoted_identifier On
declare @SQL varchar(2000)
declare @err int
declare @Result varchar (50)
--set @fechaIni = '05/31/2006' --2006-05-31
--set @fechafin = '06/05/2006'
--set @noCliente = 9990
--set @where = ''
--set @where = 'and tbEmpleado.noTarjeta = ' + char(39) + '0000000000124587487' + char(39) + ' '
set @SQL =
'select tbCliente.noCliente noCliente, '
+ 'tbCliente.nombre nombreCliente, '
+ 'tbCliente.direccion + ' + char(39) + ' ' + char(39) + ' + tbCliente.ciudad + '
+ char(39) + ' ' + char(39) + ' + tbCliente.edo direccion, '
+ 'tbCliente.RFC rfcCliente, '
+ 'tbEmpleado.noEmple noEmple, '
+ 'dbo.F_TRIM(tbEmpleado.apePaterno) + ' + char(39)+ ' ' + char(39) + ' + dbo.F_TRIM(tbEmpleado.apeMaterno) + '
+ char(39) + ' ' + char(39) + ' + dbo.F_TRIM(tbEmpleado.nomEmple) nombreEmpleado, '
+ 'tbEmpleado.rfcEmple rfcEmple, '
+ 'tbMovimientos.noTarjeta noTarjeta, '
+ 'tbMovimientos.fechaMov fechaMov, '
+ 'tbTiendas.nombre concepto, '
+ 'tbMovimientos.montoUltTrans montoUltTrans, '
+ 'tbMovimientos.saldoAnterior + tbMovimientos.MontoUltTrans saldo, '
+ char(39) + @fechaIni + char(39) + ' fechaIni, '
+ char(39) + @fechaFin + char(39) + ' fechaFin, '
+ 'cargos, '
+ 'abonos '
+ 'from tbCliente '
+ 'inner join tbMovimientos on tbCliente.noCliente = tbMovimientos.noCliente '
+ 'inner join tbEmpleado on tbCliente.noCliente = tbEmpleado.noCliente '
+ 'and tbMovimientos.noCliente = tbEmpleado.noCliente '
+ 'and tbMovimientos.noEmple = tbEmpleado.noEmple '
--and tbMovimientos.noTarjeta = tbEmpleado.noTarjeta
+ 'inner join tbTiendas on tbMovimientos.noTienda = tbTiendas.noTienda '
+ 'left join tbCambioTarjeta on tbMovimientos.noCliente = tbCambioTarjeta.noCliente '
+ 'and tbMovimientos.noEmple = tbCambioTarjeta.noEmple '
--and tbMovimientos.noTarjeta = tbCambioTarjeta.noTarjetaAnterior
+ 'left join '
+ '( '
+ 'select noCliente, noEmple, sum (tbMovimientos.MontoUltTrans) cargos '
+ 'from tbMovimientos '
+ 'where MontoUltTrans like ' + char(39)+ '-%' + char(39) + ' '
+ 'and tbMovimientos.fechaMov between ' + char(39) + @fechaIni + char(39) + 'and' + char(39)+ @fechaFin + char(39) + ' '
+ 'group by noCliente, noEmple '
+ ')tbCargos on tbCargos.noEmple = tbEmpleado.noEmple '
+ 'and tbCargos.noCliente = tbCliente.noCliente '
+ 'left join '
+ '( '
+ 'select noCliente, noEmple, sum (tbMovimientos.MontoUltTrans) abonos '
+ 'from tbMovimientos '
+ 'where MontoUltTrans not like ' + char(39) + '-%' + char(39) + ' '
+ 'and tbMovimientos.fechaMov between ' + char(39) + @fechaIni + char(39) + 'and' + char(39)+ @fechaFin + char(39) + ' '
+ 'group by noCliente, noEmple '
+ ')tbAbonos on tbAbonos.noEmple = tbEmpleado.noEmple '
+ 'and tbAbonos.noCliente = tbCliente.noCliente '
+ 'where tbMovimientos.fechaMov between ' + char(39) + @fechaIni + char(39) +'and' + char(39) + @fechaFin + char(39) + ' '
set @SQL = @SQL + @where
set @SQL = @SQL + 'order by tbEmpleado.noEmple, tbEmpleado.noTarjeta, tbMovimientos.fechaMov, tbMovimientos.hrMov '
BEGIN TRAN
EXEC(@SQL)
SET @err = @@error
IF (@err <> 0)
BEGIN
SET @Result='ERROR al Hacer la Consulta de Movimientos'
ROLLBACK TRAN
GOTO ERR_HANDLER
END
SET @Result='OK'
COMMIT TRAN
ERR_HANDLER:
SELECT @Result AS RESULT
GO
if I put directly in the @where varchar this value it works
set @where = 'and tbEmpleado.noTarjeta = ' + char(39) + '0000000000124587487' + char(39) + ' '
if I try to sent this value from VB even the one that you suggest it doesn't works.
June 14, 2006 at 12:51 pm
Sorry, missing the extra space character at the end. This should work:
whereString = "and tbEmpleado.noTarjeta = '" & noTarjeta & "' "
In the sproc, all those "char(39)" are to delimit string values in the sql query, to make it look different that the single quotes used to make string values in the sproc for the string variable containing the sql query. You do not need those in VB, as you use the double quotes (") to delimit the string values, and use the single quote (') to delimit the string values in the sql query.
It might be easier to see if you use the TSQL Debugger in Query Analyzer to step through the sproc and see the value of @SQL just before it is executed.
Hope this helps
Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply