MS Access SQL query failing?

Report · Feb 16, 2010

Hi,

I am a novice Dream Weaver MX user, developing on a XP Professional box. I am using MS Access DB from Office Professional 2007.

I am using files from a previous web site I had built some years ago. I have re-worked the site and it is finished and now running on the host server. Unfortunately I am using a disfeatured form to upload my client’s overdue debtors for processing. The fully functioning form used record sets to display my client details on log in using an ID for each client.

The record sets are from 2 tables 1 Clients 2 Company in my relational user’s database.

THE PROBLEM IS THAT WHEN I LOG IN USING A VALID PASSWORD/ID (MINE) IS ON ROW 32, BOTH MY ROW ID FOR CLIENTS AND COMPANY ARE ON ROW/ID 32. BUT WHEN I LOG IN THE RECORD SETS WILL ONLY DISPLAY THE FIRST ROW/ID NO MATTER WHAT ROW THE PASSWORD WAS LINKED FROM.

The record sets in my application panel under the Server Behaviours tab are-

Recordset {record set1} from the Company table. This is set as default to row 1

Recordset {rsclient} from the client table. Indecently this client table recordset works in the form correctly!

The query from Recordset {record set1} looks like this-

Name: Recordset1

Connection: collectoz (is working)

SQL: SELECT *

FROM Company

WHERE CompanyID like 'qrycompanyid'

Variables:

Name Default values Run-Time Value

Qrycompanyid % Request.QueryString("CompanyID")

The query from Recordset {rsclient} looke like this-

Name: rsclient

Connection: collectoz

SQL: SELECT *

FROM clients

WHERE UsrName like 'MM_Username'

Variables:

Name Default Value Run-time Value

MM_Username % Session("MM_Username")

The Dynamic Text fields for Recordset {record set1} from the Company table are-

<%= (Recordset1.Fields.Item("CompName").Value) %> (stuck on row one)

<%= Recordset1.Fields.Item("CompABN_ACN").Value %> (stuck on row one)

<%= Recordset1.Fields.Item("CompTradAs").Value %> (stuck on row one)

<%=(Recordset1.Fields.Item("CompTrdAdL1").Value)%> (stuck on row one)

<%=(Recordset1.Fields.Item("CompTrdAdL2").Value)%> (stuck on row one)

<%=(Recordset1.Fields.Item("CompTrdAdL3").Value)%> (stuck on row one)

The Dynamic Text fields for Recordset {rsclient} from the client table are-

<%= rsclient.Fields.Item("UsrName").Value %> (this one and below are working properly)

<%= rsclient.Fields.Item("Email").Value %>

<%= rsclient.Fields.Item("Phone").Value %>

<%= rsclient.Fields.Item("Fax").Value %>

If you need more information let me know

Cheers

Report · Feb 16, 2010

>Unfortunately I am using a disfeatured form

I don't know what this means. Can you explain?

>The record sets

Why do you need two recordsets? The data is related, right?

>WHERE CompanyID like 'qrycompanyid'

Why are you using the Like predicate? Unless you are doing pattern matching you should use the equality operator.

To be of any real help, we would really need to see the entire code. Posting bits and pieces does not provide enough information.

Report · Feb 16, 2010

Hi,

>Unfortunately I am using a disfeatured form

I don't know what this means. Can you explain?

From my default page http://www.debtorstocash.com you can use a link in the centre of the page JMS, (it’s a rollover image and it will turn green with curser passed, once you click it takes you to a page that explains the site, how to join, what we do, how to use the site to collect overdue debtors.) It is based on images of screen shots and document images of the legal process we use. On the first row of images the 3^rd image from the left (last image on that row is the logon image, once clicked it will take you to logon page, use my login, Len O’Grady, guest. This will take you to the online services page, click upload debtors, you will now see the de-featured form (sorry disfeatured was a typo it was late when I posted this last night). You have to enter your client details at the top of this form. The form I am trying to get working, (not loaded on host yet) uses record sets in all the client form lines so the client only have to enter debtor details.

>The record sets

Why do you need two record sets? The data is related, right?

There are two tables in the uses database Client/Company, they are relational sharing the same ID or row for each customer using the site to collect overdue debtors. The client details such as name and email go in Client Table the company details such as company name trading address go in the Company table. There is a record set for each table, when the client logs in their ID from the user/password should populate all the data at the top of the form with the two record sets (their client details saving them the trouble), they just enter the debtor details. The Client record set works the Company record set does not it is stuck on row one.

I will try to post all the code from the page in question, I do not know if this will work.

<%@LANGUAGE="VBSCRIPT"%>
<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers="0,1"
MM_authFailedURL="../content/online.htm"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
If (true Or CStr(Session("MM_UserAuthorization"))="") Or _
(InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization"))>=1) Then
MM_grantAccess = true
End If
End If
If Not MM_grantAccess Then
MM_qsChar = "?"
If (InStr(1,MM_authFailedURL,"?") >= 1) Then MM_qsChar = "&"
MM_referrer = Request.ServerVariables("URL")
if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
Response.Redirect(MM_authFailedURL)
End If
%>

<%
Dim Recordset1__qrycompanyid
Recordset1__qrycompanyid = "%"
If (Request.QueryString("CompanyID") <> "") Then
Recordset1__qrycompanyid = Request.QueryString("CompanyID")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_collectoz_STRING
Recordset1.Source = "SELECT * FROM Company WHERE CompanyID like '" + Replace(Recordset1__qrycompanyid, "'", "''") + "'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim rsclient__MM_Username
rsclient__MM_Username = "%"
If (Session("MM_Username") <> "") Then
rsclient__MM_Username = Session("MM_Username")
End If
%>
<%
Dim rsclient
Dim rsclient_numRows

Set rsclient = Server.CreateObject("ADODB.Recordset")
rsclient.ActiveConnection = MM_collectoz_STRING
rsclient.Source = "SELECT * FROM clients WHERE UsrName like '" + Replace(rsclient__MM_Username, "'", "''") + "'"
rsclient.CursorType = 0
rsclient.CursorLocation = 2
rsclient.LockType = 1
rsclient.Open()

rsclient_numRows = 0
%>
<html>
<head>
<title>debtorstocash.com Online Services</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">
<!--

<!--

</script>
</head>
<body bgcolor="336699" leftmargin="30" topmargin="0" marginwidth="30" marginheight="0" text="#FFFFFF" link="#FFFFFF" vlink="#CCCCCC" alink="#FFFFFF">
<table bgcolor="#336699" border="0" cellpadding="0" cellspacing="0" width="716" height="120" align="center">
<tr>
    <td width="716"><img name="slice01" src="../images/slice01.jpg" width="714" height="120" border="0" usemap="#m_slice01" alt="">
      <table width="716" border="0" align="center">
        <tr>
          <td><img name="Site20trafic" src="../images/Site%20trafic.jpg" width="714" height="25" border="0" usemap="#m_Site20trafic" alt=""><map name="m_Site20trafic">
<area shape="rect" coords="662,4,698,20" href="../content/Jobs.htm" alt="" >
<area shape="rect" coords="567,5,651,20" href="../content/contactus.htm" alt="" >
<area shape="rect" coords="440,5,554,21" href="../online.asp" alt="" >
<area shape="rect" coords="282,5,424,20" href="../products.asp" alt="" >
<area shape="rect" coords="77,4,269,20" href="../content/aboutus.htm" alt="" >
<area shape="rect" coords="10,4,63,21" href="../default.asp" alt="" >
</map></td>
        </tr>
      </table>
</td>
</tr>
</table>
<p>
<map name="m_slice01">
    <area shape="rect" coords="7,10,703,106" href="../default.asp" alt="" >
</map>
</p>
<table width="715" border="0" cellspacing="0" cellpadding="0" height="143" align="center">
<tr>
    <td height="121" valign="top">      <p><FONT size="+1"><B><FONT face="Arial, Helvetica, sans-serif">Upload Debtors
        <BR>
        </FONT></B><FONT size="-1" face="Arial, Helvetica, sans-serif"><A href="logout.asp">Click
        here to log out</A></FONT></FONT></p>
      <p><font face="Arial, Helvetica, sans-serif" size="-1">To begin the process
           of collecting your debt, we need to find out the neccessary information

        about your debtor. Please gather the required information and enter it
           into the fields below. When completed simply left-click on the "Upload
        Debtor to debtorstocash.com" button located at the bottom
        of the page. Lets get started.</font></p>      <form name="Upload Debtors" method="post" action="http://mailgate.server-mail.com/cgi-bin/mailgate">
        <table width="600" border="0" cellspacing="0" cellpadding="3" align="center" height="337">
          <tr>
            <td colspan="2"><b><font face="Arial, Helvetica, sans-serif" size="-1">
              Your Contact Information</font></b></td>
          </tr>
          <tr>
            <td width="269"><font face="Arial, Helvetica, sans-serif" size="-1">Company
              Name or Name of Partners:</font></td>
            <td width="331"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <input name="Client Company Name/Partners" type="text" value="<%=((Recordset1.Fields.Item("CompName").Value))%>" size="40" maxlength="1000">
              </font></td>
          </tr>
          <tr>
            <td width="269"><font face="Arial, Helvetica, sans-serif" size="-1">ABN
              / ACN:</font></td>
            <td width="331"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <input name="Cleint ABN/ACN" type="text" value="<%=(Recordset1.Fields.Item("CompABN_ACN").Value)%>" size="40" maxlength="1000">
              </font></td>
          </tr>
          <tr>
            <td width="269"><font face="Arial, Helvetica, sans-serif" size="-1">Trading
              Name/s:</font></td>
            <td width="331"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <input name="Client Trading As" type="text" value="<%=(Recordset1.Fields.Item("CompTradAs").Value)%>" size="40" maxlength="1000">
              </font></td>
          </tr>
          <tr>
            <td width="269" valign="top"><font face="Arial, Helvetica, sans-serif" size="-1">Trading
              Address:<br>
              <font size="-4">Please enter full address including State, Post
              code, and Country.</font> </font></td>
            <td width="331"><font face="Arial, Helvetica, sans-serif" size="-1">
              </font>
              <table width="75%" border="0">
                <tr>
                  <td><font color="#000000"><%=(Recordset1.Fields.Item("CompTrdAdL1").Value)%></font></td>
                </tr>
                <tr>
                  <td><font color="#000000"><%=(Recordset1.Fields.Item("CompTrdAdL2").Value)%></font></td>
                </tr>
                <tr>
                  <td><font color="#000000"><%=(Recordset1.Fields.Item("CompTrdAdL3").Value)%></font></td>
                </tr>
                <tr>
                  <td><font color="#000000"><%=(Recordset1.Fields.Item("CompTrdCrty").Value)%></font></td>
                </tr>
              </table>
              <font face="Arial, Helvetica, sans-serif" size="-1"> </font></td>
          </tr>
          <tr>
            <td colspan="2" valign="top">
              <table width="600" border="0" cellspacing="0" cellpadding="3" align="center">
                <tr>
                  <td width="269"><font face="Arial, Helvetica, sans-serif" size="-1">Contact
                    Name/s:</font></td>
                  <td width="331"> <font face="Arial, Helvetica, sans-serif" size="-1">
                    <input name="Client Contact Name" type="text" value="<%=(rsclient.Fields.Item("UsrName").Value)%>" size="40" maxlength="1000">
                    </font></td>
                </tr>
                <tr>
                  <td width="269" height="26"><font face="Arial, Helvetica, sans-serif" size="-1">E-mail
                    Address:</font></td>
                  <td width="331" height="26">
                    <input name="Client Email" type="text" value="<%=(rsclient.Fields.Item("Email").Value)%>" size="40" maxlength="1000">
                  </td>
                </tr>
                <tr>
                  <td width="269" height="26"><font face="Arial, Helvetica, sans-serif" size="-1">Telephone
                    #:</font></td>
                  <td width="331" height="26"> <font face="Arial, Helvetica, sans-serif" size="-1">
                    <input name="Client Telephone#" type="text" value="<%=(rsclient.Fields.Item("Phone").Value)%>" size="30" maxlength="30">
                    </font></td>
                </tr>
                <tr>
                  <td width="269"><font face="Arial, Helvetica, sans-serif" size="-1">Faxmail
                    #:</font></td>
                  <td width="331"> <font face="Arial, Helvetica, sans-serif" size="-1">
                    <input name="Client Fax#" type="text" value="<%=(rsclient.Fields.Item("Fax").Value)%>" size="30" maxlength="1000">
                    </font></td>
                </tr>
              </table>
              <font face="Arial, Helvetica, sans-serif" size="-1"> </font></td>
          </tr>
        </table>
        <input type=hidden name="recipient" value="debtors@collectoz.com">
        <input type=hidden name="subject" value="Debtor Uploaded">
        <input type=hidden name="redirect" value="http://www.collectoz.com/ssl/confirm.asp">
        <input type=hidden name="realname" value="Online Services">
        <table width="601" border="0" cellspacing="0" cellpadding="3" align="center">
          <tr>
            <td colspan="2"><b><font face="Arial, Helvetica, sans-serif" size="-1">Your
              Debtor</font></b></td>
          </tr>
          <tr>
            <td width="263"><font face="Arial, Helvetica, sans-serif" size="-1">Company
              Name:</font></td>
            <td width="326"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <input type="text" name="Company Name" size="40" maxlength="1000">
              </font></td>
          </tr>
          <tr>
            <td width="263"><font face="Arial, Helvetica, sans-serif" size="-1">ABN
              / ACN: </font></td>
            <td width="326"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <input type="text" name="ACN/ABN" size="40" maxlength="1000">
              </font></td>
          </tr>
          <tr>
            <td width="263"><font face="Arial, Helvetica, sans-serif" size="-1">Trading
              Name/s:</font></td>
            <td width="326"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <input type="text" name="Trading as" size="40" maxlength="1000">
              </font></td>
          </tr>
          <tr>
            <td width="263" height="63" valign="top"><font face="Arial, Helvetica, sans-serif" size="-1">Company
              Director/s:</font><font face="Arial, Helvetica, sans-serif"><br>
              <font size="-3">If there are multiple directors, please list them
              by placing a<br>
              semi-colon after each director.</font></font><br>
            </td>
            <td width="326" height="63" valign="top">
              <input type="text" name="Company Directors" value="" size="50">
            </td>
          </tr>
          <tr>
            <td width="263" valign="top">
              <p><font face="Arial, Helvetica, sans-serif" size="-1">Trading Address:<br>
                <font size="-3">Please enter full address including State, Post
                code, and<br>
                </font></font><font face="Arial, Helvetica, sans-serif" size="-3">Country.<br>
                </font></p>
            </td>
            <td width="326"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <textarea name="Trading Address" wrap="VIRTUAL" cols="30" rows="3"></textarea>
              </font></td>
          </tr>
          <tr>
            <td width="263" valign="top"><font face="Arial, Helvetica, sans-serif" size="-1">Postal
              Address:<br>
              <font size="-3">Please enter full address including State, Post
              code, and Country. </font> </font></td>
            <td width="326"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <textarea name="Postal Address" wrap="VIRTUAL" cols="30" rows="3"></textarea>
              </font></td>
          </tr>
        </table>
        <br>
        <table width="601" border="0" cellspacing="0" cellpadding="3" align="center">
          <tr>
            <td colspan="2"><b><font face="Arial, Helvetica, sans-serif" size="-1">Debtor
              Contact Information</font></b></td>
          </tr>
          <tr>
            <td width="263"><font face="Arial, Helvetica, sans-serif" size="-1">Contact
              Name/s:</font></td>
            <td width="326"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <input type="text" name="Contact Name" size="40" maxlength="1000">
              </font></td>
          </tr>
          <tr>
            <td width="263" height="26"><font face="Arial, Helvetica, sans-serif" size="-1">E-mail
              Address:</font></td>
            <td width="326" height="26">
              <input type="text" name="Email" size="40" maxlength="1000">
            </td>
          </tr>
          <tr>
            <td width="263" height="26"><font face="Arial, Helvetica, sans-serif" size="-1">Telephone
              #:</font></td>
            <td width="326" height="26"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <input type="text" name="Telephone#" size="30" maxlength="30">
              </font></td>
          </tr>
          <tr>
            <td width="263"><font face="Arial, Helvetica, sans-serif" size="-1">Faxmail
              #:</font></td>
            <td width="326"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <input type="text" name="Fax#" size="30" maxlength="1000">
              </font></td>
          </tr>
          <tr>
            <td width="263"><font face="Arial, Helvetica, sans-serif" size="-1">Mobile
              Telephone #:</font></td>
            <td width="326"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <input type="text" name="Mobile#" size="30" maxlength="1000">
              </font></td>
          </tr>
        </table>
        <br>
        <table width="602" border="0" cellpadding="0" cellspacing="0" height="240" align="center">
          <tr>
            <td colspan="2" height="34"><font face="Arial, Helvetica, sans-serif" size="-1"><b>Information
              of incurred debt</b></font></td>
          </tr>
          <tr valign="top">
            <td width="269" height="54"><font face="Arial, Helvetica, sans-serif" size="-1">The
              Invoice Number:</font><br>
              <font face="Arial, Helvetica, sans-serif" size="-3">If you have
              a number of invoices include only the first and<br>
              last issued.</font></td>
            <td height="54" width="330"> <p><font face="Arial, Helvetica, sans-serif" size="-1">First
                Invoice#
                <input type="text" name="Invoice#1" size="10">
                </font></p>
              <p><font face="Arial, Helvetica, sans-serif" size="-1">Last Invoice#
                <input type="text" name="Invoice#2" size="10">
              </font> </p></td>
          </tr>
          <tr valign="top">
            <td height="41" width="269"><font face="Arial, Helvetica, sans-serif" size="-1">Date
              of Debt:</font><br>
              <font face="Arial, Helvetica, sans-serif" size="-3">The date which
              the first invoice was issued. (DD/MM/YYYY)</font></td>
            <td height="41" width="330">
              <input type="text" name="Date of 1st Invoice" size="30">
            </td>
          </tr>
          <tr valign="top">
            <td height="40" width="269"><font face="Arial, Helvetica, sans-serif" size="-1">Date
              of Last Invoice:</font><font face="Arial, Helvetica, sans-serif"><br>
              <font size="-3">The date when the last invoice was issued. </font><font face="Arial, Helvetica, sans-serif" size="-3">(DD/MM/YYYY)</font></font></td>
            <td height="40" width="330">
              <input type="text" name="Date of last invoice" size="30">
            </td>
          </tr>
          <tr>
            <td height="48" width="269"><font size="-1" face="Arial, Helvetica, sans-serif">The
              amount of debt:</font></td>
            <td height="48" width="330">
              <input type="text" name="Amount ($)" size="30" value="$0.00">
            </td>
          </tr>
          <tr valign="top">
            <td height="26" width="269"><font face="Arial, Helvetica, sans-serif" size="-1">The
              debt was the result from:</font></td>
            <td height="26" width="330"> <font face="Arial, Helvetica, sans-serif" size="-1">
              <select name="Debt incurred by">
                <option>Please Select</option>
                <option value="goods sold">Goods sold</option>
                <option value="services rended">Services rended</option>
                <option value="dishonoured check">Dishonoured check</option>
                <option value="services rended and goods sold">Services rended
                and goods sold</option>
              </select>
            </font></td>
          </tr>
        </table>
        <br>
        <table width="600" border="0" cellspacing="0" cellpadding="0" align="center">
          <tr>
            <td colspan="2" height="29"><b><font face="Arial, Helvetica, sans-serif" size="-1">Important
              Information</font></b></td>
          </tr>
          <tr>
            <td height="26"><font face="Arial, Helvetica, sans-serif" size="-1">Do
              you have a signed Credit Application:</font></td>
            <td> <font face="Arial, Helvetica, sans-serif" size="-1">
              <select name="Credit App. Signed">
              <option selected>Please Select
              <option value="Yes">Yes
              <option value="No">No
              </select>
              </font></td>
          </tr>
          <tr>
            <td height="26"><font face="Arial, Helvetica, sans-serif" size="-1">Do
              you have a Directors Guarantee:</font></td>
            <td> <font face="Arial, Helvetica, sans-serif" size="-1">
              <select name="Directors Guarantee">
              <option selected>Please Select
              <option value="Yes">Yes
              <option value="No">No
              </select>
              </font></td>
          </tr>
        </table>
        <br>
        <table width="600" border="0" cellspacing="0" cellpadding="0" align="center">
          <tr>
            <td colspan="2" height="26"><b><font face="Arial, Helvetica, sans-serif" size="-1">What
              to do next?</font></b></td>
          </tr>
          <tr>
            <td width="375" height="26"><font face="Arial, Helvetica, sans-serif" size="-1">Would
              you like us to make a debtor assessment call:</font></td>
            <td width="225" height="26"><font face="Arial, Helvetica, sans-serif" size="-1">
              <select name="Make Debt Assess Call">
                <option selected>Please Select</option>
                <option value="Yes">Yes</option>
                <option value="No">No</option>
              </select>
              </font></td>
          </tr>
          <tr>
            <td width="375" height="38"><font face="Arial, Helvetica, sans-serif" size="-1">Would
                 you like us to issue a letter of demand from our
                 Solicitors:</font></td>
            <td width="225" height="38"><font face="Arial, Helvetica, sans-serif" size="-1">
              <select name="Issue Letter of Demand">
              <option selected>Please Select
              <option value="Yes">Yes
              <option value="No">No
              </select>
              </font></td>
          </tr>
          <tr>
            <td width="375" height="26"><font face="Arial, Helvetica, sans-serif" size="-1">Take
              immediate legal action to recover debt:</font></td>
            <td width="225" height="26"><font face="Arial, Helvetica, sans-serif" size="-1">
              <select name="Take Immediate Action">
                <option selected>Please Select</option>
                <option value="Not at this stage">Not at this stage</option>
                <option value="Yes - Magistrates Court">Yes - Magistrates Court</option>
                <option value="Yes - Minor Debt Court">Yes - Minor Debt Court</option>
              </select>
              </font></td>
          </tr>
        </table>
        <br>
        <table width="600" border="0" cellspacing="0" cellpadding="3" align="center">
          <tr>
            <td align="center">
              <input type="reset" name="clear" value="      Cancel      " onClick="MM_goToURL('parent','https://bne070u.server-securmainmenu.asp');return document.MM_returnValue">
              <input type="submit" name="Send" value="        Upload Debtor to debtorstocash.com        ">
            </td>
          </tr>
        </table>
        <br>
        <br>
        <br>
        </form>      <p> </p></td>
</tr>
</table>
<table width="714" border="0" cellpadding="0" cellspacing="0" align="center">
<tr>
    <td><font face="Arial, Helvetica, sans-serif" size="-1">| <a href="../default.asp">Home</a>
      | <a href="../content/aboutus.htm">About Us</a> | <a href="../products.asp">Products
      & Services</a> | <a href="../online.asp">Online Services</a>
      | <a href="../content/contactus.htm">Contact Us</a> | <a href="../content/Jobs.htm">Jobs</a>
      |<br>
      Site Designed by Leonard P.O'Grady<br>
      <font size="-2">© Content Copyright 2002 Leonard P.O'Grady ABN
    80 340 504 889. </font></font></td>
</tr>
</table>
</body>
</html>

<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
rsclient.Close()
Set rsclient = Nothing
%>

Cheers

Report · Feb 16, 2010

Hi,

This is what the tables look like.

**Clients**
ClientID	ClientCompanyID	Contact_Type	FstNme	LstNme	Phone	MobPhne	Fax	Email	UsrName	Pasword	TrdAdL1	TrdAdL2	TrdAdL3	TrdCrty	POBoxL1	POBoxL2	POBoxL3	POCrty
2	2								Nancy Porter	guest								Australia

**Company**
CompanyID	CompName	CompTradAs	CompTrust	CompABN_ACN	CompClt_BN	Phone	Fax	CompTrdAdL1	CompTrdAdL2	CompTrdAdL3	CompTrdCrty	CompRegAdL1	CompRegAdL2	CompRegAdL3	CompRegCrty	CompPOBoxL1	CompPOBoxL2	CompPOBoxL3	CompPOCrty	CompCatgry
2	ABW Tools Pty Ltd		Bruce Wilson Family Trust								Australia				Australia				Australia

Cheers

Report · Feb 16, 2010

I tried logging in but getting a failed login and a redirect to a non existant page:

http://www.debtorstocash.com/content/online.asp?accessdenied=%2Fssl%2Faccessdenied.asp

But I still don't understand why you need two recordsets. Can't you pull the data from both tables into a single recordset?

Once I can see the page I may understand better what you are trying to do. I'll also take a look at the code you provided when I get a chance.

Report · Feb 16, 2010

Hi,

Try this-

http://www.debtorstocash.com/online.asp

Login Name: Len O'Grady

Password: guest

Cheers

Report · Feb 16, 2010

The where clause parameter is being passed in via querystring so the first thing is to verify if the querystring is correct. Output the value of CompanyID to the screen so you can be sure it is correct.

Report · Feb 16, 2010

Hi,

" Output the value of CompanyID to the screen so you can be sure it is correct."

This looks like a great place to start but I am sorry as I said I am a novice, how do I Output the value of CompanyID to the screen?

Yours Faithfully

Overdue Debtors Collection Services

Commercial Agent, Property Agents and Motor Dealers Act 2000

Licence Holder- 2503156

Website: http://www.debtorstocash.com

ABN: 80 340 504 889

Phone: 0447 043 018

E-mail: leonardpogrady@hotmail.com

Mailing Address: 16 South Mole Boulevard Cannonvale QLD 4802

If you are not an authorised recipient of this email, please contact Leonard P. O’Grady by return email or by telephoning 0447 043 018. In any event you should not read, print, transmit, store, use or act in reliance on this email or any attachments, and all copies of them should be destroyed immediately. This email and any other attachments are confidential and may contain legally privileged information, and/or copyright material the property of Leonard P. O’Grady, their clients or third parties. You should not transmit, distribute or in any way apply this information unless you are authorised to do so. Leonard P. O’Grady does not accept responsibility for any viruses contained in or transmitted by this email.

Report · Feb 16, 2010

>how do I Output the value of CompanyID to the screen?

response.write (Recordset1__qrycompanyid)

Report · Feb 16, 2010

Hi,

>how do I Output the value of CompanyID to the screen?

response.write (Recordset1__qrycompanyid)

Once again sorry for my inexperience but I don’t know what line in the upload_debtors.asp file to insert the line of code above you have kindly supplied?

Yours Faithfully

Overdue Debtors Collection Services

Commercial Agent, Property Agents and Motor Dealers Act 2000

Licence Holder- 2503156

Website: http://www.debtorstocash.com

ABN: 80 340 504 889

Phone: 0447 043 018

E-mail: leonardpogrady@hotmail.com

Mailing Address: 16 South Mole Boulevard Cannonvale QLD 4802

If you are not an authorised recipient of this email, please contact Leonard P. O’Grady by return email or by telephoning 0447 043 018. In any event you should not read, print, transmit, store, use or act in reliance on this email or any attachments, and all copies of them should be destroyed immediately. This email and any other attachments are confidential and may contain legally privileged information, and/or copyright material the property of Leonard P. O’Grady, their clients or third parties. You should not transmit, distribute or in any way apply this information unless you are authorised to do so. Leonard P. O’Grady does not accept responsibility for any viruses contained in or transmitted by this email.

Report · Feb 18, 2010

>>response.write (Recordset1__qrycompanyid)

>

>Once again sorry for my inexperience but I don’t

>know what line in the upload_debtors.asp file to

>insert the line of code above you have kindly supplied?

Len, just insert that code anywhere after the Recordset1__qrycompanyid variable is assigned. Example:

<%
Dim Recordset1__qrycompanyid
Recordset1__qrycompanyid = "%"
If (Request.QueryString("CompanyID") <> "") Then
Recordset1__qrycompanyid = Request.QueryString("CompanyID")
End If

response.write (Recordset1__qrycompanyid)
%>

That should output it near the top of the screen.

>The database was designed by a former employee some time ago

Too bad he's not still around so you could fire him Seriously, it's a mess.

>What is a normalized database?

http://en.wikipedia.org/wiki/Database_normalization

In most applications, you want your database to achieve at least Third Normal Form. Your current design fails to meet 1NF.

>How do I pull all of the data from both tables into one recordset by using a join?

http://en.wikipedia.org/wiki/Join_%28SQL%29

You are really going to have to become familiar with SQL, database design and ASP/VBScript or you will really have a tough time getting this application to work. At this point, I would considering halting this project until you get the database redesigned. It seems badly flawed which will affect your ability to run the business in a logical manner. I don't know enough about your business process at this point to offer a design suggestion. You might also want to consider hiring a developer with the proper skill set to develop this for you. But if you want to puruse it yourself, I'm happy to help.

Report · Feb 16, 2010

I don't understand your schema. The client table stores your clients? And it contains a foreign key to Company table? Is it a 1-to-1 relationship? 1-to-many?

Report · Feb 16, 2010

Hi,

If I understand your question correctly-

"I don't understand your schema. The client table stores your clients? And it contains a foreign key to Company table? Is it a 1-to-1 relationship? 1-to-many?"

All the data/information for a customer is spread over two tables Clients and Company, the login user and password data is in the Clients table both tables share the same customer ID/key/row for each customer so if I understand the term 1-to-1 relationship correctly I guess the schema would be 1-to-1.

Did you get the login to work? I posted the two tables Client/Company after I posted my original post on this problem, did you see the tables on the forum page? That will show you exactly how the tables are presented/hosted in my root folder.

Yours Faithfully

Overdue Debtors Collection Services

Commercial Agent, Property Agents and Motor Dealers Act 2000

Licence Holder- 2503156

Website: http://www.debtorstocash.com

ABN: 80 340 504 889

Phone: 0447 043 018

E-mail: leonardpogrady@hotmail.com

Mailing Address: 16 South Mole Boulevard Cannonvale QLD 4802

If you are not an authorised recipient of this email, please contact Leonard P. O’Grady by return email or by telephoning 0447 043 018. In any event you should not read, print, transmit, store, use or act in reliance on this email or any attachments, and all copies of them should be destroyed immediately. This email and any other attachments are confidential and may contain legally privileged information, and/or copyright material the property of Leonard P. O’Grady, their clients or third parties. You should not transmit, distribute or in any way apply this information unless you are authorised to do so. Leonard P. O’Grady does not accept responsibility for any viruses contained in or transmitted by this email.

Report · Feb 16, 2010

>Clients table both tables share the same customer

>ID/key/row for each customer so if I understand the

>term 1-to-1 relationship correctly I guess the schema

>would be 1-to-1.

What was your reasoning for splitting the data into two tables? Since the database designed is not normalized at all, you could have put it all in one table and made things easier. In any case, you can pull all of the data from both tables into one recordset by using a join.

I have to say that I don't understand your business process. I would think that if you have a client sign up for services, you would create an account that could be used over again for more than one collection. The way your database is designed, that is not feasible.

Report · Feb 16, 2010

Hi,

"What was your reasoning for splitting the data into two tables? Since the database designed is not normalized at all, you could have put it all in one table and made things easier. In any case, you can pull all of the data from both tables into one recordset by using a join."

The database was designed by a former employee some time ago, at the time we intended to build the back office for the processing of our core work building legal documents automatically.

What is a normalized database?

How do I pull all of the data from both tables into one recordset by using a join?

Yours Faithfully

Overdue Debtors Collection Services

Commercial Agent, Property Agents and Motor Dealers Act 2000

Licence Holder- 2503156

Website: http://www.debtorstocash.com

ABN: 80 340 504 889

Phone: 0447 043 018

E-mail: leonardpogrady@hotmail.com

Mailing Address: 16 South Mole Boulevard Cannonvale QLD 4802

If you are not an authorised recipient of this email, please contact Leonard P. O’Grady by return email or by telephoning 0447 043 018. In any event you should not read, print, transmit, store, use or act in reliance on this email or any attachments, and all copies of them should be destroyed immediately. This email and any other attachments are confidential and may contain legally privileged information, and/or copyright material the property of Leonard P. O’Grady, their clients or third parties. You should not transmit, distribute or in any way apply this information unless you are authorised to do so. Leonard P. O’Grady does not accept responsibility for any viruses contained in or transmitted by this email.