MS SQL Function return string - what am I doing wrong?

Cannot see where I am going wrong. I always get a value of 0. I know my function works correctly, so it must be the VB.

CREATE FUNCTION [dbo].[getNextProjectID] ()
RETURNS varchar(10) AS
BEGIN
''.....................
DECLARE @.vNextProjectID varchar(10)
RETURN @.vNextProjectID
END

Sub LoadNextProjectNumber()
Dim vProjectID As String
Dim cmd As New SqlClient.SqlCommand()
cmd.Connection = sqlConn
cmd.CommandText = "getNextProjectID"
cmd.Parameters.Add("@.vNextProjectID", vProjectID)
cmd.Parameters("@.vNextProjectID").Direction = ParameterDirection.ReturnValue
cmd.ExecuteScalar()
vProjectID = cmd.Parameters("@.vNextProjectID").Value
txtProjectID.Text = vProjectID
cmd.Dispose()
End Sub
Hi Jagdipa,
Are you sure that your function executes correctly? I can run your example and get back string successfully. Try to run Profiler in MS SQL to see function execution log.|||check ifthis articlehelps|||hello..
try to use :
cmd.CommandType = storedprocedure
and
cmd.ExecuteNonQuery|||Thanks guys.
All I had to do was write the following line (thanks busyweb):
cmd.commandType = storedProcedure
Dont know why I need that line when the sql is a function and not astored procedure. I have had this code work before without that line.But, as long as it works...
Jagdip