[RESOLVED] populate DDL using data from database

I have a simple ASP.NET assignment to complete. We are supposed to create a simple database with one table. That table should contain records that contain a question and 3 possible choices. We need to populate a DDL with the 3 possible choices. But, as far as I can tell, you can only populate a DDL with a FIELD of values, not select values from one row. So, I'm confused.

Is it possible to get select values from a row of data in a databse using an AccessDataSource and populate a DDL with them?

Ideally, for this requirement table should be created like this:


And you can fill out the answer dropdownlist with this query like:

Select AnswerID, AnswerText from tblAnswer where QuestionID = @CurrentQuestionID


Hope it helps./.

This makes sense. Although the instructor required that we only use one table for all of the data including the question text and the 3 answers. I'll try your suggestion though. I'll be happy if I can get it to work like this. I'm not that familiar with this stuff yet but I think that your suggestion invloves using some "code-behind" to make this happen. The way I'm doiing it now is through the Data Source Wizard. So I'm not quite sure if I'll be able to figure this out but I'll give it a shot.

Using the Wizard you can only set the name and value properties to a FIELD. No matter what select statement I build I can only get values from a FIELD, not values from an individual record. Like, I want to get the 3 answers from one record and populate the DDL with them. I don't know if it's possible to do that.



Ok. You can do that as well.

The query will be a simple UNION ALL Query.

Check this out:

select AnswerText1 AS 'Answers' from tblQuestionAnswer
Where QuestionID = 1
union all
select AnswerText2 AS 'Answers' from tblQuestionAnswer
Where QuestionID = 1
union all
select AnswerText3 AS 'Answers' from tblQuestionAnswer
Where QuestionID = 1

I have used SqlDataSource instead of AccessDataSource here:



<asp:DropDownList ID="ddlAnswer" runat="server" DataSourceID="SqlDataSource1" DataTextField="Answers">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:testdatabaseConnectionString %>"
    SelectCommand="select AnswerText1 AS 'Answers' from tblQuestionAnswer union all select AnswerText2 AS 'Answers' from tblQuestionAnswer union all select AnswerText3 AS 'Answers' from tblQuestionAnswer">



hope it helps./.

Right on. That sounds more like what I need to do. I forgot about UNION ALL (just had a DB course last semester, so that stuff is slowly coming back to me now). I'll give this idea a shot. I'll report back one way or the other.



Thanks! Your UNION ALL solution worked. I had to change the code to work with an Access Data Source but it worked. I still have to tweak the select statement a little too so it grabs grab the exact data values from my DB that I need in the DDL but it's working.


Thanks again!

thoughts on "[RESOLVED] populate DDL using data from database"

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>