Posting data to microsoft access

**sportsman** · Jan 8, 2002 12:12

Hello,

I have a site hosted on an asp and php equipped server. Several of the pages have forms which users submit information.

How do I set up a webpage that automatically posts the information into various fields in a Microsoft Access database presumably present on the server.

would appreciate any assistance.

cheers,

Sportsman.

**Hellbent** · Jan 8, 2002 18:40

Sports, here is an example using the Northwind.mdb. You should have a copy of the Northwind Database if you have access installed. Let me know if you need any further help but for the mostpart the comments in the code are fairly good.

'CustomerList.asp'

Code:

<%@ Language=VBScript %>
<%OPTION EXPLICIT %>
<!-- #INCLUDE VIRTUAL="/samples/adovbs.inc" -->
<HTML>
<HEAD><SCRIPT Language="javascript">
function window_onload()
{
 CustForm.CustomerSelect.focus();
}
function CheckSelection()
{
  if (CustForm.CustomerSelect.value=="NONE")	
	{
     window.alert("Please choose a Customer");
     CustForm.btnEdit.disabled=true;
     CustForm.btnOrders.disabled=true;
     }
  else
	{ 
     CustForm.btnEdit.disabled=false;
     CustForm.btnOrders.disabled=false;
    } 
}
</SCRIPT>
<TITLE>Customer List</TITLE>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY language="javascript" onload="return window_onload()">
<% 
Dim rstCustomers             'Customer Recordset
Dim custRows	             'Customer Array
Dim strSQL                   'Stores SQL string
Dim strHeader                'Table Header  
Dim currentPage              'What page are we on?? 
Dim RowCount				 'Row Counter
Dim custChosen
Dim i,cnum  				 'Counter
On Error Resume Next
%>
<!-- #INCLUDE VIRTUAL="/samples/dbconx.asp" -->  
<%
objConn.Open strConnect
'Load Customer List and load records to array
strSQL="SELECT CustomerID,CompanyName FROM Customers ORDER BY CompanyName"
Set rstCustomers=objConn.Execute(strSQL)
custRows = rstCustomers.Getrows
rstCustomers.Close
Set rstCustomers = Nothing

'*** Did user make a selection from the drop down list? ***
'*** the last time this page was loaded? If not, get query string ***
custChosen=Request.Form("CustomerSelect")
If IsEmpty(custChosen) Then
    custChosen = Request.QueryString("CustomerSelect")
End If

'*** make sure that chosen item type matches DB field type for query ***
If (("X" & custChosen) <>"X") AND (custChosen <>"NONE") Then
	custChosen = CInt(custChosen)
End IF
'*** Display Customer List *** %>
<CENTER><H2> Customer List</H2></CENTER>
<BR><BR>
<P>
<FORM Name="CustForm" Method=Post LANGUAGE=javascript"> 
<B>Customers:&nbsp;&nbsp;</B>
<SELECT Name="CustomerSelect" size=1 onChange="return CheckSelection();">
	<OPTION Value="NONE">--Choose A Customer--
		<%For cnum = 0 To UBound(custRows,2) %>
			<OPTION Value="<%=custRows(0,cnum)%>"
			<%IF custRows(0,cnum) = custChosen Then %>
					SELECTED
			<%End If%>
			><%=custRows(1,cnum)%>
	    <%Next%>
</SELECT>
<P>
<BR><BR><BR><BR><BR><BR>
<Div align=center>
<INPUT Type="Submit" Name="btnEdit" value="Edit Customer" <%If IsEmpty(custChosen) Then%>disabled<%End If%> OnClick="document.CustForm.action='CustomerEdit.asp';document.CustForm.submit;">
&nbsp;&nbsp;&nbsp;&nbsp;
<INPUT Type="Submit" Name="btnOrders" value="List Customer Orders" <%If IsEmpty(custChosen) Then%> disabled<%End If%> OnClick="document.CustForm.action='CustomerOrders.asp';document.CustForm.submit;">
<BR>
</Div>
<%'*** Clean up all the DB related stuff ***
objConn.Close
'objItemRec.Close
Set objConn = Nothing
%>
</FORM>
</BODY>
</HTML>

'CustomerEdit.asp'

Code:

<%@ Language=VBScript %>
<%Option Explicit %>
<!-- #INCLUDE VIRTUAL="/samples/adovbs.inc" -->
<HTML>
<HEAD><SCRIPT Language="javascript">
function window_onload()
{
 Customers.CompanyName.focus();
}
</SCRIPT>
<TITLE>Customer Info</TITLE>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY language="javascript" onload="return window_onload()">
<CENTER><H2>Customer Info</H2></CENTER>
<%
Dim rstCustomers,objFieldNames  'Items recordset object    
Dim strSQL,strCustID,strMode
On Error Resume Next
%>
<!-- #INCLUDE VIRTUAL="/samples/dbconx.asp" -->   
<%strMode=Request.QueryString("Mode")
If strMode="Saved" then%>
    <P><H2>Record Saved</H2></P>
    <%strCustID=Request.QueryString("CustID")
Else
	strCustID=Request.Form("CustomerSelect")
End If%>
<%
objConn.Open strConnect
'*** Open Customer Records ***

strSQL="SELECT * FROM Customers WHERE CustomerID='" & strCustID & "'"
Set rstCustomers=objConn.Execute(strSQL)
%> 
<FORM ID='Customers' NAME='Customers' Action='CustomerList.asp?CustomerSelect=<%=strCustID%>' METHOD='POST' LANGUAGE='javascript'>
<TABLE Border=0 WIDTH=100%>
<TR><TR></TR>
<TD ALIGN='Right'WIDTH=10%>Company Name:</TD><TD WIDTH=20%><INPUT TYPE='Text' Name='CompanyName' VALUE='<%=rstCustomers("CompanyName")%>'></TD>
<TD ALIGN='Right'WIDTH=10%>Contact Name:</TD><TD WIDTH=20%><INPUT TYPE='Text' Name='ContactName' VALUE='<%=rstCustomers("ContactName")%>'></TD>
<TD ALIGN='Right'WIDTH=10%>Contact Title:</TD><TD WIDTH=20%><INPUT TYPE='Text' Name='ContactTitle' VALUE='<%=rstCustomers("ContactTitle")%>'></TD>
</TR>
<TR BGCOLOR=DeepSkyBlue><TD ALIGN='Center' WIDTH=100% COLSPAN=6>Location</TD></TR>
<TR>
<TD ALIGN='Right'>Address:</TD><TD ALIGN='Left' COLSPAN=4><TEXTAREA Name='Address'ROWS=3 COLS=65 WRAP><%=rstCustomers("Address")%></TEXTAREA></TD>
</TR>
<TR>
<TD ALIGN='Right'>City:</TD><TD><INPUT TYPE='Text' Name='City' VALUE='<%=rstCustomers("City")%>'></TD>
<TD ALIGN='Right'>Region:</TD><TD><INPUT TYPE='Text' Name='Region' VALUE='<%=rstCustomers("Region")%>'></TD>
</TR>
<TR>
<TD ALIGN='Right'>Postal Code:</TD><TD><INPUT TYPE='Text' Name='PostalCode' VALUE='<%=rstCustomers("PostalCode")%>'></TD>
<TD ALIGN='Right'>Country:</TD><TD><INPUT TYPE='Text' Name='Country' VALUE='<%=rstCustomers("Country")%>'></TD></TR>
</TR>
<TR BGCOLOR=DeepSkyBlue><TD ALIGN='Center' WIDTH=100% COLSPAN=6>Contact Numbers</TD></TR>
<TR>
<TD ALIGN='Right'>Phone:</TD><TD><INPUT TYPE='Text' Name='Phone' VALUE='<%=rstCustomers("Phone")%>'></TD>
<TD ALIGN='Right'>Fax:</TD><TD><INPUT TYPE='Text' Name='Fax' VALUE='<%=rstCustomers("Fax")%>'></TD></TR>
</TR>
<TR><TD><INPUT TYPE='Hidden' Name='CustomerID' VALUE='<%=strCustID%>'></TD></TR><TR></TR>
<TD ALIGN="Center" WIDTH=100% COLSPAN=5>
<INPUT TYPE="Submit" VALUE="Save" ID='Save' NAME='Save' onClick="document.Customers.action='SaveRecord.asp';document.Customers.submit();">
&nbsp;&nbsp;
<INPUT TYPE="Button" VALUE='Back'ID='Back' NAME='Back' onClick="document.Customers.submit();">
</TD>
</TABLE>
</FORM>
<%
rstCustomers.Close
objConn.Close
set rstCustomers = Nothing
set objConn = Nothing
%>
</BODY>
</HTML>

'CustomerOrders.asp'

Code:

<%@ Language=VBScript %>
<%Option Explicit %>
<!-- #INCLUDE VIRTUAL="/samples/adovbs.inc" -->
<HTML>
<HEAD><SCRIPT Language="javascript">
</SCRIPT>
<TITLE>Customer Orders</TITLE>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY language="javascript">
<CENTER><H2>Orders</H2></CENTER>
<TABLE Border=1 WIDTH=100%>
<TR ALIGN="CENTER" BGCOLOR="#FFFFC6">
<%
Dim rstCustOrders,objFieldsNames  'Items recordset object    
Dim strSQL,strCustID,strHeader
Dim currentPage,RowCount,i
%>
<!-- #INCLUDE VIRTUAL="/samples/dbconx.asp" -->   
<%
currentPage=Request.QueryString("Page")

If currentPage = "" then 
	currentPage = 1
	strCustID=Request.Form("CustomerSelect")
	
Else
	strCustID=Request.QueryString("CustomerSelect")
End If


objConn.Open strConnect 

'*** Open Customer Records ***

strSQL="SELECT * FROM Orders WHERE CustomerID='" & strCustID & "'"
Set rstCustOrders=server.CreateObject("ADODB.Recordset")
rstCustOrders.PageSize = 5  '*** Records per page ***
rstCustOrders.Open strSQL,objConn,adOpenStatic
rstCustOrders.AbsolutePage = CInt(currentPage) '*** Get this page of records ***
RowCount = 0

'*** Display only if there are Items ***
If Not rstCustOrders.EOF  Then
   '*** Loop through Field Names and display ***
   For Each objFieldsNames In rstCustOrders.Fields
	    'Exclude listed fields
		Select Case objfieldsNames.name
		  Case "OrderID","CustomerID","EmployeeID"
		  Case Else
			   strHeader="<TD>" & objFieldsNames.name & "</TD>"
			   Response.write strHeader	
		   End Select
   Next%>
   </TR>
   
   <%'*** Now loop through the records and display ***
   While Not rstCustOrders.EOF and RowCount < rstCustOrders.PageSize%>
	   <TR ALIGN="CENTER" BGCOLOR="#31B5D6">
       <%For Each objFieldsNames In rstCustOrders.Fields
			    'Exclude listed fields
			    Select Case objfieldsNames.name
			    Case "OrderID","CustomerID","EmployeeID"
			    Case Else%>
				     <TD><FONT SIZE="1">
				     <%If isnull(objFieldsNames.value)or objFieldsNames.value="" then %>
                        &nbsp;
				     <%Else%>
					    <%Response.Write objFieldsNames.value %>
				     <%End if%>
				     </A></FONT></TD>
			    <%End Select%>
		<%Next%>
		</TR>
		<%rstCustOrders.MoveNext
		RowCount = RowCount + 1
	Wend%>		
    </TABLE>
    <P>
    <CENTER>
    Jump to Page: 
	<% For i = 1 to rstCustOrders.PageCount %>
		<A href="CustomerOrders.asp?page=<%=i%>&CustomerSelect=<%=strCustID%>"><%=i%></A>&nbsp;
	<% Next%>
    <% '*** Only show the previous button if we are Not on the first page ***%>
    </CENTER>
<%Else%>
	</TABLE>
    <P><CENTER>No Orders for this customer</CENTER></P><BR><BR>
<%End IF%>
        
        
        
        
<DIV Align=center>
<FORM NAME='CustOrders' Action="CustomerList.asp?CustomerSelect=<%=strCustID%>" METHOD='POST' LANGUAGE='javascript'>
<TR><TR></TR><TR></TR><TR></TR>
<TD ALIGN="Center" WIDTH=100% COLSPAN=12><INPUT TYPE="Button" VALUE='Back'ID='Back' NAME='Back' onClick="document.CustOrders.submit();"></TD>
</TABLE>
</FORM>
</DIV>
<%
rstCustOrders.Close
objConn.Close
set rstCustOrders = Nothing
set objConn = Nothing
%>
</BODY>
</HTML>

'dbconx.asp'

Code:

<%
Dim strConnect
Dim objConn
Dim strLogID,strPasswrd
'*********************************************************************************************************
' use this connect string for the SQL Server tables
' change the following
'	YourServer	to the name of your SQL Server
'	YourDB		to the name of your SQL Server database
'	YourUID		to your SQL Server logon name
'	YourPWD		to your SQL Server password
'strConnect = "Driver={SQL Server}; Server=rvlaptop; Database=HomeInventory; UID=sa; PWD=cowgrils2"
'*********************************************************************************************************
'Get Login info
'strLogID=Request.Cookies("RaySite")("LoginID")
'strPasswrd=Request.Cookies("RaySite")("Password")
strLogID=""
strPasswrd=""

'**** OLEDB provider for ODBC ***
'strConnect = "Driver={SQL Server}; Server=YourServer; Database=YourDB"
'strConnect=strConnect & "; UID=" & strLogID &"; PWD=" & strPasswrd

'*** OLEDB provider for SQL ***
'strConnect = "Provider =SQLOLEDB; DataSource=YourServer; Initial Catalog=YourDB"
'strConnect=strConnect & "; User ID=" & strLogID &"; Password=" & strPasswrd

Set objConn=Nothing
set objConn = server.CreateObject("ADODB.Connection") 





'*********************************************************************************************************
' use this connect string for the Access tables
' change the following
' YourDB		to the physical path of your database
'strConnect = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=YourDB"
strConnect="Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\InetPub\wwwroot\samples\nwind.mdb"

'use this connect string for a DSN data store
' change the following
'	DSN=YourDSNName
'strConnect = "DSN=YourDSNName"
'strConnect = "DSN=Contact"
'*********************************************************************************************************
%>

'SaveRecord.asp'

Code:

<%@ Language=VBScript %>
<%Option Explicit%>
<!-- #INCLUDE VIRTUAL="/samples/adovbs.inc" -->
<!-- #INCLUDE VIRTUAL="/samples/dbconx.asp" --> 
<% 
Dim rstCustomers
Dim strSQL,strCustID
strCustID=Request.Form("CustomerID")
objConn.Open strConnect 
strSQL="UPDATE Customers SET CompanyName='"& Request.Form("CompanyName")&"',"
strSQL=strSQL & "ContactName='"& Request.Form("ContactName")&"',"
strSQL=strSQL & "Address='"& Request.Form("Address")&"',"
strSQL=strSQL & "City='"& Request.Form("City")&"',"
strSQL=strSQL & "Region='"& Request.Form("Region")&"',"
strSQL=strSQL & "PostalCode='"& Request.Form("PostalCode")&"',"
strSQL=strSQL & "Country='"& Request.Form("Country")&"',"
strSQL=strSQL & "Phone='"& Request.Form("Phone")&"',"
strSQL=strSQL & "Fax='"& Request.Form("Fax")&"'"
strSQL=strSQL & "  WHERE CustomerID='" & strCustID & "'"

Set rstCustomers=objConn.Execute(strSQL)
objConn.Close
set rstCustomers = Nothing
Response.Redirect "CustomerEdit.asp?Mode=Saved&CustID="& strCustID

%>

**Hellbent** · Jan 14, 2002 21:22

Does that help you at all sportsman?

**sportsman** · Jan 17, 2002 14:30

Thanks Hellbent,

Been away for a while, sorry for not getting back to you sooner. The code is great.

Again, thanks

SpMn

**Hellbent** · Jan 17, 2002 17:39

No problem glad to be of service.

User Tag List

Thread: Posting data to microsoft access

Thread Tools

Display

Posting data to microsoft access

Bookmarks

Bookmarks

Posting Permissions