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
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
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.
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.
----- 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 &
> 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.