Vs 2005 data connections assistance needed

Posted on 2007-07-21
Last Modified: 2013-11-26

on my web form I have 2 radio buttons, 1 for staff and 1 for student and a dropdown list box (cbintake) for intake (7,8,9,10,11,12,13)

I also have another dropdown list box which will display a staff/student name(cbperson).

I am having trouble achieving exactly what I want.

i have placed an sqldatasource on the form for (cbperson) listbox

<asp:DropDownList ID="CbPerson" runat="server" DataSourceID="Sql1" DataTextField="FULLNAME" DataValueField="FULLNAME" > </asp:DropDownList><asp:SqlDataSource
                  ID="Sql1" runat="server" ConnectionString="<%$ ConnectionStrings:conn1 %>"
                  SelectCommand="SELECT [FULLNAME] FROM [TblStudents] WHERE ([INTAKE] = @INTAKE) ORDER BY [INTAKE]">
                      <asp:ControlParameter ControlID="CbIntake" Name="INTAKE" PropertyName="SelectedValue"
                          Type="String" />

So, if I select 12 from 'cbintake' all year 12 students are displayed in 'cbperson' - cool!

However, now the plot thickens and I am beginning to think I need to put the above string in VB code to achieve what I want.

Here is what I want...

When the student raido button is selected I want to achieve the above.

When the staff radio button is selected I want to disable 'cbintake' (i have sorted that part) and run a different a different query to populate 'CBperson', for example: SELECT [FULLNAME] FROM [TblStaff]

webconfig connection string

            <add name="conn1" connectionString="Data Source=ARAGORN;Initial Catalog=db1SQL;Integrated Security=True" providerName="System.Data.SqlClient"/>

I hope this makes sense :O)

Thanks in advance

Question by:neoice
    LVL 48

    Expert Comment

    Heres an example:

    Paste this code in a new class - myClass

    Public Shared Function GetConnection() As SqlConnection
            Dim SqlServerName As String = "MYSERVER"
            Dim SqlServerDatabase As String = "myDB"
            Dim SqlServerLogin As String = "efw"
            Dim SqlServerPassword As String = "fwefw"

            Dim conn As SqlConnection

            Dim strConn As String = "Data Source=" & SqlServerName & ";Initial Catalog=" & SqlServerDatabase & ";User ID=" & SqlServerLogin & ";Password=" & SqlServerPassword & ";Integrated Security=SSPI;"

            conn = New SqlConnection(strConn)


            Catch SqlEx As SqlException
                MsgBox("Error SQL:" & vbCrLf & vbCrLf & SqlEx.Message, MsgBoxStyle.Critical)

            Catch ex As Exception
                MsgBox("Error message", MsgBoxStyle.Critical)
            End Try
            GetConnection = conn
        End Function

        <System.Diagnostics.DebuggerStepThrough()> _
        Public Shared Function ExecuteReader(ByVal query As String) As SqlDataReader
            Dim rdr As SqlDataReader = Nothing
            Dim conn As SqlConnection = GetConnection()


                Dim cmd As SqlCommand = New SqlCommand(query, conn)

                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            Catch SqlEx As SqlException
                MsgBox("Error SQL:" & vbCrLf & vbCrLf & SqlEx.Message, MsgBoxStyle.Critical)

            Catch ex As Exception
                MsgBox("Error message", MsgBoxStyle.Critical)

            End Try
            ExecuteReader = rdr
        End Function

    Then in the Page VB code

           Imports myClass and on Page_Load

                 fills the dropdownlist with the default text
    Dim SQL1 As String = "SELECT * FROM TblStudents;"
                Dim dr1 As SqlDataReader = ExecuteReader(SQL1)
                While dr1.Read
                    Dim item As New ListItem
                    item.Value = dr1.Item("id") if you need it ?
                    item.Text = dr1.Item("fieldinfo")
                End While

    Then on the selection of radiobutton clear the DropDownList1 and fill it again with the above code just changing the SQL statement.


    Author Comment

    Hi JP,

    Thanks for the reply. However, I am really struggling with this. I understand the logic behind what you are doing and would like to get this working. Putting this into practice is easier said than done.

    here are some of my errors:

    when i add the class item to my ASP web project i am asked the following:

    You are attempting to add a class to an application. for class to work in your site it must be placed inside the 'app_code_' folder. I agree to this.

    Ok, I get a blue scribble under 'sqlconnection' in the first line and througout the rest of the says that it is not defined.I tried replacing this with a connection I already have called conn1 but to no avail. I get the same erros with sqldatareader, sqlcommand, sqlexception and commandbehavior.

    In the vb page I select page events and choose load. I then paste in 'imports class1' but get a scribble under imports, which says syntax error. I also paste the rest of the code here.

    I would be very grateful if you could help me resolve these issues. If you happen to already have the code in a web page please feel free to send it to me @ - this would help me understand it.



    LVL 48

    Accepted Solution

    Sorry for the delay of my answer.

    First you have to put before you Partial Call _Default the Import method

    Imports System.Data
    Imports System.Data.SqlClient
    Imports myClass

    In all the pages that you want to use that function you have to import the librarys in the beginning of your pages. Also the global classes whit shared functions must be in App_Code.

    Author Comment

    Hi JP,

    Thanks for helping me - you are brilliant!!!!


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
    This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now