Problems updating database using Results wizard

randyQ · Dec 28, 2004

I am using Frontpage to maintain an online Inventory database for my company. I can add and view entries, but I can't make changes. Here is what I did.

Initial page lists all records, and uses the "autonumber" as a link to pull up an individual record.

When clicking on an autonumber, the individual record shows up in a new form, which is populated with the existing data. I am then supposed to be able to change or remove data, and hit submit.

Hitting submit "posts" the data to a forms results wizard which has a custom SQL script for updating. It then takes you back to the list of records.

Well, hitting submit brings up a Frontpage error: "Database Results Wizard Error"

Keep in mind, that I am just trying to get it all working first, before I make it truly functional. Posted below is the code used in the results wizard.

--------
--------
--------

<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do not Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Execute Tech Update</title>
<style>

</style>
</head>

<body>
<table width="100%" border="0"><tr>
<td width="50%" align="center"><a href="TechAddNewRecord.asp">Tech Add Record</a></td>
<td width="50%" align="center"><a href="TechViewAllRecords.asp">Tech View Records</a></td>
</tr></table>

<!--webbot bot="DatabaseRegionStart" s-columnnames s-columntypes s-dataconnection="TestInventory" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="FALSE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns s-criteria s-order s-sql="UPDATE Tech SET Bldg='::Bldg::', Room='::Room::', User='::User::', UserGroup='::UserGroup::', PCMake=':

CMake::', PCModel=':

CModel::', PCSerial=':

CSerial::', MonitorMake='::MonitorMake::', MonitorModel='::MonitorModel::', MonitorType='::MonitorType::', MonitorSize='::MonitorSize::', MonitorSerial='::MonitorSerial::', OS='::OS::', CPU='::CPU::', Speed='::Speed::', FSB='::FSB::', RAM='::RAM::', Dimms=':

imms::', HDD='::HDD::', Optical='::Optical::', NIC='::NIC::', Networked='::Networked::', DatePurchased=':

atePurchased::', WarrantyYears='::WarrantyYears::', WarrantyEnd='::WarrantyEnd::', RepairNotes='::RepairNotes::' WHERE ID=::ID::" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="Bldg=&amp;Room=&amp;User=&amp;UserGroup=
&amp;PCMake=&amp;PCModel=&amp;PCSerial=&amp;MonitorMake=
&amp;MonitorModel=&amp;MonitorType=&amp;MonitorSize=
&amp;MonitorSerial=&amp;OS=&amp;CPU=&amp;Speed=
&amp;FSB=&amp;RAM=&amp;Dimms=&amp;HDD=&amp;Optical=
&amp;NIC=&amp;Networked=&amp;DatePurchased=
&amp;WarrantyYears=&amp;WarrantyEnd=&amp;RepairNotes=
&amp;ID=" s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="0" botid="0" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00"><font color="#000000">This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr></table>" b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes b-WasTableFormat="FALSE" startspan -->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="UPDATE Tech SET Bldg='::Bldg::', Room='::Room::', User='::User::', UserGroup='::UserGroup::', PCMake=':

CMake::', PCModel=':

CModel::', PCSerial=':

CSerial::', MonitorMake='::MonitorMake::', MonitorModel='::MonitorModel::', MonitorType='::MonitorType::', MonitorSize='::MonitorSize::', MonitorSerial='::MonitorSerial::', OS='::OS::', CPU='::CPU::', Speed='::Speed::', FSB='::FSB::', RAM='::RAM::', Dimms=':

imms::', HDD='::HDD::', Optical='::Optical::', NIC='::NIC::', Networked='::Networked::', DatePurchased=':

atePurchased::', WarrantyYears='::WarrantyYears::', WarrantyEnd='::WarrantyEnd::', RepairNotes='::RepairNotes::' WHERE ID=::ID::"
fp_sDefault="Bldg=&Room=&User=&UserGroup=&PCMake=&PCModel=&PCSerial=
&MonitorMake=&MonitorModel=&MonitorType=&MonitorSize=&MonitorSerial=&OS=
&CPU=&Speed=&FSB=&RAM=&Dimms=&HDD=&Optical=&NIC=&Networked=&DatePurchased=
&WarrantyYears=&WarrantyEnd=&RepairNotes=&ID="
fp_sNoRecords="No records returned."
fp_sDataConn="TestInventory"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>


</body></html>

xwb · Jul 17, 2005

Could you give the full text of the error. It will look something like

Code:

# Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Text Driver] The Microsoft Jet database engine could not find the object 'data.txt'. Make sure the object exists and that you spell its name and the path name correctly.
/texttest/textdsn.vbs, line 48

It will normally tell you the line where the error has occurred. May be something like it needs global.asa but we can't tell until we know what the DRW is complaining about.

randyQ · Jul 18, 2005

Code:

[b]Database Results Wizard Error[/b]
The operation failed. If this continues, please contact your server administrator.

Also, here is my updated SQL statement:

Code:

<%
fp_sQry="UPDATE Tech SET Bldg='::Bldg::', Room='::Room::', User=
'::User::', UserGroup='::UserGroup::', PCMake='::PCMake::', PCModel='::PCModel::', PCSerial='::PCSerial::', MonitorMake='::MonitorMake::', MonitorModel='::MonitorModel::', MonitorType='::MonitorType::', MonitorSize='::MonitorSize::', MonitorSerial='::MonitorSerial::', OS='::OS::', CPU='::CPU::', Speed='::Speed::', FSB='::FSB::', RAM='::RAM::', Dimms='::Dimms::', HDD='::HDD::', Optical='::Optical::', NIC='::NIC::', Networked='::Networked::', DatePurchased='::DatePurchased::', WarrantyYears='::WarrantyYears::', WarrantyEnd='::WarrantyEnd::', RepairNotes='::RepairNotes::' WHERE [u]ID='::ID::'"[/u]
fp_sDefault="Bldg=&Room=&User=&UserGroup=&PCMake=&PCModel=&PCSerial=
&MonitorMake=&MonitorModel=&MonitorType=&MonitorSize=&MonitorSerial=&OS=
&CPU=&Speed=&FSB=&RAM=&Dimms=&HDD=&Optical=&NIC=&Networked=&DatePurchased=
&WarrantyYears=&WarrantyEnd=&RepairNotes=&ID="
fp_sNoRecords="No records returned."
fp_sDataConn="TestInventory"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

randyQ · Jul 18, 2005

Also, I don't think I mentioned that adding new entries works just fine. The only time I get the error message posted above is when I open an individual record and click the submit changes button, which runs the SQL script. I am thinking that I should probably stop being lazy, and just sit down with a good ASP book and learn to write the full scripts myself, instead of using FP web bots. Any recommendations for books that would help me learn this stuff? Anything with lots of hands-on, and explaination for all of the commands and why they are used.

Thanks so much!

randyQ · Jul 19, 2005

Ok, I did notice that in the same database, I had another table that used the same field names as the one that I want to update. I changed the names, so that the two tables do not share the same field names, and I updated my database to 2002-2003 version (yes I backed up my 2000 version just in case).

I am not quite sure what I would look for when trying to find my "unique index field". I have not set up any queries or anything that is linking the two tables together, and my "primary key" fields have different names. In my SQL statement, I am not updating the ID field, just using it as the WHERE key.

Q1) In my code insert above, the line that has the following:
fp_sDefault="Bldg=&Room=....&WarrantyEnd=&RepairNotes=&ID="
Should the '&ID=' be there?

Q2) Could it be an issue with the format of a particular field in the table (i.e. set the Room field to number format, but I typed a text entry by accicent)?

randyQ · Jul 19, 2005

Ok, I think I might have found something. While going through the FrontPage help system, I stumbled across something that said that if I am using the "Use Existing database connection" option in my query on a FrontPage server using SharePoint services, that it won't work.

I did find out from that help file that there is a database interface wizard, which removes my ignorance from the equation. I ran that to create new interface pages, and it still didn't work, so I think I will have to remove SharePoint from my server.

Once I do remove SharePoint, I will post back with my findings. Hopefully, it will be a success story.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Problems updating database using Results wizard

randyQ

IS-IT--Management

xwb

Programmer

randyQ

IS-IT--Management

randyQ

IS-IT--Management

randyQ

IS-IT--Management

randyQ

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor