Issue calling stored proc from ASP 1

[gus121]

Hi i have an issue calling stored proc from ASP i can run this proc fine in Query Analyser. For some reason it wont run in my ASP. There is no error message it just wont update my table as i would expect. I have done some response.write in the code so I know its getting to this point. any ideas?

ASP:

	Set cmd					= Server.CreateObject("ADODB.Command")
	cmd.ActiveConnection	= gConn
	cmd.CommandText		= "usp_SearchResult"
	cmd.CommandType		= adCmdStoredProc
	With cmd
		.Parameters.Append .CreateParameter("@SearchStr", adVarChar, adParamInput, 60)
		.Parameters.Append .CreateParameter("@searchBroad", adBoolean, adParamInput, 0)
		.Parameters.Append .CreateParameter("@Hits", adInteger, adParamInput, 0)
		
		.Parameters("@SearchStr")		= "testangus"
		.Parameters("@searchBroad")		= 0
		.Parameters("@Hits")		= 0
		
		cmd.Execute adExecuteNoRecords
	
	End With
	Set cmd = Nothing

The sp:

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO





ALTER   PROCEDURE [dbo].[usp_SearchResult]
	@Searchstr varchar(60),
	@searchBroad bit,
	@Hits int
AS
SET NOCOUNT ON

	INSERT INTO [SearchResult]
	(
	SearchStr,
	SearchBroad,
	Hits
	)
	VALUES
	(
	@Searchstr,
	@searchBroad,
	@Hits
	)
	


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-Gus

 

[gus121]

It prints out done1 but not done2 printed in red. Any ideas why i should see done1 but not done2 on the screen
Thanks

response.write "done1!" 'it gets here!
	Set cmd = Server.CreateObject("ADODB.Command")
[COLOR=red]	response.write "done2!" 'it does not get [/color]here
	cmd.ActiveConnection	= gConn
	cmd.CommandText		= "usp_SearchResult"
	cmd.CommandType		= adCmdStoredProc
	
	With cmd
		.Parameters.Append .CreateParameter("@SearchStr", adVarChar, adParamInput, 60)
		.Parameters.Append .CreateParameter("@searchBroad", adInteger, adParamInput, 0)
		.Parameters.Append .CreateParameter("@Hits", adInteger, adParamInput, 0)
		
		.Parameters("@SearchStr")		= "angus4"
		.Parameters("@searchBroad")		= 0
		.Parameters("@Hits")		= 0
		
		cmd.Execute adExecuteNoRecords
	
	End With
	Set cmd = Nothing

-Gus

 

[DotNetGnat]

cmd.Execute [red]adExecuteNoRecords[/red]...shouldnt this be the name of the procedure...usp_SearchResult...

??

-DNG

 

[DotNetGnat]

or may be it should be just

cmd.Execute

-DNG

 

[gus121]

Hi DNG,

adExecuteNoRecords just tells the database that we are not going to be returning a recordset. So dont bother getting ready the available memory to create the recordset.

removing it makes no difference for this query.

thanks

-Gus

 

[DotNetGnat]

yes, thanks for the info. i just browsed and understood why we use adExecuteNoRecords...as far as the some examples that i saw online, it had something like this...

cmd.Execute, ,adExecuteNoRecords

so may be i was wondering that it should be the second parameter to be passed..does that make any difference...

-DNG

 

[Sheco]

I'm not sure I understand.... are you saying that it fails on:
[red]Set cmd = Server.CreateObject("ADODB.Command")[/red]

If so does it work to do one of these:
Set cn = Server.CreateObject("ADODB.Connection")

or

Set rs = Server.CreateObject("ADODB.Recordset")

If these are failing with an error like "failed to create object" then try reinstalling the MDAC ... if they are failing with an error like "permission denied" then thats a different problem.

 

[gus121]

Hi Sheco,

Thanks for your input i swear i tryed renaming the cmd object similar to what you recommend above and it did not work yesterday. I try it again today and it works no problems.

thanks

Angus

-Gus