Parameteirsed queries (again)

Parameteirsed queries (again)

Post by John Smal » Wed, 11 Feb 1998 04:00:00



OK So the first question scrolled of the attention span of the news group
so here it is in more detail.

I trying to get parameterised queries to work and using the sample code
from the DAO 3.5 help screens I've modified it to get a weeny bit closer to
what I want but cannot for the life of me get it to work.  Any reference to
the parameter of the query causes ODBC Error 3146.

All help gratefully received.

The (plagiarized) code is as follows

  Public wrkODBC As Workspace  ' All declared public as other procs will
need to see them
    Public Tamaraw As Connection
    Public conLoop As Connection
    Public qdfCategoryTotals As QueryDef
    Public prmCategory As Parameter
    Public prmBegin As Parameter
    Public prmEnd As Parameter
    Public prpLoop As Property

Sub ConnectToTamaraw() ' the name of my DB but in this code SQL server
"pubs"

    ' Create ODBCDirect Workspace object and open Connection

' objects.
    Set Module1.wrkODBC = CreateWorkspace("NewODBCWorkspace", _
        "admin", "", dbUseODBC)
   Set Module1.Tamaraw = Module1.wrkODBC.OpenConnection("Connection1", , ,
_
 "ODBC;DSN=pubs;UID=sa;PWD=")

    Debug.Print "Database properties:" ' Just to verify that we are getting
somewhere

    ' Enumerate the Connections collection.
    For Each conLoop In wrkODBC.Connections
        Debug.Print "Connection properties for " & _
            conLoop.Name & ":"

        With conLoop
            ' Print property values by explicitly calling each
            ' Property object; the Connection object does not
            ' support a Properties collection.

Debug.Print "    Connect = " & .Connect
            ' Property actually returns a Database object.
            Debug.Print "    Database[.Name] = " & _
                .Database.Name
            Debug.Print "    Name = " & .Name
            Debug.Print "    QueryTimeout = " & .QueryTimeout
            Debug.Print "    RecordsAffected = " & _
                .RecordsAffected
            Debug.Print "    StillExecuting = " & _
                .StillExecuting
            Debug.Print "    Transactions = " & .Transactions
            Debug.Print "    Updatable = " & .Updatable

End With
 Next conLoop

' No problems to this point and no problems with the query below if it is
' not parameterised

Set Module1.qdfCategoryTotals = Module1.Tamaraw.CreateQueryDef("Authors", _
    "PARAMETERS prmName text; " & _
    "SELECT authors.au_id, authors.au_lname, authors.au_fname,
authors.phone, authors.address, authors.city, authors.state, authors.zip,
authors.contract" & Chr(13) & "" & Chr(10) & "FROM pubs.dbo.authors
authors" & _
    " where (authors.au_fname Like [prmName]) ")
  Module1.qdfCategoryTotals!prmName = "S*" <<<<<BLOWS OUT HERE WITH ERROR
3146
' Set prmBegin = qdfCategoryTotals.Parameters!dteBegin  <<< commented out
earlier tries which also blow out
' Set prmEnd = qdfCategoryTotals.Parameters!d*d <<< commented out
earlier tries
End Sub

--

 
 
 

1. Licensing Again Again

I know this question has been up a million times, but it seems all answers
are different:

Scenario:

Single website running on

Win 2000 Server running IIS 5.0 with SQL 2000 Standard as backend. Sql 2000
is used to store website content on + keep statistics on users.
In theory there is only one authenticated user in the sense that all
WebPages are using the same username/password to connect to SQL 2000. So a 5
CAL should suffice.
However most answers leans towards CPU licensing. Personally I don't see why
a 5 CAL wouldn't be legal. For Windows 2000 Server you can settle for a 5
CAL even though you use it as a webserver with 1000 of daily users, as long
as they are anonymous logons (IUSR account)

I don't seem to be the only one sharing that view. I found the following
post:


Posted of what appears to be a Microsoft SQL Server Support employee

What is right and what is wrong ?

2. SQL*Loader Error

3. Connection Again and Again

4. Difference

5. multithreading, again and again!

6. Macro-substitution in PDOXW4.5 how ?

7. Synonyms - again (Graeme hits again!)

8. business hours

9. open ADO cnt again and again

10. multithreading again and again!

11. Query Help Again ...

12. SQL Query: Let me try this again

13. sql query again