Stored Procedures and parameters in ADO not working

I've been trying to set the following code up to pass a value to and return 
a value from  a Sybase stored procedure.

Stats:

WIN 98SE on workstation
VB6/SP4
ADO 2.5
Sybase 11.2 (?) on HP unix server



First, this is the stored procedure:

************************************

create procedure sy_get_next_id
                @tableID smallint,
                @uniqueID int out
as
  declare @countID int
   select @countID = counter_id 
     from sync_tables noholdlock
    where table_id = @tableID
  if (@@rowcount = 0)
    select @countID = 10
  select @uniqueID =  next_id 
    from sync_id_file noholdlock
   where table_id = @countID
  update sync_id_file 
     set next_id = next_id + 1 
   where table_id = @countID
  if (suser_name() != "mass_update") select @uniqueID
  return @@error

********************************************

Next, here's the VB code I've written:


********************************************

Public Function GetNextIDNumber() As String
 
    Dim oRS As ADODB.Recordset
    Dim oParameter As ADODB.Parameter
    Dim oCommand As New ADODB.Command
    Dim lReturn As Long
 
 
    '(the global connection object is already opened, using the 
    'following code in another part of the program:
    'Set goDB = New Connection
    'goDB.Open "PROVIDER=MSDASQL;dsn=INVOICE_DEV;uid=****;pwd=****;
                     database=GENESIS;"
 
    With oCommand
        .CommandType = adCmdStoredProc
        .CommandText = "sy_get_next_id"
        .ActiveConnection = goDB 
        Set oParameter = .CreateParameter(Name:="retval", _
                                          Type:=adInteger, _
                                     Direction:=adParamReturnValue)
        .Parameters.Append oParameter
        .Parameters("retval").Value = 0
 
        Set oParameter = .CreateParameter(Name:="@tableID", _
                                          Type:=adInteger, _
                                          Direction:=adParamInput)
        .Parameters.Append oParameter
        .Parameters("retval").Value = 13
 
        Set oParameter = .CreateParameter(Name:="@uniqueID", _
                                          Type:=adInteger, _
                                          Direction:=adParamOutput)
        .Parameters.Append oParameter
        .Parameters("retval").Value = 0
 
    End With
 
    Set oRS = New ADODB.Recordset
 
    oRS.Open oCommand
    lReturn = oCommand.Parameters("retval").Value
 
    Debug.Print "lReturn = " & CStr(lReturn)
    Debug.Print "RS Field(0) = " & CStr(oRS.Fields(0))
    Debug.Print "@uniqueID = " & oCommand.Parameter("@uniqueID").Value
 
End Function

*************************************

This code runs and generates no errors, but returns the wrong data:

(this is the immediate window output)

*************************************
?oCommand.Parameters("@uniqueID").Value

lReturn = 0
RS Field(0) = 11522561
@uniqueID = 

*************************************

The value for oRS.fields(0) is actually the correct value for the next ID 
from the TENTH table, not the thirteenth!!



Please - if anyone has any brainy ideas what's going on here, please let me 
know.
0
sgoodwin
4/9/2001 8:08:15 PM
sybase.easerver.general 7903 articles. 0 followers. Follow

1 Replies
456 Views

Similar Articles

[PageSpeed] 16

Please try one of the ASE newsgroups

Scott

<sgoodwin@astenjohnson.com> wrote in message
news:BAF4577B29954657006E9EA285256A29.006E9EB585256A29@webforums...
> I've been trying to set the following code up to pass a value to and
return
> a value from  a Sybase stored procedure.
>
> Stats:
>
> WIN 98SE on workstation
> VB6/SP4
> ADO 2.5
> Sybase 11.2 (?) on HP unix server
>
>
>
> First, this is the stored procedure:
>
> ************************************
>
> create procedure sy_get_next_id
>                 @tableID smallint,
>                 @uniqueID int out
> as
>   declare @countID int
>    select @countID = counter_id
>      from sync_tables noholdlock
>     where table_id = @tableID
>   if (@@rowcount = 0)
>     select @countID = 10
>   select @uniqueID =  next_id
>     from sync_id_file noholdlock
>    where table_id = @countID
>   update sync_id_file
>      set next_id = next_id + 1
>    where table_id = @countID
>   if (suser_name() != "mass_update") select @uniqueID
>   return @@error
>
> ********************************************
>
> Next, here's the VB code I've written:
>
>
> ********************************************
>
> Public Function GetNextIDNumber() As String
>
>     Dim oRS As ADODB.Recordset
>     Dim oParameter As ADODB.Parameter
>     Dim oCommand As New ADODB.Command
>     Dim lReturn As Long
>
>
>     '(the global connection object is already opened, using the
>     'following code in another part of the program:
>     'Set goDB = New Connection
>     'goDB.Open "PROVIDER=MSDASQL;dsn=INVOICE_DEV;uid=****;pwd=****;
>                      database=GENESIS;"
>
>     With oCommand
>         .CommandType = adCmdStoredProc
>         .CommandText = "sy_get_next_id"
>         .ActiveConnection = goDB
>         Set oParameter = .CreateParameter(Name:="retval", _
>                                           Type:=adInteger, _
>                                      Direction:=adParamReturnValue)
>         .Parameters.Append oParameter
>         .Parameters("retval").Value = 0
>
>         Set oParameter = .CreateParameter(Name:="@tableID", _
>                                           Type:=adInteger, _
>                                           Direction:=adParamInput)
>         .Parameters.Append oParameter
>         .Parameters("retval").Value = 13
>
>         Set oParameter = .CreateParameter(Name:="@uniqueID", _
>                                           Type:=adInteger, _
>                                           Direction:=adParamOutput)
>         .Parameters.Append oParameter
>         .Parameters("retval").Value = 0
>
>     End With
>
>     Set oRS = New ADODB.Recordset
>
>     oRS.Open oCommand
>     lReturn = oCommand.Parameters("retval").Value
>
>     Debug.Print "lReturn = " & CStr(lReturn)
>     Debug.Print "RS Field(0) = " & CStr(oRS.Fields(0))
>     Debug.Print "@uniqueID = " & oCommand.Parameter("@uniqueID").Value
>
> End Function
>
> *************************************
>
> This code runs and generates no errors, but returns the wrong data:
>
> (this is the immediate window output)
>
> *************************************
> ?oCommand.Parameters("@uniqueID").Value
>
> lReturn = 0
> RS Field(0) = 11522561
> @uniqueID =
>
> *************************************
>
> The value for oRS.fields(0) is actually the correct value for the next ID
> from the TENTH table, not the thirteenth!!
>
>
>
> Please - if anyone has any brainy ideas what's going on here, please let
me
> know.


0
Scott
4/10/2001 3:02:31 PM
Reply:

Similar Artilces:

Calling a stored procedure with output parameter and parameter name binding does not work
Hello NG, Named parameters are a fine feature, you don't have to obey the parameter order and can omit parameters that have default values. BUT if you try to use output parameters in the escaped syntax for jdbc like the following: { ? = call myProc ( @MyOutParam = ? ) } it doesn't seem to work anymore. Or am I simply too stupid? Maybe I have missed some documentation detail? Maybe someone can point me to the right direction? I'm really desperate. For implementation details see code snippet below. Thanks in advance. With best regards, have a merry christmas and a ha...

Parameter store procedure not working
I have this select statement: SELECT * FROM [enews] WHERE ([name_nws] LIKE '%" + strSearch + "%' OR [title_nws] LIKE '%" + strSearch + "%'OR [sub_nws] LIKE '%" + strSearch + "%' OR [sum_nws] LIKE '%" + strSearch + "%' OR [content_nws] LIKE '%" + strSearch + "%') This statement works fine but when I turned it into a store procedure, it returns nothing. I created the store procedure as follows: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:        <Author,,Name> -- Create date: <...

How do I get ADO and Stored Procedures to work?
I'm having a hard time getting sproc's to work with ADO. Here's the error message: ************************************ Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Sybase][ODBC Driver][Adaptive Server Anywhere]Count field incorrect: Not enough values for host variables ************************************ Here's the ASP Code: <% oSP.CommandText = "et_UpdateAddress" oSP.Parameters.Append oSP.CreateParameter("AddressNumber", adInteger, adParamInput, 4, AddressNumber) oSP.Parameters.Append oSP.CreateParameter("...

Stored Procedure Parameters not working with SqlDataSource
Hi:I'm trying to populate a dropdownlist with results from the following stored procedure:CREATE PROCEDURE GetDivisionsForCompany( @EnterpriseID varchar(50), @CompanyID varchar(50) -- 'ALL' to see all divisions)asSelect DivisionID from Divisions where EnterpriseID = @EnterpriseIDand ( CompanyID = @CompanyID or @CompanyID = 'ALL' )order by CompanyIDWhen I select "Configure Data Source", the UI correctly finds the two parameter names (less the '@') after I select "Specify a custom SQL statement or stored procedure" and pick the stored procedure.I select one parameter source from...

VB6 ADO.Command with Parameter and stored procedure
Hi everyone, I'm trying to call a stored procedure from my VB6-Program using the ADODB.Command object. The code looks like that: With cmd Set .ActiveConnection = conn .CommandType = adCmdStoredProc .CommandText = "MyProc" .Parameters(0).Value = myIntValue Set ADORecordsetObject = cmd.Execute End With When used with ODBC this code will work fine. But in a new application OLE-DB must be used. With OLE-DB I get an error saying "wrong number of arguments passed to the stored proc". Why does the code work with ODBC but not with OLE-DB and what c...

Using ADO.NET Execute Second Stored Procedure Depending of Output of First Stored Procedure
When a user logs onto the app, I capture their UserId, today's date and thier WINNT using ADO.NET calling a stored procedue-no problem. Now, once the above mention fields have been populated, when the same user logs on again and this time click their daily attendance time, I need to execute and update stored procedure-no problem. Each time the user logs onto the attendance page, they need to be able to view the previuos time stamps, on page load.- no problem The problem is that I cannot get all three to work in concert, although they will work independently, somewhat. Sotred proc...

My PB 10.0 Store Procedures with PARAMETERS does not work ...
This is a multi-part message in MIME format. ---=_forums-1-dub44e08985 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit Can somebody please help me? Trying to run dws with a store procedure as data source. As long as the Store Procedures have no parameters the dws preview just fine. I am working with a Sql Server DB and ODBC within Power Builder. Please Note: All my stored procedures worked fine in PB 6.5 Direct Connect to Sql Server. Now migrating all my dw to PB 10.5 is where I have the problem. What is it missing? Thank you, ...

ExecuteReader for stored procedure with 2 input parameters not working
Hi, I have this sp: ALTER PROCEDURE dbo.UserLogin @Username nvarchar(50), @Password nvarchar(50) AS DECLARE @UserId int SELECT @UserId = UserId FROM Users WHERE @Username = Username AND @Password = Password SELECT 'UserId' = @UserId My 'Uses' Table is: UserId -int 4 Username -nvarchar 50 Password -nvarchar 50 I'm trying to send username and password to this sp and get the userId with no luck! Tried something like: Dim reader As SqlDataReader = SqlHelper.ExecuteReader(connectionString, Com...

ExecuteReader for stored procedure with 2 input parameters not working
Hi, I have this sp: ALTER PROCEDURE dbo.UserLogin @Username nvarchar(50), @Password nvarchar(50) AS DECLARE @UserId int SELECT @UserId = UserId FROM Users WHERE @Username = Username AND @Password = Password SELECT 'UserId' = @UserId My 'Uses' Table is: UserId -int 4 Username -nvarchar 50 Password -nvarchar 50 I'm trying to send username and password to this sp and get the userId with no luck! Tried something like: Dim reader As SqlDataReader = SqlHelper.ExecuteReader(connectionString, CommandType.StoredProce...

Using Tables as Input Parameters in ADO Stored Procedures
Hi. I use MSSQL 2008 and Delphi 7. The SQL stored procedure has an input parameter of a table type. My application has an array defined as a TIntegerDynArray. I want to use this array as the input parameter for the stored procedure. In my datamodule I have a TADOStoredProc. The parameter's datatype is defaulted to ftUnknown. I get a runtime error during the assignment of the value to the parameter. The stored procedure is a work-in-progess and I have no problem if I have to change it. I need to do a series of data updates within a database transaction. The input parameter was...

Parameters not working Using Stored Procedure with 3-tier architecture
I was using an objectdatasource with a table adapter that had an insert command that was commandtype txt, but now I want to change that to a stored procedure and I'm having some problems. Here is my insert function in the BLL:  Protected ReadOnly Property adapter() As accidentTableAdapter Get If _accidentadapter Is Nothing Then_accidentadapter = New accidentTableAdapter() End IfReturn _accidentadapter End Get End Property <System.ComponentModel.DataObjectMethodAttribute _(System.ComponentModel.DataObjectMethodType.Insert, True)> _ Public Function AddAcciden...

Problem passing date parameter to stored procedure via ADO
Hi: I have a table with 4 columns, where one column has a datetime datatype. I have created a stored procedure to write to this table. I am calling this stored procedure from VB6 using ADO and am using the ADO datatype of adDBTimeStamp (which works for MS SQL Server). The value I am passing is for e.g. "10/05/2001". The stored procedure executes without a problem, but the value that is being stored in the datetime column is 12:00:00 AM and not the 10/05/2001???? Can anyone tell me what I need to change (i.e. the ADO datatype in my VB code or the style of the actual data ...

ADO
--_000_A78C6259B11833419567082E2DC0C25601DB357DEEorsmsx508amrc_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I'm fairly new to using DBD::ADO, but I was wondering whether there is a wa= y to return both an output parameter and a sql print statement from a store= d procedure. I've been trying to do it and have come across what seems lik= e possibly an issue with the module (or my usage is incorrect or never inte= nded or something). For example, if I have a stored procedure set up which= accepts 10 parameters, the...

assigning count(*) to parameter in stored procedure doesn't seem to work?
Hello, I'm having a problem with retrieving a total number of rows in a query. I have no idea why it won't work. This is the C# code that calls the stored procedure (I'm using .net connector 1.0.7):   public int TotalUnreadNewsByTeam(int team_id){ // Initialise database connection and command Database db = new Database(); MySqlCommand sqlCommand = new MySqlCommand("NewsTotalUnreadByTeam", db.Connection); // Set command as SP sqlCommand.CommandType = CommandType.StoredProcedure; MySqlParameter pTeam_Id = new MySqlParameter("?_team_id", MySqlDbType.Int32); MySqlParameter...

Web resources about - Stored Procedures and parameters in ADO not working - sybase.easerver.general

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

Why Are Web Login Security Procedures So #$*&!% Dumb And Obnoxious?
The geeks who design our websites talk a lot about user friendliness, but they often let an exaggerated security concerns get in the way of commonsense. ...

Resources last updated: 1/2/2016 11:41:30 AM