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

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?


Hal9000ReturnsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hhammashCommented:
Hi,

Try Where ID=::ID::
0
Hal9000ReturnsAuthor Commented:
Still get ....
Database Results Error
The operation failed. If this continues, please contact your server administrator.
0
hhammashCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

hhammashCommented:
If your Date field in the database is Date/Time you should use # instead of '
0
hhammashCommented:
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
Hal9000ReturnsAuthor Commented:
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
hhammashCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hal9000ReturnsAuthor Commented:
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
hhammashCommented:
You are welcome,

I am glad it worked.

I use FP 2003
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.