middletree said:
I can be convinced, really. I want to learn this stuff. But my
understanding of this is that the dynamic SQL statement, because it's
in ASP code, is interpreted, not compiled, which makes it slower.
That's only a minor advantage
So beyond that, what other advantages of using the compiled method are
there?
From a development standpoint, it's a real PITA to write dynamic sql code
compared to passing parameter values to a saved query. Look at all the time
you've wasted due to delimiter issues, literal quote issues, datatype
issues, now this reserved keyword issue ... All of these issues are avoided
when you pass parameters to a saved query/stored procedure.
Let's take your query for instance. Open your database in Access, click to
the Queries tab, and create a new query in Design View. Close the Choose
Table dialog without selecting a table. Switch to SQL View and paste in your
Insert query:
INSERT INTO Login(Name,Password,Pastor) VALUES ('Bob','whatever','y')
Replace the literal values with parameter placeholders:
INSERT INTO Login(Name,Password,Pastor)
VALUES ([P1],[P2],[P3])
You can use whatever parameter names you wish, as long as the names don't
match any of the field names in your table.
INSERT INTO Login(Name,Password,Pastor)
VALUES ([pName],[pPassword],[pPastor])
Run the query to test it. Access will prompt you for parameter values. When
you run this query from ASP, you will supply the values programmatically
(take note of the order in which Access prompts for the parameter values.
You will need to supply the values in the same order when you run it from
asp).
Anyways. supply values and verify that the query works. When it's doing what
you want it to do, same it. Give it a name such as qAddLogin.
Notice that I did not have to worry about datatypes or delimiters.
In ASP, create a connection object (cn) and simply do this to run the query:
cn.open
cn.qAddLogin "Bob","whatever","y"
What could be simpler?
If you have a SELECT query, simply do this to retrieve records from it:
cn.open
set rs=server.createobject("adodb.recordset")
cn.qAddLogin "Bob","whatever","y", rs
HTH,
Bob Barrows