rtnval not working?

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  March 1, 2007 at 11:48 am

  #176200

  Can anyone help? I can't get the rtnval working. The Stored Procedure below is something I've written for a caller display program I wrote in VB. The SP is basically in 2 parts, the first part get's a name from a phone number in one table and puts it into a variable @sName and the rtnval, the second part writes the info into another table. I pickup the value of rtnval in my VB program. As it stands the VB program says the rtnval doesn't exist, but if I put select rtnval = 'something' at the start of the SP my prog picks it up. Anyone with ideas?

  CREATE  PROCEDURE spCDI_Add_New_CDI_Data  @sTelenumber as VarChar(100), @sDate as smalldatetime, @sTime as varchar(20),@sRings as varchar (20),@scallrecieved as datetime

  As

  declare @sName as varchar (2000)

  declare @iOrderofcalls as int

  if exists(select [name] from [DATA] where [Number] = @sTelenumber)

  begin

   set @sName = (select [name] from [data] where [number] = @sTelenumber)

   select RtnVal = @sName

  end

  else

  begin

   select RtnVal = 'Unknown'

   set @sName = 'Unknown'

  end

  set @iOrderofcalls = (select max (OrderofCalls) from [CDI])

  set @Iorderofcalls = @Iorderofcalls +1

  INSERT INTO [CDI]  ([Name],[Tele Number], [Date of Call],[Time of

  Call],[Rings],[callrecieved],[orderofcalls])

      Values

      (@sName,@sTelenumber,@sDate,@sTime,@sRings,@scallrecieved,@Iorderofcalls)

  GO

• Luke L

  SSC-Dedicated

  Points: 35547

  More actions

  March 2, 2007 at 8:27 am

  #692856

  Are you declaring rtnval anywhere in your code?  Also, Try declaring it as an output variable.  That should do the trick for you.

   

  To help us help you read this[/url]For better help with performance problems please read this[/url]

• Ken McKelvey

  SSCoach

  Points: 18856

  More actions

  March 2, 2007 at 9:04 am

  #692873

  You will need to return an OUTPUT parameter.

  The SP should look something like:

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_NULLS ON

  GO

  CREATE  PROCEDURE dbo.spCDI_Add_New_CDI_Data

      @sTelenumber varchar(100)

      ,@sDate smalldatetime

      ,@sTime varchar(20)

      ,@sRings varchar(20)

      ,@scallrecieved datetime

      ,@sName varchar(2000) OUTPUT

  AS

  SET NOCOUNT ON

  SELECT @sName = ISNULL([name], 'Unknown')

  FROM dbo.data

  WHERE [number] = @sTelenumber

  -- You may want to make orderofcalls an identity column to save looking at the table here.

  INSERT INTO dbo.CDI

      ([Name],[Tele Number], [Date of Call],[Time of Call],[Rings],[callrecieved],[orderofcalls])

  SELECT @sName, @sTelenumber, @sDate, @sTime, @sRings, @scallrecieved, ISNULL(MAX(OrderofCalls), 0) + 1

  FROM dbo.CDI WITH (UPDLOCK)

  GO

  I am not an expert with VB but you will need to append the output parameter to the ADO command object.

  Maybe something like:

  Dim sName As ADODB.Parameter

  Set sName = Comm.CreateParameter("sName", adVarChar, adParamOutput, 2000)

  Comm.Parameters.Append sName

  Comm.Execute

  MsgBox "sName: " & sName.Value

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  March 2, 2007 at 2:53 pm

  #692941

  many thanks Luke and Ken, I'll give it a try and get back to you with the results

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  March 5, 2007 at 3:39 pm

  #693224

  that worked a treat, thanks lads