Strange Behaviour in ADO, any ideas why this happens?

I have a program that gets data from one table using a Stored Procedure (say sp_StoredProc1) which is working fine. I substituted a different Stored procedure (say sp_StoredProc2) in place of sp_StoredProc1. I got Error: Line 1: Incorrect syntax near 'N'. The Stored Procedures are exactly the same with the exception that they Select from different tables (tables are identitical, just different data). I ran both Queries through QueryAnalyzer, so I know that they both work and return the correct Data. The funny part is that if I remove the parantheses (like sp_StoredProc2 'N'), then the second Query works fine!

I was wondeing if anyone has come across this before? I have already fixed it by removing the parentheses, I'm just interested in finding out why one works and the other doesn't.

Here's a sample of my code (I use ADO 2.5):

Code:

---

strQuery = "sp_StoredProc1('N')"
           
Set rsData = Nothing

If rsData Is Nothing Then
    Set rsData = cnConnection.Execute(strQuery, 0, adAsyncExecute)
Else
    rsData.Requery
End If

If rsData.State <> adStateExecuting Then  '<-- Breaks here when I replace sp_StoredProc1 with sp_StoredProc2
    'Code Here
End If

---

Quote:

---

Originally Posted by malleyo

I have a program that gets data from one table using a Stored Procedure (say sp_StoredProc1) which is working fine. I substituted a different Stored procedure (say sp_StoredProc2) in place of sp_StoredProc1. I got Error: Line 1: Incorrect syntax near 'N'. The Stored Procedures are exactly the same with the exception that they Select from different tables (tables are identitical, just different data). I ran both Queries through QueryAnalyzer, so I know that they both work and return the correct Data. The funny part is that if I remove the parantheses (like sp_StoredProc2 'N'), then the second Query works fine!

I was wondeing if anyone has come across this before? I have already fixed it by removing the parentheses, I'm just interested in finding out why one works and the other doesn't.

Here's a sample of my code (I use ADO 2.5):

Code:

---

strQuery = "sp_StoredProc1('N')"
           
Set rsData = Nothing

If rsData Is Nothing Then
    Set rsData = cnConnection.Execute(strQuery, 0, adAsyncExecute)
Else
    rsData.Requery
End If

If rsData.State <> adStateExecuting Then  '<-- Breaks here when I replace sp_StoredProc1 with sp_StoredProc2
    'Code Here
End If

---

---

If you think about this: "The funny part is that if I remove the parantheses (like sp_StoredProc2 'N'), then the second Query works fine!" the answer should be apparent - Do you have any kind of return value on 1st procedure? The procedure working like that after making the change you did indicates there is not return value ...

There is no Return Value. Both are Select Statements that return 1000 rows (or less) in a recordset.

you might try:

Code:

---

Dim rsData as adodb.recordset
set rsData=new adodb.recordset

Dim cmd as adodb.command
Set cmd = new ADODB.Command

cmd.CommandText = "sp_StoredProc1"

cmd.Parameters.Add cmd.CreateParameter("@ParameterName", adVarchar, adParamInput, 1, "N")

cmd.activeconnection=YourConnectionObject
rsData.Open cmd

---

or, if you do not want the command object:

Code:

---

Set rsData = Connection.Execute("EXEC sp_StoredProc1 @TheParmName ='N'")

---

Cimperiali: Thanks for your suggestion. Your first example is how I normally write my ADO stuff. The code in my original post was written by someone else. All I had to do was remove the parenteses and it worked fine. I was just curious if anyone knew why it broke with one Stored Proc and not the other.

Thanks to everyone who tried to help!

:D