Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2730
  • Last Modified:

Run a SQL Stored Procedure from a .vbs File and return a parameter

Hello

I need to execute a SQL Stored Procedure with a single input parameter (Integer) and return the result (Integer).
The code then needs to open a URL with the integer as a parameter ("exo://core_opportunity(x)")

I'm not sure how to connect to the database or return the parameter.
I need to run this from a .vbs file

Many Thanks
0
p-plater
Asked:
p-plater
  • 3
  • 2
2 Solutions
 
Scott Fell, EE MVEDeveloperCommented:
<%
' make sure to include your connection string to your db first
dim result,MyNumber
MyNumber=25
result=someFunction(MyNumber)

Function someFunction(MyParam)

   	
	Set cmd = Server.CreateObject("ADODB.Command")
With cmd
   
   	.ActiveConnection = MyConnection
    .CommandType = adCmdStoredProc
   	.CommandText = "name_of_stored_procedure" 'Name of your stored procedure
   
   	.Parameters.Append .CreateParameter("@variableX",adDouble, adParamInput)
   	.Parameters("@variableX") = MyParam 'set any variables for the SP
   
   set someFunction = .Execute

 
set cmd = nothing


end Function
%>

Open in new window

0
 
p-platerAuthor Commented:
Thanks Padas

I am getting the error " Arguments are of the wrong type, are out of acceptable range or are in conflict with each other"

Below is my Code
dim MyConnection, strSQL 
set MyConnection = CreateObject("ADODB.Connection") 
MyConnection.provider="sqloledb" 
MyConnection.Mode = "3" 
MyConnection.open  "dcserver", "sa", "xxxxxxxx" 
MyConnection.DefaultDatabase = "CRM_Test" 

	
	
' make sure to include your connection string to your db first
dim result,MyNumber
MyNumber=25
result=someFunction(MyNumber)

msgbox result

Function someFunction(MyParam)

   	
Set cmd = CreateObject("ADODB.Command")

With cmd
   
   	.ActiveConnection = MyConnection
    .CommandType = 4'adCmdStoredProc
   	.CommandText = "x_test" 'Name of your stored procedure
   	.Parameters.Append .CreateParameter("@act",adDouble, adParamInput)
   	.Parameters("@act") = MyParam 'set any variables for the SP
end with
   
   set someFunction = .Execute

 
set cmd = nothing


end Function

Open in new window


Also my Stored Procedure Code
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Ronald Kidd
-- Create date: 2013-12-05
-- Description:	Open
-- =============================================
ALTER PROCEDURE [dbo].[x_test] 
	-- Add the parameters for the stored procedure here
	@act int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @RETURN_VALUE as int
   set @RETURN_VALUE = 25
	SELECT @RETURN_VALUE
	
	insert into X_PRG_ERRORLOG(COMPUTER,STAFF,TRIGGERED,ERROR)
		values('test','ters','test',@act)
	
	return 25
	
END

GO

Open in new window

0
 
Scott Fell, EE MVEDeveloperCommented:
Oh, I always have adovbs included too http://support.web.com/vserver/ntguide/microsoft_adovbs_inc_file.htm

What line is the error happening on?
0
 
p-platerAuthor Commented:
Line 27
0
 
p-platerAuthor Commented:
OK
I've changed line 27 to"Parameters.Append .CreateParameter("@act",5, 1)"
And "cmd" to the .execute

It now runs but the message box errors - Doesn't return the parameter.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now