Re: Newbie - Stored Procedures/ASP Question

From: newbie (netinsane_at_aol.REMOVETHIS.com)
Date: 02/18/04 

Date: Wed, 18 Feb 2004 13:46:31 -0500

Thanks Bob, I'm still stuck, though:

I tried shortening the Stored Procedure, and using the script from your
generator. The stored procedure still works fine, when called to test from
within SQL server. The asp page using your script method is below:

<%
Dim cmd, param

set conn = CreateObject("adodb.connection")
conn.ConnectionString =
"Provider=SQLOLEDB;Server=server;Database=db;uid=id;pwd=password;"
conn.open

Set cmd=server.CreateObject("ADODB.Command")
With cmd
  .CommandType=adcmdstoredproc
  .CommandText = "spInsert"
  set .ActiveConnection=conn
  set param = .createparameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
  .parameters.append param
  set param = .createparameter("@First", adVarChar, adParamInput, 80,
"first")
  .parameters.append param
  set param = .createparameter("@ID", adInteger, adParamOutput, 0)
  .parameters.append param
  .execute ,,adexecutenorecords
end with

Response.Write "First: " & cmd("@First") & "<br>"
Response.Write "ErrorCode: " & cmd("@RETURN_VALUE") & "<br>"
Response.Write "Proc: " & cmd.CommandText & "<br>"
Response.Write "OrderID: " & cmd("@ID") & "<br>"
%>

The response I get is "Page cannot be displayed". When I comment out the
execute, line, I get the following response:
    First: first
    ErrorCode:
    Proc: { ? = call spInsert(?, ?) }
    OrderID:

The SQL Stored Procedure is below. Strangely, before I shortened the
procedure for testing purposes, your generated script was working (with the
old procedure). I then tried to shorten the procedure, so that I could test
my own page, line by line, to see where I went wrong. The reason is that I
set different parameters for half of the information, and call a different
procedure based on some of the data sent through from the previous page. At
any rate, here's the SQL procedure. Any ideas? Thank you for your help in
this.

CREATE PROCEDURE spInsert
 @First varchar(80)
 ,@ID int OUTPUT
AS
DECLARE @ErrorSave INT
SET @ErrorSave = 0

BEGIN TRANSACTION
 SET NOCOUNT ON
 INSERT INTO TestTable (FirstName) VALUES (@First)

 --On Error, Undo & Quit
 IF @@error <> 0
  BEGIN
   SET @ErrorSave = @@Error
   ROLLBACK TRAN
   RETURN @ErrorSave
  END

 --Get ID
SELECT @ID = @@Identity

 --Insert Card Information
 INSERT INTO Table2 (ID, TransDate) VALUES (@ID, GetDate())

 --On Error, Undo & Quit
 IF @@error <> 0
  BEGIN
   SET @ErrorSave = @@Error
   ROLLBACK TRAN
   RETURN @ErrorSave
  END

COMMIT TRANSACTION
RETURN @ErrorSave

GO

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:Op8vutM9DHA.1548@tk2msftngp13.phx.gbl...
> newbie wrote:
> > Thanks for trying--I tried both suggestions:
> >
> > removing the quotes from the name of the procedure
> > -AND-
> > adding the ,,adExecuteNoRecords after my execute
> >
> > but it did not change anything--the results were the same.
> >
> > As for the parameters, I did not list all of them on my post, but
> > they are all covered in my code.
> >
>
> It makes it tough to help you debug a problem when we are not given all
the
> information ...
>
>
> You did not mention whether or not you added the SET NOCOUNT ON statement
> into your procedure ...
>
> You say the procedure never executes? No errors? Do you have an On Error
> Resume Next statement masking your errors? Comment it out and see if you
get
> any errors.
>
> I reiterate my suggestion to give my stored procedure code generator a
try.
> It my help you resolve this problem.
>
> Bob Barrows
>
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> 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: composite key question
    ... Each time you do an insert SQL ... it's probably not a stored procedure. ... queries to perform slower, however. ... Seeing as how that script you sent was not a stored proc, ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with SQL 2005 and Sourcesafe 6
    ... I understand where you're coming from but if you open up the .sql script ... stored procedure in the database that it belongs to. ...
    (microsoft.public.sqlserver.tools)
  • Re: Encryptin stored procedures
    ... Search google for "decrypt SQL stored procedure" ... Specifying WITH ENCRYPTION for ... the script itself is plain text. ... Jasper Smith (SQL Server MVP) ...
    (microsoft.public.sqlserver.security)
  • Re: Anything that you find in SQL object scripts, you can also find them in system tables?
    ... > in the text sql scripts, are those also located in the system tables? ... you should not really script your databases more than at ... SQL Server, but this is not likely to happen this decade. ... > Can i rename an object name for example an Index name, a Stored Procedure ...
    (comp.databases.ms-sqlserver)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
Loading