Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > pass stored procedure parameters in asp

Reply
Thread Tools

pass stored procedure parameters in asp

 
 
c676228
Guest
Posts: n/a
 
      05-29-2009
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
 
Reply With Quote
 
 
 
 
c676228
Guest
Posts: n/a
 
      05-29-2009
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

 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      05-29-2009
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


 
Reply With Quote
 
c676228
Guest
Posts: n/a
 
      05-29-2009
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
>
>
>

 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      05-29-2009
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


 
Reply With Quote
 
c676228
Guest
Posts: n/a
 
      05-29-2009
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
>
>
>

 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      05-29-2009
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"


 
Reply With Quote
 
Allen Chen [MSFT]
Guest
Posts: n/a
 
      06-01-2009
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:
http://www.velocityreviews.com/forums/(E-Mail Removed).

==================================================
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.



 
Reply With Quote
 
c676228
Guest
Posts: n/a
 
      06-01-2009
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"
>
>
>

 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      06-01-2009
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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
passing parameters to stored procedure from crystal reports kavitha N via .NET 247 ASP .Net 1 02-15-2011 08:20 AM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
Error while passing input parameters to stored procedure using DAA =?Utf-8?B?TWlrZQ==?= ASP .Net 0 06-22-2005 07:44 PM
Q: number of parameters in stored procedure =?Utf-8?B?SklNLkgu?= ASP .Net 2 01-12-2005 06:42 AM
How to use parameters(record selection) and stored procedure in CR.NET web-based application? TaeHo Yoo ASP .Net 0 08-13-2003 11:13 PM



Advertisments