Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2006-07-13
3
Medium Priority
?
257 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 500 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 500 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…
To export Lotus Notes to Outlook PST or Exchange and Domino Server files to Exchange Server or PST files with ease, go for Kernel for Lotus Notes to Outlook conversion tool. Through the video, you can watch the conversion process. A common user with…

601 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