Stored Procedure Not Found

[rbroersma]

I'm having trouble w/ a vb asp.net site. I'm trying to run a MSSQL SP in order to populate a gridview. The site worked fine when I was using a SQL statement w/in the code, but when I went to a stored proc, it stopped working (I needed SP because of additional complexity needed to improve performance).

The SP IS IN the DB and I can run it fine from Query Analyzer, but when I try and run it from the website I get the following error....

System.Data.SqlClient.SqlException was unhandled by user code
Class=16
ErrorCode=-2146232060
LineNumber=0
Message="Could not find stored procedure 'spCallDataByANI '6/13/08 10:30am', '6/13/08 10:40am', '<ani>''."
Number=2812
Procedure=""
Server=" <server> "
Source=".Net SqlClient Data Provider"
State=62
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at wfCallAniRpt.lcmdRun_Click(Object sender, EventArgs e) in C:\Documents and Settings\My Documents\Visual Studio 2005\WebSites\RTOpsAdmin\wfCallAniRpt.aspx.vb:line 56
at System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e)
at System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

My code looks like this....

Dim conn As New SqlConnection("Data Source=[I]<server>[/I];Initial Catalog=[I]<db>[/I];User Id=[I]<user>[/I];Password=[I]<pswd>[/I];")
        conn.Open()
        Dim vSQL As String

        vSQL = "spCallDataByANI '" & txtBegTime.Text & "', '" & txtEndTime.Text & "', '" & txtANI.Text & "'"
        Dim cmd As New SqlCommand(vSQL, conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandTimeout = 0

        Dim rdr As SqlDataReader = cmd.ExecuteReader()
        GridView1.DataSource = rdr
        GridView1.DataBind()
        rdr.Close()
        conn.Close()

It bombs on the 'cmd.ExecuteReader()' line (obviously...).

I would appreciate any input I can get, I'm running out of ideas. I have tried putting the db and user in front of the sp name and that didn't work either (eg. <db>.<user>.spCallDataByAni).

Thanx,
ryan

 

[MattP]

Dim conn As New SqlConnection("Data Source=<server>;Initial Catalog=<db>;User Id=<user>;Password=<pswd>;")
Dim cmd As New SqlCommand
...
With cmd
    .Connection = conn
    .CommandType = CommandType.StoredProcedure
    .CommandText = "spCallDataByANI"
    .Parameters.AddWithValue("@begintime", txtBegTime.Text)
    .Parameters.AddWithValue("@endtime", txtEndTime.Text)
    .Parameters.AddWithValue("@ani", txtANI.Text)
End With
...

Taking a guess at your parameter names.

If this is a straight cut and paste from your code I'd suggest changing your connection string to reflect your server's credentials.

If you've changed the credentials for confidentiality then I'd take a look at the spelling of your stored procedure.

 

[rbroersma]

Resolved

MattP - Thanx for the reply, you were right, I didn't have the parameters set correctly. I ended up doing this.....

        cmd.Parameters.Add("@BegTime", SqlDbType.DateTime)
        cmd.Parameters.Add("@EndTime", SqlDbType.DateTime)
        cmd.Parameters.Add("@ANI", SqlDbType.VarChar, 10)

        cmd.Parameters(0).Value = txtBegTime.Text
        cmd.Parameters(1).Value = txtEndTime.Text
        cmd.Parameters(2).Value = txtANI.Text

... It's working great now.

Thanx again!