Get Values from MSSQL Stored Procedure

Hi All,

Here is my code:

Dim rs As RecordSet
Dim loc_sql as string

Loc_sql = “execute my_procedure @NumberType=‘Test’, @increment=1”
self.SQLSelect(Loc_sql)
msgbox self.errormessage

The procedure intends to return an integer value, but now it returns an error: Invalid Cursor State

However if I change to SqlExecute, no error is posted. Can anybody help?

Thanks

Tony Lam

Should it be

Loc_sql = “execute my_procedure @NumberType=‘Test’, @increment=1”;
rs = self.SQLSelect(Loc_sql)
msgbox self.errormessage

You need to use the result of the select.

This is with the Xojo database plugin for MS SQL Server?
or using MBS Plugin?

With SQLDatabaseMBS class:

[code] // with prepared statement
dim sql as string = “dbo.Test”
dim ps as SQLPreparedStatementMBS = con.Prepare(sql)

  ps.Bind("Param1", 7)
  ps.BindType("Param1", ps.kTypeLong)
  
  dim r as RecordSet = ps.SQLSelect
  
  if r<>nil then
    MsgBox r.IdxField(1).StringValue
  else
    MsgBox "Failed"
  end if[/code]

@Wayne,

Sorry typo, yes, what exactly my code is: rs = self.SQLSelect(Loc_sql)

@Christian Schmitz

Just Xojo ODBC