Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Changing a web page update (Access 2000 Database to SQL Server 2000) using a Database Results Region using FrontPage 2002

Posted on 2006-03-24
9
Medium Priority
?
231 Views
Last Modified: 2013-12-24
I am using FrontPage 2002, initial page queries and returns results into a Form (searchbyID.asp) with a Database Results region, Form posts to results.asp which has a Database Results region with the following Custom Query:
UPDATE Results
SET System_Name = '::System_Name::', SystemManager_POC = '::SystemManager_POC::',  Date = '::Date::',
Name_Who_Completed = '::Name_Who_Completed::', epaID = '::epaID::', SystemDescription = '::SystemDescription::', Name_Internal_Depend_1 = '::Name_Internal_Depend_1::', Position_Internal_Depend_1 = '::Position_Internal_Depend_1::', Internal_Depend_C1 = '::Internal_Depend_C1::', Name_Internal_Depend_2 = '::Name_Internal_Depend_2::', Position_Internal_Depend_2 = '::Position_Internal_Depend_2::', Internal_Depend_C2 = '::Internal_Depend_C2::', Name_Internal_Depend_3 = '::Name_Internal_Depend_3::', Position_Internal_Depend_3 = '::Position_Internal_Depend_3::', Internal_Depend_C3 = '::Internal_Depend_C3::', Name_Internal_Depend_4 = '::Name_Internal_Depend_4::', Position_Internal_Depend_4 = '::Position_Internal_Depend_4::', Internal_Depend_C4 = '::Internal_Depend_C4::', Name_Internal_support_1 = '::Name_Internal_support_1::', Position_Internal_Support_1 = '::Position_Internal_Support_1::', Internal_support_C1 = '::Internal_support_C1::', Name_Internal_support_2 = '::Name_Internal_support_2::', Position_Internal_Support_2 = '::Position_Internal_Support_2::', Internal_support_C2 = '::Internal_support_C2::', Name_Internal_support_3 = '::Name_Internal_support_3::', Position_Internal_Support_3 = '::Position_Internal_Support_3::', Internal_support_C3 = '::Internal_support_C3::', Name_Internal_support_4 = '::Name_Internal_support_4::', Position_Internal_Support_4 = '::Position_Internal_Support_4::', Internal_support_C4 = '::Internal_support_C4::', Name_External_Depend_1 = '::Name_External_Depend_1::', Position_External_Depend_1 = '::Position_External_Depend_1::', External_Depend_C1 = '::External_Depend_C1::', Name_External_Depend_2 = '::Name_External_Depend_2::', Position_External_Depend_2 = '::Position_External_Depend_2::', External_Depend_C2 = '::External_Depend_C2::', Name_External_Depend_3 = '::Name_External_Depend_3::', Position_External_Depend_3 = '::Position_External_Depend_3::', External_Depend_C3 = '::External_Depend_C3::', Name_External_Depend_4 = '::Name_External_Depend_4::', Position_External_Depend_4 = '::Position_External_Depend_4::', External_Depend_C4 = '::External_Depend_C4::', Name_External_Support_1 = '::Name_External_Support_1::', Position_External_Support_1 = '::Position_External_Support_1::', External_Support_C1 = '::External_Support_C1::', Name_External_Support_2 = '::Name_External_Support_2::', Position_External_Support_2 = '::Position_External_Support_2::', External_Support_C2 = '::External_Support_C2::', Name_External_Support_3 = '::Name_External_Support_3::', Position_External_Support_3 = '::Position_External_Support_3::', External_Support_C3 = '::External_Support_C3::', Name_External_Support_4 = '::Name_External_Support_4::', Position_External_Support_4 = '::Position_External_Support_4::', External_Support_C4 = '::External_Support_C4::', Criticality_Analysis_R1 = '::Criticality_Analysis_R1::', dtmDeadline_Due = '::dtmDeadline_Due::', Criticality_Analysis_C1 = '::Criticality_Analysis_C1::', Criticality_Analysis_T1 = '::Criticality_Analysis_T1::', Systems_Linked_Import = '::Systems_Linked_Import::', Systems_Linked_Export = '::Systems_Linked_Export::', Criticality_Analysis_Internal_Entities = '::Criticality_Analysis_Internal_Entities::', Criticality_Analysis_External_Entities = '::Criticality_Analysis_External_Entities::', Manual_Function = '::Manual_Function::', Manual_Function_Initiate_Period = '::Manual_Function_Initiate_Period::', Manual_Function_LifeCycle_Period = '::Manual_Function_LifeCycle_Period::', HoursRestricted = '::HoursRestricted::', Function_Time_Specific = '::Function_Time_Specific::', MainFrame_Supported = '::MainFrame_Supported::', Function_Supported_by_Application = '::Function_Supported_by_Application::', NonStandard_Software_Support = '::NonStandard_Software_Support::', Nonstandard_Software_Support_txt = '::Nonstandard_Software_Support_txt::', NonStandard_Hardware_Support = '::NonStandard_Hardware_Support::', Nonstandard_Hardware_Support_txt = '::Nonstandard_Hardware_Support_txt::', Contractor_Supported = '::Contractor_Supported::', Contractor_Supported_txt = '::Contractor_Supported_txt::', Reports_Produced = '::Reports_Produced::', Report_Name_Description_1 = '::Report_Name_Description_1::', Report_Name_Description_D1 = '::Report_Name_Description_D1::', Report_Critical_C1 = '::Report_Critical_C1::', Report_Name_Description_2 = '::Report_Name_Description_2::', Report_Name_Description_D2 = '::Report_Name_Description_D2::', Report_Critical_C2 = '::Report_Critical_C2::', Report_Name_Description_3 = '::Report_Name_Description_3::', Report_Name_Description_D3 = '::Report_Name_Description_D3::', Report_Critical_C3 = '::Report_Critical_C3::', Report_Name_Description_4 = '::Report_Name_Description_4::', Report_Name_Description_D4 = '::Report_Name_Description_D4::', Report_Critical_C4 = '::Report_Critical_C4::', Reports_Received = '::Reports_Received::', Report_Received_Name_Description_1 = '::Report_Received_Name_Description_1::', Report_Received_Name_Description_D1 = '::Report_Received_Name_Description_D1::', Report_Received_Critical_C1 = '::Report_Received_Critical_C1::', Report_Received_Name_Description_2 = '::Report_Received_Name_Description_2::', Report_Received_Name_Description_D2 = '::Report_Received_Name_Description_D2::', Report_Received_Critical_C2 = '::Report_Received_Critical_C2::', Report_Received_Name_Description_3 = '::Report_Received_Name_Description_3::', Report_Received_Name_Description_D3 = '::Report_Received_Name_Description_D3::', Report_Received_Critical_C3 = '::Report_Received_Critical_C3::', Report_Received_Name_Description_4 = '::Report_Received_Name_Description_4::', Report_Received_Name_Description_D4 = '::Report_Received_Name_Description_D4::', Report_Received_Critical_C4 = '::Report_Received_Critical_C4::', NonInformation_Tech_Resources = '::NonInformation_Tech_Resources::', PC_Back_Up = '::PC_Back_Up::', PC_Back_Up_T1 = '::PC_Back_Up_T1::', Operational_Impact_T1 = '::Operational_Impact_T1::', Human_Service_Impact = '::Human_Service_Impact::', Agency_Mission_Relevance_Value = '::Agency_Mission_Relevance_Value::', Disruption_Level­_D1 = '::Disruption_Level­_D1::', Complete = '::Complete::'
Where ::ID::= ID

I have two problems:

1. I receive the following error when trying to verify:

Server error: Unable to retrieve schema information from the query:

UPDATE Results  SET System_Name = '1', SystemManager_POC = '2',  Date = '3',  Name_Who_Completed = '4', epaID = '5', SystemDescription = '6', Name_Internal_Depend_1 = '7', Position_Internal_Depend_1 = '8', Internal_Depend_C1 = '9', Name_Internal_Depend_2 = '10', Position_Internal_Depend_2 = '11', Internal_Depend_C2 = '12', Name_Internal_Depe

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '­'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
___
When I delete from the "Report_Received_Name_Description_4" down, query is verified against the database, but then I receive the following from the results.asp page:

Database Results Error
The operation failed. If this continues, please contact your server administrator.
___

Troubleshooting Steps taken; I have SQL server database with ID a Key index and Seed, checked for possible security/authentication using simple asp page to update (no problem). I stripped out everthing in the custom query except:
UPDATE Results
SET
System_Name = '"System_Name"'
Where ID=5

... which updates the database with "System_Name" not the information in the form (expected) and obviously points only to record ID=5, not the record actually selected on the form, but it does work.

What syntax change do I need in the custom query for updating in SQL rather than Access within this Database Results Region within FrontPage?


0
Comment
Question by:Hal9000Returns
  • 6
  • 3
9 Comments
 
LVL 14

Expert Comment

by:hhammash
ID: 16284775
Hi,

Try Where ID=::ID::
0
 

Author Comment

by:Hal9000Returns
ID: 16284902
Still get ....
Database Results Error
The operation failed. If this continues, please contact your server administrator.
0
 
LVL 14

Expert Comment

by:hhammash
ID: 16292672
Your problem is with the field name Date here:
SET System_Name = '::System_Name::', SystemManager_POC = '::SystemManager_POC::',  Date = '::Date::',

Date is a reserved word,  try to change it.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 14

Expert Comment

by:hhammash
ID: 16292678
If your Date field in the database is Date/Time you should use # instead of '
0
 
LVL 14

Expert Comment

by:hhammash
ID: 16292680
Also,  in your DRW in the Normal View,  you will have to change this:

Date = '::Date::'  in the first grey line in the Webbot into
Date=#::Date::#

But keep in mind that you have to change the field name. Date is a reserve word you can't use it in sql as a name of the field.  It will create a problem
0
 

Author Comment

by:Hal9000Returns
ID: 16301400
The field name for the date may be a problem, but I trimmed everything down to 2 fields ID and field system_name and I'm still getting the error. I'm posting the system_name and ID values from a form to the results.asp and receive

Database Results Error
The operation failed. If this continues, please contact your server administrator.
(wish there was something to check on the IIS sever like event log or something to see what the actual problem is)

I've converted many ReadOnly type FrontPage ASP data results regions to a SQL Server back-end with very little effort, and this was kind of a trial run at how hard it would be rather than having to start from scratch. As you can see by the number of fields in the form, I was trying for the easy way. I'm thinking about going to .NET and starting from scratch.  
0
 
LVL 14

Accepted Solution

by:
hhammash earned 1000 total points
ID: 16301592
Ok,

This error is not descriptive,  you should do the following:

- Tools
- Site settings
- Advanced
- Show hidden files and folders
- In the folder list open _fpclass folder
- Open fpdbrgn1.inc file
- Locate fp_DEBUG = False  
- Change it to  fp_DEBUG = True

After that try the page again and post the error here
0
 

Author Comment

by:Hal9000Returns
ID: 16344989
Nice! After debug was set to false I received:

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.

See Microsoft Knowledge Base Article 817029.

Looked up the article http://support.microsoft.com/kb/817029/en-us and choose workaround 2 which revealed that the webot didn't have any s-columnnames  or s-columntypes. Once I followed the KB article, cleared the error which will allow me to proceed to resolve any further problems.

I appreciate your help, thanks! BTW, do you use FL 2002 or 2003?
 
 
0
 
LVL 14

Expert Comment

by:hhammash
ID: 16348298
You are welcome,

I am glad it worked.

I use FP 2003
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
Suggested Courses
Course of the Month10 days, 14 hours left to enroll

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question