problems finding stored procedure

[jamert]

Hi i have a strange problem, never seen this before. i created a stored procedure in the sql2k db (have many procedures already, all working) Gave the Proc the same security credentials as usual. and am running this code, this result catches on the fact that the connection cannot find the Proc? it is there! but i cannot access it, i can run it in QA without issues, but i cannot get the code to process the simple return of a value... any thoughts?

The connection open successfully, but catches cause it cannot find the procedure?

Thanks

private void DOB(string switchVal)
{
          if (switchVal == "searchDOB")
            {
                using (SqlConnection connection = new SqlConnection(basic.config.Configuration.ConnectionInfo))
                {

                    SqlCommand cmd = new SqlCommand("getPatientDOB_select", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@MID", SqlDbType.Int).Value = Convert.ToInt32(1);

                    try
                    {
                        connection.Open();
                        object data = cmd.ExecuteScalar();  //sQ +'|'+ Convert(VarChar(7), MID)
                        

                        if (data != null || (string)data != "")
                        {
                            Session["dob"] = data.ToString();
                            string SplitVal = MdaBlowfish.decDOB(data.ToString());
                            //  mm/dd/yyyy
                            string[] DBitems = SplitVal.Split(new Char[] { '/' }, 3);
                            yyyy = DBitems[2].ToString();
                            mm = DBitems[0].ToString();
                            dd = DBitems[1].ToString();
                        }
                    }
                    catch (Exception ee)
                    {
                        string temt = ee.ToString();
                        // database error need to log errors
                    }
                    finally
                    {
                        if (connection != null)
                        {
                            connection.Close(); connection.Dispose(); cmd.Dispose();
                        }
                    }
                }


....
...
..

the proc:

CREATE PROCEDURE [dbo].[getPatientDOB_select]
(
@MID int
) 
AS


select TOP 1 (Convert(varchar(15),dob) + '|') as a1  from  [_MemberDemographics] 
where MemberID = @MID

GO

 

[jmeckley]

does the account connecting to the database have access to the proc?

because your wrapping the connection object in a using block you don't need the finally block

using(IDbConnection cnn = new SqlConnection())
{
   ...
}

is the same as

IDbConnection cnn = new SqlConnection();
try 
{
   ...
}
finally
{
   cnn.Dispose();
}

you also don't need to explicitly open/close the connection either.

Jason Meckley
Programmer
Specialty Bakers, Inc.