stored procedure doesn't run properly in Access

  • Thread starter Thread starter ming
  • Start date Start date
M

ming

Hi, I called a stored procedure from access using command.
However the result recordset doesn't contain anything. I
tested the stored procedure in the SQL query analyzer, it
worked very well. How come it didn't work when i called
from Access. Please help.
 
ming said:
Hi, I called a stored procedure from access using command.
However the result recordset doesn't contain anything. I
tested the stored procedure in the SQL query analyzer, it
worked very well. How come it didn't work when i called
from Access. Please help.
Click to expand...

How did you call the stored procedure? Please show your code.
 
Here is my code in Access:

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

conn.Open "Provider=SQLOLEDB.1;Password=;Persist
security Info=False;User ID=;Initial Catalog=lab;Data
Source="
With cmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "searchname"
End With

Dim prm As ADODB.Parameter
Set prm = cmd.CreateParameter("@lname", adChar,
adParamInput, 50, lname)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@fname", adChar,
adParamInput, 50, fname)
cmd.Parameters.Append prm

Set rs = cmd.Execute

I can't get any records from rs.

Thanks.
 
ming said:
Here is my code in Access:

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

conn.Open "Provider=SQLOLEDB.1;Password=;Persist
security Info=False;User ID=;Initial Catalog=lab;Data
Source="
With cmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "searchname"
End With

Dim prm As ADODB.Parameter
Set prm = cmd.CreateParameter("@lname", adChar,
adParamInput, 50, lname)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@fname", adChar,
adParamInput, 50, fname)
cmd.Parameters.Append prm

Set rs = cmd.Execute

I can't get any records from rs.

Thanks.
Click to expand...

Hmm, I'm no great expert on ADO, but the only thing I see wrong in that
code is the line
Set rs = New ADODB.Recordset
Click to expand...

I don't think this line serves any purpose, since you're not going to
use that Recordset object variable until you set it to the recordset
created by cmd. I'd delete that line.

I don't see, though, how that would keep you from getting any records
back in the recordset returned by cmd. Questions:

1. Have you verified that cmd executed successfully?

2. Is a recordset actually returned by cmd?

3. How have you verified that the recordset is empty?

4. Are you sure that the variables (or control references) lname and
fname have the values you think they do, and that these values should
cause the stored procedure "searchname" to return records?

5. If you execute the stored procedure directly with those parameters
via, say SQL Query Analyzer, does it return records?

6. Have you checked the connection's Errors collection after executing
cmd, to see if there's any error reported there?
 
1. Have you verified that cmd executed successfully?
2. Is a recordset actually returned by cmd?
3. How have you verified that the recordset is empty?

Yes. Actually i did try in SP that I put 'select * from
tableA where lname="rop"', just this line. It turned out
the access form worked very well and I can see records
from the rs.

4. Are you sure that the variables (or control references)
lname and fname have the values you think they do, and
that these values should cause the stored
procedure "searchname" to return records?

I added quick watch and I can tell the values for the
parameters are correctly passed.

5. If you execute the stored procedure directly with those
parameters via, say SQL Query Analyzer, does it return
records?

Yes, via SQL query analyzer, the Sp outputs records.

6. Have you checked the connection's Errors collection
after executing cmd, to see if there's any error reported
there?

No, I didn't see any errors, the count for it is 0.
 
ming said:
1. Have you verified that cmd executed successfully?
2. Is a recordset actually returned by cmd?
3. How have you verified that the recordset is empty?

Yes. Actually i did try in SP that I put 'select * from
tableA where lname="rop"', just this line. It turned out
the access form worked very well and I can see records
from the rs.

4. Are you sure that the variables (or control references)
lname and fname have the values you think they do, and
that these values should cause the stored
procedure "searchname" to return records?

I added quick watch and I can tell the values for the
parameters are correctly passed.

5. If you execute the stored procedure directly with those
parameters via, say SQL Query Analyzer, does it return
records?

Yes, via SQL query analyzer, the Sp outputs records.

6. Have you checked the connection's Errors collection
after executing cmd, to see if there's any error reported
there?

No, I didn't see any errors, the count for it is 0.
Click to expand...

May I ask again, how are you determining that the returned recordset is
empty? The RecordCount property won't tell you. I tried this code,
using the "pubs" sample database that comes with Access:

'----- start of test code -----
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

conn.Open "DSN=Pubs"
With cmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "byroyalty"
End With

Dim prm As ADODB.Parameter

Set prm = cmd.CreateParameter( _
"@percentage", adInteger, adParamInput, , 100)
cmd.Parameters.Append prm

Set rs = cmd.Execute

Debug.Print rs.EOF
Debug.Print rs.BOF
Debug.Print rs.Fields.Count, rs.Fields(0)

rs.Close

Set rs = Nothing
Set prm = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing

'----- end of test code -----

It worked fine and returned records.
 
Back
Top