submit form to database - retrieve info

[angeliamy]

I have an asp page that is submitting information from a form to an access database. Once it is submitted I want to immediately retrieve that data and display it on the web page. The information is being submitted but when it retrieves the information it is bringing back the second to last record not the last record. My sql command for the access database is SQL = "SELECT TOP 1 * FROM page1 ORDER BY ID DESC;"
and therefore, theoretically, should work. It works in Access. Any suggestions?

 

[Lothario]

Try

@@Identity

-L

 

[Lothario]

I meant try:

SELECT @@IDENTITY FROM....

-L

 

[angeliamy]

I've tried that before but tried it again. I get the following error: [Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Not sure what else to try...

 

[Lothario]

Please post your query...

-L

 

[angeliamy]

The query is in the original inquiry. But here it is again - SQL = "SELECT TOP 1 * FROM page1 ORDER BY ID DESC;"

This works in Access but won't work on the page. Now the query is on a page that is also used to update the record once it is seen. I think I'll eliminate everything from the page but the query and see what happens. Unless you have a suggestion.

Thanks,

 

[mwolf00]

ID could be a keyword, try

SQL = "SELECT TOP 1 * FROM page1 ORDER BY [ID] DESC;"


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

Tsuna​

 

[angeliamy]

That didn't work either... Is there another way to request the last record entered in Access? Perhaps if I change the query alltogether....

 

[mwolf00]

It's very strange that a query that runs in Access cannot be executed in ASP - can you post the code where you execute it?

Does this run?

SQL = "SELECT * FROM page1"


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

Tsuna​

 

[angeliamy]

That works.... but it will always bring back #198 in the database for some reason.

These codes, pages are pretty lengthy...the first page is the form, it is sent to the asp page that updates the database and then is this page is included in that one. Here is the code that is used to show the record:
---------------------------------------------------------
<%
Session("DatabasePath") = "g:/dtbase/ciaf.mdb"

'-- Declare your variables

Dim DataConnection, cmdDC, RecordSet
Dim RecordToEdit, Updated, RecordToDelete

'-- Create object and open database

Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Session("DatabasePath") & ";"

Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConnection

Server.ScriptTimeout = 2147483647


'-- RecordToEdit SQL

sql = "select * from page1;"

'-- SQL = "SELECT TOP 1 * FROM page1 ORDER BY ID DESC;"

cmdDC.CommandText = SQL
Set RecordSet = Server.CreateObject("ADODB.Recordset")

'-- Cursor Type, Lock Type

'-- ForwardOnly 0 - ReadOnly 1
'-- KeySet 1 - Pessimistic 2
'-- Dynamic 2 - Optimistic 3
'-- Static 3 - BatchOptimistic 4


RecordSet.Open cmdDC, , 0, 3

If Request.Form("btnUpdate") = "Update" Then

RecordSet.Fields("title") = Request.Form("txttitle")
RecordSet.Fields("edyr") = Request.Form("txtedyr")
RecordSet.Fields("updby") = Request.Form("txtupdby")
RecordSet.Fields("goal") = Request.Form("txtgoal")
RecordSet.Fields("expt") = Request.Form("txtexpt")
RecordSet.Fields("conc") = Request.Form("txtconc")
RecordSet.Fields("sms") = Request.Form("txtsms")
RecordSet.Fields("gle") = Request.Form("txtgle")
RecordSet.Fields("cp") = Request.Form("txtcp")
RecordSet.Fields("obj") = Request.Form("txtobj")
RecordSet.Fields("gedtrw") = Request.Form("txtgedtrw")
RecordSet.Fields("inac") = Request.Form("txtinac")
RecordSet.Fields("asses") = Request.Form("txtasses")
RecordSet.Fields("maplo") = Request.Form("txtmaplo")
RecordSet.Fields("resource") = Request.Form("txtresource")
RecordSet.Update
Updated = "True"

End If
Server.ScriptTimeout = 2147483647


%>

<form action="edit_title.asp?ID=<%= RecordToEdit %>" method="post">


<% If Updated = "True" Then %>


<b><font size=4 color="#330099"> <%= RecordSet.Fields("id") %></b>'s curriculum has been updated.</font><P>



<%
Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Session("DatabasePath") & ";"

Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConnection

Set cmdDC = Nothing
DataConnection.Close
Set DataConnection = Nothing

End If

Server.ScriptTimeout = 2147483647
'-- Go to the first record

If Not RecordSet.BOF Then
RecordSet.MoveFirst
%>

<font size=4 color=#cc0000>Editing information for <b><i></font><font color=#333399 size=4.5><%= RecordSet.Fields("id") %></i></font></b><p>

<table border="1" cellpadding="4" cellspacing="4" align="center" bordercolordark=#660099>

<tr><th colspan="2"><font color="#000080" size="4"><%= RecordSet.Fields("title") %></font></th></tr>
<td>Date Submitted: <%=RecordSet.Fields("dtdate")%></td><td>ID: <%=RecordSet.Fields("id")%></td>
<tr>
<td colspan="2"><strong>Title:</strong><br><input type="text" name="txttitle" maxlength="200" size="100" value="<%= RecordSet.Fields("title") %>"></td>
</tr>
<tr>
<td colspan="2"><strong>Educational Year:</strong><br><input type="text" name="txtedyr" maxlength="200" size="100" value="<%= RecordSet.Fields("edyr") %>"></td>
</tr>
<tr>
<tr>
<td colspan="2"><strong>Updated By:</strong><br><input type="text" name="txtupdby" maxlength="200" size="100" value="<%= RecordSet.Fields("updby") %>"></td>
</tr>
<tr>
<td colspan="2"><strong>Strand/Unit:</strong><br><input type="text" name="txtgoal" maxlength="200" size="100" value="<%= RecordSet.Fields("goal") %>"></td>
</tr>
<tr>
<td colspan="2"><strong>Expectations:</strong><br><input type="text" name="txtexpt" maxlength="255" size="100" value="<%= RecordSet.Fields("expt") %>"></td>
</tr>
<tr><td colspan="2"><strong>Concept</strong><br><input type="text" name="conc" size="100" maxlength="255" value="<%= RecordSet.Fields("conc") %>"></td>
</tr>
</table>
<table border="1">
<tr><td colspan="9"><strong>Objective 1</strong></td></tr>
<tr>
<td><strong>Show-Me-Standard</strong><br><input type="text" name="txtsms" maxlength="10" size="4" value="<%= RecordSet.Fields("sms") %>"><br>
<strong>C/P Content/Process</strong><br><input type="text" name="txtcp" maxlength="10" size="4" value="<%= RecordSet.Fields("cp") %>"><br>
<strong>G/E/D/R/T/W</strong><br><input type="text" name="txtgedtrw" maxlength="10" size="12" value="<%= RecordSet.Fields("gedtrw") %>"><br>
</td>
<td><strong>GLE</strong><br><input type="text" name="txtgle" maxlength="10" size="3" value="<%= RecordSet.Fields("gle") %>"></td>
<td><strong>Objective</strong><br><textarea cols="20" rows="10" name="txtobj" value="<%= RecordSet.Fields("obj") %>"></textarea></td>
<td><strong>Instructional Activities</strong><br><textarea cols="20" rows="10" name="txtinac" value="<%= RecordSet.Fields("inac") %>"></textarea></td>
<td><strong>Assessment</strong><br><textarea cols="20" rows="10" name="txtasses" value="<%= RecordSet.Fields("asses") %>"></textarea></td>
<td><strong>Map/<br>Local</strong><br><input type="text" name="txtmaplo" maxlength="5" size="5" value="<%= RecordSet.Fields("maplo") %>"></td>
<td><strong>Resources</strong><br><textarea cols="20" rows="10" name="txtresource" value="<%= RecordSet.Fields("resource") %>" class=textarea></textarea></td>
</tr>
</table>

<p>
<center>
<input type="submit" name="btnUpdate" value="Update">
</center></form>
</form>
<table><tr><td><img src="images/arrow.gif" width="33" height="18" alt="">&nbsp;&nbsp;<a href="list2.asp">Return to Curriculum List</a></td></tr></table>
<%
Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Session("DatabasePath") & ";"

Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConnection

Set cmdDC = Nothing
DataConnection.Close
Set DataConnection = Nothing
End If
%>

 

[angeliamy]

Notice I had commented out the original select statement to try the select all statement again.

 

[mwolf00]

I recommend that you not use the command object for what you are trying to do. Just use a connection object.

set cn = server.createObject("adodb.connection")
cn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Session("DatabasePath") & ";"

SET rs = cn.execute("SELECT TOP 1 * FROM page1 ORDER BY [ID] DESC;")

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

Tsuna​

 

[angeliamy]

It must be in one of my other connecting pages, when i run this page by itself it brings up the correct record...I just can't figure out where it's at. Somehow it is not pulling it up immediately...do you know how to make the page wait until to make sure the record is added?

 

[way2many]

I use something like this:

<%
Dim cn, NewID, intNewID
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db.mdb;
cn.Execute("INSERT INTO tableName(field1, field2) VALUES('value1', 'value2');")
Set NewID = cn.Execute("SELECT @@IDENTITY")
intNewID = NewID(0)

NewID.Close
Set NewID = Nothing
cn.Close
Set cn = Nothing
%>
I found it some time ago on one of the web sites and it worked for me...

 

[angeliamy]

Thanks but all I did was add a 25 second delay with a message for the load and that worked. But I'll try what you have as well. Thanks!