Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP General (http://www.velocityreviews.com/forums/f65-asp-general.html)
-   -   pass stored procedure parameters in asp (http://www.velocityreviews.com/forums/t804822-pass-stored-procedure-parameters-in-asp.html)

c676228 05-29-2009 06:08 PM

pass stored procedure parameters in asp
 
Hi all,

I encountered some strange issues when I tried to attach parameters to a
stored procedure. I don't have the similar issues when there is no need to
pass parameters from asp program to a stored procedure.
The adVarChar, adParamInput
are defined in the include file.

The stored procedure is like this:
CREATE proc [dbo].[voidTran]( @productName varchar(25), @tranNumber
varchar(12))
and it runs ok in sql analyzer.

but when I call through my asp program, it seems troublesome.
....
conn.Open "dsn=TXXXX;uid=XXXXX;pwd=mXXXXX"
.....

cmdTemp.CommandText = "voidTran" 'stored procedure for exec
cmdTemp.CommandType = adCmdStoredProc

cmdTemp.Parameters.Append cmdTemp.CreateParameter("@productName",
adVarChar, adParamInput, 25, "AC")

cmdTemp.Parameters.Append cmdTemp.CreateParameter("@tranNumber", adVarChar,
adParamInput, 12, "VKYF67803456")
'attach store procedure parameter
cmdTemp.Execute
when I didn't add length in the createParameter, the error message is like
this:

Provider error '80020005'
type mismatch

after add lengths(25, 12) for each parameter.
The error message is like this:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
procedure 'voidTran'.

and voidTran procedure is definitely there. Don't know what I should do.
I googled, but didn't find something helpful.
Can you help?
--
Betty

c676228 05-29-2009 06:52 PM

RE: pass stored procedure parameters in asp
 
Now I chnaged my code to:
cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
cmdTemp.CommandType = adCmdStoredProc
cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName", adChar,
adParamInput)
cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber", adChar ,
adParamInput)
'attach store procedure parameter
cmdTemp("productName")="AC"
cmdTemp("tranNumber")="VKYF68483010"
cmdTemp.Execute

and the error message is like this:

Parameter object is improperly defined. Inconsistent or incomplete
information was provided.


--
Betty


"c676228" wrote:

> Hi all,
>
> I encountered some strange issues when I tried to attach parameters to a
> stored procedure. I don't have the similar issues when there is no need to
> pass parameters from asp program to a stored procedure.
> The adVarChar, adParamInput
> are defined in the include file.
>
> The stored procedure is like this:
> CREATE proc [dbo].[voidTran]( @productName varchar(25), @tranNumber
> varchar(12))
> and it runs ok in sql analyzer.
>
> but when I call through my asp program, it seems troublesome.
> ...
> conn.Open "dsn=TXXXX;uid=XXXXX;pwd=mXXXXX"
> ....
>
> cmdTemp.CommandText = "voidTran" 'stored procedure for exec
> cmdTemp.CommandType = adCmdStoredProc
>
> cmdTemp.Parameters.Append cmdTemp.CreateParameter("@productName",
> adVarChar, adParamInput, 25, "AC")
>
> cmdTemp.Parameters.Append cmdTemp.CreateParameter("@tranNumber", adVarChar,
> adParamInput, 12, "VKYF67803456")
> 'attach store procedure parameter
> cmdTemp.Execute
> when I didn't add length in the createParameter, the error message is like
> this:
>
> Provider error '80020005'
> type mismatch
>
> after add lengths(25, 12) for each parameter.
> The error message is like this:
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
> procedure 'voidTran'.
>
> and voidTran procedure is definitely there. Don't know what I should do.
> I googled, but didn't find something helpful.
> Can you help?
> --
> Betty


Bob Barrows 05-29-2009 07:12 PM

Re: pass stored procedure parameters in asp
 
c676228 wrote:
> Now I chnaged my code to:
> cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
> cmdTemp.CommandType = adCmdStoredProc
> cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
> adChar, adParamInput)
> cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber",
> adChar , adParamInput)
> 'attach store procedure parameter
> cmdTemp("productName")= "AC"
> cmdTemp("tranNumber")= "VKYF68483010"
> cmdTemp.Execute
>
> and the error message is like this:


You have no output parameters. Just call your procedure like this:

conn.voidTran "AC", "VKYF68483010"

Make sure the user account being used by the connection has rights to
that stored procedure.

--
HTH,
Bob Barrows



c676228 05-29-2009 07:54 PM

Re: pass stored procedure parameters in asp
 
Hi Bob,
Nice to hear from you again.
I tried. the error is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
procedure 'voidTran'.

but voidTran stored procedure is there.
--
Betty


"Bob Barrows" wrote:

> c676228 wrote:
> > Now I chnaged my code to:
> > cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
> > cmdTemp.CommandType = adCmdStoredProc
> > cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
> > adChar, adParamInput)
> > cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber",
> > adChar , adParamInput)
> > 'attach store procedure parameter
> > cmdTemp("productName")= "AC"
> > cmdTemp("tranNumber")= "VKYF68483010"
> > cmdTemp.Execute
> >
> > and the error message is like this:

>
> You have no output parameters. Just call your procedure like this:
>
> conn.voidTran "AC", "VKYF68483010"
>
> Make sure the user account being used by the connection has rights to
> that stored procedure.
>
> --
> HTH,
> Bob Barrows
>
>
>


Bob Barrows 05-29-2009 08:21 PM

Re: pass stored procedure parameters in asp
 
Then you have a permissions problem. The user account being used in your
connection string has not been granted permission to execute that
procedure.

c676228 wrote:
> Hi Bob,
> Nice to hear from you again.
> I tried. the error is:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
> procedure 'voidTran'.
>
> but voidTran stored procedure is there.
> --
> Betty
>
>
> "Bob Barrows" wrote:
>
>> c676228 wrote:
>>> Now I chnaged my code to:
>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
>>> cmdTemp.CommandType = adCmdStoredProc
>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
>>> adChar, adParamInput)
>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber",
>>> adChar , adParamInput)
>>> 'attach store procedure parameter
>>> cmdTemp("productName")= "AC"
>>> cmdTemp("tranNumber")= "VKYF68483010"
>>> cmdTemp.Execute
>>>
>>> and the error message is like this:

>>
>> You have no output parameters. Just call your procedure like this:
>>
>> conn.voidTran "AC", "VKYF68483010"
>>
>> Make sure the user account being used by the connection has rights to
>> that stored procedure.
>>
>> --
>> HTH,
>> Bob Barrows


--
HTH,
Bob Barrows



c676228 05-29-2009 09:51 PM

Re: pass stored procedure parameters in asp
 
Bob,

But I use the exact same connection string, it doesn't have any problems to
execute other stored procedures. All those procedures belong to dbo including
voidTran.
so I don' have any clues.
--
Betty


"Bob Barrows" wrote:

> Then you have a permissions problem. The user account being used in your
> connection string has not been granted permission to execute that
> procedure.
>
> c676228 wrote:
> > Hi Bob,
> > Nice to hear from you again.
> > I tried. the error is:
> >
> > Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
> > procedure 'voidTran'.
> >
> > but voidTran stored procedure is there.
> > --
> > Betty
> >
> >
> > "Bob Barrows" wrote:
> >
> >> c676228 wrote:
> >>> Now I chnaged my code to:
> >>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
> >>> cmdTemp.CommandType = adCmdStoredProc
> >>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
> >>> adChar, adParamInput)
> >>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber",
> >>> adChar , adParamInput)
> >>> 'attach store procedure parameter
> >>> cmdTemp("productName")= "AC"
> >>> cmdTemp("tranNumber")= "VKYF68483010"
> >>> cmdTemp.Execute
> >>>
> >>> and the error message is like this:
> >>
> >> You have no output parameters. Just call your procedure like this:
> >>
> >> conn.voidTran "AC", "VKYF68483010"
> >>
> >> Make sure the user account being used by the connection has rights to
> >> that stored procedure.
> >>
> >> --
> >> HTH,
> >> Bob Barrows

>
> --
> HTH,
> Bob Barrows
>
>
>


Bob Barrows 05-29-2009 10:59 PM

Re: pass stored procedure parameters in asp
 
It's permissions...
Or, you did not create the procedure where you thought you did ...

c676228 wrote:
> Bob,
>
> But I use the exact same connection string, it doesn't have any
> problems to execute other stored procedures. All those procedures
> belong to dbo including voidTran.
> so I don' have any clues.
>
>> Then you have a permissions problem. The user account being used in
>> your connection string has not been granted permission to execute
>> that procedure.
>>
>> c676228 wrote:
>>> Hi Bob,
>>> Nice to hear from you again.
>>> I tried. the error is:
>>>
>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>>>
>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
>>> procedure 'voidTran'.
>>>
>>> but voidTran stored procedure is there.
>>> --
>>> Betty
>>>
>>>
>>> "Bob Barrows" wrote:
>>>
>>>> c676228 wrote:
>>>>> Now I chnaged my code to:
>>>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
>>>>> cmdTemp.CommandType = adCmdStoredProc
>>>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
>>>>> adChar, adParamInput)
>>>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber",
>>>>> adChar , adParamInput)
>>>>> 'attach store procedure parameter
>>>>> cmdTemp("productName")= "AC"
>>>>> cmdTemp("tranNumber")= "VKYF68483010"
>>>>> cmdTemp.Execute
>>>>>
>>>>> and the error message is like this:
>>>>
>>>> You have no output parameters. Just call your procedure like this:
>>>>
>>>> conn.voidTran "AC", "VKYF68483010"
>>>>
>>>> Make sure the user account being used by the connection has rights
>>>> to that stored procedure.
>>>>
>>>> --
>>>> HTH,
>>>> Bob Barrows

>>
>> --
>> HTH,
>> Bob Barrows


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"



Allen Chen [MSFT] 06-01-2009 10:10 AM

Re: pass stored procedure parameters in asp
 
Hi Betty,

>But I use the exact same connection string, it doesn't have any problems

to
>execute other stored procedures. All those procedures belong to dbo

including
>voidTran.
>so I don' have any clues.


Could you run SQL Server Profiler at server side to see the query passed
from client? What's the query?

http://msdn.microsoft.com/en-us/library/ms187929.aspx

Can it work if you run the query directly in the management studio?

Regards,
Allen Chen
Microsoft Online Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subs.../aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.




c676228 06-01-2009 09:34 PM

Re: pass stored procedure parameters in asp
 
Hi Bob,

I believe it is the permission issue.
For now the only thing I can think of is
voidTran is a stored procedure for deleting records from the database.
While any other stored procedures I don't have any problems are just
insert or update records in the database.

Maybe there is a special permission needed for deleting records?

--
Betty


"Bob Barrows" wrote:

> It's permissions...
> Or, you did not create the procedure where you thought you did ...
>
> c676228 wrote:
> > Bob,
> >
> > But I use the exact same connection string, it doesn't have any
> > problems to execute other stored procedures. All those procedures
> > belong to dbo including voidTran.
> > so I don' have any clues.
> >
> >> Then you have a permissions problem. The user account being used in
> >> your connection string has not been granted permission to execute
> >> that procedure.
> >>
> >> c676228 wrote:
> >>> Hi Bob,
> >>> Nice to hear from you again.
> >>> I tried. the error is:
> >>>
> >>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >>>
> >>> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
> >>> procedure 'voidTran'.
> >>>
> >>> but voidTran stored procedure is there.
> >>> --
> >>> Betty
> >>>
> >>>
> >>> "Bob Barrows" wrote:
> >>>
> >>>> c676228 wrote:
> >>>>> Now I chnaged my code to:
> >>>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
> >>>>> cmdTemp.CommandType = adCmdStoredProc
> >>>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
> >>>>> adChar, adParamInput)
> >>>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber",
> >>>>> adChar , adParamInput)
> >>>>> 'attach store procedure parameter
> >>>>> cmdTemp("productName")= "AC"
> >>>>> cmdTemp("tranNumber")= "VKYF68483010"
> >>>>> cmdTemp.Execute
> >>>>>
> >>>>> and the error message is like this:
> >>>>
> >>>> You have no output parameters. Just call your procedure like this:
> >>>>
> >>>> conn.voidTran "AC", "VKYF68483010"
> >>>>
> >>>> Make sure the user account being used by the connection has rights
> >>>> to that stored procedure.
> >>>>
> >>>> --
> >>>> HTH,
> >>>> Bob Barrows
> >>
> >> --
> >> HTH,
> >> Bob Barrows

>
> --
> Microsoft MVP - ASP/ASP.NET - 2004-2007
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>


Bob Barrows 06-01-2009 09:51 PM

Re: pass stored procedure parameters in asp
 
No. you grant Execute permission to the user for this procedure the same way
you grant permissions for the other procedures.

Here is the T-SQL statement to do so:

Use NameOfYourDatabase
GO
GRANT Execute For dbo.voidTran NameOfUser

This just occurred to me: what is the default schema for NameOfUser? If it
is not "dbo", change it to "dbo":

ALTER USER NameOfUser WITH DEFAULT_SCHEMA = dbo;


c676228 wrote:
> Hi Bob,
>
> I believe it is the permission issue.
> For now the only thing I can think of is
> voidTran is a stored procedure for deleting records from the database.
> While any other stored procedures I don't have any problems are just
> insert or update records in the database.
>
> Maybe there is a special permission needed for deleting records?
>
>
>> It's permissions...
>> Or, you did not create the procedure where you thought you did ...
>>
>> c676228 wrote:
>>> Bob,
>>>
>>> But I use the exact same connection string, it doesn't have any
>>> problems to execute other stored procedures. All those procedures
>>> belong to dbo including voidTran.
>>> so I don' have any clues.
>>>
>>>> Then you have a permissions problem. The user account being used in
>>>> your connection string has not been granted permission to execute
>>>> that procedure.
>>>>
>>>> c676228 wrote:
>>>>> Hi Bob,
>>>>> Nice to hear from you again.
>>>>> I tried. the error is:
>>>>>
>>>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>>>>>
>>>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
>>>>> stored procedure 'voidTran'.
>>>>>
>>>>> but voidTran stored procedure is there.
>>>>> --
>>>>> Betty
>>>>>
>>>>>
>>>>> "Bob Barrows" wrote:
>>>>>
>>>>>> c676228 wrote:
>>>>>>> Now I chnaged my code to:
>>>>>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
>>>>>>> cmdTemp.CommandType = adCmdStoredProc
>>>>>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
>>>>>>> adChar, adParamInput)
>>>>>>> cmdTemp.Parameters.Append
>>>>>>> cmdTemp.CreateParameter("tranNumber", adChar , adParamInput)
>>>>>>> 'attach store procedure parameter
>>>>>>> cmdTemp("productName")= "AC"
>>>>>>> cmdTemp("tranNumber")= "VKYF68483010"
>>>>>>> cmdTemp.Execute
>>>>>>>
>>>>>>> and the error message is like this:
>>>>>>
>>>>>> You have no output parameters. Just call your procedure like
>>>>>> this:
>>>>>>
>>>>>> conn.voidTran "AC", "VKYF68483010"
>>>>>>
>>>>>> Make sure the user account being used by the connection has
>>>>>> rights to that stored procedure.
>>>>>>
>>>>>> --
>>>>>> HTH,
>>>>>> Bob Barrows
>>>>
>>>> --
>>>> HTH,
>>>> Bob Barrows

>>
>> --
>> Microsoft MVP - ASP/ASP.NET - 2004-2007
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"




All times are GMT. The time now is 06:36 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.