Calling Stored SQL Function from asp.net how?

[Roger]

I put this in the microsoft.public.dotnet.langueages.vb.data newsgroup, but
noticed there aren't alot of people there frequently like this newsgroup.
So I thought I would try here.

I get an error stating this call is to a function not a stored procedure?
How do I call a stored function in SQL Server from vb.net

Thanks....


asp.Net Code..... (VB)

sqlCmd = New SqlClient.SqlCommand("R_SiphonDetailForDays",
sCon) sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Parameters.Add("@sDate", Session("Date"))
sqlCmd.Parameters.Add("@sDate2", Session("Date")) rdr =
sqlCmd.ExecuteReader(CommandBehavior.Default)
DataGrid1.DataSource = rdr
DataGrid1.DataBind()


T-SQL Stored Function

ALTER FUNCTION dbo.R_SiphonDetailForDays
(
@sDate nvarchar(10),
@sDate2 nvarchar(10)
)
RETURNS TABLE
AS
RETURN ( SELECT SiteNum, FileDate, FileName, FileExt, FileSize
FROM a_SiphonDetail
WHERE (FileDate > CONVERT(smalldatetime, CONVERT(varchar(10),
DATEADD(day, - 1, @sdate), 101)) + '10:00 PM') AND (FileDate <
CONVERT(smalldatetime,
CONVERT(varchar(10), @sdate2), 101) + '10:00
PM') )

 

[Michael C#]

Not too familiar with calling functions from VB.NET, although in general you
would use a function to return a scalar value. When calling functions, you
usually want to add dbo. to the front:

' Un-tested, but what the heck?
sqlCmd = New SqlClient.SqlCommand("SELECT * FROM dbo.R_SiphonDetailForDays",
sCon)

Also I would get rid of this line:

sqlCmd.CommandType = CommandType.StoredProcedure

Try that. If it doesn't work, I would just turn your UDF into an SP and
call it. That might be your easiest solution...

 

[chanmmn]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp

chanmm