A question about shared db connections

[William]

Hi and Thanks in advance for taking a look at my question.

I am using an "include" page with a sub to open a db connection for multiple
Recordsets. My problem is that in some cases I need to use set connection
parameters such as adOpenDynamic, adLockPessimistic, adCmdText.

Is there a way to add these to the "Open Data Connection Command" or to the
"Open Recordset Command"?

Examples Follow


**** Included File ******

<%
'******* Open Connection to MyData
Sub Opendata( DatCon )
Set DatCon = Server.CreateObject("ADODB.Connection")
DatCon.Open "DSN=data04;"
End Sub

'******* Close Connection to MyData
Sub CloseData( DatCon )
DatCon.Close
Set DatCon = NOTHING
End Sub

'******* Open Recordset from DatCon
Sub OpenRs( Sql, rs )
Set rs = DatCon.Execute(Sql)
End Sub

'******* Close Recordset from DatCon
Sub CloseRs( rs )
rs.Close
Set rs = NOTHING
End Sub

%>


********** Original Page info************

Sql = "SELECT * FROM p_CurrentUpdate WHERE CustId = " & StrCusId & " ORDER
BY Timestamp ASC"
OpenRs Sql, rs
StrCurrentUpdate = "True"
If rs.EOF Then
StrCurrentUpdate = "False"
Else
rs.MoveLast
StrCustUpdateLast = rs.Fields("Timestamp").Value
End If
CloseRs rs

*******************

When I execute this code I get [Microsoft][ODBC Microsoft Access Driver] Too
few parameters. Expected 1.

Now, all the Variables and Objects are "Dim" at the top of the page, and if
I don't use the ORDER BY parameter it will work, so I am pretty sure it is
because the proper parameters are not set for DatCon and record interaction.

Please give any assistance you can. Thank you

William

[Bob Barrows]

William wrote:

> Hi and Thanks in advance for taking a look at my question.
>
> I am using an "include" page with a sub to open a db connection for
> multiple Recordsets. My problem is that in some cases I need to use
> set connection parameters such as adOpenDynamic, adLockPessimistic,
> adCmdText.

Actually, these are recordset properties. If you set them at the Connection
lefvel, it uses the settings for the recordsets that are created
automatically.

>
> Is there a way to add these to the "Open Data Connection Command" or
> to the "Open Recordset Command"?

I would use them in the statement used to open the recordset. You need to
explicitly create the recordset object and use the recordset's Open method
to do this:

set rs = server.createobject("adodb.recordset")
rs.cursorlocation = adUseServer
'or adUseClient, however, if you use this, you will only get a static cursor
rs.cursortype = adOpenDynamic
rs.locktype = adLockPessimistic
'Seriously, avoid this setting in asp code! You will greatly restrict
'your application's scalability
rs.Open Sql,DatCon,,,adCmdText

However, I do question the need to use anything other than the default
forward-only cursor in asp code. Using these settings is more expensive,
since the cursor being built needs to do more things, making it take up more
space in memory, and making it take longer to retrieve data from the
database.

>
> Examples Follow
>
>
> **** Included File ******
>
> <%
> '******* Open Connection to MyData
> Sub Opendata( DatCon )
> Set DatCon = Server.CreateObject("ADODB.Connection")
> DatCon.Open "DSN=data04;"

Avoid ODBC. It is obsolete. There is native OLEDB provider for Jet databases
that offers more reliabilty and functionality than the old ODBC driver. See
[url]www.able-consulting.com/ado_conn.htm[/url] for examples of OLEDB connection
strings.

>

<snip>

> ********** Original Page info************
>
> Sql = "SELECT * FROM p_CurrentUpdate WHERE CustId = " & StrCusId & "

Avoid using "Select *" in production code. It forces ADO to make an extra
trip to the database to change the * into an actula list of columns. It is
best to explicitly name the columns you wish to return.

> ORDER BY Timestamp ASC"

<snip>

>
> When I execute this code I get [Microsoft][ODBC Microsoft Access
> Driver] Too few parameters. Expected 1.

This has nothing to do with your ADO syntax (or whether or not you are
setting recordset cursor properties). The problem here is that "Timestamp"
is a reserved keyword ([url]http://www.aspfaq.com/show.asp?id=2080[/url]). Your best
course of action is to change the name of that field to a non-reserved word.
If you cannot do that, then your only recourse is to always remember to
surround it with brackets when you use it in queries:

ORDER BY [Timestamp] ASC"

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"

[Aaron Bertrand - MVP]

> course of action is to change the name of that field to a non-reserved
word.

Hey, that's a column, not a field. Don't make me unleash Celko on you. :-)

[Bob Barrows]

Aaron Bertrand - MVP wrote:

>> course of action is to change the name of that field to a
>> non-reserved word.

>
> Hey, that's a column, not a field. Don't make me unleash Celko on
> you. :-)

Yikes! :-)
When talking "Access", I tend to revert to "field" rather than "column",
since that is how Access refers to them. I know, I know .. :-)

Bob

--
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"