0

I have an SP in SQL Server (2012). It returns a single row...verified using SSMS 

exec dbo.cpas_DeleteProjects N'3555,3565'

It returns a valid row with record count info in the columns.

From Access(2007), I'm calling it using an adodb call:

Sub DevolveProjects(ProjectIDs As String)
''
'   Looking for a comma-delimited list of project IDs in string form: "34,52,14"
'

Dim cnn As New adodb.Connection
Dim rstReturnValues As adodb.Recordset

cnn.ConnectionString = "Provider=SQLOLEDB;Server=" & CurrentServerName() & ";Initial Catalog=" & CurrentDBName() & ";Integrated Security=SSPI;"
cnn.Open
Set rstReturnValues = cnn.Execute("exec dbo.cpas_DeleteProjects N'" & ProjectIDs & "'")

With rstReturnValues...

Problem is, rstReturnValues is closed. No data.

I've verified using SQL Profiler that the sp is running the expected SQL when called from the Access app as above, and seems to be performing just fine, so there are no issues with authentication. I'm just not getting my recordset populated. When I copy and paste the exact SQL statement SQL Profiler saw into an SSMS query, it returns EXACTLY what I expect....

Have I coded the .execute call incorrectly? Ideas?

JimS-CLT
  • 665
  • 4
  • 13
  • 30

1 Answers1

1

The code looks right, although you could try using bind parameters instead of a single SQL string. For something quick and dirty it doesn't make any difference, but this removes the possibility of SQL injection

Dim lConnection as new ADODB.Connection
Dim lCommand as new ADODB.Command
Dim lParameter as ADODB.Parameter
Dim lRecordset as ADODB.Recordset

lConnection.ConnectionString = "Provider=SQLOLEDB;Server=" & CurrentServerName() & ";Initial Catalog=" & CurrentDBName() & ";Integrated Security=SSPI;"
lConnection.Open
lCommand.ActiveConnection = lConnection
lCommand.CommandText = "dbo.cpas_DeleteProjects"
lCommand.CommandType = adCmdStoredProc
set lParameter = lCommand.CreateParameter(, adVarWChar, adParamInput, 200, ProjectIDs) 'replace 200 with parameter length, different syntax if nvarchar(max)
lCommand.Parameters.Append lParameter 
Set lRecordset = lCommand.Execute()

Debug.Print lRecordset(0) 'Does the recordset contain anything?

...
lRecordset.Close
lConnection.Close
Laurence
  • 10,896
  • 1
  • 25
  • 34
  • I had posted an earlier question and it was answered, rendering this one moot. For the answer that worked, see http://stackoverflow.com/questions/18664139/connection-string-for-access-to-call-sql-server-stored-procedure/18676579#18676579 – JimS-CLT Sep 11 '13 at 14:47