vb.net dynamic drop down list

Hi Experts,

Im trying to code a dynamic drop down list in vb.net (using visual studio). I have created a connection to the database and have dragged a drop down box onto the page from the menu, called DropDownList.

I know that I need to include a SQL query SELECT DISTINCT M_REFERENCE FROM CAPD_MODULE WHERE M_REFERENCE like 0%

Can anyone help?

Many Thanks.
deanlee17Asked:
Who is Participating?
 
CodeCruiserConnect With a Mentor Commented:
I think with Oracle, you would need to manually populate the list in code behind. Something like below

Dim dbadp As New OracleDataAdapter("Select ...", "connection string")
Dim dTable As New DataTable
dbadp.Fill(dTable)
dbadp.Dispose()

DropDownList1.DataSource = dTable
DropDownList1.DataTextField = "column name"
DropDownList1.DataValueField = "column name"
DropDownList1.DataBind()
0
 
CodeCruiserCommented:
Is this for ASP.NET? Are you using SQL Server?

Use a SQLDataSource control as shown in this example

http://www.java2s.com/Tutorial/ASP.NET/0360__ADO.net-Database/LinkaspDropDownListwithaspSqlDataSource.htm
0
 
Pratima PharandeCommented:
SqlDataReader ddDR = null;
SqlConnection ddSqlConnection = new SqlConnection("server=(local);database=[dbname];user id=username;password=password;connection reset=false;connection lifetime=5;min pool size=1;max pool size=50;");
SqlCommand ddSqlCommand = new SqlCommand("SELECT * FROM TableName", ddSqlConnection);
ddSqlConnection.Open();
ddDR = ddSqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
DropDownList1.DataSource = ddDR;
DropDownList1.DataTextField = "DisplayValueFieldFromDB";
DropDownList1.DataValueField = "DataValueFieldFromDB";
DropDownList1.DataBind();


more details refer
http://www.codersource.net/asp-net/asp-net-articles/dropdownlist-in-asp-net.aspx
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
deanlee17Author Commented:
Hmmm got the error 'declaration expected'

The window now looks like this....


Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

        If Me.TextBox1.Text = "" Then

            Me.Button1.Text = "Click to clear fields"
            Me.TextBox1.Text = "I have now changed!!!!!"
            Me.TextBox2.Text = "Click the button again to clear us!!"
        Else
            Me.TextBox1.Text = ""
            Me.TextBox2.Text = ""
            Me.Button1.Text = "Click to populate fields"
        End If

    End Sub



SqlDataReader ddDR = null;

SqlConnection ddSqlConnection = new SqlConnection("server=(local);database=[XXX];user id=XXX;password=XXX;connection reset=false;connection lifetime=5;min pool size=1;max pool size=50;");

SqlCommand ddSqlCommand = new SqlCommand("SELECT M_REFERENCE FROM CAPD_MODULE", ddSqlConnection);
ddSqlConnection.Open();
ddDR = ddSqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
DropDownList1.DataSource = ddDR;
DropDownList1.DataTextField = "M_REFERENCE";
    'DropDownList1.DataValueField = "DataValueFieldFromDB";
DropDownList1.DataBind();



End Class

Open in new window

0
 
deanlee17Author Commented:
Codecruiser its for ASP.NET?
0
 
CodeCruiserCommented:
Yes.

Code provided by pratima_mcs is in C#.
0
 
Pratima PharandeCommented:
Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

        If Me.TextBox1.Text = "" Then

            Me.Button1.Text = "Click to clear fields"
            Me.TextBox1.Text = "I have now changed!!!!!"
            Me.TextBox2.Text = "Click the button again to clear us!!"
        Else
            Me.TextBox1.Text = ""
            Me.TextBox2.Text = ""
            Me.Button1.Text = "Click to populate fields"
        End If
SqlDataReader ddDR = null;

SqlConnection ddSqlConnection = new SqlConnection("server=(local);database=[XXX];user id=XXX;password=XXX;connection reset=false;connection lifetime=5;min pool size=1;max pool size=50;");

SqlCommand ddSqlCommand = new SqlCommand("SELECT M_REFERENCE FROM CAPD_MODULE", ddSqlConnection);
ddSqlConnection.Open();
ddDR = ddSqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
DropDownList1.DataSource = ddDR;
DropDownList1.DataTextField = "M_REFERENCE";
    DropDownList1.DataValueField = "M_REFERENCE";
DropDownList1.DataBind();
    End Sub







End Class

Open in new window


try thsi

where you are exactly getting error
0
 
Pratima PharandeCommented:
Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

        If Me.TextBox1.Text = "" Then

            Me.Button1.Text = "Click to clear fields"
            Me.TextBox1.Text = "I have now changed!!!!!"
            Me.TextBox2.Text = "Click the button again to clear us!!"
        Else
            Me.TextBox1.Text = ""
            Me.TextBox2.Text = ""
            Me.Button1.Text = "Click to populate fields"
        End If
Dim ddDR As SqlDataReader = Nothing

Dim ddSqlConnection As New SqlConnection("server=(local);database=[XXX];user id=XXX;password=XXX;connection reset=false;connection lifetime=5;min pool size=1;max pool size=50;")

Dim ddSqlCommand As New SqlCommand("SELECT M_REFERENCE FROM CAPD_MODULE", ddSqlConnection)
ddSqlConnection.Open()
ddDR = ddSqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
DropDownList1.DataSource = ddDR
DropDownList1.DataTextField = "M_REFERENCE"
DropDownList1.DataValueField = "M_REFERENCE"
DropDownList1.DataBind()

    End Sub
0
 
CodeCruiserCommented:
@pratima_mcs

I really liked your blend of VB and C# :-)
0
 
deanlee17Author Commented:
See attached errors.

i see the errors when I hit 'view in browser '
error.doc
0
 
deanlee17Author Commented:
Im trying to stick to vb please :-)
0
 
CodeCruiserCommented:
Did you try my example?
0
 
deanlee17Author Commented:
CodeCruiser im going to have a try now. But i thought alot of the code should be in the default.aspx.vb page not in the default.aspx page where all the html is?
0
 
Pratima PharandeCommented:
0
 
deanlee17Author Commented:
Code cruiser I went with your example and i now have....

 <asp:DropDownList ID="DropDownList1" runat="server" ConnectionString="<%$ Data Source=unite;Persist Security Info=True;User ID=XXX;Password=XXX;Unicode=True %>">

    SelectCommand="SELECT DISTINCT M_REFERENCE FROM MODULE ORDER BY [M_REFERENCE]"></asp:SqlDataSource>
     
        &nbsp;</div>
        Choose a genre:
        <asp:DropDownList ID="Module"
                          runat="server"
                          AutoPostBack="True"
                          DataSourceID="SqlDataSource1"
                          DataTextField="Genre"
                          DataValueField="Genre">


    </asp:DropDownList>

I got the following error.....

Parser Error Message: The expression '<%$ Data Source=unite;Persist Security Info=True;User ID=XXX;Password=XXX;Unicode=True %>' is invalid. Expressions use the syntax <%$ prefix:value %>.
0
 
CodeCruiserCommented:
That code is not from my example is it? My example uses a SQLDataSource control.



<asp:SqlDataSource ID="booksDataSource"
                           runat="server"
                           ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT * FROM [Books] WHERE ([Genre] = @Genre)">
        </asp:SqlDataSource>

    <asp:DropDownList ID="Module"
                          runat="server"
                          AutoPostBack="True"
                          DataSourceID="SqlDataSource1"
                          DataTextField="Genre"
                          DataValueField="Genre">


    </asp:DropDownList>
0
 
deanlee17Author Commented:
Oh i thought i needed to change <%$ ConnectionStrings:ConnectionString %>".

Ok im back to the original example, tried to view in browser and I get: Error creating control, the end tag is missing for

<asp:DropDownList ID="DropDownList1" runat="server" ConnectionString=<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT M_REFERENCE FROM MODULE ORDER BY [M_REFERENCE]"></asp:SqlDataSource>
0
 
CodeCruiserCommented:
Please try to follow the example. You are mixing up the code.  The connection string and selectcommand go into the <asp:SQLDataSource> control not the DropDownList control. You have the example above in my previous comment.
0
 
deanlee17Author Commented:
Ok changed  that. Hit view in browser and all i get is....

------ Build started: Project: WebApplication2, Configuration: Debug Any CPU ------
  WebApplication2 -> C:\Documents and Settings\dlee.STARGATE.001\Desktop\WebApplication2\WebApplication2\bin\WebApplication2.dll
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

But it doesnt load th page?!
0
 
CodeCruiserCommented:
Because its a DLL.
0
 
CodeCruiserCommented:
What type of project template did you choose for the project?
0
 
deanlee17Author Commented:
ASP.net web application.
0
 
Pratima PharandeCommented:
0
 
CodeCruiserCommented:
Are you precompiling then? The build process is producing a DLL. Try "Start Debugging" or press F5.
0
 
deanlee17Author Commented:
pratima_mcs: I have not tried it yet.

CodeCruiser: i pressed F5 and it puts it into running mode and adjusts the window, but doesnt actually launch a browser :(
0
 
CodeCruiserCommented:
In the solution explorer, right click on the page and click on "Set As Startup Page" or right click the page and click "View In Browser"
0
 
deanlee17Author Commented:
Ah good working again.


Error: Exception Details: System.InvalidOperationException: The connection name 'ConnectionString' was not found in the applications configuration or the connection string is empty.

Didnt like.....

<asp:SqlDataSource ID="booksDataSource"
0
 
CodeCruiserCommented:
Its in the following

<asp:SqlDataSource ID="booksDataSource"
                           runat="server"
                           ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT * FROM [Books] WHERE ([Genre] = @Genre)">
        </asp:SqlDataSource>


Now either add a connection string to the web.config or change this

 ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

to your connection string. For example

 ConnectionString="Data Source = .\SQLExpress; Initial Catalog = dbname; Integrated Security = True"
0
 
deanlee17Author Commented:
Ok seem to have sorted that with....

ConnectionString="Data Source=unite;Persist Security Info=True;User ID=XXX;Password=XXX;Unicode=True"

Now have error....

Parser Error Message: The server tag is not well formed.

Source Error:


Line 30:         </asp:SqlDataSource>
Line 31:
Line 32:     <asp:DropDownList ID="Module"
Line 33:                           runat="server"
Line 34:                           AutoPostBack="True"
 

Source File: /Default.aspx    Line: 32


Will be back in 30 minutes.
0
 
CodeCruiserCommented:
Is it because you are enclosing the username and password in quotes?
0
 
deanlee17Author Commented:
Ok code now looks like the attached.

Im getting 'Keyword not supported: 'unicode'. '

username and password were not in quotes.
<asp:SqlDataSource ID="booksDataSource" 
                           runat="server" 
                           ConnectionString="Data Source=unite;Persist Security Info=True;User ID=XXX;Password=XXX;Unicode=True"
            SelectCommand="SELECT M_REFERENCE FROM [MODULE]">
        </asp:SqlDataSource>


    <asp:DropDownList ID="Module"
                          runat="server"
                          AutoPostBack="True"
                          DataSourceID="booksDataSource"
                          DataTextField="M_REFERENCE"
                          DataValueField="M_REFERENCE">

    </asp:DropDownList>

Open in new window

0
 
CodeCruiserCommented:
Remove the unicode part then.
0
 
deanlee17Author Commented:
0
 
CodeCruiserCommented:
Is it not self explanatory?
0
 
deanlee17Author Commented:
Yes the problem is, but not the solution. Its an Oracle database that we are accessing.

I assumed it was a problem with the connection string, however i copied and pasted it from a previous data source that i created.
0
 
CodeCruiserCommented:
Oh wow. So you are trying to access Oracle database using the SQL Server provider. This is turning into a real test of my patience. You need to download the Oracle Data Provider for .NET (google for ODP.NET)
0
 
deanlee17Author Commented:
Well im sorry to test your patience.
0
 
CodeCruiserCommented:
Its ok :-) Have you downloaded ODP?
0
 
deanlee17Author Commented:
No, im reading up on it now. I assumed it wass something the network admin would have to install on the server.
0
 
deanlee17Author Commented:
CodeCruiser, can I not in any way use the connection that already exists, that i created earlier, see attached. error.doc
0
 
CodeCruiserCommented:
You can not use a SQLDataSource to access Oracle.
0
 
deanlee17Author Commented:
So can i download ODP on my system alone as opposed to the actual server where the database sits?
0
 
deanlee17Author Commented:
Just looking through a previous attempt to create a connection (that works)

The code was....

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
    SelectCommand="SELECT DISTINCT &quot;CLS_REF&quot; FROM &quot;MIS_BN_COURSE_LIST&quot;">
</asp:SqlDataSource>

and it appears to have created the connection in the web.config file as....

<connectionStrings>
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
      providerName="System.Data.SqlClient" />
    <add name="ConnectionString" connectionString="Data Source=unite;Persist Security Info=True;User ID=XXX;Password=XXX;Unicode=True"
      providerName="System.Data.OracleClient" />
  </connectionStrings>


Is there anything wrong with doing it this way?

Thanks.
0
 
CodeCruiserCommented:
Yes. It does not need to be installed on the server. It needs to be on the computer where the program runs.
0
 
deanlee17Author Commented:
Ok thanks,

Did you see my previous post?
0
 
CodeCruiserCommented:
>Is there anything wrong with doing it this way?
Yes. Its like trying to open a Toyota with a Honda key.
0
 
deanlee17Author Commented:
lol good analogy. But it does work :)
0
 
CodeCruiserCommented:
Really? With SQLDataSource? That is strange. But do download the ODP and use that to avoid problems. Or use OLEDB
0
 
deanlee17Author Commented:
Well i do like to do things the correct way, so i have requested that the ODP be downloaded and installed. It does currently work, but I could not tell you how lol.

Shall i scrap the code that we were working on then, or is a slight tweak to chage from accessing a SQL server to Oracle?
0
 
deanlee17Author Commented:
Ok mate thanks very much. i think ive taken up enough of your time now.
0
 
CodeCruiserCommented:
Its alright. Let me know if you encounter any problem.
0
 
deanlee17Author Commented:
ok, thanks....
0
 
deanlee17Author Commented:
btw, hope the weather is as nice up there as it is in Essex :)
0
 
CodeCruiserCommented:
It is actually very nice but sitting inside the office, it does not matter :-(
0
 
deanlee17Author Commented:
Using oledb....

Dim con As New OleDb.OleDbConnection("Data Source=unite;User Id=unite;Password=unitehar005;Integrated Security=no;")


 Dim dbadp As New OleDb.OleDbDataAdapter("Select M_REFERENCE FROM MODULE", "connection string")
    Dim dTable As New DataTable
dbadp.Fill(dTable)
dbadp.Dispose()

DropDownList1.DataSource = dTable
DropDownList1.DataTextField = "M_REFERENCE"
DropDownList1.DataValueField = "M_REFERENCE"
DropDownList1.DataBind()

I keep getting decleration expected on every line after....dbadp.Fill(dTable)

Thanks
0
 
CodeCruiserCommented:
That is strange. "Dim dTable As New DataTable" is on new line right?
0
 
deanlee17Author Commented:
yuuuup
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.