Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

How do I send parameter to Store procedure?

Hello,
I need help Experts!
I am a beginner of MS-SQL and ASP.
I made a store prPcedure on MS-SQL (See below) and I need to send 2 parameters to SQL and I have no idea how to do it. I have searched internet and this is my coding so far...

SQL Store procedure:

-- Create Table
create table test (
      firstname nvarchar(10)
,      lastname nvarchar(10)
)

-- Create Store Procedure
CREATE PROCEDURE CreateNewMember2  (@firstname nvarchar(10) , @lastname nvarchar(10))
AS
SET NOCOUNT ON

INSERT INTO test (firstname,lastname)
VALUES (@firstname, @lastname)

GO


ASP CODING:
<!--#include file ="../function/dbopen.asp"-->

<%
-- I brought two values from previous page which I wanted to input on my data with store procedure
firstname2 = request("firstname")
lastname2= request("lastname")

Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = db
    .Commandtext = "CreateNewMember2 firstname, lastname"
    .CommandType = adCmdStoredProc
      .Parameters.Append .CreateParameter("@firstname", advarchar,adParamInput,10)
      .Parameters.Append .CreateParameter("@lastname",adnvarWchar,adParamInput,10)
    .Parameters("@firstname") = firstname2
      .Parameters("@lastname") = lastname2
    .Execute , , adExecuteNoRecords

End with

Set cmd = Nothing

db.close
Set db = Nothing

%>


I am not familiar with Parameter and command object. I copied from other forum so if you can explain about on how to use command and parameter, it will be a big help!
Thank you guys!
0
erin027
Asked:
erin027
  • 2
1 Solution
 
dbbishopCommented:
.Parameters.Add(("@firstname", adNVarChar,adParamInput,10)

I don't know if Parameters collecvioon defaults to .Value. I don't like to trust defaults, so code:
Parameters("@firstname").Value = firstname2

Also, in your code, you are defining @firstname as VarChar (adVarChar) and last name as unicode (adNVarWChar-which is not a valid enumeration, it should be adNVarChar).
0
 
Jai STech ArchCommented:
another way is to directly call the sp
  .Commandtext = "CreateNewMember2 '" & firstname  &"','" & lastname & "'"
    .CommandType = adCmdStoredProc
'      .Parameters.Append .CreateParameter("@firstname", advarchar,adParamInput,10)
'      .Parameters.Append .CreateParameter("@lastname",adnvarWchar,adParamInput,10)
'    .Parameters("@firstname") = firstname
'      .Parameters("@lastname") = lastname2
    .Execute , , adExecuteNoRecords

and if you want to pass parameteres only then
Commandtext = "CreateNewMember2 @firstname, @lastname"


0
 
erin027Author Commented:
Thanks for the Comment.
However, I changed the coding and I am still getting an error message

Changed Code:
<!--#include file ="../function/dbopen.asp"-->

<%
firstname2 = request("firstname")
lastname2 = request("lastname")

Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = db
    .Commandtext = "CreateNewMember2 firstname, lastname"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@firstname", advarchar,adParamInput,10)
    .Parameters.Append .CreateParameter("@lastname", advarchar,adParamInput,10)
    .Parameters("@firstname").Value = firstname2
    .Parameters("@lastname").Value = lastname2
    .Execute , , adExecuteNoRecords

End with

Set cmd = Nothing

db.close
Set db = Nothing
%>

And I am getting a following error message:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation
/member/test.asp, line 19

The line 19 was >>>>   .Execute , , adExecuteNoRecords

Also, am I doing in a right way? Is there any other way to do this?
I am new with everything so any advise will help me.
Thank you so much
0
 
erin027Author Commented:
Ignore my previous Message.
It worked. Thank you!!!!!!!!!!!!!!!!!!!!!!!!!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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