This might be somewhat orthogonal to the question being asked here. But in

recent days, I've seen a lot of queries relating to the constructon of

specific SQL statements and the specification of the auspicious combination

of ADO parameters that makes the whole thing click. I struggled with some of

these things at first. First some disclaimers: I started life in the ASP

world only in October. So I am not a seasoned ASP programmer, although I am

a sophisticated OO programmer. I work primarily with IBM DB/2 and not with

Access or SQL Server. I wanted to briefly share the set of things that work

for me:



0. I also, while in learning mode (I still am in that mode actually), ran

into most of the problems people have posted here. With help from the list

and with lots of trial and error, I've come up with a set of practices that

work for me.



1. For the specific app I am working on, I've to deal a lot with executing

various SQL queries, displaying result sets, sometimes in paged fashion. I

also have to delete, update, insert in addition to just SELECT. Sometimes

I've to construct complex queries whose syntax I couldn't possibly construct

from memory.



2. I've concluded mixing DB/2 artifacts with ASP artifacts is NOT a good

thing. Besides performance, there are other things which sometimes prevent

the query from being executed properly or the query executes, but the

fetching of results via the result set is not proper etc.



3. I no longer have *any* SQL artifacts in my ASP code. All my SQL queries

are now constructed as stored procedures and reside in the DB2 environment.

I compose the queries using the appropriate wizards so that my queries are

always syntactically correct. This includes complicated JOINs, conditional

clauses, order by etc. I also test my sp immediately in the DB2 environment

using the appropriate db tools.



4. Once I am happy with the SQL artifacts, I shift to the ASP world and then

trigger the sp from ASP using ADODB.Command. I struggled with some issues

here, but they were the usual teething troubles.



5. With respect to displaying records in a paged fashion, I saw examples in

the Pro ASP Data Access book and also in various ASP-supported websites.

They all worked. But they all involved lots of code. They did all work and

in many cases, I didn't even have to grok the code. I decided I didn't want

to maintain the code and found a really cool ASP component called ASP Grid

which does a lot of things related to record set display which I like. It

does hell of a lot more than simply paged display and thus far I am very

happy with it. (Disclaimer: I've no affiliation with ASP Grid other than as

a satisfied user.)



6. In a month or so, when I've gotten the first version of my app working, I

plan to freeze it and then migrate it to ASP.Net. For a variety of reasons,

not the least of which is that ASP.Net is alleged to provide a more

productive and mature software engineering platform. At that time, I will

like jettison ASP Grid with .Net's inbuilt DataGrid server-side control.

However, I plan to preserve all my SQL investments via stored procedures.

Unchanged.



7. Summary: For composing SQL statements, I've eschewed the very awkward

process of concatenating unwieldy strings that results in less than

photogenic ASP code. As time passes on, the SQL code will get more complex

and I would like to deal with it in a habitat that is more natural to it.

And that, in my view, is the specific database environment world -- that

typically includes a wealth of tools such as the Command Center, Stored

Procedure Builder, XML Extender etc. For the moment, I care only about DB/2,

but if I had to deal with Oracle or SQL Server, very likely I would adopt

the same approach. Thanks for listening.



--sridhar





----- Original Message -----

From: "Ken Schaefer" <ken@a...>

To: "ASP Databases" <asp_databases@p...>

Sent: Sunday, December 09, 2001 8:10 PM

Subject: [asp_databases] Re: HELP - - what's wrong with my code?





> : strSQL = "UPDATE Students SET Password = '" & Request.Form("txtnewpwd")

> : & "' WHERE StudentID = & Session("StudentID")

>

> Look at the bit after StudentID 

> You don't have a " before the &

>

> Cheers

> Ken

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "tsivas" <tsivas@o...>

> Subject: [asp_databases] HELP - - what's wrong with my code?

>

>

> : Hi all,

> : I have a problem that has to do with update statement.Could anybody help

> : me? I'm using an Access database with OLE-DB Connection.

> : I have created a form with 3 text fields that a user fills with the old

> : password,the new password and the confirmation of the new password.

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>

>




$subst('Email.Unsub').

>