Well the thing is that my connection string is in my web.config file, so i was trying to avoid using it in two places within the site.

Hello,

You can get the connection string from the web.config:

sds.ConnectionString = WebConfigurationManager.ConnectionStrings["myConnectionString"].ToString();

Please let me know if you have any questions.

Thanks,

Valerie

Ok so this is what i tried:

                    SqlDataSource sds = new SqlDataSource();
                    sds.ConnectionString = WebConfigurationManager.ConnectionStrings["myConnectionString"].ToString();
                    sds.SelectCommand = "getEmployees";
                    sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;

                    WebDropDown1.DataSource = sds;
                    WebDropDown1.TextField = "Craftsperson";
                    WebDropDown1.ValueField = "Craftsperson";

This is what is in my dataAccess.cs file that i had been using to get to my store procedures:

    #region getEmployees()
    /// <summary>
    /// getEmployees()
    /// </summary>
    /// <returns></returns>
    public static SqlDataReader getEmployees()
    {
        SqlConnection connection = null;
        try
        {
            connection = new SqlConnection(utilities.getConnectionString());
            SqlCommand command = new SqlCommand("dbo.spGetEmployees", connection);
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter returnValueParam = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
            returnValueParam.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(returnValueParam);

            connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            if (connection != null)
                connection.Close();
            throw;
        }
    }
    #endregion

when i run it on my site it get an "Object reference not set to an instance of an object." error on my site. Am i just being thick and need to put something else in the code you gave me to get this to work?

Thanks for your help!

Hi,

I didn't realize you were getting you data from a method call. Your best option is to change your method to return a DataSet instead of an SqlDataReader and then bind the dataset to the WebDropDown using the original syntax you provided.

 WebDropDown1.DataSource = dataAccess.getUsers();;

        WebDropDown1.TextField = "ProductName";

        WebDropDown1.DataBind();

Please let me know if you have any questions.

Thanks,
Valerie

I am still having a bit of trouble getting this all figure out. I tried developing the dataset you mentioned inside my dataAccess.cs file, here is what i came up with:

    #region getEmployees()
    /// <summary>
    /// getEmployees()
    /// </summary>
    /// <returns></returns>
    public static DataSet getEmployees()
    {

        SqlConnection connection = null;
        try
        {
            connection = new SqlConnection(utilities.getConnectionString());
            SqlCommand command = new SqlCommand("dbo.spGetEmployees", connection);
            command.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataSet dsEmployees = new DataSet();
            adapter.Fill(dsEmployees, "fullname");

            connection.Open();
            return dsEmployees;
        }
        catch
        {
            if (connection != null)
                connection.Close();
            throw;
        }
    }
    #endregion

when i try to load the site i get a Cannot find the object 'spGetEmployees', because it does not exist or you do not have permission. i am not sure why it does this because i left the connection part of the code alone. Here is my SP, im not sure if that will help at all, it selects the id of the person and combines there first, last, middle names together.

SELECT user_id ,
ced_pref_sur + ', ' + ced_pref_giv + ' ' + ced_pref_mi AS fullname
FROM r1.dbo.database (NOLOCK)
ORDER BY fullname

I know this piece of code works correctly, i am worried that since the statement is selecting two values something is happening when the dataset is being loaded, but ultimately i am not sure (i have never used a dataSet before, im rather new to this)

Thanks again!

Hello,

Your code looks fine. I noticied you are using dbo.spGetEmployees in your command and r1.dbo.database in your stored procedure, the error may have to do with how you are connecting to your database or database server. If your connection string does not specify the specific database then you may need to call your stored procedure as r1.dbo.spGetEmployees.

Valerie