SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2000
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Posting data to microsoft access

    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.

  2. #2
    Chikin Choker Hellbent's Avatar
    Join Date
    May 2001
    Location
    Arlington, Texas
    Posts
    210
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    
    %>
    Straight from the TP! And I don't mean the Trailer Park.

  3. #3
    Chikin Choker Hellbent's Avatar
    Join Date
    May 2001
    Location
    Arlington, Texas
    Posts
    210
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Does that help you at all sportsman?
    Straight from the TP! And I don't mean the Trailer Park.

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2000
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Hellbent,

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

    Again, thanks

    SpMn

  5. #5
    Chikin Choker Hellbent's Avatar
    Join Date
    May 2001
    Location
    Arlington, Texas
    Posts
    210
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No problem glad to be of service.
    Straight from the TP! And I don't mean the Trailer Park.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •