Can't find stored procedure

[tshad]

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]

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.

 

[tshad]

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.

I tried to change it from a stored Procedure to a regular Sql Command (which
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.

 

[David Young]

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

 

[tshad]

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.

I was using an example from www.4guysfromrolla.com example - don't know if 4
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.

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.

I agree. I would normally use parameters, but I am just testing the stored
procedure, so I am just doing it simply.

Thanks,

Tom

 

[tshad]

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'

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]

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

 

[tshad]

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.

Tom

 

[tshad]

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

 

[Dave Fancher]

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