Re: pass stored procedure parameters in asp

Bob,

It is definitely a permission issue. I just cannot delete a record through
asp program.
I used another way of programming to void a transaction. The program tells
me that the void is successful. But when I looked at the data in the
database, the data are still there.
I used reponse.write to verify that my program got the right command to
execute the delete statement. Here is the code:

If Request("ProductName") ="" Then
Response.Write "You need to select a product." & "<br>"
Response.End
Else
product=Request("ProductName")
Select Case product
Case "AC"
productTable="table1"
Case "IMED"
productTable="Table2"
End Select
End If
If Request("TrxnNumber")="" Then
Response.Write "You need to enter a transaction number." & "<br>"
Response.End
Else
trxnNumber=Trim(Request("TrxnNumber"))
End If
cmdTemp.CommandText="Select order_id from " & productTable & " where
pnref='" & trxnNumber & "'"

set rs=cmdTemp.Execute

If rs.EOF Then
Response.Write "Record was not found."
Else
Response.Write rs(0)

cmdStr="Delete from " & productTable & " where order_id='" & rs(0) & "'"
conn.Execute(cmdStr)
cmdStr1="Delete from tis_vs where order_id='" & rs(0) & "'"
conn.Execute(cmdStr1)

Response.Write "The transaction " & trxnNumber & " was voided."
End If

--
Betty


"Bob Barrows" wrote:

Well then, all I can suggest is using SQL Profiler to run a trace and make
sure the expected user accountis being used to connect to the database, and
that the user is successfully logged in, and that the correct procedure name
is being called.


c676228 wrote:
Yes, I did log in as bettys and execute voidTran, no problem at all.

In Management Studio, open a new query window, logging in with the
bettys account and try to execute the procedure.

Alternatively, you can use the EXECUTE AS statement to test the
ability of the bettys account to run the procedure, like this:

EXECUTE AS User='bettys'
exec voidTran ...
REVERT --revert to your own context


c676228 wrote:
Bob,
I got the script from the mgment studio and bettys already under dbo
schema. CREATE USER [bangaltiger] FOR LOGIN [bettys] WITH
DEFAULT_SCHEMA=[dbo]

And I looked at the properties(mgment studio, right click on stored
procedure and properties) of voidTran stored procedure and other
procedures, there isn't anything different. That's bizzare.

And I did the following:
use mydatabase
go
grant execute on dbo.voidTran to bettys

still the same error message.



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"

--
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"



.

Relevant Pages

  • Re: pass stored procedure parameters in asp
    ... you can use the EXECUTE AS statement to test the ... procedure and properties) of voidTran stored procedure and other ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.general)
  • Re: pass stored procedure parameters in asp
    ... GRANT Execute For dbo.voidTran NameOfUser ... I believe it is the permission issue. ... voidTran is a stored procedure for deleting records from the database. ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.general)
  • Re: Display ADO Recordset in Access
    ... or are you simply using a saved parameter query (aka ... stored procedure) to access a table in the same database? ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: Item cannot be found in the collection corresponding to the requested name or ordinal
    ... > I'm running a stored procedure that inserts a record into a table then ... > (CustomerID, CustomerName, ContactName, TelNo, FaxNo, Email, ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.general)
  • Re: How to grab results from recordset
    ... > Here's the deal, when I run the stored procedure in query analyzer, it ... > display the resultset and email the error message. ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.general)
Loading