Calling Stored SQL Function from asp.net how?

[Roger]

I put this in the microsoft.publi c.dotnet.langue ages.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.SqlCo mmand("R_Siphon DetailForDays",
sCon) sqlCmd.CommandT ype = CommandType.Sto redProcedure
sqlCmd.Paramete rs.Add("@sDate" , Session("Date") )
sqlCmd.Paramete rs.Add("@sDate2 ", Session("Date") ) rdr =
sqlCmd.ExecuteR eader(CommandBe havior.Default)
DataGrid1.DataS ource = rdr
DataGrid1.DataB ind()
T-SQL Stored Function

ALTER FUNCTION dbo.R_SiphonDet ailForDays
(
@sDate nvarchar(10),
@sDate2 nvarchar(10)
)
RETURNS TABLE
AS
RETURN ( SELECT SiteNum, FileDate, FileName, FileExt, FileSize
FROM a_SiphonDetail
WHERE (FileDate > CONVERT(smallda tetime, CONVERT(varchar (10),
DATEADD(day, - 1, @sdate), 101)) + '10:00 PM') AND (FileDate <
CONVERT(smallda tetime,
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.SqlCo mmand("SELECT * FROM dbo.R_SiphonDet ailForDays",
sCon)

Also I would get rid of this line:

sqlCmd.CommandT ype = CommandType.Sto redProcedure

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" <da*****@netins .net> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

I put this in the microsoft.publi c.dotnet.langue ages.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.SqlCo mmand("R_Siphon DetailForDays",
sCon) sqlCmd.CommandT ype = CommandType.Sto redProcedure
sqlCmd.Paramete rs.Add("@sDate" , Session("Date") )
sqlCmd.Paramete rs.Add("@sDate2 ", Session("Date") ) rdr =
sqlCmd.ExecuteR eader(CommandBe havior.Default)
DataGrid1.DataS ource = rdr
DataGrid1.DataB ind()
T-SQL Stored Function

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

[chanmmn]

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

chanmm

"Roger" <da*****@netins .net> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

I put this in the microsoft.publi c.dotnet.langue ages.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.SqlCo mmand("R_Siphon DetailForDays",
sCon) sqlCmd.CommandT ype = CommandType.Sto redProcedure
sqlCmd.Paramete rs.Add("@sDate" , Session("Date") )
sqlCmd.Paramete rs.Add("@sDate2 ", Session("Date") ) rdr =
sqlCmd.ExecuteR eader(CommandBe havior.Default)
DataGrid1.DataS ource = rdr
DataGrid1.DataB ind()
T-SQL Stored Function

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