recordset

[YanaD]

I run a store procedure which inserting new data into the table and on the last statement in store procedure i selecting the id(autonumber) and returning this value to VB applic. program. The Store proced runs very well and inform is in table and selecting id, but the recordset is not working after telling the msg that object is closed eventhough i didn't close the recordset.
this is store proc executing
Set rs = cmdInsertNewCustomer.Execute
If rs.RecordCount <> 0 Then
frmFc.txtID = rs.Fields(&quot;ID&quot.Value
end if

Help!

 

[woyler]

the rs is not getting a valid recordset back from the procedure(there is no value) You cant set an rs to a empty recordset from a SP

 

[YanaD]

you didn't get my point and i can't get yours.

 

[woyler]

The point is ...
You wrote i selecting the id(autonumber) and returning this value to VB applic. program.

You are trying to Set a recordset like so:
Set rs = cmdInsertNewCustomer.Execute
The value returned from cmdInsertNewCustomer.Execute
is either empty or not a recorset. You can not Set a Recordset to an incorrect or empty value. So when you try to reference that recordset with your code:If rs.RecordCount <> 0 Then, you will get the error telling you that you can not manipulate the object if it is closed. Your command must return valid recordset info for the recordset to be valid.

 

[YanaD]

correct. But what should I do at this point. If you have some experience to share with me, I will appreciate it.
Thanx

 

[woyler]

Gladly,
I first need to see what you are executing with cmdInsertNewCustomer.Execute

 

[YanaD]

cmdInsertNewCustomer - is a command that executing the store procedure on a server to add new customer in a cutomertable where id incremented by itself by one. When I wrote all parameters in a code to input to the table, then i need to retrieve the Id of this new customer to vb control(CustID- on the screen). The store procedure is working fine because after this line cmdinsertnewcustomer.execute it is saving new customer and give the cust number, but when I debug the program and go the next line of the code where i check if recodset <>0 then it says that object is closed eventhough i didn't close the recordset.
In Store procedure i have insert statement and right after select statement where i selecting Id = @@IDENTITY.
If something is still unclear let me know.

 

[woyler]

Are you returning a value(Integer or something) from the stored procedure or are you returning a cursor?

 

[YanaD]

Yes I am returning a value (CustID), and right after when I am executing the store procedure, i check if recordset.recordcount <> 0 then place this (CustID) value into the CustomerID control on a screen. The problem is that after executing and then checking the recordset for recordcount (debugging), when i place my cursor into the yellow line of code to the recordset, it shows the msg that object is closed. How it can be closed if it run and executed the store procedure from this recordset, and I just continued to work with the same recordset.

 

[woyler]

Can you post your Stored Procedure?

 

[woyler]

Alsoo have you set your Command object to:
cmdInsertNewCustomer.CommandType = adCmdStoredProc

 

[YanaD]

Ofcourse I did.
Here is the store proc. I know it is working because i checked and moreover the info from the application is stored into the table through this store proced.
*******************************
CREATE PROCEDURE [sp_InsertNewCustomer1]
(@FirstName nvarchar (100),
@LastName nvarchar (100),
@MI char (2),
@Prefix char (5),
@Suffix char (5),
@CompanyName nvarchar (50),
@Address1 nvarchar (50),
@Address2 nvarchar (30),
@City nvarchar (40),
@State char (2),
@ZipCode nvarchar (10),
@DayPhone nvarchar (10),
@NightPhone nvarchar (10),
@FaxPhone nvarchar (10),
@Email nvarchar (60),
@AgentCreated varchar (50),
@DateCreated varchar(8))
AS
INSERT INTO tblCustInfo
(FirstName, LastName, MI, Prefix, Suffix, CompanyName, Address1, Address2, City, State, ZipCode, DayPhone, NightPhone, FaxPhone, Email, AgentCreated, DateCreated)
VALUES (@FirstName, @LastName, @MI, @Prefix, @Suffix, @CompanyName, @Address1, @Address2, @City, @State, @ZipCode, @DayPhone, @NightPhone, @FaxPhone, @Email, @AgentCreated, @DateCreated)
SELECT CustID FROM tblCustInfo WHERE CustID =@@IDENTITY
GO
************************************
in the VB code I did create parameter for each value and I followed all syntax of it to execute and it is executing the problem is after when i try to retrieve a value of CustID.

 

[woyler]

After you execute your sp, If you run

dim rsTest as ADODB.Recordset

Set rsTest = Connection.Execute(&quot;SELECT CustID FROM tblCustInfo WHERE CustID =@@IDENTITY&quot

Do you get any records back?