Start Free Trial

asked on

How do I get an intranet html web form to accept input while saving to a local mdb file in an intranet setting?

I would like to use an html web form in an intranet setting, which users can input data into, and it will save to a mdb file.
Any user should have the ability to update the form, through the html page, so that the next user that opens the same page will see the latest data which has been input.
The html file and the access database mdb file will be stored in a folder on server 2008.

Bernard Savonet

This would work without any problem on a web server inside your intranet.

Do you have one? Does not really matter whether it is Apache or IIS or other.

ASKER

On the 2008 Windows Server, under IIS, our intranet is set up
as a site, and I can pull up our Intranet via an http address.
Is this what you meant?

Bernard Savonet

yes
can you display a page from this server?
if yes, you "only" need to create one or several pages (html or asp depending on features) which will dirctly use the base

ASKER

Yes, I can display an html page from this server. That's what I use as the intranet. What steps would be needed to create an html page which pulls from an mdb file?

On your other question, I gave you the code below. You can simply replace with the appropriate connection string to your db http://www.connectionstrings.com/access/

<%

Option Explicit



'Dim
Dim conn
Dim rsObject
Dim strSQL, LastRecord, errMesg, NextID, strHTML,addSQL,updateSQL

errMesg =""
'Setup connection
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\ee\inventory\;Extended Properties=""text;HDR=Yes;FMT=Delimited"";"

' --------- GET LAST RECORD ----------------
Set rsObject = Server.CreateObject("ADODB.Recordset") 

'GET LAST RECORD'
strSQL = "SELECT TOP 1 ID from inventory.csv ORDER BY ID DESC"
rsObject.Open strSQL, conn
if not rsObject.EOF then
	LastRecord = rsObject("ID")
	else
	LastRecord = 0
end if
rsObject.close
NextID = cdbl(LastRecord) + 1 ' when inserting a record, the new id will be NextID

' --------  ADD A RECORD ------'
if request.form("action") = "add" then
	errMesg = ""
	if not isnumeric(request.form("qty")) then
		errMesg = "<p>Please enter a numeric qty</p>"
	end if
	if request.form("item")="" then
		errMesg = errMesg & "<p>Please enter in an item</p>"
	end if

	if errMesg ="" then ' ok to add'
		addSQL="INSERT INTO inventory.csv (ID,Item,Qty,[Date])"
		addSQL=addSQL & " VALUES "
		addSQL=addSQL & "('" & NextID & "',"
		addSQL=addSQL & "'" & request.form("item") & "',"
		addSQL=addSQL & "'" & request.form("qty") & "',"
		addSQL=addSQL & "'" &  now() & "')"

		conn.Execute addSQL
	end if

end if



%>


<style>
table {width:600px;}
tr:nth-child(even) {background: #CCC}
tr:nth-child(odd) {background: #FFF}
</style>
<div>Add Item</div>
<div>
<form action="index.asp"  method="post">
	<input type="hidden" name="action" value="add">
	<input name="item" placeholder="item">
	<input name="qty" placeholder="qty">
	<button type="submit">Add Item</button>
</form>	
</div>
<%
response.write errMesg
strSQL = "SELECT max(ID) as ID, Item, sum(Qty) as Qty, max(Date) as iDate from inventory.csv GROUP BY Item Order By Item"
rsObject.Open strSQL, conn

strHTML="<table>"
strHTML=strHTML&"<thead><tr><th>ID</th><th>Item</th><th>Qty</th></tr></thead><tbody>"
If not rsObject.EOF then
	do until rsObject.EOF
		strHTML = strHTML &"<tr><td>"& rsObject("ID") & "</td><td>" & rsObject("Item") & "</td>" 
		strHTML = strHTML & "<td>"&rsObject("Qty")&"</td><td>"&rsObject("iDate")&"</td></tr>"
	rsObject.movenext
	Loop
end if
strHTML = strHTML & "</tbody></table>"
response.write strHTML
'Close and clear
rsObject.close
conn.close
set rsObject = nothing
set conn = nothing


%>

Open in new window

ASKER

Is this vbscript?

Yes it is. Classic asp with vbs

ASKER

@scott How will I be able to tell if ASP has been installed on the 2008 server in order for this to work?

Try this

<%="It works"%>

Open in new window

http://www.iis.net/learn/application-frameworks/running-classic-asp-applications-on-iis-7-and-iis-8/classic-asp-not-installed-by-default-on-iis

ASKER

Thanks. When I look at the Role Services on the server that hosts the Intranet, ASP is showing as installed, under Application development.

Where do I place the <%="It works"%>, in a script, or do I save it in notepad as an html or something else?

Just make an html page

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>
<html>
<body>
<%
response.write("<p>Hello World!</p>")
dim x
x = 10
y = x * 2
response.write("<p>X = "&x&"<br>x * 2 = "&y&"</p>"
response.write("<p>Today  is: "&date&"</p>"
%>
</body>
</html>

Open in new window

ASKER

Thanks, I will give this a try.

Also, with the script you provided back on July 6th, is that saved to a vbs extension?
And if yes, in what path should it be saved in?

That is an asp page. You should save it as .asp and run in your browser on a webserver that has classic asp installed.

This script I just gave you is also asp.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>
<html>
<body>
<%
response.write("<p>Hello World!</p>")
dim x
x = 10
y = x * 2
response.write("<p>X = "&x&"<br>x * 2 = "&y&"</p>"
response.write("<p>Today  is: "&date&"</p>"
%>
</body>
</html>

Open in new window

You could take the vb portion with some minor changes and save as .vbs and run

<%

dim x
x = 10
y = x * 2
WScript.Echo "X = "&x&" x * 2 = "&y

%>

Open in new window

ASKER

When I save this to a .html format, ie testpage.html when I pull it up, nothing happens.
The browser comes up however nothing is displayed.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>
<html>
<body>
<%
response.write("<p>Hello World!</p>")
dim x
x = 10
y = x * 2
response.write("<p>X = "&x&"<br>x * 2 = "&y&"</p>"
response.write("<p>Today  is: "&date&"</p>"
%>
</body>
</html>

Open in new window

ASKER

Additionally if I try to pull up testpage.html using the internal ip address for the intranet..

I get the following which shows up:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%> <% response.write("
Hello World!
") dim x x = 10 y = x * 2 response.write("
X = "&x&"
x * 2 = "&y&"
" response.write("
Today is: "&date&"
" %>

Open in new window

ASKER

Also, if I save this as .asp, it comes up with an internal server error.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>
<html>
<body>
<%
response.write("<p>Hello World!</p>")
dim x
x = 10
y = x * 2
response.write("<p>X = "&x&"<br>x * 2 = "&y&"</p>"
response.write("<p>Today  is: "&date&"</p>"
%>
</body>
</html>

Open in new window

That is because you need to save it as an .asp and not .html

What is the error?

You may have to turn on errors if you are getting a 500 error

http://www.iis.net/learn/application-frameworks/running-classic-asp-applications-on-iis-7-and-iis-8/classic-asp-script-error-messages-no-longer-shown-in-web-browser-by-default

ASKER

500 - Internal server error.

There is a problem with the resource you are looking for, and it cannot be displayed.

Ok, you have to turn on sending errors to the browser.

Try this simple version

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>
<html>
<body>
<%="<p>Hello World!</p>"%>
</body>
</html>

Open in new window

ASKER

Ok, now when I save it as for instance test2.asp onto the server, and I try to open it within the internal intranet ip address:

It opens a browswer and all that is displayed is:

Hellow World!

is this good? If yes, what are my next steps?

yes, except for my spelling. I didn't realize I added an extra w!

Next step is to make a simple form.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
dim name, email
name=""
email=""
if request.form<>"" then ' we have new data
    name= request.form("name")
    email = request.form("email")
end if
%>
<!DOCTYPE html>
<html>
<body>
<%
if name<>"" then
    response.write "Your data was accepted.  Name:"&name&" Email: "&email&"<hr>"
end if
%>
<form method="post" action="">
<input name="name" placeholder="Name" value="<%=name%>">
<input name="email" placeholder="Email" value="<%email%>>
<button type="submit">Submit</button>
</form>
</body>
</html>

Open in new window

ASKER

After this form is made, I presume saving it in .asp, what would come next?
Don't I need a form to input my data into or change the date of the inventory?

ASKER

Scott, should I open up another forum strictly for writing from an asp page to a .csv, since it seems that this is where this is headed now, as opposed to writing to an mdb, since I am starting to follow the direction on your July 6th entry above.

Maybe. Do you just want the form data to go to a csv file on your intranet?

Start by giving the folder write access for the IUSR account.

Next convert the input to csv

<%
dim name, email, data_line
name=""
email=""
if request.form<>"" then ' we have new data
    name= request.form("name")
    email = request.form("email")

   data_line = chr(34)&name&chr(34)&","&chr(34)&email 

Const fsoForWriting = 2

Dim objFSO
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")

'Open the text file
Dim objTextStream
Set objTextStream = objFSO.OpenTextFile("C:\SomeFile.txt", fsoForWriting, True)  'this would be the  path on your server

'Display the contents of the text file
objTextStream.WriteLine  data_line

'Close the file and clean up
objTextStream.Close
Set objTextStream = Nothing
Set objFSO = Nothing



end if
%>

Open in new window

ASKER

Yes, for now it seems that this might work, let's try sending the data that is input in an asp page to an inventory.csv file. That would work fine.

First of all, which folder should I give write access for the IUSR account?
The Intranet folder resides on a D drive of the server where ASP is turned on.

To clarify, I just need an asp/html web page where I can update it with the latest inventory items, and if it can write to a .csv then that would be fine. The goal is the next time I open that asp/html web page that it should show any changes I made to the updated inventory last time and it should give me the ability to update the inventory on hand etc.. I thought your entry from July 6th above was what I needed to start with.

ASKER

Scott,

When for writing to an inventory.csv file, you provided the following script below.

The directory c:\inetpub\wwwroot\ee\inventory\;... is this to be pointing to a folder within the inetpub\wwwroot subfolders or can I place it anywhere on the server that has ASP installed?
And if I can place it anywhere, the C or D drive mentioned in this path, is from the standpoint of the server where ASP resides, for instance it's the C or D drive etc.. on the server where ASP resides?

Can it also be a \\ path to the name of the server and subfolders?

My goal is to update an asp/html page with the latest inventory information, and it keeps that data.
The next time I pull up the webpage is must show the last data entered etc. and give me ability to make any changes to the inventory count.

<%

Option Explicit



'Dim
Dim conn
Dim rsObject
Dim strSQL, LastRecord, errMesg, NextID, strHTML,addSQL,updateSQL

errMesg =""
'Setup connection
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\ee\inventory\;Extended Properties=""text;HDR=Yes;FMT=Delimited"";"

' --------- GET LAST RECORD ----------------
Set rsObject = Server.CreateObject("ADODB.Recordset") 

'GET LAST RECORD'
strSQL = "SELECT TOP 1 ID from inventory.csv ORDER BY ID DESC"
rsObject.Open strSQL, conn
if not rsObject.EOF then
	LastRecord = rsObject("ID")
	else
	LastRecord = 0
end if
rsObject.close
NextID = cdbl(LastRecord) + 1 ' when inserting a record, the new id will be NextID

' --------  ADD A RECORD ------'
if request.form("action") = "add" then
	errMesg = ""
	if not isnumeric(request.form("qty")) then
		errMesg = "<p>Please enter a numeric qty</p>"
	end if
	if request.form("item")="" then
		errMesg = errMesg & "<p>Please enter in an item</p>"
	end if

	if errMesg ="" then ' ok to add'
		addSQL="INSERT INTO inventory.csv (ID,Item,Qty,[Date])"
		addSQL=addSQL & " VALUES "
		addSQL=addSQL & "('" & NextID & "',"
		addSQL=addSQL & "'" & request.form("item") & "',"
		addSQL=addSQL & "'" & request.form("qty") & "',"
		addSQL=addSQL & "'" &  now() & "')"

		conn.Execute addSQL
	end if

end if



%>


<style>
table {width:600px;}
tr:nth-child(even) {background: #CCC}
tr:nth-child(odd) {background: #FFF}
</style>
<div>Add Item</div>
<div>
<form action="index.asp"  method="post">
	<input type="hidden" name="action" value="add">
	<input name="item" placeholder="item">
	<input name="qty" placeholder="qty">
	<button type="submit">Add Item</button>
</form>	
</div>
<%
response.write errMesg
strSQL = "SELECT max(ID) as ID, Item, sum(Qty) as Qty, max(Date) as iDate from inventory.csv GROUP BY Item Order By Item"
rsObject.Open strSQL, conn

strHTML="<table>"
strHTML=strHTML&"<thead><tr><th>ID</th><th>Item</th><th>Qty</th></tr></thead><tbody>"
If not rsObject.EOF then
	do until rsObject.EOF
		strHTML = strHTML &"<tr><td>"& rsObject("ID") & "</td><td>" & rsObject("Item") & "</td>" 
		strHTML = strHTML & "<td>"&rsObject("Qty")&"</td><td>"&rsObject("iDate")&"</td></tr>"
	rsObject.movenext
	Loop
end if
strHTML = strHTML & "</tbody></table>"
response.write strHTML
'Close and clear
rsObject.close
conn.close
set rsObject = nothing
set conn = nothing


%>

Open in new window

ASKER

Once I formed the index.asp file, and I try to open the web page, I get the error below.
I saved the inventory.csv to a C path on the server and that's what I inserted in the script.

Server Error

500 - Internal server error.

There is a problem with the resource you are looking for, and it cannot be displayed.

The folder location I gave in my example was from me testing. That should be a physical location on your own server. It does not matter where on your server it is. However, you need to give that folder write / execute permissions to the IUSR account.

ASKER

Once I formed the index.asp file, and I try to open the web page, I get the error below.
I saved the inventory.csv to a C path on the server and that's what I inserted in the script.

Server Error

500 - Internal server error.

There is a problem with the resource you are looking for, and it cannot be displayed.

Those 500 errors are generic. You have to turn on errors in asp as I pointed out to determine what the issue is http:Q_28468068.html#a40216658

ASKER

I turned on errors in asp, however I am still getting this message:

Server Error

500 - Internal server error.

There is a problem with the resource you are looking for, and it cannot be displayed.

ASKER

Does anyone have any other suggestions for me at this time?

ASKER

I turned on errors in asp, however I am still getting this message:

Server Error

500 - Internal server error.

There is a problem with the resource you are looking for, and it cannot be displayed.

Try running it from the server

You can try finding errors like this

<%

Option Explicit
On Error Resume Next


'Dim
Dim conn
Dim rsObject
Dim strSQL, LastRecord, errMesg, NextID, strHTML,addSQL,updateSQL

errMesg =""
'Setup connection
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\ee\inventory\;Extended Properties=""text;HDR=Yes;FMT=Delimited"";"


response.write "1<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING


' --------- GET LAST RECORD ----------------
Set rsObject = Server.CreateObject("ADODB.Recordset") 

response.write "2<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING

'GET LAST RECORD'
strSQL = "SELECT TOP 1 ID from inventory.csv ORDER BY ID DESC"
rsObject.Open strSQL, conn

response.write "3<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING
if not rsObject.EOF then
response.write "4<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING
    LastRecord = rsObject("ID")
    else
    response.write "5<br>" '***** TESTING
    response.write Err.description &"<br>" '***** TESTING
    LastRecord = 0
end if
rsObject.close
response.write "6<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING
NextID = cdbl(LastRecord) + 1 ' when inserting a record, the new id will be NextID

response.write "7<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING

' --------  ADD A RECORD ------'
if request.form("action") = "add" then
response.write "8<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING

    errMesg = ""
    if not isnumeric(request.form("qty")) then
    response.write "9<br>" '***** TESTING
    response.write Err.description &"<br>" '***** TESTING

        errMesg = "<p>Please enter a numeric qty</p>"
    end if
    if request.form("item")="" then
    response.write "10<br>" '***** TESTING
    response.write Err.description &"<br>" '***** TESTING

        errMesg = errMesg & "<p>Please enter in an item</p>"
    end if

    if errMesg ="" then ' ok to add'
    response.write "11<br>" '***** TESTING
    response.write Err.description &"<br>" '***** TESTING
        addSQL="INSERT INTO inventory.csv (ID,Item,Qty,[Date])"
        addSQL=addSQL & " VALUES "
        addSQL=addSQL & "('" & NextID & "',"
        addSQL=addSQL & "'" & request.form("item") & "',"
        addSQL=addSQL & "'" & request.form("qty") & "',"
        addSQL=addSQL & "'" &  now() & "')"

        conn.Execute addSQL
    end if
response.write "12<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING
end if
response.write "13<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING


%>


<style>
table {width:600px;}
tr:nth-child(even) {background: #CCC}
tr:nth-child(odd) {background: #FFF}
</style>
<div>Add Item</div>
<div>
<form action="index.asp"  method="post">
    <input type="hidden" name="action" value="add">
    <input name="item" placeholder="item">
    <input name="qty" placeholder="qty">
    <button type="submit">Add Item</button>
</form> 
</div>
<%
response.write "14<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING

response.write errMesg
strSQL = "SELECT max(ID) as ID, Item, sum(Qty) as Qty, max(Date) as iDate from inventory.csv GROUP BY Item Order By Item"
rsObject.Open strSQL, conn

response.write "15<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING

strHTML="<table>"
strHTML=strHTML&"<thead><tr><th>ID</th><th>Item</th><th>Qty</th></tr></thead><tbody>"
If not rsObject.EOF then
    do until rsObject.EOF
        strHTML = strHTML &"<tr><td>"& rsObject("ID") & "</td><td>" & rsObject("Item") & "</td>" 
        strHTML = strHTML & "<td>"&rsObject("Qty")&"</td><td>"&rsObject("iDate")&"</td></tr>"
    rsObject.movenext
    Loop
end if
strHTML = strHTML & "</tbody></table>"
response.write strHTML
response.write "16<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING
'Close and clear
rsObject.close
conn.close
set rsObject = nothing
set conn = nothing

response.write "17<br>" '***** TESTING
response.write Err.description &"<br>" '***** TESTING
%>

Open in new window

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I found a program that works and the saves to an mdb database.