Solved

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

Posted on 2006-07-13
3
242 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now