[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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

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
bianchef
Asked:
bianchef
2 Solutions
 
deanvanrooyenCommented:
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
 
nauman_ahmedCommented:
Are you sure that you are connecting to the right database?

--Nauman.
0
 
bianchefAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now