custom ASP programming has clashed with Frontpage DRW pages with SQL INSERT and UPDATE

[Tim Thorp]

In a nutshell, things that used to work on my site have stopped working and
the error seems to have occurred since I called the msado15.dll file in a
custom ASP file.
A LITTLE MORE BACKGROUND INFO:
I created an ASP site in MS Frontpage 2003 with an Access database to store
and report on my departments training events, registrations and evaluations.
The site worked pretty well, but I wanted to take some functionality to a
higher level and found that I had exausted the capabilities of the Frontpage
Database Results Wizard, so I stared to read about creating ASP pages in
Beginning ASP 3.0 (WROX press).
WHEN THE PROBLEM AROSE:
I only have permission to make web sites at one web server, so I thought I
would integrate some custom ASP pages with my exiting database and Frontpage
generated ASP pages. In my experimenting, I used datastore.asp to connect to
the database and also called the metadata type="typelib" File="C:\Program
Files\...\msado15.dll". In the process of experimenting with the code, I
made some great progress in learning to use ASP and hope to continue
learning again soon.
WHAT IS THE PROBLEM:
After I was done with my custom ASP work, I needed to do some work on some
other pages in my site and started to receive error messages on pages that
used to work fine. Specifically on pages which have database results with
Custom SQL statements containing UPDATE or INSERT queries. Here are two
error messages: "Database Results Wizard Error
Unable to find operator in query string. Query string currently is INSERT
INTO registrations (fname, lname, email, phone, dept, platform, attend,
eventID, precourse_status) VALUES ('::fname::', '::lname::', '::email::',
':hone::', '::dept::', ':latform::', '::attend::', ::eventID::,
':recourse_status::')" and "Database Results Wizard Error
Your page contains a query with user input parameters that could not be
resolved.
This could happen if your DatabaseRegionStart webbot has an empty or missing
s-columnnames or s-columntypes attributes."

 

[Kevin Spencer]

I have no idea what you mean by "called the metadata type="typelib"
File="C:\Program Files\...\msado15.dll". However, this is likely the cause
of your problem. You should never refer to a specific DLL in your ASP/ADO
code. Let the Connection String and MDAC handle it for you. It sounds like
you went to a lot of trouble you didn't need to, and shouldn't have.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Jon]

Tim,
you're barking up the wrong tree here. I think what you want to do is leave
the FP wizard behind and code your own asp, correct?

If so for any database operation (select,insert,update,delete) you do the
same thing - create a connection to your database and then execute an sql
statement against that connection. So to start we create a connection
<%
set oConn = server.createobject("adodb.connection")
oConn.open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("/yourdb.mdb"))
%>
now we've got a connection we can execute some sql, eg
<%
' to insert a record
oConn.execute("insert into table(field) values('some text'))
' to update
oConn.execute("update table set field = 'some text')
' to return a recordset
set oRs = oConn.execute("select field from table")
%>

I think what's thrown you off is firstly the typelibrary - you should never
include this in your code and secondly trying to mix your asp with the FP
wizard. You're on the right track as far as forgetting the FP wizard but
don't try and mix the 2 - forget the wizard and "roll your own" asp

Jon
Microsoft MVP - FP

 

[Jim Buyens]

The FP2003 DRW definitely has issues running SQL
statements other than SELECT. So, as long as you're
writing ASP pages anyway, browse:

Saving Form Data in a Database
http://www.interlacken.com/winnt/tips/tipshow.aspx?tip=44

This explains how to do an INSERT, but UPDATE and DELETE
work the same.


For those who don't know, the following statment, added to
the global.asa file:

<!--METADATA TYPE="TypeLib"
NAME="ADO"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll"
UUID="00000200-0000-0010-8000-00AA006D2EA4" VERSION="2.0"
-->

summarily makes all ADO named constants available to all
ASP pages in the application. This frees the programmer
from having to SSI-include the adovbs.inc file in each
page, from defining constants by hand, or from hard-coding
magic numbers.

However, this obviously interferes with pages that already
include adovbs.inc or hand-coded constants. You get
duplicate name errors.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

-----Original Message-----
In a nutshell, things that used to work on my site have stopped working and
the error seems to have occurred since I called the msado15.dll file in a
custom ASP file.
A LITTLE MORE BACKGROUND INFO:
I created an ASP site in MS Frontpage 2003 with an Access database to store
and report on my departments training events, registrations and evaluations.
The site worked pretty well, but I wanted to take some functionality to a
higher level and found that I had exausted the capabilities of the Frontpage
Database Results Wizard, so I stared to read about creating ASP pages in
Beginning ASP 3.0 (WROX press).
WHEN THE PROBLEM AROSE:
I only have permission to make web sites at one web server, so I thought I
would integrate some custom ASP pages with my exiting database and Frontpage
generated ASP pages. In my experimenting, I used datastore.asp to connect to
the database and also called the metadata type="typelib" File="C:\Program
Files\...\msado15.dll". In the process of experimenting with the code, I
made some great progress in learning to use ASP and hope to continue
learning again soon.
WHAT IS THE PROBLEM:
After I was done with my custom ASP work, I needed to do some work on some
other pages in my site and started to receive error messages on pages that
used to work fine. Specifically on pages which have database results with
Custom SQL statements containing UPDATE or INSERT queries. Here are two
error messages: "Database Results Wizard Error
Unable to find operator in query string. Query string currently is INSERT
INTO registrations (fname, lname, email, phone, dept, platform, attend,
eventID, precourse_status) VALUES

('::fname::', '::lname::', '::email::',

 

[Tim Thorp]

Thanks Jon, Kevin & Jim! I have a few response questions:

Jon, is it OK to have custom asp pages and frontpage DRW pages in the same
site? or do I need to make another site?

Jim, you seem to know about the msado15.dll file. Is it possible that when I
called it on a specific page (not in the global.asa) that other pages in the
site are being confused by this msado15.dll? If so, can I turn off
msado15.dll? I must really sound like a newbie on this one, so pardon my
weird question. I was merely following the instructions on page 573 of WROX
Beginning ASP 3.0 and have had errors on my site since then.

Thanks for all the help!!! Soon I will be custom coding my own asp pages.

Tim

 

[Kevin Spencer]

Both Jim and I told you that you shouldn' be referencing any DLL in your
database connection code. You should use a Connection String to connect to
your database. You might want to take a look at some of the articles and
interactive tutorials on my web site to see how it should be used:
http://www.takempis.com. Here's an example of a Database Connection using
ADO:

<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open Application("ConnectionString")
q = "SELECT * FROM mytable WHERE myfield LIKE 'my goodness!'"
Set rs = cn.Execute(q)

' Do something with the result set

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
%>

It is certainly fine to have both FrontPage DRW pages and your own cuatom
ASP pages on the same site. The FrontPage DRW pages uses an
automatically-generated Connection String in the global.asa file. You can
also store your Connection String there, or, better yet, use the one that
FrontPage generated, if you're connecting to the same Data Source.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Tim Thorp]

I will certainly not be referencing any dlls on my web pages going forward,
but I still need to resolve behavior changes to my current pages from when I
did call it in the past.

Is it possible that the server is "remembering" that I called the dll and
still using it in my current pages?

Thanks,

Tim

 

[Kevin Spencer]

Is it possible that the server is "remembering" that I called the dll and

still using it in my current pages?

I've never heard of any such thing, and I've used ASP since it was first
released. I think I would check my code. YOu might have missed something.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.