#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2013
    Posts
    7
    Rep Power
    0

    Need some help with my code


    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
    %>
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,608
    Rep Power
    278
    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?
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2013
    Posts
    7
    Rep Power
    0
    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.
  6. #4
  7. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,608
    Rep Power
    278
    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...
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by 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...

    How would i go about fixing it? I am a newbie and confused. I really appreciate you looking at this for me!
  10. #6
  11. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2013
    Posts
    7
    Rep Power
    0
    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.
  12. #7
  13. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,608
    Rep Power
    278
    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
    Code:
    <%
    '************************************************* ***************************************
    '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
    %>
  14. #8
  15. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2013
    Posts
    7
    Rep Power
    0
    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")
  16. #9
  17. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,608
    Rep Power
    278
    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
  18. #10
  19. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2013
    Posts
    7
    Rep Power
    0
    Thanks.. The page still comes up broken... Page can not be displayed....
  20. #11
  21. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,608
    Rep Power
    278
    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
  22. #12
  23. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2013
    Posts
    7
    Rep Power
    0
    I ended up switching the Identity column name in the table to ID and it worked!! Thank you!
  24. #13
  25. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,608
    Rep Power
    278
    so, it was sql error identity is reserved word
  26. #14
  27. No Profile Picture
    Registered User

    Join Date
    Oct 2012
    Location
    Russia
    Posts
    8
    Rep Power
    0
    What necessary words... super, a remarkable idea

Similar Threads

  1. Add code for font size under php echo code
    By newphpcoder in forum HTML, JavaScript And CSS Help
    Replies: 3
    Last Post: November 14th, 2010, 02:17 PM
  2. Replies: 0
    Last Post: November 2nd, 2010, 07:00 PM
  3. Code for reading xml code doesnt work anymore
    By littleNewbie in forum .NET Development
    Replies: 1
    Last Post: May 18th, 2009, 03:23 AM
  4. Moving code to code behind file causing weird problem...
    By palomar in forum Visual Basic Programming
    Replies: 0
    Last Post: June 21st, 2005, 08:10 PM
  5. JavaScript prompt when code is used without [code] tags!
    By baseballdude_ in forum Suggestions & Feedback
    Replies: 9
    Last Post: April 9th, 2005, 02:07 PM

IMN logo majestic logo threadwatch logo seochat tools logo