How to call stored procedure with parameter in vbscript in classic ASP

Question

0.00/5 (No votes)

See more:

i have one stored procedure which takes an one input parameter and returns the result. i tried lots of solutions but it not worked. it is throwing an internal server error (HTTP 500 Internal Server Error)and the page is blank
(The website cannot display the page). Could you please help me to sort out this problem.

What I have tried:

VB

Dim strSQLConnection
dim strPass2
strSQLConnection = "driver={SQL Server};server=[servername];database=[DB name];uid=;pwd=;"
Set Conn = Server.CreateObject("ADODB.Connection")
Set objCommand = Server.CreateObject("ADODB.Command")
Set objParam = Server.CreateObject("ADODB.Parameter")
Set rstUsers = Server.CreateObject("ADODB.Recordset")
Conn.Open strSQLConnection

objCommand.ActiveConnection = strSQLConnection
objCommand.commandtext = "Getdetails" 
objCommand.CommandType = 4   'defines cmd type as stored proc
Set objParm = objCommand.CreateParameter("@UserId", 3,1,,strUserId)          
objCommand.Parameters.Append objParm

set rstUsers = objCommand.Execute

If Not rstUsers.EOF Then
	strPass2 = rstUsers("ntuserid")
      
End if

Posted 8-Jun-17 4:45am

Member 8854617

Updated 8-Jun-17 5:07am

Leo Chapiro

v3

Add a Solution

Comments

Richard Deeming 8-Jun-17 11:09am

Assuming strUserId is actually an integer, that code looks OK as far as I can see. But it has been 15+ years since I last touched ADODB!
Append and CreateParameter Methods Example (VB)[^]

You're going to need to add a custom error page, and log the error details somewhere.
How To Create a Custom ASP Error Handling Page[^]
Creating Custom ASP Error Pages[^]

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 7663390 · Answer 1 · 2024-08-06T08:34:00

Solution 1

Leo, your parameter object is called objParam, but you using objParm in your Set and Append statements.

Posted 6-Aug-24 8:34am

Member 7663390

Comments

Dave Kreskowiak 6-Aug-24 16:25pm

Look at the date on the question. You're seven years too late.

Richard Deeming 13-Aug-24 4:19am

Not only far too late, but also inaccurate. Without specifying Option Explicit, VBScript doesn't care if you use a variable without declaring it first.

The Set objParm = ... line will create a new parameter and assign it to a new variable. The .Append objParm line will append that new parameter object to the command's parameters. The objParam variable will not be used anywhere.

Whilst it's always a good idea to use Option Explicit to pick up simple typos like this, the typo was clearly not the cause of the OP's problem.

How to call stored procedure with parameter in vbscript in classic ASP

1 solution

Solution 1

Add your solution here

Preview 0

How to call stored procedure with parameter in vbscript in classic ASP

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us