Re: How do I use Oracle bind variables using ADO in Visual Basic?

which reminds me of a question I've meant to ask a number of times. Is it possible to use named parameters rather than the question mark notation in ADO ? so instead of

SELECT x FROM y WHERE z = ?

being passed you can actually pass something like

where z = namedparameter.

"tojo" <TomJordanTojo_at_hotmail.com> wrote in message news:MPG.174376807c7a2b7a98968a_at_news.t-online.de...
> In article <df35b276.0205080727.6933e5f3_at_posting.google.com>,
> burgess_at_sympatico.ca says...
> > We are using VB through ADO & OLEDB with Oracle 8i. Right now, we're
> > just sending hard-coded SQLs (ie. SELECT x FROM y WHERE z = 5) to the
> > Execute method of the ADODB Connection object and getting the results
> > from the Recordset object.
> > We'd MUCH rather use bind variables in cases like this.
> >
> > I'd rather not rewrite the database layer to use OO4O, if at all
> > possible. If there a way to use bind variables (ie. SELECT x FROM y
> > WHERE z = :v) in ADO?
> >
>
> Just use ADO's parameters collection and a question mark (?) as a bind
> variable in your SQL. Here's an example from the docs:
>
>
> Dim objCon As NEW ADODB.Connection
> Dim objCmd As NEW ADODB.Command
> Dim prEmpno As NEW ADODB.Parameter
> Dim prEname As NEW ADODB.Parameter
> ...
> objCmd.ActiveConnection = objCon
> objCmd.CommandType = adCmdText
>
> ' Create and append the parameters to the command object
> Set prEmpno = objCmd.CreateParameter("prEmpno", adSmallInt,
> adParamInput, ,8521)
> ' prEname is bound to a NVARCHAR column in the EMP table
> Set prEname = objCmd.CreateParameter("prEname", adBSTR, adParamInput, ,
> "Joe")
> objCmd.Parameters.Append prEmpno
> objCmd.Parameters.Append prEname
>
> ' Enabling the NDatatype property indicates to the provider
> ' that one or more of the bound parameters is of N datatype
> objCmd.Properties("NDatatype") = TRUE
>
> ' Assume column ENAME in table EMP is of NVARCHAR type
> objCmd.CommandText = "INSERT INTO EMP (EMPNO, ENAME) VALUES (?, ?)"
>
> ' Execute the SQL
> objCmd.Execute
>
> ' It is a good idea to disable the property after execute as the same
> command
> ' object may be used for a different SQL statement
> objCmd.Properties("NDatatype") = FALSE
>
>
> -- tojo
Received on Thu May 09 2002 - 03:20:14 CDT