Vs 2005 data connections assistance needed


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

Who is Participating?
Jorge PaulinoIT Pro/DeveloperCommented:
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.
Jorge PaulinoIT Pro/DeveloperCommented:
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.

neoiceAuthor Commented:
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 asp.net 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 code.it 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 @ neoice5009@aol.com - this would help me understand it.



neoiceAuthor Commented:
Hi JP,

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

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.