|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can't find stored procedureAddNewResumeTemplate. The error it shows is: Could not find stored procedure 'AddNewResumeTemplate '1234',1001,'This is a test','This is our resume',5'. The problem is that it is there. I even have Sql Profiler running which shows it trying to execute: exec AddNewResumeTemplate '1234',1001,'This is a test','This is our resume',5 Which is correct and when I copy and paste it into Sql Query Analyser, it works. My code is the same as I have been running for a long time, so I can't seem to see what is missing. Here is what it looks like: ********************************************************************************* Dim ConnectionString as String =System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_ftsolutions") Dim objConn as New SqlConnection (ConnectionString) Dim CommandText as String = "AddNewResumeTemplate '1234',1001,'This is a test','This is our resume',5" Dim objCmd as New SqlCommand(CommandText,objConn) objCmd.CommandType = 4 trace.warn("objConn.ConnectionString = " & objConn.ConnectionString) objConn.Open() objCmd.ExecuteNonQuery ************************************************************************************* The trace.warn shows that it is accessing the correct database on the server, so I don't know why it can't find it. Tom Either the CommandType (4) is wrong (I won't even try to identify the value
of 4) or your must use the prefix dbo. before AddNewResumeTemplate. (Of course, if the current user is the owner of the procedure; otherwisee use the proper prefix schema. It is also possible that your connection string point to the wrong database. Finally, the fact that you are not using parameters instead of « ... '1234',1001, ... » is totally outdated. S. L. Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:%23XKMkHXNFHA.3668@TK2MSFTNGP14.phx.gbl... > On Sql Server 2000, my program can't seem to find the Stored Procedure: > AddNewResumeTemplate. > > The error it shows is: > > Could not find stored procedure 'AddNewResumeTemplate '1234',1001,'This is > a test','This is our resume',5'. > > The problem is that it is there. I even have Sql Profiler running which > shows it trying to execute: > > exec AddNewResumeTemplate '1234',1001,'This is a test','This is our > resume',5 > > Which is correct and when I copy and paste it into Sql Query Analyser, it > works. > > My code is the same as I have been running for a long time, so I can't > seem to see what is missing. Here is what it looks like: > > ********************************************************************************* > Dim ConnectionString as String > =System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_ftsolutions") > Dim objConn as New SqlConnection (ConnectionString) > Dim CommandText as String = "AddNewResumeTemplate '1234',1001,'This is a > test','This is our resume',5" > Dim objCmd as New SqlCommand(CommandText,objConn) > objCmd.CommandType = 4 > trace.warn("objConn.ConnectionString = " & objConn.ConnectionString) > objConn.Open() > objCmd.ExecuteNonQuery > ************************************************************************************* > > The trace.warn shows that it is accessing the correct database on the > server, so I don't know why it can't find it. > > Tom > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> I was using an example from www.4guysfromrolla.com example - don't know if 4 wrote in message news:%23f2WbOXNFHA.1308@TK2MSFTNGP15.phx.gbl... > Either the CommandType (4) is wrong (I won't even try to identify the > value of 4) or your must use the prefix dbo. before AddNewResumeTemplate. > (Of course, if the current user is the owner of the procedure; otherwisee > use the proper prefix schema. is correct. As I showed in my other post (that I posted at about the same time you posted), the program works fine if you change the stored procedure to a regular Sql statement. So obviously the database is correct. > I agree. I would normally use parameters, but I am just testing the stored > It is also possible that your connection string point to the wrong > database. > > Finally, the fact that you are not using parameters instead of « ... > '1234',1001, ... » is totally outdated. procedure, so I am just doing it simply. Thanks, Tom Show quote > > S. L. > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:%23XKMkHXNFHA.3668@TK2MSFTNGP14.phx.gbl... >> On Sql Server 2000, my program can't seem to find the Stored Procedure: >> AddNewResumeTemplate. >> >> The error it shows is: >> >> Could not find stored procedure 'AddNewResumeTemplate '1234',1001,'This >> is a test','This is our resume',5'. >> >> The problem is that it is there. I even have Sql Profiler running which >> shows it trying to execute: >> >> exec AddNewResumeTemplate '1234',1001,'This is a test','This is our >> resume',5 >> >> Which is correct and when I copy and paste it into Sql Query Analyser, it >> works. >> >> My code is the same as I have been running for a long time, so I can't >> seem to see what is missing. Here is what it looks like: >> >> ********************************************************************************* >> Dim ConnectionString as String >> =System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_ftsolutions") >> Dim objConn as New SqlConnection (ConnectionString) >> Dim CommandText as String = "AddNewResumeTemplate '1234',1001,'This is a >> test','This is our resume',5" >> Dim objCmd as New SqlCommand(CommandText,objConn) >> objCmd.CommandType = 4 >> trace.warn("objConn.ConnectionString = " & objConn.ConnectionString) >> objConn.Open() >> objCmd.ExecuteNonQuery >> ************************************************************************************* >> >> The trace.warn shows that it is accessing the correct database on the >> server, so I don't know why it can't find it. >> >> Tom >> > > Show quote
"tshad" <tscheider***@ftsolutions.com> wrote in message I tried to change it from a stored Procedure to a regular Sql Command (which news:%23XKMkHXNFHA.3668@TK2MSFTNGP14.phx.gbl... > On Sql Server 2000, my program can't seem to find the Stored Procedure: > AddNewResumeTemplate. > > The error it shows is: > > Could not find stored procedure 'AddNewResumeTemplate '1234',1001,'This is > a test','This is our resume',5'. > > The problem is that it is there. I even have Sql Profiler running which > shows it trying to execute: > > exec AddNewResumeTemplate '1234',1001,'This is a test','This is our > resume',5 > > Which is correct and when I copy and paste it into Sql Query Analyser, it > works. > > My code is the same as I have been running for a long time, so I can't > seem to see what is missing. Here is what it looks like: > > ********************************************************************************* > Dim ConnectionString as String > =System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_ftsolutions") > Dim objConn as New SqlConnection (ConnectionString) > Dim CommandText as String = "AddNewResumeTemplate '1234',1001,'This is a > test','This is our resume',5" > Dim objCmd as New SqlCommand(CommandText,objConn) > objCmd.CommandType = 4 > trace.warn("objConn.ConnectionString = " & objConn.ConnectionString) > objConn.Open() > objCmd.ExecuteNonQuery > ************************************************************************************* > > The trace.warn shows that it is accessing the correct database on the > server, so I don't know why it can't find it. happens to be a stored procedure) by doing: objConn.Open() Dim applicantReader as SqlDataReader = objCmd.ExecuteReader if applicantReader.Read then trace.warn("at read ResumeID = " & applicantReader("ResumeID")) end if This seems to work fine. Why doesn't the Stored procedure work as a Stored Procedure type? Also, I tried to access the error code that is passed from my return like so: trace.warn("after emailReader read error = " & objCmd.parameters(0).ToString()) And I get the error: Exception Details: System.IndexOutOfRangeException: Invalid index 0 for this SqlParameterCollection with Count=0. But that is where someone else said the error would be objCmd.Parameters(0). If this is not the case, where would I find it? Thanks, Tom. Tom,
I'm assuming that the items following the stored procedures name are parameters that the stored proc needs for processing? If so, then you've almost got it. When you set your command text: objCmd.CommandText = "AddNewResumeTemplate" objCmd.CommandType = CommandType.StoredProcedure Then you need to add your parameters: objCmd.Parameters.Add("PARAM_NAME", DataType) *** the parameter name will come from your stored procedure *** Then set the value of the parameter: objCmd.Parameters["PARAM_NAME"].Value = '1234' The rest of it should work. David Show quote
"David Young" <REMOVE_THIS.dmy75***@yahoo.com> wrote in message So you can't do it in one long string (not that I normally would - this is news:Otm0FTXNFHA.204@TK2MSFTNGP15.phx.gbl... > Tom, > I'm assuming that the items following the stored procedures name are > parameters that the stored proc needs for processing? > > If so, then you've almost got it. > > When you set your command text: > objCmd.CommandText = "AddNewResumeTemplate" > objCmd.CommandType = CommandType.StoredProcedure > > Then you need to add your parameters: > > objCmd.Parameters.Add("PARAM_NAME", DataType) > *** the parameter name will come from your stored procedure *** > > Then set the value of the parameter: > objCmd.Parameters["PARAM_NAME"].Value = '1234' just a test of my Stored Produre)? Also, what would be the better way to do it - as a stored procedure as we are doing here or as a regular Sql command where we use the "exec"? Or is there really a difference? Thanks, Tom Show quote > > The rest of it should work. > > David > > We used to be able to do that with classic ASP, ie.
objCmd.CommandText = "spName param1, param2, param3" and then call the objCmd.Execute() I'm not sure if you still have that functionality in .Net Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:uPw9JaXNFHA.1176@TK2MSFTNGP12.phx.gbl... > > So you can't do it in one long string (not that I normally would - this is > just a test of my Stored Produre)? > > Also, what would be the better way to do it - as a stored procedure as we > are doing here or as a regular Sql command where we use the "exec"? Or is > there really a difference? > > Thanks, > > Tom "David Young" <REMOVE_THIS.dmy75***@yahoo.com> wrote in message I did get it working, except for the error that is returned (return @error news:uYgx%23kXNFHA.3620@TK2MSFTNGP10.phx.gbl... > We used to be able to do that with classic ASP, ie. > > objCmd.CommandText = "spName param1, param2, param3" > > and then call the objCmd.Execute() > > I'm not sure if you still have that functionality in .Net from stored procedure). Here is the code: ************************************************************* Dim ConnectionString as String =System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_ftsolutions") Dim objConn as New SqlConnection (ConnectionString) Dim objCmd as New SqlCommand("AddNewResumeTemplate",objConn) objCmd.CommandType = 4 objCmd.parameters.add("@ClientID",SqldbType.VarChar,20).value = "1234" objCmd.parameters.add("@ApplicantID",SqlDbType.Bigint).value = 1001 objCmd.parameters.add("@ResumeTitle",SqlDbType.VarChar,45).value = "this is a test" objCmd.parameters.add("@Resume",SqlDbType.text).value = "This is our Resume" objCmd.parameters.add("@CoverLetterID",SqlDbType.int).value = 5 trace.warn("objConn.ConnectionString = " & objConn.ConnectionString) objConn.Open() Dim applicantReader = objCmd.ExecuteReader trace.warn("after emailReader read error = " & objCmd.parameters(0).ToString()) if applicantReader.Read then trace.warn("read id as " & applicantReader(0)) end if ************************************************************* The responses I get from the traces are: after emailReader read error = @ClientID read id as 5 The "read id as 5" is correct. But I thought (and I could be wrong) that parameters(0) was where the error code would be. But here it is @ClientID, which was the first parameter I set up. Tom Show quote > > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:uPw9JaXNFHA.1176@TK2MSFTNGP12.phx.gbl... >> >> So you can't do it in one long string (not that I normally would - this >> is >> just a test of my Stored Produre)? >> >> Also, what would be the better way to do it - as a stored procedure as we >> are doing here or as a regular Sql command where we use the "exec"? Or > is >> there really a difference? >> >> Thanks, >> >> Tom > > Show quote
"tshad" <tscheider***@ftsolutions.com> wrote in message I did find someplace that showed how to set up a return for a stored news:OQzH%23rXNFHA.4092@tk2msftngp13.phx.gbl... > "David Young" <REMOVE_THIS.dmy75***@yahoo.com> wrote in message > news:uYgx%23kXNFHA.3620@TK2MSFTNGP10.phx.gbl... >> We used to be able to do that with classic ASP, ie. >> >> objCmd.CommandText = "spName param1, param2, param3" >> >> and then call the objCmd.Execute() >> >> I'm not sure if you still have that functionality in .Net > > I did get it working, except for the error that is returned (return @error > from stored procedure). > > Here is the code: > > ************************************************************* > Dim ConnectionString as String > =System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_ftsolutions") > Dim objConn as New SqlConnection (ConnectionString) > > Dim objCmd as New SqlCommand("AddNewResumeTemplate",objConn) > objCmd.CommandType = 4 > objCmd.parameters.add("@ClientID",SqldbType.VarChar,20).value = "1234" > objCmd.parameters.add("@ApplicantID",SqlDbType.Bigint).value = 1001 > objCmd.parameters.add("@ResumeTitle",SqlDbType.VarChar,45).value = "this > is a test" > objCmd.parameters.add("@Resume",SqlDbType.text).value = "This is our > Resume" > objCmd.parameters.add("@CoverLetterID",SqlDbType.int).value = 5 > trace.warn("objConn.ConnectionString = " & objConn.ConnectionString) > objConn.Open() > Dim applicantReader = objCmd.ExecuteReader > trace.warn("after emailReader read error = " & > objCmd.parameters(0).ToString()) > if applicantReader.Read then > trace.warn("read id as " & applicantReader(0)) > end if > ************************************************************* > > The responses I get from the traces are: > > after emailReader read error = @ClientID > read id as 5 > > The "read id as 5" is correct. > > But I thought (and I could be wrong) that parameters(0) was where the > error code would be. > > But here it is @ClientID, which was the first parameter I set up. > procedure: ************************************************************************ objCmd.parameters.add("@return",SqlDbType.int) objCmd.parameters("@return").direction = ParameterDirection.ReturnValue trace.warn("objConn.ConnectionString = " & objConn.ConnectionString) objConn.Open() Dim applicantReader = objCmd.ExecuteReader trace.warn("after emailReader read error = " & objCmd.parameters("@return").value) ****************************************************************************************** I am getting nothing back. I know it does send back a 0 or 1 and it does work in Sql Query Analyser. Tom Show quote > Tom > > >> >> >> "tshad" <tscheider***@ftsolutions.com> wrote in message >> news:uPw9JaXNFHA.1176@TK2MSFTNGP12.phx.gbl... >>> >>> So you can't do it in one long string (not that I normally would - this >>> is >>> just a test of my Stored Produre)? >>> >>> Also, what would be the better way to do it - as a stored procedure as >>> we >>> are doing here or as a regular Sql command where we use the "exec"? Or >> is >>> there really a difference? >>> >>> Thanks, >>> >>> Tom >> >> > > I typically use output parameters when retrieving a value from a stored
procedure so I could be mistaken about this but I'm pretty sure that the return value is not available until you have reached the end of the record set exposed by the DataReader. HTH ---------------- Dave Fancher http://davefancher.blogspot.com Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:OVaLS8XNFHA.3772@TK2MSFTNGP15.phx.gbl... > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:OQzH%23rXNFHA.4092@tk2msftngp13.phx.gbl... >> "David Young" <REMOVE_THIS.dmy75***@yahoo.com> wrote in message >> news:uYgx%23kXNFHA.3620@TK2MSFTNGP10.phx.gbl... >>> We used to be able to do that with classic ASP, ie. >>> >>> objCmd.CommandText = "spName param1, param2, param3" >>> >>> and then call the objCmd.Execute() >>> >>> I'm not sure if you still have that functionality in .Net >> >> I did get it working, except for the error that is returned (return >> @error from stored procedure). >> >> Here is the code: >> >> ************************************************************* >> Dim ConnectionString as String >> =System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_ftsolutions") >> Dim objConn as New SqlConnection (ConnectionString) >> >> Dim objCmd as New SqlCommand("AddNewResumeTemplate",objConn) >> objCmd.CommandType = 4 >> objCmd.parameters.add("@ClientID",SqldbType.VarChar,20).value = "1234" >> objCmd.parameters.add("@ApplicantID",SqlDbType.Bigint).value = 1001 >> objCmd.parameters.add("@ResumeTitle",SqlDbType.VarChar,45).value = "this >> is a test" >> objCmd.parameters.add("@Resume",SqlDbType.text).value = "This is our >> Resume" >> objCmd.parameters.add("@CoverLetterID",SqlDbType.int).value = 5 >> trace.warn("objConn.ConnectionString = " & objConn.ConnectionString) >> objConn.Open() >> Dim applicantReader = objCmd.ExecuteReader >> trace.warn("after emailReader read error = " & >> objCmd.parameters(0).ToString()) >> if applicantReader.Read then >> trace.warn("read id as " & applicantReader(0)) >> end if >> ************************************************************* >> >> The responses I get from the traces are: >> >> after emailReader read error = @ClientID >> read id as 5 >> >> The "read id as 5" is correct. >> >> But I thought (and I could be wrong) that parameters(0) was where the >> error code would be. >> >> But here it is @ClientID, which was the first parameter I set up. >> > > I did find someplace that showed how to set up a return for a stored > procedure: > > ************************************************************************ > objCmd.parameters.add("@return",SqlDbType.int) > objCmd.parameters("@return").direction = ParameterDirection.ReturnValue > trace.warn("objConn.ConnectionString = " & objConn.ConnectionString) > objConn.Open() > Dim applicantReader = objCmd.ExecuteReader > trace.warn("after emailReader read error = " & > objCmd.parameters("@return").value) > ****************************************************************************************** > > I am getting nothing back. > > I know it does send back a 0 or 1 and it does work in Sql Query Analyser. > > Tom >> Tom >> >> >>> >>> >>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>> news:uPw9JaXNFHA.1176@TK2MSFTNGP12.phx.gbl... >>>> >>>> So you can't do it in one long string (not that I normally would - this >>>> is >>>> just a test of my Stored Produre)? >>>> >>>> Also, what would be the better way to do it - as a stored procedure as >>>> we >>>> are doing here or as a regular Sql command where we use the "exec"? >>>> Or >>> is >>>> there really a difference? >>>> >>>> Thanks, >>>> >>>> Tom >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics