February 28, 2004 at 5:41 am
I have the following stored procedure I call from a webservice
CREATE PROCEDURE GetFacilities
@UserID NVARCHAR(100),
@language NVARCHAR(100)
AS
SELECT Name,
(SELECT @language FROM lkpfacilitytype WHERE ID = Type) AS Type,
Address1,
Address2,
City,
State,
ZIP,
Country,
Phone,
Fax,
WebSite
FROM tblFacilities
WHERE UserID = @user-id
GO
My ASP.NET code is:
<WebMethod(Description:="Returns all Facilities for a specific UserID.")>
Public Function GetFacilities(ByVal UserID As String, ByVal Language As String) As DataSet
Dim con As SqlConnection = New SqlConnection("data source=1.2.170.253;initial catalog=company; user id=tey1234; password=tey1234")
Dim daCust As New SqlDataAdapter("Execute GetFacilities " & userID & "," & "language", con)
Dim ds As New DataSet()
daCust.Fill(ds, "Facility")
Return ds
End Function
However, I always get this error message
System.Data.SqlClient.SqlException: Procedure or function GetFacilities has too many arguments specified.
It has 2 arguments. Plus running 'GetFacilities joedoe, English' in SQL Query Analyzer works. Any ideas? Thanks
March 1, 2004 at 8:01 am
I would use the SqlCommand object as oppossed to the the Connection object. This way you could make use of the parameters method. The parameter method ensures you are passing parameters back and forth appropriately. Sample:
Dim scmCmdToExecute As SqlCommand = New SqlCommand()
scmCmdToExecute.CommandText = "dbo.GetFacilities"
scmCmdToExecute.CommandType = CommandType.StoredProcedure
scmCmdToExecute.Connection = "data source=1.2.170.253;initial catalog=company; user id=tey1234; password=tey1234"
scmCmdToExecute.Parameters.Add(New SqlParameter("@UserID ", SqlDbType.NVarChar, 100, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, m_UserID))
scmCmdToExecute.Parameters.Add(New SqlParameter("@language", SqlDbType.NVarChar, 100, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, m_language))
// Open connection.
m_scoMainConnection.Open()
' // Execute query.
scmCmdToExecute.ExecuteNonQuery()
Note this is not complete code. You still need to define m_language and m_Userid (Defined in Properties is a good idea). If you are new to VB.NET I recommend using a code generator like LLBLGen (free version or the paid version). The code is readable and it gives you great ideas for how the data layer should work. It will even create a basic data layer (insert, update, delete for base tables)
Good luck
Francis
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply