Loading a Dropdown using a stored procedure

Hi,

 

I am trying populate a dropdown list on a webpage by using a store procedure. Basically when the SP is executed it will return the names of all of the active users in the database, which should then be populated into the ddl, its a basic get SP.

 

However i cannot figure out how to implement it with an infragisitcs dropdown. (i do have it working for a regular asp dropdown.)  ive tried this:

 

        WebDropDown1.DataSource = dataAccess.getUsers();;

WebDropDown1.TextField = "ProductName";

WebDropDown1.DataBind();

 

Where getUsers() is the SP. Any thoughts?

Hello,

You can use and SqlDataSource:

        SqlDataSource sds = new SqlDataSource();
        sds.ConnectionString = myConnectionString;
        sds.SelectCommand="getUsers";
        sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;

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

Please let me know if you have any questions.

Thanks,

Valerie

 

 

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

so i fixed the issue i was having before, now the page loads but in the ddl all it displays is System.Data.DataRowView

 

 

code behind:

WebDropDown1.DataSource =

 

dataAccess


.getEmployees();

WebDropDown1.TextField =

 

"Craftsperson"


;

WebDropDown1.ValueField =

 

"Craftsperson"


;

WebDropDown1.DataBind();

 

aspx:

 

 

 

 

<


ig


:


WebDropDown




ID


="WebDropDown1"




runat


="server"




Width


="200px">

 

 

 

</


ig


:


WebDropDown


>

your thoughts?

so i fixed the issue i was having before, now the page loads but in the ddl all it displays is System.Data.DataRowView

 

 

code behind:

WebDropDown1.DataSource =

 

dataAccess


.getEmployees();

WebDropDown1.TextField =

 

"Craftsperson"


;

WebDropDown1.ValueField =

 

"Craftsperson"


;

WebDropDown1.DataBind();

 

aspx:

 

 

 

 

<


ig


:


WebDropDown




ID


="WebDropDown1"




runat


="server"




Width


="200px">

 

 

 

</


ig


:


WebDropDown


>

your thoughts?

i fixed my final issue, had to do with what i named the textfield and valuefields….they didnt match up with what i was trying to pull with my SP. Thanks for all of your help!