Solved

Could not find stored procedure 'GetDatesForMonth' is an error I receive on the aspx page

Posted on 2006-07-13
3
248 Views
Last Modified: 2008-02-01
Could not find stored procedure 'GetDatesForMonth' is an error I receive on the aspx page. The page loads ok with a calendar and an iframe info box but i receive this error from my <asp:Label ID="lblMsg" Runat="server" CssClass="Alert"></asp:Label> code. The procedure is created in the database. Any ideas?

my ms sql server code to create the stored procedure is

CREATE PROC dbo.GetAllDateDetail (
@StartDate datetime
)

AS

DECLARE @beginTime datetime
DECLARE @endTime datetime

SET @begintime = Convert(datetime,convert(char(2),Datepart(m,@startDate)) + '/' +
convert(char(2),Datepart(d,@startDate)) + '/' +
convert(char(4),Datepart(yy,@startDate)))

SET @endtime = Convert(datetime,DATEADD(ms,-10,DATEADD(d,1,@begintime)))

SELECT
ItinID,
DatePart(d,StartDate) as ShowDay,
StartDate,
StartTime,
Venue,
City,
State,
Country
FROM Itin Where
StartDate BETWEEN @beginTime AND @endTime


GO


CREATE PROC dbo.GetDateDetail (
@ItinID int
)

AS

SELECT
ItinID,
DatePart(d,StartDate) as ShowDay,
StartDate,
StartTime,
Venue,
City,
State,
Country
FROM Itin Where ItinID = @ItinID

GO

CREATE PROC dbo.GetDatesForMonth(
@startDate datetime
)

AS

DECLARE @endDate datetime

SET @endDate = Dateadd(s,-1,Dateadd(m,1,@startDate))

SELECT
ItinID,
DatePart(d,StartDate) as ShowDay,
StartDate,
StartTime,
Venue
FROM Itin Where StartDate BETWEEN @startDate AND @endDate  

my code behind is

    Private Function DatesForMonth(ByVal Month As DateTime) As DataTable

        Dim cn As SqlConnection = New SqlConnection(cnString)
        Dim cmd As SqlDataAdapter = New SqlDataAdapter("GetDatesForMonth", cn)
        cmd.SelectCommand.CommandType = CommandType.StoredProcedure
        Dim dt As New DataTable

        Try
            AppendSQLParameter(cmd.SelectCommand, "@startDate", SqlDbType.DateTime, ParameterDirection.Input, 8, Month)
            cmd.Fill(dt)
            cmd.Dispose()
            cn.Close()
        Catch x As SqlException
            lblMsg.Text = x.Message
        Finally
            cn = Nothing
        End Try

        Return dt

    End Function
0
Comment
Question by:bianchef
3 Comments
 
LVL 12

Accepted Solution

by:
deanvanrooyen earned 125 total points
ID: 17102655
i use c# but try this


        Dim cn As SqlConnection = New SqlConnection(cnString)
        Dim cmd As SqlDataAdapter = New SqlDataAdapter("GetDatesForMonth", cn)
        cmd.SelectCommand.CommandType = CommandType.StoredProcedure
        Dim dt As New DataTable

        Try
            AppendSQLParameter(cmd.SelectCommand, "@startDate", SqlDbType.DateTime, ParameterDirection.Input, 8, Month)

try re shuffle your code - use a command, add the parameter to the command and then set the adapter select command to the command like so

        MySqlConnection myConn = new MySql.Data.MySqlClient.MySqlConnection(myconstr.MySqlConnectionString);
        //create new MySqlcommamd from stored MySql procedure
        MySqlCommand myCmd = new MySqlCommand("spGetUserDetails", myConn); //`spGetUserDetails`(iduser INTEGER(11))
        myCmd.CommandType = CommandType.StoredProcedure;
        //add parameters
        myCmd.Parameters.Add("iduser", Session["userid"].ToString());
        //open MySql and execute
        MySqlDataAdapter mysqlad = new MySqlDataAdapter();
        mysqlad.SelectCommand = myCmd;
        DataTable table = new DataTable();
        myConn.Open();
        mysqlad.Fill(table);
        myConn.Close();


have you tried calling the procedure from query analyser? does the user credentials logging in into the db for the website have permissions to call that procedure?

good luck
0
 
LVL 25

Assisted Solution

by:nauman_ahmed
nauman_ahmed earned 125 total points
ID: 17103955
Are you sure that you are connecting to the right database?

--Nauman.
0
 

Author Comment

by:bianchef
ID: 17122289
Both answers helped me find a solution. I will split the points.

125 points to deanvanrooyen for being first to answer and suggesting the starting steps
calling the procedure from the query analyser was how i found that i needed to create user credentials and permissions for the procedure

125 points to nauman_ahmed
connecting to the right database was the answer to add the connections to the <appSettings> my web.config

Thanks to both.
 
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question