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') )

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...

"Roger" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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') )
>
>
>
>

http://msdn.microsoft.com/library/de.../html/daab.asp

chanmm

"Roger" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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') )
>
>
>
>