Submitting Form to Access Database

Since this has been asked numerous times, I am going to take a stab at this here. First, I am assuming you are using IIS5 or IIS6 and you are doing this in ASP using VBScript as your programming language...step 1: Create the subdirectory on your server you are going to put your lectora files in. Create a subdirectory in that subdirectory and call it DB. That DB subdirectory is where you will place your access database.step 2: Create your access database with the correct tables etc. (the example here utilizes one table but you can update multiple tables if you like). In my example, I created a database called n395aeval.mdb and have only ONE table in it called MAIN.) Put the database in your DB folder you created in step 1. Make sure that the IUSR account has full rights to that subdirectory and inherited files. If they don't have write privileges to the database, they won't be able to add to the database. (The permissions on that subdirectory for IUSR will have to be set by the server administrator.)Step 3: take the asp file I have put in the next reply window and copy/paste it into an editor that supports line numbering. You will need that because I am going to address the code by line number here.Now I will explain the lines by line number of the example in the next reply box:1. required as is. Creates the pipe type that connects to the database. Just use that line EXACTLY as it appears.3. q1=request.form("q1")the q1 on the right (that is embedded in quotes) MUST correspond EXACTLY to the variable name you created in Lectora. In my example I called the first variable in Lectora q1. That is what is in the quotes. The q1 on the LEFT of the equal sign is an asp variable. It does NOT have to agree with the name on the right but by convention it is probably a good thing to do. Keep the =request.form( exactly the same and note that the q1 on the right MUST be embedded in standard double quotes.In ALL of my variables (from lines 3 to 21) I make my asp variables (on the left of the equal sign), the same name as the variables embedded in quotes on the right of the equal sign which are the variable names I created in Lectora. (Keep in mind the variables are referenced three times: once in Lectora, once in ASP, and then again as column names in the database. I find it VERY helpful that if I am recording the street name in a Lectora variable that we will call STREET, for example, I will use that same name for the ASP variable of the same data AND I will also use that as the column name in the database as well. Naming them all the same isn't required but it is a good convention, particularly when trying to debug.10. since this is an SQL project, if you have an open text field and the user puts in an apostrophe, that will break everything. You need to filter out apostrophes BEFORE they hit the SQL code in line 25. The Replace method replaces inside the variable comments the apostrophe with the tilda. I selected the tilda because it really isn't used in my projects anywhere else but you can use any keystroke you aren't using for anything else. The apostrophe will crop up in names like O'Malley or streets of that same name....or if they write comments with words like "don't" instead of "do not". You can't control what they will write and you certainly don't want an apostrophe to break things...so that is what lines 10, 14, 16 do in my application. (Frankly I should have had it replaced in the other places too because someone could maliciously put an apostrophe in a zipcode.) So any time you have a textbox you should test for and remove the apostrophe like I have demonstrated.23. I don't use a DSN name for Access. The ODBC-DSN connection for Acess is broken...it constantly gives me errors. Instead I directly connect to the Access database (for SQL2K I do use a DSN...just not for Access). Access is a slow database so a direct connect is faster and less likely to cause a hangup. Type it in EXACTLY as I have it but replace the path with your path to the Access database starting with the root of the drive. Make sure you use the quotes EXACTLY as I have them. (NOTE: you will probably need to get the full path from your server administrator). Another advantage of doing it directly is you don't have to have the server manager create a DSN name for every database application you are creating.Now for the tough line....this is SQL:25. the word MAIN in the sql statement is the table name in my Access database. Make sure you change it to whatever YOUR table name is. The first set of variables, in the parens MUST agree with the column names in YOUR database table. The variables that come next MUST agree with the variable names to the left of the equal signs in lines 3-21 above. NOTE: the syntax of those variables here is EXTREMELY messy...it is a single quote, followed by a double quote, followed by an ampersand, followed by the variable name, followed by another ampersand, followed by a double quote, followed by a single quote with NO spaces. Each clump is separated with a comma. The entire mess ends with the parens. Pay very close attention to this line. ONLY change the variable names. Make sure the entire statement right of the equal sign is embedded in double quotes. Also note that what we are creating a a variable called sqladd. We will execute that variable in the next line. If you are going to make a mistake, trust me, it will be on this line.27. This is the real action. Make sure that the variable name in parens agrees with the variable name we created above in line 25 just to the left of the equal sign there. Also note that the server object we created in line 1, addstuff, is also the addstuff we open in line 23 (we are opening a pipe to the database) and what we are executing in line 27. 28. This closes the pipe since it is not needed anymore29. This crushes the pipe for security reasons so nothing else can use it and infect your database.30. closes aspJust leave 27-30 alone and copy as is.Save this file with some name....such as: submit.asp and put it in the same subdirectory with your Lectora filesNow when you go to the form properties in your Lectora form, check submit to CGI and put in submit.asp as the file you are submitting to. By saving this file in the same directory as your lectora files you won't have to create a path for that cgi script in the form properties.whew......now I will post the asp file.Edited By: tekprof on 2005-12-8 22:19:21