Help with Access DB

Associate
Joined
6 Jun 2004
Posts
93
Location
London
Can anyone help! THis is done in classic ASP
I’ve got a form that submits to an Access DB. It’s a simple form with some JavaScript validations. What I need to do next is to display the data of the person who completed the form and submitted it into another or same page and also them to print it.

For example if I fill in a form then press submit then it will display all my information just submitted, including an Auto number that is generated in access.. I will then be able to print the page sign it and send it to the department collecting the information!

Hope this makes sense,
I’ve managed to display all the data but at the moment it’s displaying everything in DB, and all I want is the record of the person filling the form.

Can this be done!

Here is the code that displays all the data from DB

Code:
<%@ LANGUAGE="VBSCRIPT" %>
<%   
Option Explicit

Dim objConn						' Our Connection Object
Dim objRS							' Our Recordset Object
Dim strSQL						  ' Our SQL String to access the database
Dim strConnection			 ' Our Connection string to access the database
Dim i	
Dim VacancyNo, JobTitle							' a counter variable

' -- Create objects
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

' -- Connection String Value
strConnection = Server.mapPath("data/jcage.mdb")

' -- Open the Connection
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strConnection & ";Persist Security Info=False" 

' -- Our SQL Statement
strSQL = "SELECT * FROM telltale WHERE JobTitle = '" & Request.form("JobTitle") & "'"  & "ORDER BY VacancyNo DESC"
' -- Populate our Recordset with data
set objRS = objConn.Execute (strSQL)


if (objRS.BOF and objRS.EOF) then
	response.write "No records found"
	response.end
End if
'----------------------------------------------------------------------
' Begin HTML output
'----------------------------------------------------------------------
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML>
<HEAD>
	<TITLE>10 Minute Sample.  Asp Primer 1: Accesing Dat from a Database</TITLE>
</HEAD>

<BODY>
<TABLE BORDER="1" CELLPADDING="2" CELLSPACING="1" WIDTH="100%">
<%
	' -- Output the Field Names as the first row in the table
	Response.Write "<TR BGCOLOR=""#CCCCCC"">"
	For i = 0 to objRS.Fields.Count - 1
		Response.Write "<TH><FONT FACE=""ARIAL"" SIZE=""2"">" & objRS.Fields(i).Name & "</FONT></TH>"
	Next
	Response.write "</TR>"
	' -- Now output the contents of the Recordset
	objRS.MoveFirst
	Do While Not objRS.EOF
		' -- output the contents
		Response.Write "<TR>"
		For i = 0 to objRS.Fields.Count - 1
			Response.Write "<TD><FONT FACE=""ARIAL"" SIZE=""1"">" & objRS.Fields(i) & "</FONT></TD>"
		Next
		Response.write "</TR>"
		' -- move to the next record
		objRS.MoveNext
	Loop

	objRS.Close
	set objRS = Nothing
	objConn.Close
	set objConn = Nothing
	


	
 %>
</TABLE>


</BODY>
</HTML>
<%
'----------------------------------------------------------------------
' End HTML Output
'----------------------------------------------------------------------

'----------------------------------------------------------------------
' All ASP post processing code goes here, as well as 
' sub routines and functions
'----------------------------------------------------------------------


 %>
 
Associate
OP
Joined
6 Jun 2004
Posts
93
Location
London
ha What?

Hi Berserker, Don.
Not really sure what you mean by little 'less shouting'. How do hear shouting in an forum post? i'd appreciate if you can clarify that!

thanks
 
Associate
OP
Joined
6 Jun 2004
Posts
93
Location
London
Can no one help me on this

Is there no one who knows asp on this forum?

Berserker, thanks for helping and cleaning up my posts!
I am kinda new at this thing so i am not quite aware of forum disciplines!

thanks again for your help
 
Associate
Joined
20 Oct 2002
Posts
680
Well currently its displaying all the records (of the same JobTitle) because you are asking it too here...

SELECT * FROM telltale WHERE JobTitle = '" & Request.form("JobTitle") & "'" & "ORDER BY VacancyNo DESC"

You need to make use of the autonumber in the access database by only pulling out the number of the record that was just added to the the database. For this you need to get the number out as a variable.

Something like...

getID = objConn.Execute("SELECT Max(NameOfAutoNumberFieldHere) as MaxID FROM telltale")
var_autonumber = getID("MAXID")

This gives you the variable 'var_autonumber' set to the number of the record you just added. You could then use this in the sql statement that selects the correct record. I.e. replace your SQL with this...

"SELECT * FROM telltale WHERE NameOfAutoNumberField = " &var_autonumber

I have not done this for a while but the above should work I hope :confused:
 
Associate
OP
Joined
6 Jun 2004
Posts
93
Location
London
Hi,

Thank you so much for writing and help me! I will try this and let you know whether its worked! Once again thanks a lot for all your efforts really appreciate it!
 
Back
Top Bottom