none
Trying to pass parameters to a stored procedure using old fashioned ADODB because I need a recordset not a dataset. RRS feed

  • Question

  • I have wasted a day on this. It seems like the recordsets that I get back return one record, and it is the subtotal line that is in the stored procedure, and it has subtotals of zero. When I call the procedure in Query Analyzer I get records back. So that tells me that the parameters are being passed as null because that would return zero records.

    ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

    Here is variable declaration in stored procedure.

    CREATE PROCEDURE finance_daily_facility_rev_report_mcr_new
    @facility_nm varchar(50),
    @Location_Code varchar(50)

     AS
    /* used to create rev report for consumption f the facilities*/

    SELECT    

    /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

     

     

    Here is the code I am using.

    //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

    // Create a Recordset from all the records in the stored procedure.

    ADODB.Connection objConn = new ADODB.Connection();

    ADODB._Recordset objRS = null;

    objConn.Open("Provider=sqloledb;Data Source=" +

    "ptpsql02" + ";" + "Initial Catalog = Lc_Data_Wrhse;", "AvaliduserId", AvalidPassword, 0);

    objConn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

    object objRecAff;

    ADODB.Command cmd = new ADODB.Command();

    ADODB.Parameter param = new ADODB.ParameterClass();

    ADODB.Parameter param2 = new ADODB.ParameterClass();

    param2.Name = "@facility_nm";

    param = cmd.CreateParameter("@facility_nm",

    ADODB.DataTypeEnum.adVarChar,

    ADODB.ParameterDirectionEnum.adParamInput,

    50, "'" + Hospital + "'");

    param2 = cmd.CreateParameter("@Location_Code",

    ADODB.DataTypeEnum.adVarChar,

    ADODB.ParameterDirectionEnum.adParamInput,

    50,

    "'"+Campus+"'");

    //add Parameters ot

    cmd.Parameters.Append(param);

    cmd.Parameters.Append(param2);

    Debug.Write(cmd.Parameters[0].Value);

    object objParameters = cmd.Parameters;

    cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;

    //cmd.Parameters.Append(param);

    if (Medicare)

    {

    cmd.CommandText = "finance_daily_facility_rev_report_comm_new";

    }

    else

    {

    cmd.CommandText = "finance_daily_facility_rev_report_mcr_new";

    }

    cmd.ActiveConnection = objConn;

    cmd.CommandTimeout = 600;

    cmd.Parameters[0].Value = Hospital;

    cmd.Parameters[1].Value = Campus;

    objRS = (ADODB._Recordset)cmd.Execute(out objRecAff, ref objParameters, (int)ADODB.CommandTypeEnum.adCmdStoredProc);

    objRS.MoveLast();

    objRS.MoveFirst();

     

    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

    Tuesday, April 1, 2008 9:58 PM

Answers

  •  

    I found an answer. I left the code that I changed in here but commented it out. The trick was to call refresh to the the command object to pull the parameters from the database, then set them. I did have to use ADODB for this application because I was using automation to place the recordset into an Excel spreadsheet, and it cannot use a dataset yet.

     

    ADODB.Connection objConn = new ADODB.Connection();

    ADODB._Recordset objRS = null;

    objConn.Open("Provider=sqloledb;Data Source=" +

    "ptpsql02" + ";" + "Initial Catalog = Lc_Data_Wrhse;", "Something", "Something", 0);

    objConn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

    object objRecAff;

    ADODB.Command cmd = new ADODB.Command();

     

    cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;

    cmd.ActiveConnection = objConn;

    cmd.CommandTimeout = 600;

    //cmd.Parameters.Append(param);

    if (!Medicare)

    {

    cmd.CommandText = "finance_daily_facility_rev_report_comm_new";

    }

    else

    {

    cmd.CommandText = "finance_daily_facility_rev_report_mcr_new" ;

    }

    cmd.Parameters.Refresh();

    cmd.Parameters[1].Value = Hospital;

    cmd.Parameters[2].Value = Campus;

    //param = cmd.CreateParameter("@facility_nm",

    // ADODB.DataTypeEnum.adVarChar,

    // ADODB.ParameterDirectionEnum.adParamInput,

    // (int)Hospital.ToString().Length, Hospital);

     

    //param2 = cmd.CreateParameter("@Location_Code",

    // ADODB.DataTypeEnum.adVarChar,

    // ADODB.ParameterDirectionEnum.adParamInput,

    // (int)Campus.ToString().Length,

    // Campus);

    //add Parameters ot

    //cmd.Parameters.Append(param);

    //cmd.Parameters.Append(param2);

    object objParameters = cmd.Parameters;

     

    objRS = (ADODB._Recordset)cmd.Execute(out objRecAff, ref objParameters, (int)ADODB.CommandTypeEnum.adCmdStoredProc);

    Wednesday, April 2, 2008 9:36 PM

All replies

  • Do you need to use ADODB? Can you use native ADO.NET classes (or better yet, Enterprise Library Data Access Application Block)?

     

    Wednesday, April 2, 2008 8:14 PM
  •  

    I found an answer. I left the code that I changed in here but commented it out. The trick was to call refresh to the the command object to pull the parameters from the database, then set them. I did have to use ADODB for this application because I was using automation to place the recordset into an Excel spreadsheet, and it cannot use a dataset yet.

     

    ADODB.Connection objConn = new ADODB.Connection();

    ADODB._Recordset objRS = null;

    objConn.Open("Provider=sqloledb;Data Source=" +

    "ptpsql02" + ";" + "Initial Catalog = Lc_Data_Wrhse;", "Something", "Something", 0);

    objConn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

    object objRecAff;

    ADODB.Command cmd = new ADODB.Command();

     

    cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;

    cmd.ActiveConnection = objConn;

    cmd.CommandTimeout = 600;

    //cmd.Parameters.Append(param);

    if (!Medicare)

    {

    cmd.CommandText = "finance_daily_facility_rev_report_comm_new";

    }

    else

    {

    cmd.CommandText = "finance_daily_facility_rev_report_mcr_new" ;

    }

    cmd.Parameters.Refresh();

    cmd.Parameters[1].Value = Hospital;

    cmd.Parameters[2].Value = Campus;

    //param = cmd.CreateParameter("@facility_nm",

    // ADODB.DataTypeEnum.adVarChar,

    // ADODB.ParameterDirectionEnum.adParamInput,

    // (int)Hospital.ToString().Length, Hospital);

     

    //param2 = cmd.CreateParameter("@Location_Code",

    // ADODB.DataTypeEnum.adVarChar,

    // ADODB.ParameterDirectionEnum.adParamInput,

    // (int)Campus.ToString().Length,

    // Campus);

    //add Parameters ot

    //cmd.Parameters.Append(param);

    //cmd.Parameters.Append(param2);

    object objParameters = cmd.Parameters;

     

    objRS = (ADODB._Recordset)cmd.Execute(out objRecAff, ref objParameters, (int)ADODB.CommandTypeEnum.adCmdStoredProc);

    Wednesday, April 2, 2008 9:36 PM