Passing Parameter Int Stored Procedure from Page

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Passing Parameter Int Stored Procedure from Page

Author

Topic

Sirchrisak
Starting Member

19 Posts

Posted - 2005-06-13 : 06:57:24

Hi guys, I have a store procedure that inserts into a table, this procedure is trying to insert differnt coursecode against one person in each row of my table so it is expected that when i call this procedure from my page only one parameter will changeing in all the rows inserted. form my class I pass these parameters that will not change as constants and the changing one as content of ListBox.I then use loop to excute the Procedure as many times as there are items in my listBox in the process changing the dynamic parameter to item in the lisBox at every loop. my problem is that if i have only one item in the listBox the code will insert successfull,but if i have more than one item in the ListBox an exception that "Procedure or function STP_STUDENTCOURSEREGISTERED_INSERT1 has too many arguments specified" will be throw. STP_STUDENTCOURSEREGISTERED_INSERT1 is the name of my stored procedure. help me fix this bug. the code from my class that executes the procedure is pasted below

Public Sub AddRegisteredCourse(ByVal StudentId As Integer, ByVal SessionId As Int16, ByVal StudentLevel As String, ByVal Semester As String, ByVal LisCourseCode As ListBox, ByVal CourseMajorCode As String)
        objcommand.CommandText = "STP_STUDENTCOURSEREGISTERED_INSERT1"
        Dim i As Int16
        For i = 0 To LisCourseCode.Items.Count - 1
            With objcommand.Parameters
                .Add(New SqlParameter("@StudentId", SqlDbType.SmallInt, 2)).Value = StudentId
                .Add(New SqlParameter("@SessionId", SqlDbType.SmallInt, 2)).Value = SessionId
                .Add(New SqlParameter("@StudentLevel", SqlDbType.VarChar, 10)).Value = StudentLevel
                .Add(New SqlParameter("@Semester", SqlDbType.VarChar, 10)).Value = Semester
                .Add(New SqlParameter("@CourseCode", SqlDbType.VarChar, 7)).Value = LisCourseCode.Items.Item(i).ToString
                .Add(New SqlParameter("@CourseMajorCode", SqlDbType.VarChar, 7)).Value = CourseMajorCode

            End With
            objcommand.CommandType = CommandType.StoredProcedure
        Objcnn.Open()

        objcommand.ExecuteNonQuery()
        Objcnn.Close()

        Next
 Also My stored Procedure code is pasted Below:
CREATE PROCEDURE STP_STUDENTCOURSEREGISTERED_INSERT1 
(
  
  @CourseMajorCode varchar(7),
   @StudentId int,
  @StudentLevel varchar(7),
  @Semester varchar(10),
  @SessionId int,
  @CourseCode varchar(7)
)

AS
 
 
 
     BEGIN 
            INSERT INTO STUDENT_COURSE_RegisteredCourses(StudentId , SessionId,Semester,CourseCode,CourseMajorCode,StudentLevel)
           VALUES (@StudentId,@SessionId,@Semester,@CourseCode,@CourseMajorCode,@StudentLevel)
                   
     END
GO

thanks.

Chris

jhermiz

3564 Posts

Posted - 2005-06-13 : 08:00:29

Morning,

A few pointers on your code.
Why are you looping and recalling that same procedure over and over. YOu only need to call that procedure once if you used the IN predicate in SQL. SELECT blah FROM foo WHERE foobar IN (....)

Secondly, check your code you are closing the connection to the database in the for loop but never reopening, imho it is more cost effective in this situation to leave the connection open until all the calls are done otherwise you are constantly opening and closing the connection and thats just a hefty cost. BTW, you may want to set your objcommand to notihng and reallocate it in this case if you are calling it over and over, but as I have stated you do not need a loop here.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources