Visual Studio Web Newbie Question

[BSman]

I apologize if this is the wrong forum, but it's the closest I could find. I'm trying to develop a browser based database app using Visual Studio Web (back end is SQL Server 2000 at this point). Have lots of experience with Access, VBA, etc. I've set up a few data sources on the web page, some of which should use the value selected in a dropdownlist to limit the values displayed in another dropdownlist. I can't figure out how to do the following (or if they are possible) and haven't found answers in a book or the on line help:

1. When a value is selected in a dropdownlist I want to requery the SQL data source that is used for the next dropdownlist (or requery the next dropdownlist).

2. Be able to get the value of a column returned with the value selected in the dropdownlist. For example, if the data source returns ID and title, but title is what is displayed, is there a way for me to directly get the value of ID? (Similar to using the column specification of a combo box in Access to get values of columns not displayed, but returned by the underlying query.)

Thanks,
Bob

 

[jbenson001]

1. You can access the sqldatasource's select statment:

SqlDataSource1.SelectCommand = "Your Select statment"

2. You can get the value selected in the dropdown like this:

DropDownList1.SelectedValue

Jim

 

[BSman]

If the data source for the dropdownlist provides two values, in this case the ID and the description, is there a way to get the value of the ID for the text of the description selected by the user? Not all descriptions are unique, but the ID will always be unique, so I can't make a sqldatasource that uses the user selected text and be assured that I will return the correct ID.

If there's no way to get the ID directly, I guess I could create a virtual column that would combine the description with the ID and use that for the dropdownlist control as well as for the sqldatasource that would use the user selected text, but it doesn't create a very nice display.

Bob

 

[tperri]

ddlYourDropDown.SelectedValue gets your unique ID
ddlYourDropDown.SelectedIndex gets the index of the item in the DropDown
ddlYourDropDown.SelectedText gets your Display (not necessarily unqiue) text

 

[BoulderBum]

One thing I'd add is that you probably want to set the AutoPostBack property of the DropDownList to true if you're doing what you're planning. This will post back to the server every time the value of the DropDownList changes (which you'll want to do if you have to populate the next DropDownList with values from the database).

You can also spice up the process with AJAX/client callbacks/Atlas to potentially improve the user experience.

 

[tperri]

BoulderBum,

Do you know of the top of your head if you can use the Atlas Cascading DropDown in a DataGrid EditTemplate?

 

[BoulderBum]

I haven't actually got my hands dirty with Atlas yet, but I'd assume it would work as well in the EditTemplate as it would anywhere else.

If you try it out, please let us know!

 

[BSman]

Thanks to tperri. I did find that the text and value properties do indeed allow access to a second column that is part of the SQL source for the drop down list. This is a big help for me to achieve the result I'm looking for. And I couldn't find any explanation in the manuals/on line help about what the properties mean and how they can be used, even though this is a pretty basic requirement.

Right after I submitted the original posting I found the AutoPostBack property described in the ASP.Net for Dummies book as needed to be set to true for the textchange property to actually cause code to execute.

But I have one other manjor problem.....Once a value has changed for a drop down list and I've set up code to detect that situation, what is the code to force a requery of the data source used for the next DDL, since the next DDL is dependent upon the value selected by the user in the first DDL? I've tried some things that sometimes work for one of the DDLs on my form, but I can't find anyplace the right way to force a specific control to requery its data source.

Since my controls (which are typically DDLs) depend upon the value selected in a previous DDL, this is extremely important.

Bob

 

[jshurst]

You will need to write another function and bind the data on the selectedindex_changed event for the DDL. Don't forget to put If not page.ispostback in the form_load, otherwise the first ddl will be rebound everytime and you'll loose your value.

 

[BSman]

jshurst,

OK, you've lost me. I've been able to get code to execute when I change the value in, let's call it, DDL1. The next control (call it DDL2) has a data source that uses the value in DDL1 as it's filter criteria.

When you say "write another function", I think that that is exactly what I can't figure out how to do. As I see it (based on using VBA, etc.), I need to either write a statement that will either force DDL2 to re-execute its SQL data source or force the data source to re-execute and, as a result, re-populate the values which will be displayed in DDL2.

I found a DDL property called "AppendDataBoundItems" and set it to true, since based on the on line help it appeared that it might force a re-execution of the SQL data source for DDL2, but it didn't work. In fact, I have no idea what it did (or will do).

Bob

 

[jshurst]

Double click on the first DDL to access it's Selected_index changed event. Then add the code to requery the database. Something like

 Dim displaySpecificCodeDataTable As New DataTable
Dim sqlCommand As New SqlCommand("Select * from table where id = '" & ddl1.selectedvalue & "'", MyConnection1)

.CommandType = CommandType.Text

Dim dataAdapter As New SqlDataAdapter(sqlCommand)
dataAdapter.Fill(displaySpecificCodeDataTable)

ddl2.datasource = displaySpecificCodeDataTable
ddl2.databind()

Step through that, if you are not getting the correct value for ddl1.selectedvalue (or selecteditem.value) then you might be loosing the data when the form is rebound in the page_load event (I can walk you through that if that is the case).

P.S. I do pretty much eveything with code, so if you're using drag and drop stuff then someone else here is probably better equiped to help you (but I'll try).

 

[BSman]

I tried your code and received errors that said the types "datatable" and "sqlcommand" are not defined.

In order to attempt using the sqldatasource I already defined I then tried setting ddl2.datasource to the datasource control and followed that command with the ddl2.databind() statement. This did not work. No data was actually displayed.

It seems to all get back to some statement that I can't find that forces the sqldatasource to actually re-query the database to get new data. There should be a command hidden somewhere, since this is a basic requirement.

 

[jshurst]

You're not importing the namespace at the top of the page. Try this...

Dim displaySpecificCodeDataTable As New [blue]System.Data.DataTable()[/blue]
Dim sqlCommand As New  [blue]System.Data.SqlClient.SqlCommand[/blue]("Select * from table where id = '" & ddl1.selectedvalue & "'", MyConnection1)

.CommandType = CommandType.Text

Dim dataAdapter As New [blue]System.Data.SqlClient.SqlDataAdapter(sqlCommand)[/blue]
dataAdapter.Fill(displaySpecificCodeDataTable)

ddl2.datasource = displaySpecificCodeDataTable
ddl2.databind()

You'll have to change the sql statement and the connection to whatever yours are. Look at jbensons post above if you want to use the dataadapter that you already set up and just change the command.

 

[BSman]

I found the problem. In my sql statement for the query I set up the criteria to match the text contents of ddl1, rather than the (hidden) ID value. Once I changed the filter to use the ID column in the query rather than the text associated with the ID, it matched the result selected by the user in DDL1.

And it seems that the requery occurs automatically every time a value is selected by the user, so life is much easier.

Thanks for your help (and patience).

Bob