Driver name was Re: How make a function in vb

Walter

Just out of interest you refer to MSDAORA.2, I have always used MSDAORA.1 does the number refer to a version of the driver or particular functionality?

I agree with the book recommendation, another excellent wrox book.

curious

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Walter T Rejuney" <BlueSax_at_Unforgetable.com> wrote in message
news:3A3F96D3.3B50B3CA_at_Unforgetable.com...


> Avi Abrami wrote:

>

> > Walter T Rejuney wrote:

> >

> > > MP wrote:

> > >

> > > > How can call a function in Oracle that return a number  in VB

> > > > Thanks

> > >

> > > Could you restate this? The sentence, as written, doesn't make any

> > > sense.

> >

> > Although I didn't write the original sentence, allow me to express

> > my interpretation ...

> >

> > From a Visual Basic program, I wish to execute an Oracle database

> > stored function and I know that the database function returns a number.

> >

> > Is that better?

> > Avi.

>

> Ok, if this is what is wanted, the actual methods depend on what approach

> one takes to data access. Generally I use ADO and this means that I deal

> with OLE DB syntax. However, there are many others who prefer OO4O and the

> syntax for that approach is entirely different.

>

> I'll illustrate in a general way how I go about this using ADO and OLE DB:

>

> First, lets assume that instead of using a function, you actually use a

> stored procedure that accepts one IN parameter and returns the output

 value


> in an OUT parameter. Also, there are two additional OUT parameters which

> will hold the output from SQLCODE and SQLERRM (I have found that I need

> this because sometimes the ADO error detection does not really work as it

> should when stored procedures are involved.

>

> So, here is my stored procedure:

>

> create or replace procedure myproc (InParm    IN    Varchar2,

>                                                        OutParm OUT Number,

>                                                        Errcode   OUT

> Number,

>                                                        Errmsg    OUT

> Varchar2)

> Begin

>     If Inparm = 'xxx' then

>         OutParm := 1;

>     Else

>         OutParm := 2;

>     End if;

> Exception

>     When Others Then

>         Errcode := Sqlcode;

>         Errmsg := Substr(Sqlerrm,1,300);

> End;

>

>

> In my vb code I'd do something like this:

>

> Public Sub GetValue()

>     Dim MyConnection as ADODB.Connection

>     Dim MyCommand as ADODB.Command

>     Dim Parm1 as ADODB.Parameter

>     Dim Parm2 as ADODB.Parameter

>     Dim Errcode as ADODB.Parameter

>     Dim Errmsg  as ADODB.Parameter

>     Dim OraError as ADODB.Error

>     Dim strSql as String

>     Const strUsername As String = "scott"

>     Const strPassword As String = "tiger"

>     Const strService  As String = "orcl"

>     Const strProvider As String = "MSDAORA.2"

>     Const strConnect  As String = "Provider=" & strProvider & ";" & _

>                                      "Data Source=" & strService & ";" & _

>                                      "User Id=" & strUsername & ";" & _

>                                      "Password=" & strPassword & ";"

>

>

>

>     Set MyConnection = New ADODB.Connection

>     MyConnection.ConnectionString = strconnec

>     MyConnection.CursorLocation = adUseServer

>     MyConnection.Open

>

>     strSql = "scc.InsertCallDetail"

>

>     Set MyCommand = New ADODB.Command

>     Set MyCommand.ActiveConnection = MyConnection

>     MyCommand.CommandType = adCmdStoredProc

>     MyCommand.CommandText = strSql

>     Set Parm1 = MyCommand.CreateParameter(, adVarchar, adParamInput)

>     Parm1.Value = "yyy"

>     MyCommand.Parameters.Append Parm1

>     Set Parm2 = MyClmmand.CreateParameter(,adNumeric,adParamOutput)

>     Parm2.Value = ""

>     MyCommand.Parameters.Append Parm2

>     Set ErrCode = MyCommand.CreateParameter(,adNumeric,adParamOutput)

>     ErrCode.Value = 0

>     MyCommand.Parameters.Append ErrCode

>     Set ErrMsg = MyCommand.createParameter(,adVarchar,adParamOutput)

>     ErrMsg.Value = ""

>     MyCommand.Parameters.Append Errmsg

>     MyCommand.Execute

>

>     If Errcode.Value <> 0 Then

>         MsgBox Errmsg.Value

>     Else

>         MsgBox "The return value is: " & Cstr(Parm2.Value)

>     End if

> End Sub

>

>

> Of course, this is only the very basics. There are a lot of variables

> depending on what exactly you are trying to accomplish. The best book on

> this subject is: "VB Oracle 8 Programmers Reference" by Dov Trietsch.

>