I have created a simple stored procedure called usp GetClassData. I do not know how to reference it from my ASP VB.Net code. I refernce my tables in SQL statements as, for example, "SELECT COUNT(DISTINCT instructorfullname) as instructorcount FROM steve48_sa.tblSfcasInstructors" I am trying to reference my stored procedure from a statement Dim Cmd1 As New SqlCommand("steve48_sa.uspGetClassData", Conn) Cmd1.CommandType=Data.CommandType.StoredProcedure Cmd1.Parameters.AddWithValue("@startdate", DateLimits1.StartDate) Cmd1.Parameters.AddWithValue("@enddate", DateLimits1.EndDate) The error I get is "Could not find stored procedure 'steve48_sa.uspGetClassData" The name Winhost assigned to my db is s02.DB_3391_sfcas.dbo Any help is appreciated. TIA
Connection I did not see code for your connection string. I had problems writing to a table unless I use the username provided by Winhost. Not sure why, but I have not had time to figure it out so I am using just that username. here is an example of my connection.... Dim cnConnection As New Data.SqlClient.SqlConnection(Password=*****;Persist Security Info=True;User ID=DB_XXXX_database_user;Data Source=tcp:s01.Winhost.com;Initial Catalog=DB_XXXX_database) DatabaseConnection.Open() I just wanted to check you are using proper connection string if you are I am not sure why it would not work. NOTE: I AM NOT USING A STORED PROCEDURE ON MY WEBSITE WITH THIS CODE, BUT HAVE USED THIS CODE ON REGULAR WINDOWS APPLICATIONS WITH STORED PROCEDURE (WITH DIFFERENT CONNECTION STRING And Database).
I have no problem with my connection. All other queries not involving the stored procedure work fine. This is connection string. <add name="steve48ConnectionString" connectionString="Data Source=tcp:s02.Winhost.com;Initial Catalog=DB_xxxx_sfcas;User ID=DB_xxxx_sfcas_user;Password=xxx;Integrated Security=False;" providerName="System.Data.SqlClient" />
Alternate commands I tried that didn't work 'Dim Cmd1 As New SqlCommand("steve48_sa.procClassDataByDateSelect", Conn) 'Dim Cmd1 As New SqlCommand("procClassDataByDateSelect", Conn) 'Dim Cmd1 As New SqlCommand("dbo.procClassDataByDateSelect", Conn) 'Dim Cmd1 As New SqlCommand("steve48_sa.dbo.procClassDataByDateSelect", Conn) Dim Cmd1 As New SqlCommand("s02.DB_XXXX_sfcas.dbo.procClassDataByDateSelect", Conn) If I execute the stored procedure directly using Visual Studio it works fine
If you are not having connection string problems then you should be able to run a SQL Statement against the SQL server. If you are trying to run a store procedure, try using the "Execute" command in your SQL Statement within your application. http://msdn.microsoft.com/en-us/library/ms188332.aspx