[RESOLVED] VB & SQLStoredProcedures

[kishore.kr]

hi,

i am using vb and storedprocedures., in vb adodb.command is used. i am inserting into a table using stored procedure. after inserting i want to return the inserted row to a recordset.

in a select storedprocedure the "set rs = command1.execute" works fine. but in a insert sp, after the insert statement any select statement is not considered. so in insert sp, "set rs = command1.execute" does not work. can anyone explain me why and is there any way to return the row in the insert sp itself.

[kishore.kr]

hi again

it is possible in vb's adodb.command, to use the output parameter type or returnparameter type and return single value from storedprocedure. but how to return a recordset itself, like in any select_storedprocedure..

[dee-u]

Could you post your code in your StoredProc?

[kishore.kr]

hi,

Code is here

CREATE PROCEDURE [sInsert_mPEmp]
    ( @E_Fname_2    [nvarchar](255),
     @E_Mname_3     [nvarchar](255),
     @E_Lname_4     [nvarchar](255),
     @E_Desg_5  [int],
     @E_DOB_6   [smalldatetime],
     @E_DOJ_7   [smalldatetime],
     @E_LDR_8   [smalldatetime],
     @E_Status_9    [int])
 
AS
begin
 INSERT INTO [DBP].[dbo].[mPEmp] 
     ([E_Fname],
     [E_Mname],
     [E_Lname],
     [E_Desg],
     [E_DOB],
     [E_DOJ],
     [E_LDR],
     [E_Status]) 
 
VALUES 
    ( @E_Fname_2,
     @E_Mname_3,
     @E_Lname_4,
     @E_Desg_5,
     @E_DOB_6,
     @E_DOJ_7,
     @E_LDR_8,
     @E_Status_9)
 
select E_Id, E_Fname + ' ' + E_MName + ' ' + E_LName as E_Name, E_Desg from mpEmp where E_Id = (select max(E_Id) from mpemp)
end
GO

if I remove then Insert part and keep the select query then it works fine. the sp works fine in QueryAnalyser(it inserts and it returns the inserted row.). so i think the problem is in Adodb.command. [i guess it will not accept returned rowset, when insert statement is used in sp.]

[pranoy]

I've tried with a similar sp and it works file

Dim myCon As New ADODB.Connection
   Dim myCmd As New ADODB.Command
   Dim rs As Recordset
   
   myCon.Open myConnectionString
   myCmd.ActiveConnection = con
   myCmd.CommandText = "sp_mysp"
   myCmd.CommandType = adCmdStoredProc
   myCmd.Parameters.Append myCmd.CreateParameter("param1", adVarChar, adParamInput, 10, "ABCD")
   
   Set rs = myCmd.Execute

Another option is

Dim myCon As New ADODB.Connection
   Dim rs As Recordset
 
   myCon.Open myConnectionString
   
   rs.open "exec sp_mysp",myCon,adOpenStatic,adLockReadOnly

In both the above examples, the recordset retrieves the inserted rows

[kishore.kr]

hi,

the code when uses select SP then it works fine. or if Insert alone is used then also works fine. but when it the sp executes a Insert command and then followed by select., the result of select is not received by ADODb.command. i say adodb.command does not receives bcos when i run in sql query analyser, the insert and select both get executed and the select displays the result..

regarding the second option you want me to execute the sp like

rs.open "exec insert_sp " & param1 & "," & param2 & "," ...,myCon,adOpenStatic,adLockReadOnly

Hmmmm, it look tedious with 10 or 15 parameters, anyhow i'll try it. i am trying out other options also. thank you for the response..

[techgnome]

Our entire application is build upon this stuff and it works just fine. I suspect there's something else wrong here. The SP looks OK, so let's look at the code.

A few questions:
How do you know that it's not working?
What does your VB code look like?

Tg

[kishore.kr]

hi,

i say that adodb.command is not working bcos when i execute the sp in queryanalyser it works fine and returns the inserted row., but it does'nt even open the recordset in adodb.command(means rs.state is 0).

com.CommandType = adCmdStoredProc
  com.CommandText = "sInsert_mpEmp"
  Set par = com.CreateParameter("Fname", adVarChar, adParamInput, 100, txtFName.Text)
  com.Parameters.Append par 'com.Parameters("@E_Fname_2") = par
  Set par = com.CreateParameter("Mname", adVarChar, adParamInput, 100, txtMName.Text)
  com.Parameters.Append par 'com.Parameters("@E_Mname_3") = par
  Set par = com.CreateParameter("Lname", adVarChar, adParamInput, 100, txtLName.Text)
  com.Parameters.Append par 'com.Parameters("@E_Lname_4") = par ' 
  Set par = com.CreateParameter("Designation", adInteger, adParamInput, , cmbDesignation.List(cmbDesignation.ListIndex, 1))
  com.Parameters.Append par 'com.Parameters("@E_Desg_5") = par '
  Set par = com.CreateParameter("DOB", adDate, adParamInput, , dtDOB.Value)
  com.Parameters.Append par 'com.Parameters("@E_DOB_6") = par '
  Set par = com.CreateParameter("DOJ", adDate, adParamInput, , dtDOJ.Value)
  com.Parameters.Append par 'com.Parameters("@E_DOJ_7") = par '
  Set par = com.CreateParameter("LDOR", adDate, adParamInput, , dtLDOR.Value)
  com.Parameters.Append par 'com.Parameters("@E_LDR_8") = par '
  Set par = com.CreateParameter("Status", adInteger, adParamInput, , cmbEStatus.List(cmbEStatus.ListIndex, 1))
  com.Parameters.Append par 'com.Parameters("@E_Status_9") = par '
  set rs = com.Execute

[brucevde]

Every SQL statement (insert, update, select, deletes) executed in a stored procedure return a result to the client. The only way ADO can capture these results is via its Recordset object.

The Recordset is closed because the Insert statement does not return records. However, there is another recordset available via the Recordset.NextRecordset property

Two options to solve this problem.

set rs = com.Execute ' first recordset contains result of Insert statement.
set rs = rs.nextRecordset 'next recordset contains result of Select statement

or

Add Set NoCount On to the beginning of your stored procedure. This means only the result of the last sql statement, i.e. your Select, will return its results to ADO. There would be no need to use rs.NextRecordset.

[szlamany]

I agree with BRUCEVDE - use the SET NOCOUNT ON - it will eliminate the unwanted "rows affected" block from coming back to the client after the INSERT - which is getting in the way of you seeing the SELECT recordset.

[techgnome]

We've gotten into the habit of settin RowCount OFF that I forgot about that. Something else we normaly do is that an insert is supposed to do just that... insert. We typicaly use an outbound param to return the id, then make a separate trip back to retrieve the record.... and unless we have good reason to do otherwise, that comes back as output parameters too.....

Tg

[kishore.kr]

hi guys,

I also forgot and did not used the Set NoCount On option. Thanks brucevde and guys.