Could not find stored procedure

[whateveragain]

I have an app that works fine on my desktop; however, once I load it to the web site, it works up to a point. The first call to the SQL server works, but the next call doesn't. I tried rem'ing out calls to see if it were isolated to a particular stored procedure, but it isn't. The app only allows 1 call to the SQL server then it can't seem to find it any longer. The connection strings are all the same as it's not hard coded. Thanks

The error msg is as follows:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Could not find stored procedure 'Update_Selstate'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Could not find stored procedure 'Update_Selstate'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[SqlException (0x80131904): Could not find stored procedure 'Update_Selstate'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
scnstate.updateSelstate() in C:\Users\Joanne\Documents\Visual Studio 2008\WebSites\WebSite1\scnstate.aspx.vb:1164
scnstate.ImageButton1_Click(Object sender, ImageClickEventArgs e) in C:\Users\Joanne\Documents\Visual Studio 2008\WebSites\WebSite1\scnstate.aspx.vb:1432
System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +108
System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +118
System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.3082; ASP.NET Version:2.0.50727.3082

 

[jmeckley]

the imediate problem is it cannot find the stored proc Update_Selstate. this proc either does not exist on the production database, or the user does not have rights to execute the proc. however not finding this proc shouldn't cause all other db calls to fail.

My gut says you are managing the connection/command objects incorrectly which is why 1 failing sql command is affecting all the rest.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[whateveragain]

But why would the first call work when they are all handled with the same variable? I'm using a session variable. See code below:

Connection string in Global.asax:
Session("MyString") = "Server=174.36.218.228;Database=Joanne;uid=Wei;pwd=EvG6szEq4qg;"

Importing:
Imports System
Imports System.Data
Imports System.Web.UI.WebControls
Imports System.Data.SqlClient
Imports System.Configuration

VB.net code in .aspx.vb file:
Sub updateSelstate()
myID = Session.SessionID()

Dim sqlConn As New SqlConnection
Dim sqlComm As New SqlCommand

myID = Session.SessionID()

sqlComm.Connection = sqlConn

sqlComm.CommandText = "Update_Selstate" '-- specifying the command text
sqlComm.CommandType = CommandType.StoredProcedure '-- specifying the command text is a stored proc

With sqlComm

'-- adding the parameters
.Parameters.Add("@Statelist", SqlDbType.VarChar, 500, Session("selstate"))

'-- specifying the parameter directions
.Parameters("@Statelist").Direction = ParameterDirection.Input

'-- assigning values to the parameters
.Parameters("@Statelist").Value = Session("selstate")

End With

'-- opening the connection
sqlConn.Open()

sqlComm.Connection = sqlConn
sqlComm.ExecuteNonQuery()
'-- executing the command
'stCount = sqlComm.ExecuteScalar ' return value
'-- close the connection
sqlConn.Close()
End Sub

SQL stored procedure:
ALTER PROCEDURE [dbo].[Update_SelState]
-- Add the parameters for the stored procedure here
@statelist varchar(500)
AS

SET NOCOUNT ON;

BEGIN

SELECT * into selstate
FROM state
where ' ' + @Statelist + ' ' Like '% ' + state.statecode + ' %'
ORDER BY State.Statecode, state.statename ;

END

 

[jmeckley]

according to the exception message the problem is the stored proc doesn't exist. not that it doesn't work, it doesn't exist.

from there I see 2 other pitfalls and 1 issue.
pitfalls
1. you are not disposing of your connections or commands. wrap your connection and command with using blocks

using(var c = new SqlConnection(...))
{
   c.Open();
   using(var command = c.CreateCommand())
   {
   }
}

converting that to VB shouldn't be too difficult.
2. you are opening and closing the entire connection for each sql statement. this is a very poor use of the connection object. I would recommend open/closing the connection when the request is started/ended. There is a FAQ on this. see my signature for the link.

issues
1. you are storing the connection string in session. Don't. use the web.config and pull the connection string from

ConfigurationManager.ConnectionStrings["name of key"].ConnectionString

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190