Using an OLE DB Provider's Functions in Code

[Lyle Fairfield]

Sometimes we use script or code whose variable types do not correspond to
the variable types of other technologies with which they interface.
It's possible that our db provider may come to our rescue, both by
applyling its functions and by "returning" a compatible dtat type.

Here is a VBA-ADO-MS-SQL example. It uses MS-SQL functions to trim a
string. (Yes, I know VBA has these functions; I am attempting to
demonstrate a capability, not to show an explicit solution for something. I
am experimenting with using this in dealing with the ASP Request.Form
object in javascript.)

Sub temp()
Dim s As String
s = " PaddedString "
Debug.Print Len(s) ' 16
s = CurrentProject. Connection.Exec ute( _
"SELECT TOP 1 LTRIM(RTRIM('" & s & "')) " _
& "FROM Schools").Colle ct(0)
Debug.Print Len(s) ' 12
End Sub
--
Lyle
--

[Dimitri Furman]

On Apr 02 2005, 07:11 pm, Lyle Fairfield <Lo******@FFDBA .Com> wrote in
news:Xn******** ***********@216 .221.81.119:

Sub temp()
Dim s As String
s = " PaddedString "
Debug.Print Len(s) ' 16
s = CurrentProject. Connection.Exec ute( _
"SELECT TOP 1 LTRIM(RTRIM('" & s & "')) " _
& "FROM Schools").Colle ct(0)
Debug.Print Len(s) ' 12
End Sub

A Sunday morning nitpick: You don't need the FROM clause in there.

You mention using the data provider to "return" a compatible data type. I
don't think this is demonstrated in your example, and I'm not sure I know
exactly how you would do this. Another example, perhaps?

--
remove a 9 to reply by email

[Lyle Fairfield]

Dimitri Furman <df*****@cloud9 9.net> wrote in
news:Xn******** *************** *****@127.0.0.1 :

On Apr 02 2005, 07:11 pm, Lyle Fairfield <Lo******@FFDBA .Com> wrote in
news:Xn******** ***********@216 .221.81.119:

Sub temp()
Dim s As String
s = " PaddedString "
Debug.Print Len(s) ' 16
s = CurrentProject. Connection.Exec ute( _
"SELECT TOP 1 LTRIM(RTRIM('" & s & "')) " _
& "FROM Schools").Colle ct(0)
Debug.Print Len(s) ' 12
End Sub

A Sunday morning nitpick: You don't need the FROM clause in there.

You mention using the data provider to "return" a compatible data type. I
don't think this is demonstrated in your example, and I'm not sure I know
exactly how you would do this. Another example, perhaps?

Yes, and when you don't use the FROM the TOP 1 is redundant as well. Thank
you.

This is some trivial ASP-Javascript stuff.

a form

<form method="post" action="copy of classes.asp" >
<input type ="text" value =" Dimitri "
<input type="submit">
</form>

some asp-javascript in copy of classes.asp

var s=Request.Form;
Response.Write( '<p>'+typeof s+'</p>');
Response.Write( '<p>'+s.constru ctor+'</p>');
Response.Write( '<p>'+s+':'+s.l ength+'</p>');
s=c.Execute('SE LECT LTRIM(RTRIM(\'' +s+'\'))').Coll ect(0);
Response.Write( '<p>'+typeof s+'</p>');
Response.Write( '<p>'+s.constru ctor+'</p>');
Response.Write( '<p>'+s+':'+s.l ength+'</p>');

which yields:

object
undefined
:undefined
string
function String() { [native code] }
:0

it seems we have to convert Request.Form to a string before we can deal
with it in some ways, such a trimming it; the sql call is one way; so is
var s=new String(Request. Form); s=s.toString() is not as it produces an
error on my server.

So the sql is not required here but as it seems MS is committed to VB like
scripts and data structures there may be some situation where it will be
the only way I can find.

Did you know ADO GetRows returns a VBArray type which must? be converted
with toArray when used in Jscript? In a non-MS technology I expect one
would have to something heroic to make this conversion, if GetRows is
available at all. To be safer I have been using GetString and Splits. The
resulting array may have a more untuitive structure; that is element [5][2]
corresponds to record 6 field 3 rather than record 3 field 6.

I digress ...
--
Lyle
--

[Dimitri Furman]

On Apr 03 2005, 05:49 pm, Lyle Fairfield <Lo******@FFDBA .Com> wrote in
news:Xn******** ***********@216 .221.81.119:

This is some trivial ASP-Javascript stuff.

a form

<form method="post" action="copy of classes.asp" >
<input type ="text" value =" Dimitri "
<input type="submit">
</form>

some asp-javascript in copy of classes.asp

var s=Request.Form;
Response.Write( '<p>'+typeof s+'</p>');
Response.Write( '<p>'+s.constru ctor+'</p>');
Response.Write( '<p>'+s+':'+s.l ength+'</p>');
s=c.Execute('SE LECT LTRIM(RTRIM(\'' +s+'\'))').Coll ect(0);
Response.Write( '<p>'+typeof s+'</p>');
Response.Write( '<p>'+s.constru ctor+'</p>');
Response.Write( '<p>'+s+':'+s.l ength+'</p>');

which yields:

object
undefined
:undefined
string
function String() { [native code] }
:0

it seems we have to convert Request.Form to a string before we can
deal with it in some ways, such a trimming it; the sql call is one
way; so is var s=new String(Request. Form); s=s.toString() is not as it
produces an error on my server.

So the sql is not required here but as it seems MS is committed to VB
like scripts and data structures there may be some situation where it
will be the only way I can find.

Not having any ASP or javascript, I can't be sure that I understand this
properly; I think I see your point, but cannot come up with a VB/VBA
scenario that would require this, other than the obvious one of using some
"foreign" functions not available in VB/VBA. Apparently this technique can
be useful in ASP-javascript - it may be also helpful if you show how this
can benefit VB/VBA code.

--
remove a 9 to reply by email