HELP! ASP and SQL problem.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Munzilla (brianwmunz_at_gmail.com)
Date: 10/06/04 

Date: 6 Oct 2004 15:05:39 -0700

Ok, I have an ASP page that I use to add several pieces of information
to a database and also display that information in an edit mode. The
problem is, when I use the page for edit, not all of the info is drawn
out of the database. Some of the information is drawn out and
displayed properly, but other information is not. The strangest part
is if I do a response.write to display the objRS() from the database
of a field that isn't coming up, it works. Here is the code to show
you what I'm talking about.
This is really weird, and any help would be GREATLY appreciated.
Thanks!

<html>
<head>
<title>~Data Entry~</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
</head>
<body>
<%
Dim edit
Dim id
DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString =
"dsn=firms;uid=ssl_firms_admin;pwd=stacibeth;network=dbmssocn"
objConn.Open
DIM objRS
Set objRS = Server.CreateObject("ADODB.Recordset")

DIM mySQL
DIM company, address1, address2, city, state1, zip, county, site,
contact, contactlast, title, phone, email, premium, desc
edit=Request.QueryString("edit")
id=Request.QueryString("id")
if edit="1" then
        mySQL= "SELECT * FROM agencies WHERE agency_id=" & id & ""
        objRS.Open mySQL, objConn
response.write(objRS("phone_number"))
        response.write("foo" & objRS("email_address"))
        company = objRS("company_name")
        address1 = objRS("street_address_1")
        address2 = objRS("street_address_2")
        city = objRS("city")
        county = objRS("county")
        state1 = objRS("state")
        desc = objRS("description")
        site = objRS("website_link")
        contact = objRS("contact_person")
        contactlast = objRS("contact_person_last")
        title = objRS("contact_person_title")
        phone = objRS("phone_number")

        email = objRS("email_address")

        premium = objRS("premium")
        zip = objRS("zip")
end if
DIM objSelectedClassifications
Set objSelectedClassifications = CreateObject("Scripting.Dictionary")
if edit="1" then
        DIM existingQuery
        existingQuery = "SELECT * FROM agency_classification_rel WHERE
agency_id = " & id
        DIM objExistingRS
        SET objExistingRS = Server.CreateObject("ADODB.Recordset")
        objExistingRS.Open existingQuery, objConn
        Do While Not objExistingRS.EOF
                objSelectedClassifications.Add
CSTR(objExistingRS("classification_id")), ""
                objExistingRS.MoveNext
        Loop
        Set objExistingRS = Nothing
end if
DIM categoryQuery, countyQuery
categoryQuery = "SELECT category_id, category_name FROM
agency_categories ORDER BY category_sort"
countyQuery = "SELECT county_id, county_name FROM counties ORDER BY
county_name"
DIM objCategoryRS, objSubcategoryRS, objClassificationRS, objCountyRS
SET objCategoryRS = Server.CreateObject("ADODB.Recordset")
SET objSubcategoryRS = Server.CreateObject("ADODB.Recordset")
SET objClassificationRS = Server.CreateObject("ADODB.Recordset")
SET objCountyRS = Server.CreateObject("ADODB.Recordset")
objCountyRS.Open countyQuery, objConn
Dim countyOptions
countyOptions = "<option value=""0"">None</option>"
Do While Not objCountyRS.EOF
        countyOptions = countyOptions & "<option value=""" &
objCountyRS("county_id") & """"
        if CSTR(objCountyRS("county_id")) = CSTR(county) then
                countyOptions = countyOptions & " selected"
        end if
        countyOptions = countyOptions & ">" & objCountyRS("county_name") &
"</option>"
        objCountyRS.MoveNext
Loop
Set objCountyRS = Nothing
%>
<form name="companyform" method="post" action="companysubmit.asp">
  <table border="0" cellspacing="0" cellpadding="4">
    <tr>
                <td>Company Name: &nbsp;
            <input name="company" type="text" id="company" <% if company <>
"" then %>value="<%=company%>" <%end if%>size="75"></td>
        </tr>
        <tr>
                <td>Street Address 1: &nbsp;
            <input name="address1" type="text" id="address1" <% if address1
<> "" then %>value="<%=address1%>" <%end if%>size="75"></td>
        </tr>
        <tr>
                <td>Street Address 2:&nbsp;&nbsp;
            <input name="address2" type="text" id="address2" <% if address2
<> "" then %>value="<%=address2%>" <%end if%>size="75"></td>
        </tr>
        <tr>
                <td>City: &nbsp;
    <input name="city" type="text" id="city" <% if city <> "" then
%>value="<%=city%>" <%end if%>>
    &nbsp;&nbsp;&nbsp;State: &nbsp;
    <input name="state" type="text" id="state" <% if state1 <> "" then
%>value="<%=state1%>" <%end if%> size="4" maxlength="2">
    &nbsp;&nbsp;Zip: &nbsp;
    <input name="zip" type="text" id="zip" size="10" <% if zip <> ""
then %>value="<%=zip%>" <%end if%> maxlength="5">
    &nbsp;&nbsp;</td>
        </tr>
        <tr>
                <td>County: &nbsp;
                <select name="county">
                <%=countyOptions%>
                </select></td>
    </tr>
        <tr><td>
        <table border="0" cellspacing="0" cellpadding="2"><tr>
        <%
        objCategoryRS.Open categoryQuery, objConn
        Do While Not objCategoryRS.EOF%>
        <td>
        <strong><%=objCategoryRS("category_name")%>:</strong><br>
        <select name="classifications" size="15" multiple>
          <%
          DIM subCategoryQuery
          subCategoryQuery = "SELECT subcategory_id, subcategory_name FROM
agency_subcategories WHERE category_id = " &
objCategoryRS("category_id") & " ORDER BY subcategory_sort"
          objSubCategoryRS.Open subCategoryQuery, objConn
          Do While Not objSubCategoryRS.EOF%>
                <option value="" style="background-color:CC3333"
disabled>-=<%=objSubCategoryRS("subcategory_name")%>=-</option>
          <%
            DIM classQuery
                classQuery = "SELECT classification_id, classification_name from
agency_classifications WHERE subcategory_id = " &
objSubCategoryRS("subcategory_id") & " ORDER BY classification_sort"
                objClassificationRS.Open classQuery, objConn
                Do While Not objClassificationRS.EOF
                  Response.write("<option value=""" &
objClassificationRS("classification_id") & """")
                   if objSelectedClassifications.Exists(CSTR(objClassificationRS("classification_id")))
then Response.write(" selected") end if
                   Response.write(">" & objClassificationRS("classification_name") &
"</option>")
                objClassificationRS.MoveNext
                Loop
                objClassificationRS.Close
          objSubCategoryRS.MoveNext
          Loop
          objSubCategoryRS.Close%>
        </select>
        </td>
        <%
        objCategoryRS.MoveNext
        Loop
        Set objCategoryRS = Nothing
        Set objSubCategoryRS = Nothing
        Set objClassificationRS = Nothing
        %>
        </tr></table>
        </td></tr>
        <tr>
                <td>Web Site: &nbsp;
                <input name="site" type="text" id="site" <% if site <> "" then
%>value="<%=site%>" <%end if%> size="60"></td>
        </tr>
        <tr>
                <td>Contact Person First Name: &nbsp;
                <input name="contact" type="text" id="contact" <% if contact <> ""
then %>value="<%=contact%>" <%end if%>>
            &nbsp;&nbsp;&nbsp;Last Name:
            <input type="text" name="contactlast" id="contactlast" <% if
contactlast <> "" then %>value="<%=contactlast%>" <%end if%>></td>
        </tr>
        <tr>
                <td>Contact Person Title: &nbsp;
            <input name="title" type="text" <% if title <> "" then
%>value="<%=title%>" <%end if%> id="title"></td>
        </tr>
        <tr>
                <td>Phone Number: &nbsp;
    <input name="phone" type="text" id="phone" <% if phone <> "" then
%>value="<%=phone%>" <%end if%>>
    &nbsp;&nbsp;&nbsp;Email:&nbsp;&nbsp;
    <input name="email" type="text" id="email" size="40" <% if email
<> "" then %>value="<%=email%>" <%end if%>></td>
        </tr>
        <tr>
                <td>Premium Member: &nbsp;
    <input name="premium" type="checkbox" id="premium" value="yes" <%
if premium <> "" then %>checked<%end if%>></td>
        </tr>
        <tr>
                <td>Description of Services:<br>
            <textarea name="description" cols="90" rows="10"
id="description"><% if desc <> "" then %><%=desc%><%end
if%></textarea></td>
        </tr>
        <tr>
                <td><input type="submit" name="Submit" value="Submit"></td>
        </tr>
</table>
<input type="hidden" value="<%=edit%>" name="edit">
<input type="hidden" value="<%=id%>" name="id">
</form>
</html>
<%
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

Relevant Pages

  • Re: HELP! ASP and SQL problem.
    ... >to a database and also display that information in an edit mode. ... >DIM objConn ...
    (microsoft.public.inetserver.asp.general)
  • Re: SQL access from Cobol Test
    ... Dim oWrite As System.IO.StreamWriter ... Dim workline As String ... 000380 01 NEW-GENERIC-REC. ... 000810 DISPLAY "INVALID READ FROM IMPORT TEXT FILE". ...
    (comp.lang.cobol)
  • SQL access from Cobol Test
    ... fetching on a SQL cursor in COBOL. ... Dim oWrite As System.IO.StreamWriter ... 000380 01 NEW-GENERIC-REC. ... 000810 DISPLAY "INVALID READ FROM IMPORT TEXT FILE". ...
    (comp.lang.cobol)
  • Re: Stay-on-top form?
    ... Global Const WM_SM_CXVSCROLL = 2 ... Dim DisplayHeight As Integer ... iRtn = WM_apiReleaseDC'release display contex ... Sub xg_MaximizeWindow ...
    (microsoft.public.access.formscoding)
  • Re: Stay-on-top form?
    ... Global Const WM_SM_CXVSCROLL = 2 ... Dim DisplayHeight As Integer ... iRtn = WM_apiReleaseDC'release display contex ... ' Maximize the active form window, ...
    (microsoft.public.access.formscoding)