By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,693 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,693 IT Pros & Developers. It's quick & easy.

Calling Stored SQL Function from asp.net how?

P: n/a
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') )


Nov 21 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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" <da*****@netins.net> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
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') )

Nov 21 '05 #2

P: n/a

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

chanmm

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



Nov 21 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.