Definite difference between using OLEDB and ODBC as the driver

[Laphan]

Hi All

Some of you may not remember (or even care for that matter ;0)), but I
mentioned in a previous post that I had difficulty using the OLEDB for
Access connection over the ODBC one because there were gremlins lurking to
cock-up my code.

Well I've managed to source 2 of these problems and would appreciate it if
you could confirm why they are doing the following and more importantly how
to correct them:

set oCmd=Server.CreateObject("ADODB.Command")
set oCmd.ActiveConnection = oConn
oCmd.CommandText = "Admin_GetProdList()"
oCmd.CommandType = 4
set oRSv=Server.CreateObject("ADODB.recordset")
oRSv.CursorLocation = 3
oRSv.open oCmd

If I leave in the brackets () after Admin_GetProdList, OLEDB reports an
error, but ODBC works fine. If I remove the brackets OLEDB works and ODBC
still works. Not a calamity this one, but why is it doing this?

Set oCmd=Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "Admin_GetSpecificProduct('" & strProdID & "')"
oCmd.CommandType = 4
'Response.Write oCmd.CommandText
Set oRSv=Server.CreateObject("ADODB.recordset")
oRSv.open oCmd

This one is more important as I can't use my stored procs with OLEDB.

At present, the above generates the below error on OLEDB, but is fine on
ODBC:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in parameters clause. Make sure the parameter exists and
that you typed its value correctly.
/asp/details.asp, line 698

I thought that the ADO coding was exactly the same irrespective of the
connection method, but the above appears to be of the contrary?

Rgds

Laphan

[Bob Barrows]

Laphan wrote:

> Hi All
>
> Some of you may not remember (or even care for that matter ;0)), but I
> mentioned in a previous post that I had difficulty using the OLEDB for
> Access connection over the ODBC one because there were gremlins
> lurking to cock-up my code.
>
> Well I've managed to source 2 of these problems and would appreciate
> it if you could confirm why they are doing the following and more
> importantly how to correct them:
>
> set oCmd=Server.CreateObject("ADODB.Command")
> set oCmd.ActiveConnection = oConn
> oCmd.CommandText = "Admin_GetProdList()"

Why the parentheses? I've never used them for any database ...

> oCmd.CommandType = 4
> set oRSv=Server.CreateObject("ADODB.recordset")
> oRSv.CursorLocation = 3
> oRSv.open oCmd
>
> If I leave in the brackets () after Admin_GetProdList, OLEDB reports
> an error, but ODBC works fine. If I remove the brackets OLEDB works
> and ODBC still works. Not a calamity this one, but why is it doing
> this?

Not sure about this. Why put the parentheses there in the first place?

>
> Set oCmd=Server.CreateObject("ADODB.Command")
> Set oCmd.ActiveConnection = oConn
> oCmd.CommandText = "Admin_GetSpecificProduct('" & strProdID & "')"
> oCmd.CommandType = 4
> 'Response.Write oCmd.CommandText
> Set oRSv=Server.CreateObject("ADODB.recordset")
> oRSv.open oCmd
>
> This one is more important as I can't use my stored procs with OLEDB.
>
> At present, the above generates the below error on OLEDB, but is fine
> on ODBC:

Really? This works with ODBC? It shouldn't - you've set the CommandType to 4
(adCmdStoredProc), so it should not be recognizing parameters in the
CommandText. I'll accept your word that it works, I'm just surprised that it
does. This is not how I would choose to run a stored procedure.

>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in parameters clause. Make sure the parameter exists and
> that you typed its value correctly.
> /asp/details.asp, line 698
>
> I thought that the ADO coding was exactly the same irrespective of the
> connection method, but the above appears to be of the contrary?

Nobody said that. I may have mentioned that the values/constants used to set
properties of ADO objects do not vary depending on the connection library
being used. That is still true. In both cases you are setting the
CommandText property to a string. What is different is how each provider is
handling that string. The CommandText is being sent off to the ODBC or OLEDB
library, each of which has its own requirements. It's not a big deal. The
best way to execute a saved query/stored procedure is to use the
"procedure-as-native-connection-method" technique. Here is how it would work
with the above query (you do not need a command object):

Set oRSv=Server.CreateObject("ADODB.recordset")
oConn.Admin_GetSpecificProduct strProdID, oRSv

If your query does not return records, then don't use the recordset object:
oConn.ActionQuery parm1,parm2, ..., parmN

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Astra]

Bob

Thanks for your reply, but I'm more confused now.

I've always done Connection code, then Command code and then Recordset code
(as outlined by Wrox Press), because this seemed to be the neatest and more
flexible way. I can reuse the same Command object for several transactions
and then use it against my Recordset object several times without creating
any more than 1 Command Object and 1 Recordset Object.

Calling a non-parameter SProc with the parentheses is no issue to me, I will
leave them off, but performing 'parameter-laden' SProcs in the way you have
described has bamboozled me.

I'm also confused that you are surprised that the parameters work when I set
the CommandType to 4. I was of the understanding that 4 is for SProcs and I
simply add the parameters that I want to send to this SProc as per my
example.

As I'd like to keep the method I currently use, do I take it that my line
should be as follows:

oCmd.CommandText = Admin_GetSpecificProduct strProdID

Thanks for your assistance and patience.

Rgds

Robbie

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eb41$Hb9DHA.3176@TK2MSFTNGP11.phx.gbl...
Laphan wrote:

> Hi All
>
> Some of you may not remember (or even care for that matter ;0)), but I
> mentioned in a previous post that I had difficulty using the OLEDB for
> Access connection over the ODBC one because there were gremlins
> lurking to cock-up my code.
>
> Well I've managed to source 2 of these problems and would appreciate
> it if you could confirm why they are doing the following and more
> importantly how to correct them:
>
> set oCmd=Server.CreateObject("ADODB.Command")
> set oCmd.ActiveConnection = oConn
> oCmd.CommandText = "Admin_GetProdList()"

Why the parentheses? I've never used them for any database ...

> oCmd.CommandType = 4
> set oRSv=Server.CreateObject("ADODB.recordset")
> oRSv.CursorLocation = 3
> oRSv.open oCmd
>
> If I leave in the brackets () after Admin_GetProdList, OLEDB reports
> an error, but ODBC works fine. If I remove the brackets OLEDB works
> and ODBC still works. Not a calamity this one, but why is it doing
> this?

Not sure about this. Why put the parentheses there in the first place?

>
> Set oCmd=Server.CreateObject("ADODB.Command")
> Set oCmd.ActiveConnection = oConn
> oCmd.CommandText = "Admin_GetSpecificProduct('" & strProdID & "')"
> oCmd.CommandType = 4
> 'Response.Write oCmd.CommandText
> Set oRSv=Server.CreateObject("ADODB.recordset")
> oRSv.open oCmd
>
> This one is more important as I can't use my stored procs with OLEDB.
>
> At present, the above generates the below error on OLEDB, but is fine
> on ODBC:

Really? This works with ODBC? It shouldn't - you've set the CommandType to 4
(adCmdStoredProc), so it should not be recognizing parameters in the
CommandText. I'll accept your word that it works, I'm just surprised that it
does. This is not how I would choose to run a stored procedure.

>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in parameters clause. Make sure the parameter exists and
> that you typed its value correctly.
> /asp/details.asp, line 698
>
> I thought that the ADO coding was exactly the same irrespective of the
> connection method, but the above appears to be of the contrary?

Nobody said that. I may have mentioned that the values/constants used to set
properties of ADO objects do not vary depending on the connection library
being used. That is still true. In both cases you are setting the
CommandText property to a string. What is different is how each provider is
handling that string. The CommandText is being sent off to the ODBC or OLEDB
library, each of which has its own requirements. It's not a big deal. The
best way to execute a saved query/stored procedure is to use the
"procedure-as-native-connection-method" technique. Here is how it would work
with the above query (you do not need a command object):

Set oRSv=Server.CreateObject("ADODB.recordset")
oConn.Admin_GetSpecificProduct strProdID, oRSv

If your query does not return records, then don't use the recordset object:
oConn.ActionQuery parm1,parm2, ..., parmN

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows]

Astra wrote:

> Bob
>
> Thanks for your reply, but I'm more confused now.
>
> I've always done Connection code, then Command code and then
> Recordset code (as outlined by Wrox Press),

The Wrox books are good, but not complete. and they don't alway show the
most efficient ways to do things.

> because this seemed to be
> the neatest and more flexible way. I can reuse the same Command
> object for several transactions and then use it against my Recordset
> object several times without creating any more than 1 Command Object
> and 1 Recordset Object.

Well, now you see a simpler method. No reason to be confused. There are
several ways to do most tasks in ADO. Instead of creating a command object,
use the same connection and recordset objects over and over. Much cleaner:

set cn=createobject ...
set rs=createobject ...
parm=<some value>
cn.sproc parm, rs
'process the results
rs.close
parm=<some new value>
cn.sproc parm, rs

ADO creates a command object behind the scenes when you do this.

>
> Calling a non-parameter SProc with the parentheses is no issue to me,
> I will leave them off, but performing 'parameter-laden' SProcs in the
> way you have described has bamboozled me.

That your technique works has bamboozled me. For some reason, ADO is
ignoring the fact that you set the commandtype to 4. In your technique you
are creating a dynamic sql statement (any time you use "&" to create a
statement, you are creating a dynamic sql statement, even when part of the
statement involves running a stored procedure) and executing it, so your
commandtype should be 1 (adCmdText). For some reason, ADO is ignoring your
commandtype setting when using the ODBC driver ... I wonder if that's
because your ODBC driver is a Jet 3.51 driver. Jet 3.51 did not support
stored procedures.

By using dynamic sql to execute your stored procedure, you are losing two of
the benefits of using a stored procedure:

1. strong data typing of your parameter values - this can prevent errors and
allows data to be passed across the network and cross process boundaries
more efficiently

2. Not an issue with Access, but with SQL Server: protection against sql
injection ([url]http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23[/url])

>
> I'm also confused that you are surprised that the parameters work
> when I set the CommandType to 4. I was of the understanding that 4
> is for SProcs and I simply add the parameters that I want to send to
> this SProc as per my example.

No. You are supposed to use the Parameters collection to pass parameters to
a stored procedure when you set the commandtype to 4. See my reply in the
other thread for an example.

>
> As I'd like to keep the method I currently use, do I take it that my
> line should be as follows:
>
> oCmd.CommandText = Admin_GetSpecificProduct strProdID
>

No, that would not work. You need to supply a string containing simply the
name of the procedure to CommandText when you set the CommandType to 4.
Since you did not put quotes around the above text, you would get an error.
I gave an example of the correct way to pass parameters via the Parameters
collection in the other thread.

With Access, however, I still suggest eschewing the Command object and use
the "procedure-as-cnnection-method" technique.

The only time I advise explicitly creating a Command object is when your SQL
Server stored procedure has output parameters or you need to read the value
of the Return parameter.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Laphan]

Geees Bob, have you been on something today!!!

Replies like lightning!!

Much appreciated though.

This info has been really insightful and I'll try and keep to these
guidelines.

What's eschewing mean anyway??

Rgds

Laphan


Bob Barrows <reb01501@NOyahoo.SPAMcom> wrote in message
news:eIddC6h9DHA.440@TK2MSFTNGP10.phx.gbl...
Astra wrote:

> Bob
>
> Thanks for your reply, but I'm more confused now.
>
> I've always done Connection code, then Command code and then
> Recordset code (as outlined by Wrox Press),

The Wrox books are good, but not complete. and they don't alway show the
most efficient ways to do things.

> because this seemed to be
> the neatest and more flexible way. I can reuse the same Command
> object for several transactions and then use it against my Recordset
> object several times without creating any more than 1 Command Object
> and 1 Recordset Object.

Well, now you see a simpler method. No reason to be confused. There are
several ways to do most tasks in ADO. Instead of creating a command object,
use the same connection and recordset objects over and over. Much cleaner:

set cn=createobject ...
set rs=createobject ...
parm=<some value>
cn.sproc parm, rs
'process the results
rs.close
parm=<some new value>
cn.sproc parm, rs

ADO creates a command object behind the scenes when you do this.

>
> Calling a non-parameter SProc with the parentheses is no issue to me,
> I will leave them off, but performing 'parameter-laden' SProcs in the
> way you have described has bamboozled me.

That your technique works has bamboozled me. For some reason, ADO is
ignoring the fact that you set the commandtype to 4. In your technique you
are creating a dynamic sql statement (any time you use "&" to create a
statement, you are creating a dynamic sql statement, even when part of the
statement involves running a stored procedure) and executing it, so your
commandtype should be 1 (adCmdText). For some reason, ADO is ignoring your
commandtype setting when using the ODBC driver ... I wonder if that's
because your ODBC driver is a Jet 3.51 driver. Jet 3.51 did not support
stored procedures.

By using dynamic sql to execute your stored procedure, you are losing two of
the benefits of using a stored procedure:

1. strong data typing of your parameter values - this can prevent errors and
allows data to be passed across the network and cross process boundaries
more efficiently

2. Not an issue with Access, but with SQL Server: protection against sql
injection ([url]http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23[/url])

>
> I'm also confused that you are surprised that the parameters work
> when I set the CommandType to 4. I was of the understanding that 4
> is for SProcs and I simply add the parameters that I want to send to
> this SProc as per my example.

No. You are supposed to use the Parameters collection to pass parameters to
a stored procedure when you set the commandtype to 4. See my reply in the
other thread for an example.

>
> As I'd like to keep the method I currently use, do I take it that my
> line should be as follows:
>
> oCmd.CommandText = Admin_GetSpecificProduct strProdID
>

No, that would not work. You need to supply a string containing simply the
name of the procedure to CommandText when you set the CommandType to 4.
Since you did not put quotes around the above text, you would get an error.
I gave an example of the correct way to pass parameters via the Parameters
collection in the other thread.

With Access, however, I still suggest eschewing the Command object and use
the "procedure-as-cnnection-method" technique.

The only time I advise explicitly creating a Command object is when your SQL
Server stored procedure has output parameters or you need to read the value
of the Return parameter.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Laphan wrote:

> Geees Bob, have you been on something today!!!
>
> Replies like lightning!!
>
> Much appreciated though.
>
> This info has been really insightful and I'll try and keep to these
> guidelines.
>
> What's eschewing mean anyway??
>

:-) Oops. Forgot you weren't a native English speaker ...

Eschewing = Avoiding, as in, not using.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.