Hi:

I'm developing a C# mobile web application that needs to access a Sybase ASE database (12.5) on the back end.  I'm going through OleDB.  Here's the problem, although my stored procedures run fine on Sybase, I cannot get my ASP.NET objects to populate from them.  I've got 2 objectlists that I'm trying to populate.  A code snippet is below.  It looks pretty vanilla to me, but I just cannot get it to work.  Is there even a way to test data access in VS 2005 without running the app

All advice greatly appreciated!

-Dave

// Create the Connection

OleDbConnection cnOLEDB = new OleDbConnection();

cnOLEDB.ConnectionString = "Provider=Sybase.ASEOLEDBProvider.2;Data Source=vcijis_test;" +

"Initial Catalog=probation;** blocked out remainder **";

// Our NEW dataset model

dsClientInfo mySet = new dsClientInfo();

// Create the Name & Address Command

OleDbCommand oledbCom1 = new OleDbCommand();

oledbCom1.CommandText = "pa_get_client_info";

oledbCom1.CommandType = CommandType.StoredProcedure;

// This command uses one Parameter

OleDbParameter par1 = oledbCom1.Parameters.Add( , OleDbType.Integer);

par1.Value = Convert.ToInt32 (txtPersonNbr.Text);

// Create the Demographic Command

OleDbCommand oledbCom2 = new OleDbCommand();

oledbCom2.CommandText = "pa_get_client_demographics";

oledbCom2.CommandType = CommandType.StoredProcedure;

// This command uses one Input Parameter

OleDbParameter par2 = oledbCom2.Parameters.Add( , OleDbType.Integer);

par2.Value = Convert.ToInt32(txtPersonNbr.Text);

// Open the Connection

//DataAdapter Stuff goes here

OleDbDataAdapter adap1 = new OleDbDataAdapter();

adap1.Fill(mySet, "clientBasics");

adap1.Fill(mySet, "clientDemographics");

// Close the Connection

// Work with the ObjectList

ObjectList1.DataSource = mySet.Tables["clientBasics"].DefaultView;

ObjectList1.LabelField = "last";

ObjectList2.DataMember = "clientDemographics";

this.ActiveForm = frmDemographic;

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

And the Stored Procedure is:

Create Proc dbo.pa_get_client_demographics (

                                           )
As
Begin
/**********************************************************
*Procedure Name: pa_get_client_demographics
*Database: probation
*Server: UT_SERVER
*CAST Tools5,1,3,490
*
*Business Function : Returns most recent physical description

*Author BF: ROM  Date BF: 11/21/2006
*File Path:
* Z:\vc_agcy\pa\sql\development\pa_get_client_demographics
*********************************************************/

CREATE TABLE #clientdemo
       (
          gender     varchar(10)  NULL,
          race       varchar(20)  NULL,
          height     smallint     NULL,
          weight     smallint     NULL,
          eye_color  varchar(10)  NULL,
          hair_color varchar(20)  NULL,
          lang       varchar(30)  NULL
        )
      

INSERT INTO #clientdemo
SELECT g.descrip,
       r.descrip,
       pd.height,
       pd.weight,
       e.descrip,
       h.descrip,
       l.descrip
FROM person_demographics pd,
     code_entries g,
     code_entries r,
     code_entries e,
     code_entries h,
     code_entries l

       pd.owner_agency = 'vcpa' and
       pd.dt_invalid   = null  and
       pd.dt_updated   = (select max(dt_updated) from person_demographics

                                dt_invalid = null)) and
       g.code_nbr =* pd.gender_code and
       r.code_nbr =* pd.ethnicity and
       e.code_nbr =* pd.eye_color and
       h.code_nbr =* pd.hair_color and
       l.code_nbr =* pd.language_code     
       

INSERT INTO #clientdemo
SELECT g.descrip,
       r.descrip,
       pd.height,
       pd.weight,
       e.descrip,
       h.descrip,
       l.descrip
FROM person_demographics pd,
     code_entries g,
     code_entries r,
     code_entries e,
     code_entries h,
     code_entries l

       pd.dt_invalid   = null  and
       pd.dt_updated   = (select max(dt_updated) from person_demographics

       g.code_nbr =* pd.gender_code and
       r.code_nbr =* pd.ethnicity and
       e.code_nbr =* pd.eye_color and
       h.code_nbr =* pd.hair_color and
       l.code_nbr =* pd.language_code 

SELECT gender,
       race,
       height,
       weight,
       eye_color,
       hair_color,
       lang
      
FROM  #clientdemo

END

I see nothing.  Nada.  Squat!!!  Count is 0 in the de****.

I wish I could test the dataset / stored procedure in the development application (without having to run the web page).  If there is a way, and I'm just ignorant (very possible), please let me know!

I may be getting closer to a solution. The stored procedure was modified to use output paramaters for the results. However, in VS I only see a value in one output param, and a partial value in the last output param. Again, it runs fine when tested outside of VS. Here is a recap:

Using OleDB objects and methods.

Parm #1: Result of Stored Procedure. Shows successful completion ("0").

Parm #2: Input Parm (an ID number). shows up fine in de****.

Parm #3: Output Parm (persons name). This is returned accurately.

Parm #4: Output Parm: (street address). Comes back empty.

Parm#5: Output Parm: (city). Comes back empty.

Parm#6: Output Parm: (phone). Come back as "() -" which does exist in the phone string. Why only those chars

Here is the stored procedure **********************************

)
As
Begin
/**********************************************************
*Procedure Name: pa_get_client_info
*Database: probation
*Server: UT_SERVER
*CAST Tools5,1,3,490
*
*Business Function : Gets the name, address, and phone for a person number

*Author BF: ROM Date BF: 11/29/2006
*File Path:
* Z:\vc_agcy\pa\sql\development\pa_get_client_info
*********************************************************/

/* First, get their proper name */

/* Next, where do they live */

/* Now, the phone. */

Return

end

I think I must assume that the problem lies in the OleDB system software layer.  I can run this same procedure in MS SQL/Server using SQL Data Adapters and get full results.  When I run it in Sybase using the OleDB Data Adapters, I only get 1 1/2 out of 5 output columns. 

One output, a beautiful phone number should look like (800) 777-7777, but instead I only get () -.  Just the special characters.  Very odd.

I will look for other database connectivity options.  If anyone has any suggestions, I'm happy to learn!

Okay, here is the answer:

I ran afoul of the ansi null monster.  There was code in the stored procedures like (Where  [some variable] = NULL).

Had to change the syntax to (Where [some variable] IS NULL).

Magic.  It works now.