Solved

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

Posted on 2006-07-13
3
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

726 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