Stored Procedure

Hye
Please read the following and reply me with solution

suppose there is a database in sql server named test, a table named tbltest and a stored procedure named sptest within this database.

tbltest hase two fields say a(int), b(varchar(10))

stored procedure sptest is given below:

/*start of stored procedure*/
create procedure sptest as
declare @n int
if (select count(a) from tbltest)=0
select @n=0
else
select @n=max(a) from tbltest

insert tbltest values(@n+1, 'zzzzzz')

select * from tbltest
/*end of stored procedure*/

vb-ide details
reference used: Microsoft ActiveX Data Object 2.5 Library
project type: standard.exe
form: a single form within this project

vb code is given below:

/* general section start*/
Dim cmm As ADODB.Command
Dim rs As ADODB.Recordset
/* general section end*/

Private Sub Form_Load()
Set cmm = New ADODB.Command
Set rs = New ADODB.Recordset

With cmm
.ActiveConnection = "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=test;Data Source="Please use your database server name here""
.CommandType = adCmdStoredProc
.CommandText = "sptest"
End With

rs.Open cmm, , adOpenKeyset, adLockOptimistic
MsgBox rs.Fields(0)
End Sub


operating system used: Windows XP professional
database used: SQL Server 7.0
vb-ide: Visual Basic 6.0

my comments:
when stored procedure sptest execute (i.e. i run the vb project) it can insert values to table tbltest but the select statement following insert statement does not execute (i may be wrong).

please give me the solution. but change in stored procedure is not allowed; also VB data access reference

your messagebox will always show the same value, since you select the first row, first column in the recordset.

I you would like to see the inserted number you have to scroll in the recordset to the last record and display that.

If you, in your stored procedure do a select * from tbltest where a = @n, you will receive a one row recordset and the rs.Fields(0) will return the a column value of the newly inserted row.

You could accomplish the same thing if you just make the last statement in your stored procedure as select @n.

Thanks,