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.
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
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()
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 see some issues with your schema:
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.