Skip to main content
W
whitsunday_lad
Known Participant
February 18, 2010
Question

MS Access SQL query failing?

  • February 18, 2010
  • 2 replies
  • 3005 views

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

This topic has been closed for replies.

2 replies

B
bregent
Participating Frequently
February 18, 2010

Len I replied to your original thread but that was getting long and difficult to read so I've posted my latest reply to this thread:

>>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.

W
whitsunday_ladAuthor
Known Participant
February 18, 2010

Hi Bregent,

Thanks for that, I have some pressing client overdue debtor collection work to do so I will get it to it latter. I will re-post again after I try some of your suggestions.

I would really like to do this myself as I want to learn and I have the website almost the way I want it, it would be a shame to have to use a developer now and not have the satisfaction of doing it myself.

Yours Faithfully

Leonard P.O'Grady

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.

IrishNJ
IrishNJ
Inspiring
February 18, 2010

Are you passing a QueryString to the page when you initially log in?

Your Recordset 1 is looking for a QueryString.  If you don't pass a QueryString to the page, it will load the default value.

Regards
Nath.

W
whitsunday_ladAuthor
Known Participant
February 18, 2010

Hi Nathon,

Thanks for joining in.

"Are you passing a QueryString to the page when you initially log in?

Your Recordset 1 is looking for a QueryString. If you don't pass a QueryString to the page, it will load the default value."

I am a novice Dreamweaver MX user Nathan so please be aware my responses may not be in tech speak.

I guess it should, the way I had it designed was the customer entered user/password and the row/ID with that user/password would then populate the record sets with appropriate data from the customer row/ID to fill in the customer details in the top of the form saving them the trouble.

The form is for uploading overdue debtors so the client then populates the debtor information below their own customer details and submits the completed form information to my email.

In my original post you will see the data comes from two tables Clients/Company the client information works fine the Customer table/record set will only populate the first row of the db no matter what user, password/ID is used.

At one point the former employee that built all of this for me set up a form that added and deleted customers from the database, that was when this problem first started happening. He fixed it in 3 minutes but told me not to use that form again.

I think that 6 years ago when this site was fully operational under an another name that one of my employees may have used that form and caused the current error.

I wonder could this form somehow have corrupted the original Customer table QueryString causing it to load a default value?

What do you think?

Yours Faithfully

Leonard P.O'Grady

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.

IrishNJ
IrishNJ
Inspiring
February 19, 2010

whitsunday lad wrote:

I guess it should, the way I had it designed was the customer entered user/password and the row/ID with that user/password would then populate the record sets with appropriate data from the customer row/ID to fill in the customer details in the top of the form saving them the trouble.

The problem with that is that you have said that, when a user logs in, you have a company recordset but that this recordset is populated by a QueryString.  This won't work because the company ID needs to come from the user recordset, right?

My guess is that your company recordset is simply loading a default value, because there is no querystring, which is more than likely the first record in your company table.

I hope this is making sense and/or that I'm understanding you properly.

Regards
Nath.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices