Hi all,
I have a Project due after one week. It is a web service project. I have a Web Form which communicates to the web service and this web service communicates to the database. I have all my SQL statements in the Data Acess Layer to create more secure web service application. The Web service class is also in the Data Access Layer. I need to populates the 4 Dropdown list boxes on the web form from one table. I have Customer table which contains information About CustomerName, CustomerType, Address, City. These 4 colum values should populate the 4 Dropdown lists. And then I need to implement those 4 dropdowns like this. When the user selects a Customername it should display relevant customer information in the GridView. Or when the user selects values from all 4 listboxes it should display all information on GridView as well.
For all the above I have just populated one listbox but I don't know how to populate the other 3 dropdown lists and implement them on the web form.
I have created one method in the Data Access Layer class and one method in the Web Service class and then I instaitiated the web service object in the web form by adding a web reference. It works fine for just populating one dropdown list box but for the whole 4 dropdown list I am unable to populated and Implement them.
Below are the methods and the code for one dropdownlist.
 
(1)Web Form code:

 WebService.Service ws = new WebService.Service();
 protected void Page_Load(object sender, EventArgs e)
      {

         object[] alist;
         alist = ws.GetBusinessType(string.Empty);
         DropDownList1.DataSource = alist;
          DropDownList1.DataBind();
      

(2) Web Service Method:

      [WebMethod]
      public ArrayList GetBusinessType(string btype)
      {
                  DAccess ODA = new DAccess(); //Data Access object
                  ArrayList list = new ArrayList();
                  list = ODA.GetBusinessType(btype); //Data Access method
                  return list;
      }

(3) Data Access method:
public ArrayList GetBusinessType(string btype)
      {
         SqlConnection connection = GetConnection;
         try
         {
            ArrayList arrbt = new ArrayList();
                       
            StringBuilder sql = new StringBuilder()
            .AppendLine("SELECT")
            .AppendLine("\tTYPENAME")
            .AppendLine("\tFROM")
            .AppendLine("\tBUSINESS_TYPE");
            SqlParameter paramTYPENAME = new SqlParameter("TYPENAME", SqlDbType.NVarChar);
            paramTYPENAME.Value = btype;
            connection.Open();
            SqlDataReader reader = ExecuteQuery(connection, sql.ToString(), paramTYPENAME);
            
            while (reader.Read())
            {              
              string result = reader["TYPENAME"].ToString();
              arrbt.Add(result);             
            }
            return arrbt;
         }
         catch (Exception ex)
         {
            return null;
            //do some log here Call a class that logs the errors 
         }
         finally
         {
            connection.Close();
         }

All the above code works fine for just populating the one dropdown list but not implementing. I just want the implementation something like....When the user select one dropdown or all of them then it should display the result in the gridview. I have not shown the aspx code here but the main thing is the code above. Can any one help me to implement this
I really appreciate your help. Looking forward to your reply.
Thanks
Koonda

In your Page_Load, you have no test for not IsRepost! The dropdownlist was keep getting re-loaded. Why not load all the drop-down lists during page_load or are you trying to get a cascade whereby selecting one dropdown selects the records to populate the next?
You will need to disable postback for each of the DDL and provide a command button to load the GridView.
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped. 

Thanks TATWORTH for your quick reply. I also have page postback thing but I forgot to include in the code. The main problem is how can I populate the 4 Dropdowns from just one table and then how to implement them?
 
Please give me more ideas, suggestions, and code examples to figure out the problem. You can see my code above but I cannot implement it further for 3 more dropdownlists and other functionality for users when they select they should get the desired output.
 
Thanks and looking forward to your reply.
 
Koonda

Without knowing your table structure, I cannot think of what could be put in the other DDL.
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped. 

Sorry for my late reply I was very busy. Ok, I have a table called Customer. I have the columns BusinessName, BusinessType, Address, City. I want to populate 4 Dropdownlist boxes for each of these 4 column from the Customer table. Then I want to implement it. Your urgent required if possible for you because I have very short time to submit the project.
Looking forward to your reply.
 
Thanks
 
Koonda 
 
 

From you reply, I a, assuming that that BusinessName, BusinessType, Address, City are all stored as text within the Customer table. Hence you need the following TSQL to populate your other drop-down lists.
SELECT '   ' UNION SELECT DISTINCT BusinessName FROM Customer ORDER BY 1
SELECT '   ' UNION SELECT DISTINCT BusinessType FROM Customer ORDER BY 1
SELECT '   ' UNION SELECT DISTINCT Address FROM Customer ORDER BY 1
SELECT '   ' UNION SELECT DISTINCT City FROM Customer ORDER BY 1
In your select pass in all 4 as VARCHAR(50?), then 
IF DATALENGTH(RTRIM(@BusinessName )) = 0 SET @BusinessName = Null   -- and so on
SELECT BusinessName, BusinessType, Address, City FROM CUSTOMER
WHERE COALESCE(@BusinessName, BusinessName) = BusinessName
AND COALESCE(@City , City ) = City -- and so on for other columns
This will have the effect of allowing a selection from any combination.
 
 
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped. 

Thanks for your reply but I am still confused on this querry. Did you mean that I should use this Query in the Data Access Layer?. But the other issue is on the implementation. Would you be able to send me some code examples relating to my question? I really appreciate your help.
 
Looking forward to your reply.
 
Koonda

These are for you drop-down lists
SELECT '   ' UNION SELECT DISTINCT BusinessName FROM Customer ORDER BY 1
SELECT '   ' UNION SELECT DISTINCT BusinessType FROM Customer ORDER BY 1
SELECT '   ' UNION SELECT DISTINCT Address FROM Customer ORDER BY 1
SELECT '   ' UNION SELECT DISTINCT City FROM Customer ORDER BY 1
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped. 

In you select stored procedure to populate the datagrid (or gridview), use something like:
Pass in the select parameters as VARCHAR(50?), then 
IF DATALENGTH(RTRIM(@BusinessName )) = 0 SET @BusinessName = Null   -- and so on
SELECT BusinessName, BusinessType, Address, City FROM CUSTOMER
WHERE COALESCE(@BusinessName, BusinessName) = BusinessName
AND COALESCE(@City , City ) = City -- and so on for other columns
This will have the effect of allowing a selection from any combination.
Regrettably, I will not be able to provide you with sample code until the weekend when I will be at home and on my own development box. Please post a message around 12:00 UTC next Friday to remind me.
 
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.