?
Solved

vb.net dynamic drop down list

Posted on 2011-04-20
57
Medium Priority
?
508 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:deanlee17
  • 28
  • 24
  • 5
57 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431540
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35431546
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
 

Author Comment

by:deanlee17
ID: 35431572
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:deanlee17
ID: 35431575
Codecruiser its for ASP.NET?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431579
Yes.

Code provided by pratima_mcs is in C#.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35431580
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35431590
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431593
@pratima_mcs

I really liked your blend of VB and C# :-)
0
 

Author Comment

by:deanlee17
ID: 35431597
See attached errors.

i see the errors when I hit 'view in browser '
error.doc
0
 

Author Comment

by:deanlee17
ID: 35431599
Im trying to stick to vb please :-)
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431603
Did you try my example?
0
 

Author Comment

by:deanlee17
ID: 35431617
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35431633
0
 

Author Comment

by:deanlee17
ID: 35431644
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431658
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
 

Author Comment

by:deanlee17
ID: 35431673
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431703
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
 

Author Comment

by:deanlee17
ID: 35431733
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431740
Because its a DLL.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431742
What type of project template did you choose for the project?
0
 

Author Comment

by:deanlee17
ID: 35431749
ASP.net web application.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35431753
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431755
Are you precompiling then? The build process is producing a DLL. Try "Start Debugging" or press F5.
0
 

Author Comment

by:deanlee17
ID: 35431767
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431775
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
 

Author Comment

by:deanlee17
ID: 35431790
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431801
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
 

Author Comment

by:deanlee17
ID: 35431822
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431827
Is it because you are enclosing the username and password in quotes?
0
 

Author Comment

by:deanlee17
ID: 35431838
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35431842
Remove the unicode part then.
0
 

Author Comment

by:deanlee17
ID: 35431989
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432000
Is it not self explanatory?
0
 

Author Comment

by:deanlee17
ID: 35432018
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432041
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
 

Author Comment

by:deanlee17
ID: 35432101
Well im sorry to test your patience.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432140
Its ok :-) Have you downloaded ODP?
0
 

Author Comment

by:deanlee17
ID: 35432157
No, im reading up on it now. I assumed it wass something the network admin would have to install on the server.
0
 

Author Comment

by:deanlee17
ID: 35432175
CodeCruiser, can I not in any way use the connection that already exists, that i created earlier, see attached. error.doc
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432211
You can not use a SQLDataSource to access Oracle.
0
 

Author Comment

by:deanlee17
ID: 35432238
So can i download ODP on my system alone as opposed to the actual server where the database sits?
0
 

Author Comment

by:deanlee17
ID: 35432265
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432266
Yes. It does not need to be installed on the server. It needs to be on the computer where the program runs.
0
 

Author Comment

by:deanlee17
ID: 35432272
Ok thanks,

Did you see my previous post?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432275
>Is there anything wrong with doing it this way?
Yes. Its like trying to open a Toyota with a Honda key.
0
 

Author Comment

by:deanlee17
ID: 35432280
lol good analogy. But it does work :)
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432302
Really? With SQLDataSource? That is strange. But do download the ODP and use that to avoid problems. Or use OLEDB
0
 

Author Comment

by:deanlee17
ID: 35432317
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 35432349
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
 

Author Comment

by:deanlee17
ID: 35432359
Ok mate thanks very much. i think ive taken up enough of your time now.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432370
Its alright. Let me know if you encounter any problem.
0
 

Author Comment

by:deanlee17
ID: 35432387
ok, thanks....
0
 

Author Comment

by:deanlee17
ID: 35432390
btw, hope the weather is as nice up there as it is in Essex :)
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432432
It is actually very nice but sitting inside the office, it does not matter :-(
0
 

Author Comment

by:deanlee17
ID: 35432544
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432888
That is strange. "Dim dTable As New DataTable" is on new line right?
0
 

Author Comment

by:deanlee17
ID: 35432912
yuuuup
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month13 days, 19 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question