Issue with SQL Server Stored Procedure

Go To


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)
SELECT Period 
FROM tmTeacher
WHERE Period = @Period


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:

    SQL = "spGetPeriod '" + (cboTeacher.Text) + "'"
    'SQL = "select * from teacher"
        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


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

    Catch ex As Exception
        Call ErrorControl(ex)

    End Try

    Dim Reader As SqlDataReader = SqlCmd.ExecuteReader()
    While Reader.Read()

    End While
    ToolStripStatusLabel1.Text = "Periods Loaded!"

End Sub
2012-04-04 20:27
by Kevin Schultz
You need to include the VB code that you are using to capture the results of the SP - James 2012-04-04 20:30
Wouldn't you want to pass the 'teacherid' to the procedure and then within the sproc select the 'period' from the 'teacher' table where 'teacherid' = @teacherid - temarsden 2012-04-04 20:32
Is that data being returned from your stored proc? If so, then we need to see your code to give you an answe - Abe Miessler 2012-04-04 20:34
@james - Added the cod - Kevin Schultz 2012-04-04 20:36
What does the "Checked" and "Unchecked" mean? Also I would think about using Int for your Id columns rather than varchar(10) - Christopher Rathermel 2012-04-04 20:37
@Mendicant - added cod - Kevin Schultz 2012-04-04 20:37
Checked and Unchecked refers to the ALLOW NULL Collumns, that it how it copied over - Kevin Schultz 2012-04-04 20:38
@Mendicant, can you give me an example - Kevin Schultz 2012-04-04 20:39


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

2012-04-04 20:55
by Diego


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)
SELECT Period 
FROM tmTeacher
WHERE TeacherId = @TeacherId


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"
2012-04-04 20:41
by temarsden
I tried your suggestion and it dies. I get an exception error of: Procedure or function 'spGetPeriod' expects parameter '@TeacherId', which was not supplied - Kevin Schultz 2012-04-04 20:44
It's because you have that line commented out in the VB code. The value of selectedIndex that you are passing to the parameter looks suspect too - James 2012-04-04 20:46
My bad, need lowercase 'd' in Id, try...

WHERE TeacherId = @TeacherId

also I assume that cboTeacher.SelectedIndex is an INT whereas TeacherId in your table is a varchar column, might check that too - temarsden 2012-04-04 20:48

I made that change and the error is gone, I just do not get a return of any information - Kevin Schultz 2012-04-04 20:51
Have you checked to see that your query is returning rows? Maybe try binding the datasource for the combo box to the reader like I lay out above.. - temarsden 2012-04-04 21:30


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.

2012-04-04 20:49
by RyanHennig