×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Calling stored procedures with inout parameters in C#

Calling stored procedures with inout parameters in C#

Calling stored procedures with inout parameters in C#

(OP)
I'm calling a pervasive stored procedure from a c# app and it requires an "inout" parameter.  My code appears to be working fine but the returned parameter value returned is always null even if I hard set it in the procedure.  Any ideas?
Im running PVSW 8.6

RE: Calling stored procedures with inout parameters in C#

How are you calling the procedure?  
Are you using ODBC, OLEDB, or the Managed Provider?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Calling stored procedures with inout parameters in C#

(OP)
I'm using odbc namespace.  I just experimented with the demo data where I wrote the outval into a table and that worked fine but the C# code still returns null.
snippet:
OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["PVSWDemoData"].ToString());
        OdbcCommand cmd = new OdbcCommand("{ CALL ProcOut(?,?) }", conn);

        cmd.CommandType = CommandType.StoredProcedure;
        OdbcParameter par1 = cmd.Parameters.Add
           ("@id", OdbcType.Int);
        par1.Value = 132;
        OdbcParameter par = cmd.Parameters.Add
           ("@outval", OdbcType.Int);
        par.Direction = ParameterDirection.Output;


        try
        {
            conn.Open();
            OdbcDataReader dr = cmd.ExecuteReader();
            dr.Read();
            if (dr.HasRows && cmd.Parameters["@outval"].Value != null)
            {

                bReturnValue = true;
            }

RE: Calling stored procedures with inout parameters in C#

And what does your CREATE PROCEDURE look like?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Calling stored procedures with inout parameters in C#

(OP)
CREATE PROCEDURE  PROCOUT (in :id Int, out :outval INTEGER)  
AS BEGIN
SELECT COUNT(*) INTO :outval FROM Enrolls WHERE Class_Id = :id;

Drop Table t2;

CREATE TABLE t2 DCOMPRESS PAGESIZE=1024 (c1 INT DEFAULT 10);
INSERT INTO t2(c1)
VALUES (:outval);
 
END;

RE: Calling stored procedures with inout parameters in C#

I changed your code to the following and it returned "45" (the value from the OUT parameter:

CODE

conn.Open();
cmd.ExecuteNonQuery();
if (cmd.Parameters["@outval"].Value !=null)
    {
        MessageBox.Show (cmd.Parameters["@outval"].Value.ToString());
    }
The problem in your code is the ExecuteReader.  The SP does not return a result set so the .HasRows will always be false.  
Unless your SP has a RETURNS clause, a resultset is not returned and the ExecuteNonQuery would be a better choice.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Calling stored procedures with inout parameters in C#

(OP)
This was just a test case. My actual procedure does return a value.  The only reason I'm using the out parameter is because for some reason in the pervasive the datareader hasrows property always returns a true even though the recordset returns 0 records as verified in the data manager.

RE: Calling stored procedures with inout parameters in C#

(OP)
Unfortunately I still have the problem; I do not see a return value.  I thought possibly because I was in an asp.net app so I tried a windows app with still the same results.  I am running in VS.net 2005.  What environment are you running in?  

RE: Calling stored procedures with inout parameters in C#

My test was against VS.NET 2003 initially but I just tried it with VS.NET 2005 and it works for me still.  
Does the ExecuteNonQuery work for you (and return the OUT value)?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Calling stored procedures with inout parameters in C#

(OP)
Yoops, the coffee kicked in.  using the cmd.ExecuteNonQuery does return a value.  I guess I'll have to change my methodoly.  thanks for the help!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close