Stored procedure Parameters - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Stored procedure Parameters

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-04-06 : 06:17:54

I am using (trying to use) this function to popiulate a dropdownlist. However I get an error saying the parameters are not being passed to the sp. What is wrong. My sp is show also.

Function GetCallList() As DataSet
'Populate the ddlDataSet

Dim MySQL As String = "spSL_GetCallList"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strDivision", Me.Session("LoginDivision")))
Cmd.Parameters.Add(New SqlParameter("@strService", Me.Session("LoginService")))
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(MySQL, strConn)
Dim ddlDataSet As New DataSet
myDataAdapter.Fill(ddlDataSet, "tblSL_Calls")

Return ddlDataSet

End Function

CREATE PROCEDURE spSL_GetCallList

@strDivision nvarchar(100),
@strService nvarchar(100)
AS
Select DISTINCT CallNo, TicketNo, convert(varchar(20),TicketNo) + '::' + Surname + ' :: ' + Call_desc as Ticket from tblSL_Calls where Division = @strDivision and Service = @strService
and Completed_time is null and Cancelled is null ORDER BY TicketNo desc
GO

kimberr
Starting Member

9 Posts

Posted - 2005-04-06 : 06:25:27

Are you sure Me.Session("LoginDivision") and Me.Session("LoginService") hold information. You might try making the parameters of the stored procedure optional ('= null' after the datatype) to see if the problem is with the Stored Procedure or the VB.NET.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-04-06 : 06:51:07

Yes, I checked that by filling 2 labels with their value in the Page Load event.

Any other ideas !

kimberr
Starting Member

9 Posts

Posted - 2005-04-06 : 07:01:38

I've not used used VB.NET for a while, so this could be completely wrong. But shouldn't you be passing Cmd into the DataAdapter instead of MySQL?

New SqlDataAdapter(Cmd, strConn)

vf
Starting Member

1 Post

Posted - 2005-04-06 : 07:02:44

You should use your command object (cmd) to run the procedure!

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-04-07 : 03:52:42

I changed MySQL to Cmd but it was underlined in blue. Could you edit my code for me please.

TIA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources