Need some help with my code

[clydo]

I have an asp page that updates a record in a SQL database. Here is the updatestat.asp page that then goes to a update_formstat.asp page.. the update_formstat.asp page does work. It comes up broken, if i take out the lngRecordNo the page displays but doesn't work as it can't find the ID number...
I cut out some of the html to make it shorter for you to read.....

updatestat.asp


<%
'Dimension variables
Dim todaysDate
Dim adoCon 'Holds the Database Connection Object
Dim rsIssues'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim Page
Dim RowCount
Dim PageCounter

Page = Request.QueryString("Page")

'If there is no page set it to page 1
If Page = "" then
Page = 1
End if

RowCount = 0

Set adoCon = Server.CreateObject("ADODB.Connection")
Set rsIssues = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM dbo.stattbl"
adoCon.Open "Provider=sqloledb;SERVER=TESTDB2.mysite.org;DATAB ASE=nuclear;UID=cardnorth;PWD=hockey1;"
rsIssues.CursorType = 3
rsIssues.PageSize = 10
rsIssues.Open strSQL, adoCon
'rsIssues.AbsolutePage = cInt(Page)
%>
Current page <b><%=Request.Querystring("page")%></b></div>
</td>
</tr>
<tr>
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0" bgcolor="#000000">
<tr align="center" class="body">
<td colspan="5" class="Med_whitetxt"><b> ADMIN</b></td>
</tr>
<tr>
<td>
<input type="BUTTON" value="SUBMIT POSTING" onClick="window.location.href='statlogsubmit.asp'" name="BUTTON2"/>
</td>
<td>
<input type="BUTTON" value="VIEW POSTINGS" onClick="window.location.href='viewallstat.asp'" name="BUTTON22"/>
</td>
<td>
<input type="BUTTON" value="EDIT RECORD" onClick="window.location.href='updatestat.asp'" name="BUTTON3"/>
</td>
<td>
<input type="BUTTON" value="DELETE RECORD" onClick="window.location.href='delete_recordstat.a sp'" name="BUTTON32"/>
</td>
<td>
<input type="BUTTON" value="Filter Records" onClick="window.location.href='filterstat.asp'" name="BUTTON32"/>
</td>
</tr>
</table>
</td>
</tr>
<% Do while not rsIssues.eof and RowCount < rsIssues.PageSize %>
<tr>
<td>
<table cellspacing="0" cellpadding="0" border="1" width="100%">
<tr>
<td align="left" colspan="2">&nbsp; </td>
</tr>
<tr>
<td align="left" width="30%"> <strong>ID:</strong>
</td>
<td align="left">
<% Response.Write (rsIssues("Identity")) %>
</td>
</tr>
<tr>
<td align="left" width="30%"> <strong>Date:</strong>
</td>
<td align="left">
<% Response.Write (rsIssues("date")) %>
</td>
</tr>
<tr>
<td align="left"><b>First Name<strong>:</strong>
</b></td>
<td align="left">
<% Response.Write (rsIssues("firstname")) %>
</td>
</tr>
<tr>
<td align="left"><b>Last Name<strong>:</strong> </b></td>
<td align="left">
<% Response.Write (rsIssues("lastname")) %>
</td>
</tr>
<tr>
<td align="left"><b>MRN<strong>:</strong> </b></td>
<td align="left">
<% Response.Write (rsIssues("mrn")) %>
</td>
</tr>
<tr>
<td align="left"><b>Date of Study<strong>:</strong> </b></td>
<td align="left">
<% Response.Write (rsIssues("dos")) %>
</td>
</tr>
<tr>
<td align="left"><b>Study<strong>:</strong> </b></td>
<td align="left">
<% Response.Write (rsIssues("study")) %>
</td>
</tr> <tr>
<td align="left"><b>Date Of Study<strong>:</strong> </b></td>
<td align="left">
<% Response.Write (rsIssues("dos")) %>
</td>
</tr> <tr>
<td align="left"><b>Referring Physician<strong>:</strong> </b></td>
<td align="left">
<% Response.Write (rsIssues("referring")) %>
</td>
</tr> <tr>
<td align="left"><b>Reading Physician<strong>:</strong> </b></td>
<td align="left">
<% Response.Write (rsIssues("reading")) %>
</td>
</tr> <tr>
<td align="left"><b>IN/OUT/ES<strong>:</strong> </b></td>
<td align="left">
<% Response.Write (rsIssues("ioe")) %>
</td>
</tr> <tr>
<td align="left"><b>Location<strong>:</strong> </b></td>
<td align="left">
<% Response.Write (rsIssues("location")) %>
</td>
</tr> <tr>
<td align="left"><b>Tech<strong>:</strong> </b></td>
<td align="left">
<% Response.Write (rsIssues("tech")) %>
</td>
</tr> <tr>
<td align="left"><b>Stat/Routine<strong>:</strong> </b></td>
<td align="left">
<% Response.Write (rsIssues("stat")) %>
</td>
</tr>
<tr>
<td align="left"><strong>Diagnosis</strong></td>
<td align="left"><% Response.Write (rsIssues("diagnosis")) %></td>
</tr>
<tr>
<td align="left"><strong>Insurance</strong></td>
<td align="left"><% Response.Write (rsIssues("insurance1")) %></td>
</tr>
<tr>
<td align="left"><strong>Write in Insurance </strong></td>
<td align="left"><% Response.Write (rsIssues("insurance2")) %></td>
</tr>
<tr>
<td align="left"><strong>Authorization Number </strong></td>
<td align="left"><% Response.Write (rsIssues("authno")) %></td>
</tr>
<tr>
<td align="left"><b>Other Comments<strong>:</strong>
</b></td>
<td align="left">
<% Response.Write (rsIssues("comments")) %>
</td>
</tr>
<tr>
<td align="left">&nbsp;</td>
<td align="left"><strong><a href="update_formstat.asp?ID=<% Response.Write (rsIssues("Identity")) %>">Update Entry</a></strong> </td>
</tr>
</table>
<table width="100%" border="0">
<tr>
<td class="body">&nbsp;</td>
</tr>
</table>
</td>
</tr>
<% 'Write the HTML to display the current record in the recordset
rsIssues.Movenext
RowCount = RowCount + 1
Loop
%>
<tr>
<td bordercolor="#666666" align="Middle">
<%For PageCounter = 1 to rsIssues.PageCount %>
<a href='update.asp?Page=<%=PageCounter %>' class='myclass'>Page
<%=PageCounter %></a>
<%Next%>
</td>
</tr>
</table>
</td>
</tr>
</table>
<p><br>
</p>
<blockquote>&nbsp;</blockquote>
</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
</tbody>
</table>
<!-- InstanceEndEditable -->
</td>
</tr>
</tbody>
</table>



update_formstat.asp




<%

'************************************************* ***************************************



'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsIssues 'Holds the recordset for the record to be updated
Dim strSQL 'Holds the SQL query for the database
Dim lngRecordNo 'Holds the record number to be updated


'Read in the record number to be updated
'lngRecordNo = CLng(Request.QueryString("ID"))
Set lngRecordNo = CLng(Request.QueryString("identity"))

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")
'Create an ADO recordset object

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=Issues"
adoCon.Open "Provider=sqloledb;SERVER=TESTDB2.mysite.org;DATAB ASE=nuclear;UID=cardnorth;PWD=hockey1;"


Set rsIssues = Server.CreateObject("ADODB.Recordset")


'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM stattbl WHERE Identity=" & lngRecordNo



'Set the cursor type we are using so we can navigate through the recordset
rsIssues.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsIssues.LockType = 3

'Open the recordset with the SQL query
'adoCon.Open adoCon
rsIssues.Open strSQL, adoCon

%>

<table width="800" border="0" cellspacing="1" cellpadding="0" class="body">
<tr class="bgwhite">
<td align="center"><table width="100%" border="0" cellspacing="1" cellpadding="0" bgcolor="#000000">
<tr align="center" class="body">
<td colspan="5" class="Med_whitetxt"><b> ADMIN</b></td>
</tr>
<tr>
<td><input type="BUTTON" value="SUBMIT POSTING" onClick="window.location.href='statlogsubmit.asp'" name="BUTTON2"/>
</td>
<td><input type="BUTTON" value="VIEW POSTINGS" onClick="window.location.href='viewallstat.asp'" name="BUTTON22"/>
</td>
<td><input type="BUTTON" value="EDIT RECORD" onClick="window.location.href='updatestat.asp'" name="BUTTON3"/>
</td>
<td><input type="BUTTON" value="DELETE RECORD" onClick="window.location.href='delete_recordstat.a sp'" name="BUTTON32"/>
</td>
<td><input type="BUTTON" value="Filter Records" onClick="window.location.href='filterstat.asp'" name="BUTTON32"/>
</td>
</tr>
</table></td>
</tr>
</table>


<form name="form" method="post" action="update_entrystat.asp">

<table cellspacing="0" cellpadding="0" border="1" width="100%">
<tr>
<td align="left" colspan="2">&nbsp;</td>
</tr>
<tr align="center" bgcolor="#CCCCCC">
<td colspan="2">Issue #
<% Response.Write (rsIssues("identity")) %> </td>
</tr>
<tr>
<td align="left" width="30%"> <strong>Date:</strong> </td>
<td align="left">
<input type="text" name="date" size="40" value="<% = rsIssues("date") %>"> </td>
</tr>
<tr>
<td align="left"><b>First Name<strong>:</strong> </b></td>
<td align="left">
<input type="text" name="firstname" size="40" value="<% = rsIssues("firstname") %>"> </td>
</tr>
<tr>
<td align="left"><b>Location<strong>:</strong> </b></td>
<td align="left">
<input type="text" name="lastname" size="40" value="<% = rsIssues("lastname") %>"> </td>
</tr>

<tr>
<td align="left"><b>MRN<strong>:</strong> </b></td>
<td align="left">
<input name="mrn" type="text" value="<% = rsIssues("mrn") %>" size="40"> </td>
</tr>

<tr>
<td align="left">&nbsp;</td>
<td align="left">&nbsp;</td>
</tr>
</table>
<p/></p>
<div align="center">
<input type="hidden" name="ID" value="<% = rsIssues("ID") %>">
<input type="submit" value="Update" name="submit">
</div>
</form>
</td>
</tr>
</table>
<p><br>
</p>
<blockquote>&nbsp;</blockquote>
</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
</tbody>
</table>



<!-- InstanceEndEditable -->
</td>
</tr>
</tbody>
</table>




<%
'Reset server objects
rsIssues.Close
Set rsIssues = Nothing
Set adoCon = Nothing
%>

[gk53]

Why you "take out the lngRecordNo"? You use that on you SQL statement and it is required value for that page. What you are trying to achieve?

[clydo]

I have a page that i click update and that record appears with the fields values in updateable form fields. I can then change the values and update the database for that record.

[gk53]

So, you have updatestat.asp page which displayed table with all records from database and link to "Update Entry" - update_formstat.asp page. That page displaying data from database for one record and as you said it works. You sending ID in querystring and lngRecordNo = Request.QueryString("ID") you do not need to convert to integer because you not using lngRecordNo as number, but you need to have lngRecordNo for
strSQL = "SELECT * FROM stattbl WHERE Identity=" & lngRecordNo
Your line
Set lngRecordNo = CLng(Request.QueryString("identity"))
does not make sense because
1. it is no object lngRecordNo it is variable, so set not need it
2. Request.QueryString("identity") is empty, you have link update_formstat.asp?id=<% Response.Write (rsIssues("Identity")) %>, so in update_formstat.asp Request.QueryString("identity") is empty, but Request.QueryString("ID") has value...

[clydo]

So, you have updatestat.asp page which displayed table with all records from database and link to "Update Entry" - update_formstat.asp page. That page displaying data from database for one record and as you said it works. You sending ID in querystring and lngRecordNo = Request.QueryString("ID") you do not need to convert to integer because you not using lngRecordNo as number, but you need to have lngRecordNo for
strSQL = "SELECT * FROM stattbl WHERE Identity=" & lngRecordNo
Your line
Set lngRecordNo = CLng(Request.QueryString("identity"))
does not make sense because
1. it is no object lngRecordNo it is variable, so set not need it
2. Request.QueryString("identity") is empty, you have link update_formstat.asp?id=<% Response.Write (rsIssues("Identity")) %>, so in update_formstat.asp Request.QueryString("identity") is empty, but Request.QueryString("ID") has value...

How would i go about fixing it? I am a newbie and confused. I really appreciate you looking at this for me!

[clydo]

Updatestat.asp works but when i click on the Update Entry link it goes to a broken page update_formstat.asp?ID=11 or whatever record i click on ie; 1, 2, 3 or whatever.

[gk53]

your sql should be
strSQL = "SELECT * FROM stattbl WHERE Identity=" & request.Querystring("ID")
and the rest should work if you send good ID (I mean if your sql return from database record)
So code below should work

<%
'************************************************* ***************************************
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsIssues 'Holds the recordset for the record to be updated
Dim strSQL 'Holds the SQL query for the database
Dim lngRecordNo 'Holds the record number to be updated

'Read in the record number to be updated
lngRecordNo = CLng(Request.QueryString("ID"))
'Set lngRecordNo = CLng(Request.QueryString("identity")) ' that statement is wrong
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=Issues"
adoCon.Open "Provider=sqloledb;SERVER=TESTDB2.mysite.org;DATAB ASE=nuclear;UID=cardnorth;PWD=hockey1;"
'Create an ADO recordset object
Set rsIssues = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM stattbl WHERE Identity=" & lngRecordNo
'Set the cursor type we are using so we can navigate through the recordset
'rsIssues.CursorType = 2 ' you do not need that now it is default
'Set the lock type so that the record is locked by ADO when it is updated
'rsIssues.LockType = 3 ' you do not need that now it is default
'Open the recordset with the SQL query
'adoCon.Open adoCon
rsIssues.Open strSQL, adoCon
if rsIssues.eof then
    response.write "No records found"
    response.Flush
    response.End
end if 
%>
<form name="form" method="post" action="update_entrystat.asp">

<table cellspacing="0" cellpadding="0" border="1" width="100%">
<tr>
<td align="left" colspan="2">&nbsp;</td>
</tr>
<tr align="center" bgcolor="#CCCCCC">
<td colspan="2">Issue #
<% Response.Write (rsIssues("identity")) %> </td>
</tr>
<tr>
<td align="left" width="30%"> <strong>Date:</strong> </td>
<td align="left">
<input type="text" name="date" size="40" value="<% = rsIssues("date") %>"> </td>
</tr>
<tr>
<td align="left"><b>First Name<strong>:</strong> </b></td>
<td align="left">
<input type="text" name="firstname" size="40" value="<% = rsIssues("firstname") %>"> </td>
</tr>
<tr>
<td align="left"><b>Location<strong>:</strong> </b></td>
<td align="left">
<input type="text" name="lastname" size="40" value="<% = rsIssues("lastname") %>"> </td>
</tr>

<tr>
<td align="left"><b>MRN<strong>:</strong> </b></td>
<td align="left">
<input name="mrn" type="text" value="<% = rsIssues("mrn") %>" size="40"> </td>
</tr>

<tr>
<td align="left">&nbsp;</td>
<td align="left">&nbsp;</td>
</tr>
</table>
<p/></p>
<div align="center">
<input type="hidden" name="ID" value="<% = rsIssues("ID") %>">
<input type="submit" value="Update" name="submit">
</div>
</form>
</td>
</tr>
</table>
<p><br>
</p>
<blockquote>&nbsp;</blockquote>
</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
</tbody>
</table>
<!-- InstanceEndEditable -->
</td>
</tr>
</tbody>
</table>
<%
'Reset server objects
rsIssues.Close
Set rsIssues = Nothing
Set adoCon = Nothing
%>

[clydo]

Thank you for getting back to me!
So use the code you posted above but replace
strSQL = "SELECT * FROM stattbl WHERE Identity=" & lngRecordNo
with
strSQL = "SELECT * FROM stattbl WHERE Identity=" & request.Querystring("ID")

[gk53]

you can use just
strSQL = "SELECT * FROM stattbl WHERE Identity=" & request.Querystring("ID")
and forget about lngRecordNo or like I posted code in my prev post

[clydo]

Thanks.. The page still comes up broken... Page can not be displayed....

[gk53]

Do you have line number or error you get?
I guess you error on reading data from database. Check your connection string and connection object status after you open connection it should be 1

[clydo]

I ended up switching the Identity column name in the table to ID and it worked!! Thank you!

[gk53]

so, it was sql error identity is reserved word

[KileHildKnope]

What necessary words... super, a remarkable idea