I am using the following stored procedure to call period numbers to match them to teachers.

We are writing an attendance taking application where upon selecting the teacher's name from a dropdown menu, the "period" dropdown menu populates. For some reason we are not getting the period data to populate.

Stored Procedure:

ALTER PROCEDURE spGetPeriod @Period varchar(10)
AS
SELECT Period 
FROM tmTeacher
WHERE Period = @Period

RETURN

Teacher Table 
TeacherId   varchar(10) Unchecked
FName           varchar(20) Checked
LName           varchar(20) Checked
StudentId   varchar(10) Unchecked
Period          varchar(10) Unchecked
TeacherName varchar(50) Checked

Period Table
PeriodId    varchar(10) Unchecked
Period          varchar(50) Unchecked
ClassId         varchar(10) Unchecked

Thanks in advance!

Code calling the SP:

'*****  SETUP THE CONNECTION ***
    SQL = "spGetPeriod '" + (cboTeacher.Text) + "'"
    'SQL = "select * from teacher"
    Try
        With SqlCmd
            .CommandText = SQL
            '.CommandType = CommandType.StoredProcedure
            .Connection = objConnection
            '.Parameters.AddWithValue("@TeacherId", cboTeacher.SelectedIndex)
        End With
        SqlCmd = New SqlCommand(SQL, objConnection)
    Catch ex As Exception
        Call ErrorControl(ex)
    End Try

    '*** MAKE THE CONNECTION ***

    Try
        HandleConnection(objConnection)
        'MsgBox("Connection Established!")
        ToolStripStatusLabel1.Text = "Connection Established!"


    Catch ex As Exception
        Call ErrorControl(ex)

    End Try

    '*** EXECUTE THE COMMAND AND FILL THE COMBO ***
    Dim Reader As SqlDataReader = SqlCmd.ExecuteReader()
    While Reader.Read()
        cboPeriod.Items.Add(Reader(0).ToString)

    End While
    ToolStripStatusLabel1.Text = "Periods Loaded!"




End Sub

• sql-server
• vb.net

1) You are adding a parameter called TeacherId on your code but on your proc it doesnt exist. You must pass the correct parameter to the procedure from the interface

2)Your procedure is called spGetPeriod but on your SQL you have select * from teacher

You've defined the parameter as "@Period" in the sproc, but when you add the parameter to the command you are calling it "@TeacherId". Your code looks ok at a glance, try altering the procedure as such...

ALTER PROCEDURE spGetPeriod @TeacherId varchar(10)
AS
SELECT Period 
FROM tmTeacher
WHERE TeacherId = @TeacherId

RETURN

for your issue with not having any data, maybe try binding the combo box's datasource to the reader instead of looping through the reader and adding each, leave the ExecuteReader statement and try replacing your while loop at the end with this...

cboPeriod.DataSource = Reader
cboPeriod.DataTextField = "Period"
cboPeriod.DataValueField = "Period"
cboPeriod.DataBind()

I see some issues with your schema:

• Your various Ids should be integers, NOT varchar.
• The teacher table has FName, LName, and TeacherName. This is not normalized. If FName and LName are for the student, they should be kept in a seperate Student table with a StudentId Primary Key.
• StudentId probably shouldn't be in the Teacher table. If you want to store which students are in which periods, you should use a join table which is standard for many-to-many relations. You could call it "PeriodStudent" for example, and it would store the StudentId and the PeriodId.

Take the time to understand DB normalization concepts like First, Second, and Third Normal form. If you don't adhere to basic normalization principles, you are asking for major problems down the road, and those problems will be very expensive to fix later on.