ADO unable to get values

[Guest]

This may not be in the correct place, but I am getting desperate. I am
trying to get the field @FileName using vbscript.

The store procedure looks like this, and I have already tried putting
@FILENAME OUTPUT below the declarations and I received an error.

if exists (select * from sysobjects where id =
object_id('dbo.wu_ProcessQueue') and sysstat & 0xf = 4)
drop procedure dbo.wu_ProcessQueue
GO

CREATE PROCEDURE wu_ProcessQueue AS
declare @FCN int
declare @FileName varchar(8)
declare @myCommand varchar(175)
IF (SELECT count(*) from wu_Queue where FCN = 0) = 0 RETURN
exec @FCN = wu_CreateNewFCN
'I' ,
@FILENAME OUTPUT
select * from pfa.dbo.myTable
select @FileName
update wu_Queue SET FCN = @FCN where FCN = 0
GO

GRANT EXECUTE ON dbo.wu_ProcessQueue TO myName
GO


I am using VBScript and this is what the ADO looks like:

Dim myNewFilex
Set objCmd = CreateObject("ADODB.command")


Set objCmd.ActiveConnection = cnDB
objCmd.CommandText = "pfa.dbo.myTable "
objCmd.CommandType = 4
Set paraFileName = objCmd.CreateParameter("@FileName")
paraFileName.Type = 200
paraFileName.Value = vbNULL
paraFileName.Direction = 2
objCmd.Execute
MsgBox myNewFilex
myNewFilex = parafileName.Value
MsgBox myNewFilex

On MessageBox, all I am getting is an integer of 1 on both instances.
Thanks in advance for any insight.

 

[Greg Low [MVP]]

Hi ae,

You've got the syntax of the CREATE PROC mixed up. It needs to look like:

CREATE PROCEDURE wu_ProcessQueue
@FCN int, -- parameter declarations
look like this
@Filename varchar(8) OUTPUT
AS
body of the proc goes here, along with any variable declares that aren't
parameters.

HTH,

 

[Stefan]

Hi,

Im not quite sure I follow your question but it looks to me like you
never change the value of @FileName in your SQL.

On a side not it is more efficient to check for existence of rows in a
table using this syntax:

if not exists (select field from wu_Queue where FCN = 0)

Instead of:

IF (SELECT count(*) from wu_Queue where FCN = 0) = 0 RETURN

If you could elaborate a bit on your question I would be glad to help.

C# GURU
<a href="http://www.dotnetovation.com">www.DotNETovation.com</a>