Introduction to ADO - The Command Object

• shamshudheen

  SSCrazy

  Points: 2390

  More actions

  January 28, 2009 at 11:32 pm

  #934407

  hi Andy

  i have procedure in sql server s follows

  ALTER proc testing

  as

  begin

  --select 'testing'

  delete from obj

  INSERT INTO Obj(objName,blnisvalid)

  SELECT NAME,dbo.fn_IsValidObjectName(NAME) FROM sysobjects

  WHERE XTYPE='V'

  SELECT objName FROM Obj

  WHERE blnisvalid = 1

  ORDER BY objName ASC

  end

  and i am calling this procedure from vb using ADO Command object by clicking command button . see the code below

  Private Sub Command1_Click()

  Dim cnn As New ADODB.Connection

  Dim rs As ADODB.Recordset

  Dim cmd As New ADODB.Command

  cnn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=true;User ID=sa;pwd=system;Initial Catalog=EMS_DB_435_FULL;Data Source=SW-SHAM"

  cnn.Open

  cmd.ActiveConnection = cnn

  cmd.CommandText = "Exec testing"

  cmd.CommandType = adCmdText

  Set rs = cmd.Execute

  MsgBox rs.State

  End Sub

  after running the code i am getting rs.state is 0 . when i run the stored procedure in query analyzer i am getting records from ado command object i am getting.

  after analysis i found the stored procedure is getting executed ( records were inserted ) but selected result not returning from ADO Command Object

  if you have any idea to get the records from the stored procedure, please let me know

  thanks