Visual Basic Crystal Reports - error in sql and vb

Author

error in sql and vb

javad.ebrahimnezhad

2005-05-23, 3:58 am

hello to all

i have a procedure blow in my sql server database and i userd it in a
crestal report v9.2

--------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE STTESTP @stklist as text AS
exec ('select * from stuntt where stunt_cod in (' + @stklist + ') ')

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--------------------------------------------

when i execute this instruction in query analyser its true and work

exec STTESTP '1,2'

when in cr9,2 inviroment i pass 1,2 in report input box its true and
report works true

but when i pass in vb this parameter it has a error
error ---> incorrect syntax near ')'

thx a lot

Renjith

2005-05-23, 3:58 am

Hi

instrad of using
"exec ('select * from stuntt where stunt_cod in (' + @stklist + ') ') "
try

sp_ExecuteSQL 'select * from stuntt where stunt_cod in (' + @stklist + ')'
or

exec 'select * from stuntt where stunt_cod in (' + @stklist + ') '

Renjith

"javad.ebrahimnezhad" wrote:

>
> hello to all
>
>
> i have a procedure blow in my sql server database and i userd it in a
> crestal report v9.2
>
>
> --------------------------------------------
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER PROCEDURE STTESTP @stklist as text AS
> exec ('select * from stuntt where stunt_cod in (' + @stklist + ') ')
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> --------------------------------------------
>
> when i execute this instruction in query analyser its true and work
>
> exec STTESTP '1,2'
>
>
> when in cr9,2 inviroment i pass 1,2 in report input box its true and
> report works true
>
> but when i pass in vb this parameter it has a error
> error ---> incorrect syntax near ')'
>
>
>
> thx a lot
>
>
>
>

Jens Süßmeyer

2005-05-23, 3:58 am

There was a qoute missing in there (the closing quote of the Exec statement)

ALTER PROCEDURE STTESTP @stklist as text AS
exec ('select * from stuntt where stunt_cod in (' + @stklist + ') ''')

But I would prefer (if you really want to use dynamic sql, to put hat in a
nvarchar variablr and pass this to the EXEC Statement. You will have a
besser error handling if something goes wrong and could even "log" the
statement with passing this to a log table, eg.: INSERT INTO
Logtable(Statement) Values @YourCommandstring.

--
HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---
"javad.ebrahimnezhad" <sorena@parskhazar.net> schrieb im Newsbeitrag
news:%23QLU490XFHA.2128@TK2MSFTNGP14.phx.gbl...
>
> hello to all
>
>
> i have a procedure blow in my sql server database and i userd it in a
> crestal report v9.2
>
>
> --------------------------------------------
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER PROCEDURE STTESTP @stklist as text AS
> exec ('select * from stuntt where stunt_cod in (' + @stklist + ') ')
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> --------------------------------------------
>
> when i execute this instruction in query analyser its true and work
>
> exec STTESTP '1,2'
>
>
> when in cr9,2 inviroment i pass 1,2 in report input box its true and
> report works true
>
> but when i pass in vb this parameter it has a error
> error ---> incorrect syntax near ')'
>
>
>
> thx a lot
>
>
>

Jeff Johnson [MVP: VB]

2005-05-23, 4:00 pm

Please don't abuse crossposting. What does this have to do with controls or
installation? Ask again in the syntax or general discussion group and you'll
get better assistance.

Sponsored Links