Problem with output parameter

Problem with output parameter

Post by Joe Clark » Sun, 18 Mar 2001 08:42:00



You could call the sp with a seeded value of 0 for the output parameter, if
you're getting a problem with the execution of the stored procedure.

Also, your code probably needs to look more like this:




> An update to my earlier mail:

> Actually I already get an error at the cmd1.Execute line, saying that the

> But why would I supply it? It's an output parameter... not an input
> parameter.

> Running the stored procedure stand-alone (outside code) executes
> successfully after asking me for the 5 input parameters (not 6) and
returns
> "Procedure executed successfully but did not return any records", and
indeed
> the record is inserted.

> -- Hans



> > What I'm trying to do is insert a record and return the new record's
> primary
> > key value (school_review_id)

> > This is the stored procedure:

> > Alter Procedure "sp_create_school_review"






> > As
> > INSERT tbl_SRI (district_review_id, school_id, review_status,
> > review_status_date, selection_status)
> > VALUES (





> > )

> > Return

> > And here is the Access code that calls it:
> > Dim str_sp as string
> > Dim new_school_review_id as long

> > str_sp = "sp_create_school_review (58, 213, 2, '3/15/2001', -1)"
> > Set cmd1 as New ADODB.Command
> > cmd1.ActiveConnection = CurrentProject.Connection
> > cmd1.CommandType = adCmdStoredProc
> > cmd1.CommandText = str_sp
> > cmd1.Execute
> > new_school_review_id = cmd1("new_school_review_id")
> > cmd1 = Nothing

> > Whhat I would expect is that new_school_review_id will contain the
output
> > parameter's value, but instead I get an error: "Data Provider or other
> > Service returned an E_FAIL status"

> > How should I properly do what I'm trying to do?

> > -- Hans

 
 
 

Problem with output parameter

Post by Hans De Schrijve » Sat, 17 Mar 2001 04:35:00


An update to my earlier mail:

Actually I already get an error at the cmd1.Execute line, saying that the

But why would I supply it? It's an output parameter... not an input
parameter.

Running the stored procedure stand-alone (outside code) executes
successfully after asking me for the 5 input parameters (not 6) and returns
"Procedure executed successfully but did not return any records", and indeed
the record is inserted.

-- Hans



> What I'm trying to do is insert a record and return the new record's
primary
> key value (school_review_id)

> This is the stored procedure:

> Alter Procedure "sp_create_school_review"






> As
> INSERT tbl_SRI (district_review_id, school_id, review_status,
> review_status_date, selection_status)
> VALUES (





> )

> Return

> And here is the Access code that calls it:
> Dim str_sp as string
> Dim new_school_review_id as long

> str_sp = "sp_create_school_review (58, 213, 2, '3/15/2001', -1)"
> Set cmd1 as New ADODB.Command
> cmd1.ActiveConnection = CurrentProject.Connection
> cmd1.CommandType = adCmdStoredProc
> cmd1.CommandText = str_sp
> cmd1.Execute
> new_school_review_id = cmd1("new_school_review_id")
> cmd1 = Nothing

> Whhat I would expect is that new_school_review_id will contain the output
> parameter's value, but instead I get an error: "Data Provider or other
> Service returned an E_FAIL status"

> How should I properly do what I'm trying to do?

> -- Hans


 
 
 

Problem with output parameter

Post by Hans De Schrijve » Sat, 17 Mar 2001 04:23:02


What I'm trying to do is insert a record and return the new record's primary
key value (school_review_id)

This is the stored procedure:


As
INSERT tbl_SRI (district_review_id, school_id, review_status,
review_status_date, selection_status)

)

Return

And here is the Access code that calls it:
Dim str_sp as string
Dim new_school_review_id as long

str_sp = "sp_create_school_review (58, 213, 2, '3/15/2001', -1)"
Set cmd1 as New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = str_sp
cmd1.Execute
new_school_review_id = cmd1("new_school_review_id")
cmd1 = Nothing

Whhat I would expect is that new_school_review_id will contain the output
parameter's value, but instead I get an error: "Data Provider or other
Service returned an E_FAIL status"

How should I properly do what I'm trying to do?

-- Hans

 
 
 

Problem with output parameter

Post by BurtonRobert » Mon, 19 Mar 2001 07:59:14


Why don't you try the old fashioned way of using the .CreateParameter method
of the command object and .Append for each  parameter?  I've never seen
before the shortcut you are using .
Burton Roberts



> What I'm trying to do is insert a record and return the new record's
primary
> key value (school_review_id)

> This is the stored procedure:

> Alter Procedure "sp_create_school_review"






> As
> INSERT tbl_SRI (district_review_id, school_id, review_status,
> review_status_date, selection_status)
> VALUES (





> )

> Return

> And here is the Access code that calls it:
> Dim str_sp as string
> Dim new_school_review_id as long

> str_sp = "sp_create_school_review (58, 213, 2, '3/15/2001', -1)"
> Set cmd1 as New ADODB.Command
> cmd1.ActiveConnection = CurrentProject.Connection
> cmd1.CommandType = adCmdStoredProc
> cmd1.CommandText = str_sp
> cmd1.Execute
> new_school_review_id = cmd1("new_school_review_id")
> cmd1 = Nothing

> Whhat I would expect is that new_school_review_id will contain the output
> parameter's value, but instead I get an error: "Data Provider or other
> Service returned an E_FAIL status"

> How should I properly do what I'm trying to do?

> -- Hans

 
 
 

Problem with output parameter

Post by Hans De Schrijve » Wed, 21 Mar 2001 05:49:24


Specifying all the parameters in parentheses is a common shortcut... It has
always worked for me

-- Hans



> What I'm trying to do is insert a record and return the new record's
primary
> key value (school_review_id)

> This is the stored procedure:

> Alter Procedure "sp_create_school_review"






> As
> INSERT tbl_SRI (district_review_id, school_id, review_status,
> review_status_date, selection_status)
> VALUES (





> )

> Return

> And here is the Access code that calls it:
> Dim str_sp as string
> Dim new_school_review_id as long

> str_sp = "sp_create_school_review (58, 213, 2, '3/15/2001', -1)"
> Set cmd1 as New ADODB.Command
> cmd1.ActiveConnection = CurrentProject.Connection
> cmd1.CommandType = adCmdStoredProc
> cmd1.CommandText = str_sp
> cmd1.Execute
> new_school_review_id = cmd1("new_school_review_id")
> cmd1 = Nothing

> Whhat I would expect is that new_school_review_id will contain the output
> parameter's value, but instead I get an error: "Data Provider or other
> Service returned an E_FAIL status"

> How should I properly do what I'm trying to do?

> -- Hans

 
 
 

Problem with output parameter

Post by BurtonRobert » Thu, 22 Mar 2001 06:59:31


I guess I'll give it a try, then
Burton Roberts



> Specifying all the parameters in parentheses is a common shortcut... It
has
> always worked for me

> -- Hans



> > What I'm trying to do is insert a record and return the new record's
> primary
> > key value (school_review_id)

> > This is the stored procedure:

> > Alter Procedure "sp_create_school_review"






> > As
> > INSERT tbl_SRI (district_review_id, school_id, review_status,
> > review_status_date, selection_status)
> > VALUES (





> > )

> > Return

> > And here is the Access code that calls it:
> > Dim str_sp as string
> > Dim new_school_review_id as long

> > str_sp = "sp_create_school_review (58, 213, 2, '3/15/2001', -1)"
> > Set cmd1 as New ADODB.Command
> > cmd1.ActiveConnection = CurrentProject.Connection
> > cmd1.CommandType = adCmdStoredProc
> > cmd1.CommandText = str_sp
> > cmd1.Execute
> > new_school_review_id = cmd1("new_school_review_id")
> > cmd1 = Nothing

> > Whhat I would expect is that new_school_review_id will contain the
output
> > parameter's value, but instead I get an error: "Data Provider or other
> > Service returned an E_FAIL status"

> > How should I properly do what I'm trying to do?

> > -- Hans

 
 
 

1. SP Output parameter problem

I'm trying to bind an output parameter to a SQLCHAR. The problem
is that I always get a character array of size 50 even though I
only passed back a string that has 7 characters.  The remaining
space are ascii 32. Is there a way to determine how much data was
actually passed back?  NTS returns 50 which tells me SQL copied 50
characters.  Any suggestions?

My StoredProc is as follows:


   RETURN 0

The code snippet is as follows:
SQLCHAR tst[50];
SQLINTEGER NTS = SQL_NTS;
SQLBindParameter(hstmt,1,SQL_PARAM_OUTPUT,SQL_C_CHAR,SQL_CHAR,50,0,
(SQLPOINTER)tst,50,&NTS);

-Mike Simon

Sent via Deja.com http://www.deja.com/
Before you buy.

2. dBase III+ Problem

3. Output Parameter Problem

4. NULL string = '' ???

5. OutPut Parameter problem

6. Installation of newer SQL Server ODBC driver only. Is it possible ?

7. Problem with output parameter of stored procedure..HELP

8. Full Text Search (dropping a table gives error)

9. Problem with sp_sproc_columns getting Return_value as output parameter

10. Problems with output parameter

11. ADO Output Parameter Problem!

12. output parameter problem

13. Problem using Output parameter in sp