Access Database in Frontpage (Full Version)

All Forums >> [Web Development] >> Microsoft FrontPage Help



Message

Benny P -> Access Database in Frontpage (8/18/2002 16:24:34)

I' m a newbie. I' m having problems getting an Access Database to run in Frontpage. I' ve used the FDW to no avail. The farthest I' ve gotten is to this error message.

Cannot open record set
Error Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

Error Number: -2147217900

Error Source: Microsoft OLE DB Provider for ODBC Drivers



I' m trying to create a form that will send information to an Access Database in a Frontpage web. I' ve changed the extension of my page to .asp, and I' ve run through the wizard numerous times.

If further information is required to reach the solution to my problem, then I' d be glad respond to any questions...

Thanks



Spooky -> RE: Access Database in Frontpage (8/18/2002 17:42:35)

Using the DRW, are you able to query a simple database and display the results on an asp page?



Long Island Lune -> RE: Access Database in Frontpage (8/19/2002 0:08:25)

Did you create your database outside of FP using Access, and imported it into your web, or did you let FP create the database for you automatically?

When you created your page and used the DRW, upon the DRW' s competion, FP brought up a dialog box that told you to change the extension from .HTML to .ASP right?



Long Island Lune -> RE: Access Database in Frontpage (8/19/2002 0:16:17)

Microsoft has a definition for your problem. They have a few articles on your " error number" . This might help. You can go there now:

http://search.support.microsoft.com/search/default.aspx?Catalog=LCID%3D1033%26CDID%3DEN-US-KB%26PRODLISTSRC%3DON&withinResults=&QuerySource=gsfxAdvancedSearch_Query&Product=fp2000&Queryc=-2147217900+&Query=-2147217900+&KeywordType=ALL&maxResults=25&Titles=false&numDays=



Benny P -> RE: RE: Access Database in Frontpage (8/19/2002 11:31:38)

I dont have my connection set up to query my database. But if I' m not mistaken, querying would envolve requesting information from my data base. Unless the DB is required to be set up like this, I would only like to know how to have information sent to the DB.



Benny P -> RE: RE: Access Database in Frontpage (8/19/2002 11:39:57)

I did create an Access DB outside of frontpage. I imported the DB by copying and pasting it into the web. FP then created, I' m guessing, my connection...so the answer to your question is no, FP did not create the DB.

I changed the page extension myself when I first saved the page, which was before I even started the wizard. I never encountered a dialog box.



Long Island Lune -> RE: Access Database in Frontpage (8/19/2002 15:45:29)

Benny P.

Creating the database OUTSIDE of FP using MS-Access is the BEST way to do it. If you let FP do it, it adds fields that you don' t really need. But cut, copy and pasting it into your site would not be the best way if you ask me. You want the Global.Asa file to know what it' s dealing with. It' s my guess that is where your problem is. IMPORT the database into your FP Project. This way the Global.Asa file has all the poop it needs to work with your external database.

First Step: (Clear out the problem)
Get the database OUT of your site.

Second Step: (Import the Database)
1): File > Import > Add File
2): Locate your database file on your hard drive.
3): Click OK.
4): A dialog will appear asking you to name the database. Give it a name. This is the name that you will referencing it by in your code and the DRW.
5): FP will import it in and correctly update your Global.Asa file.

Third Step: (Verifiy that it is OK)
1): Tools > Web Settings > Database. You will see a green question mark next to your newly imported database in the list.
2): Select your database and click on Verify.

That' s all there is too it. Now you KNOW you have a correct connection to your database in FP through your global.Asa file. Now you can use the DRW, or hand code in .asp to update (send info to) the database. The Global.Asa file handles all types of transactions between your database and your site. Querying or Sending. It does not matter. Try this.
LLL





Benny P -> RE: RE: Access Database in Frontpage (8/19/2002 20:01:11)

Thanks for your reply...I' ll try it out.

Benny P



Benny P -> RE: RE: RE: Access Database in Frontpage (8/20/2002 9:06:54)

One more thing: I' m not certain if this has an effect on any part of the process, but I recently installed DreamWeaver on my box. For some reason, my global.asa file will only open in DreamWeaver... could this be part of the problem?

I' ve tried the exact procedure that you mentioned above and still, to this very moment, I have had no sucess in getting this thing to work...ie same error keeps on occuring...

Is there anything else I can do?

Benny



Long Island Lune -> RE: Access Database in Frontpage (8/20/2002 11:25:39)

Benny,

You installed Dreamweaver??? And you can only open your Global.Asa file in Dreamweaver??? Actually, the Global.Asa by default cannot be opened in Frontpage. If you try to open it, you get an error. To open it in FP, you have to single-click on the Global.Asa file in your list to select it, then right-click and select " Open With" . Then select " Text Editor" . That' s how I view my Global.Asa file.

Questions:
1): Are you getting the SAME error, or a new one?
2): I need to see your SQL Query. Your error is stating a problem in your FROM clause.

I went to the Microsoft site and looked up every error they have listed under error number: -2147217900. They don' t have anything that matches your error.

I don' t know if Dreamweaver has any effect with FP. Let me know what error your getting now and let me see your SQL Query. Do you know where it is?
LLL




Benny P -> RE: Access Database in Frontpage (8/20/2002 13:16:11)

I was wrong... I can open my global.asa in a text editor. DreamWeaver just automatically opens after doulble clicking on the file.

I am still getting the same error message that I first wrote about.

About the SQL query: that is just the code for my query right?

If so, I' ll post it immediately.




Long Island Lune -> RE: Access Database in Frontpage (8/20/2002 13:33:03)

Benny,

Yes, just the code for your query.



Benny P -> RE: Access Database in Frontpage (8/20/2002 13:51:03)

Voila: (and excuse my stupidity, about the SQL query thing)

<%
' FP_ASP ASP Automatically generated by a Frontpage Component. Do not Edit.
On Error Resume Next

strErrorUrl = " "

If Request.ServerVariables(" REQUEST_METHOD" ) = " POST" Then
If Request.Form(" VTI-GROUP" ) = " 0" Then
Err.Clear

Set fp_conn = Server.CreateObject(" ADODB.Connection" )
FP_DumpError strErrorUrl, " Cannot create connection"

Set fp_rs = Server.CreateObject(" ADODB.Recordset" )
FP_DumpError strErrorUrl, " Cannot create record set"

fp_conn.Open Application(" SwimteamsDB_ConnectionString" )
FP_DumpError strErrorUrl, " Cannot open database"

fp_rs.Open " Data Table" , fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
FP_DumpError strErrorUrl, " Cannot open record set"

fp_rs.AddNew
FP_DumpError strErrorUrl, " Cannot add new record set to the database"
Dim arFormFields0(7)
Dim arFormDBFields0(7)
Dim arFormValues0(7)

arFormFields0(0) = " T2"
arFormDBFields0(0) = " E-mail"
arFormValues0(0) = Request(" T2" )
arFormFields0(1) = " T3"
arFormDBFields0(1) = " Club"
arFormValues0(1) = Request(" T3" )
arFormFields0(2) = " T4"
arFormDBFields0(2) = " Phone"
arFormValues0(2) = Request(" T4" )
arFormFields0(3) = " T5"
arFormDBFields0(3) = " State"
arFormValues0(3) = Request(" T5" )
arFormFields0(4) = " T6"
arFormDBFields0(4) = " Zip"
arFormValues0(4) = Request(" T6" )
arFormFields0(5) = " T10"
arFormDBFields0(5) = " City"
arFormValues0(5) = Request(" T10" )
arFormFields0(6) = " T1"
arFormDBFields0(6) = " Address"
arFormValues0(6) = Request(" T1" )

FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0


fp_rs.Update
FP_DumpError strErrorUrl, " Cannot update the database"

fp_rs.Close
fp_conn.Close

Session(" FP_SavedFields" )=arFormFields0
Session(" FP_SavedValues" )=arFormValues0
Response.Redirect " Confirm.html"

End If
End If

%>



Long Island Lune -> RE: Access Database in Frontpage (8/20/2002 14:55:10)

Benny,

That is what FP generated for you??? I was expecting something like this:

INSERT INTO SwimteamsDB (E-mail, Club, Phone, State, Zip, City, Address) VALUES ' ::Email::' , ' ::Club::' , ' ::Phone::' , ' ::State::' , ::Zip::, ' ::City::' , ' ::Address::' )

In your first post you said you used the FDW. What is that? Do you mean that you used the DRW??? Creating a form is a relatively simple endeavor. Then posting it to a second page is simple to. On the second page you create another DRW. Here is what I would have done:

1): Create the form you want on page 1.
2): Post the form to page 2.
3): On page 2, run the DRW.

On Page Two Using the DRW:

In Step 1 of 5 - select your database connection in the drop down list box.
In Step 2 of 5 - select " Custom Query" , then click on " Edit" .
In the Edit box, enter the SQL I just gave you above.
Complete the remaining Steps 3 to 5 in the DRW.

Since you are putting data into the database, you do not need to see anything, so...

In Step 4 of 5, select " Table - one record per row" . UNSELECT anything below it.
In Step 5 of 5, click on " Display all records together" and UNSELECT anything else in Step 5. This will show nothing on your page.

In the SQL query I listed above, replace " SwimteamsDB" with whatever your actual table name is (if SwimteamsDB is not the table name).

Also, I would change the field name of " E-mail" to " Email" in your query and in MSAccess too.

And remember one thing: Text-Fields are entered as ' ::Zip::' , whereas Numeric-Fields are entered as ::Zip:: in the SQL Query line I gave you (above). I don' t know what data types your fields are. I only guessed. Adjust them accordingly if the SQL I gave you does not work. Chances are that you did not enter the field types correctly.

Last Note:
Make sure the field types in your Access database table MATCH the field types in your SQL query.

That should do it.
Do you want to try that???
It' s easier and gets the job done.
Let me know what happens












Benny P -> RE: Access Database in Frontpage (8/20/2002 16:27:53)

Well go figure...
(this is what happened when I used the DRW and used the " Edit Custom Query" : After inserting and changing your code, I clicked the Verify Query tab and it gave me a dialog box that read--> The Server Encounted an Error While Processing a Database Request. This is what I got when I clicked under the Details tab:

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

INSERT INTO Data Table (Name, Email) VALUES (' 1' , ' 2' )

against a database using the connection string

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/swimdata.mdb.

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 Microsoft Access Driver] Syntax error in INSERT INTO statement.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)

Note: it is possible to verify the query if you place the name of the database (Database1) in place of the name of the table (Data Table), but the form still doesnt work.

Might there be something we' re forgetting in the SQL query?



Spooky -> RE: Access Database in Frontpage (8/20/2002 17:53:15)

Name is a reserved word - what other column names do you have?

If you use a reserved word, you need to place it in brackets like so :

[name]



Long Island Lune -> RE: Access Database in Frontpage (8/20/2002 20:43:40)

Hmmmm... You confused me. Where did " Name" come from? Spooky was right. Show us the exact SQL you are using. " Name" wasn' t mentioned in your original printout.

Here is the exact INSERT INTO syntax:

insert into " tablename" (first_column,...last_column) values (first_value,...last_value);

Make sure you include all the columns (fields) and that they are the correct data type. " Unable to retrieve schema information from the query:" means that what is in your table does not match what you are trying to send to it using the " INSERT INTO" Query.




Spooky -> RE: RE: Access Database in Frontpage (8/20/2002 22:07:06) 

INSERT INTO Data Table (Name, Email) VALUES (' 1' , ' 2' )


The (' 1' ,' 2' ) are usually a giveaway [;)]



Benny P -> RE: Access Database in Frontpage (8/21/2002 9:06:11)

I truly appreciate all your help... there was obviously something wrong with the SQL query I was creating but I ended up scratching that query and starting over. For some reason it worked this time and maybe one day I' ll understand why the last one gave me so much trouble. In the mean time, I' m just going to leave the creation of queries up to Frontpage and hope for the best...

Thank you Lune...
Thanks Spooky

Benny P



Long Island Lune -> RE: Access Database in Frontpage (8/21/2002 10:44:12)

Glad it worked.[;)]



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
9.277344E-02