|
| |
|
|
mjmtravel
Posts: 330
Joined: 7/30/2006
Status: offline
 |
drw update - 3/23/2009 18:02:53
I'm having trouble with updating my database with the 3 page system. I have a hyperlink inside the first drw carrying the CustomerID which is the ID for the table I'm using.
It populates correctly on the first page but when I click on the link to the edit page, I get all of the records with that user name. I cannot make it only pull the record that I want to edit. This is spooky log in protected also.
I'm pulling the link with this type (2commentsedit.asp?CustomerID=CustomerID)
Any ideas?
|
|
|
|
mjmtravel
Posts: 330
Joined: 7/30/2006
Status: offline
|
RE: drw update - 3/25/2009 19:03:08
Could the problem be the where clause I have at the end. I've always used that code when dealing with the spooky log in, but that might be searching everything under that user name?
Here is the code I use on the edit page.
fp_sQry="SELECT * FROM CustomerTrips WHERE (UserName = '" & session("UserName") & "')"
fp_sDefault="UserName="
fp_sNoRecords="No records returned."
fp_sDataConn="Database2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&CustomerID=3&UserID=3&UserName=202&TripTitle=202&TripDate=202&FlightDepart=202&FlightReturn=202&MyComments=203&AirMiles=202&"
fp_iDisplayCols=6
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: drw update - 3/28/2009 10:18:09
Just a guess, but if you are wanting to edit a trip, then your link should be to the unique ID of the trip, not the user.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
mjmtravel
Posts: 330
Joined: 7/30/2006
Status: offline
|
RE: drw update - 3/28/2009 16:56:14
Here is what the link looks like: 2commentsedit.asp?CustomerID=CustomerID. Now the CustomerID is the auto number created when a user posts their trip.
Since this member is logged in with spooky's log in I thought I had to keep using this in order to show who the user is?
fp_sQry="SELECT * FROM CustomerTrips WHERE (UserName = '" & session("UserName") & "')"
Should I add another field with a new TripID and use that to do the editing?
Thanks again guys.
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: drw update - 3/28/2009 17:02:22
Well, if a user can have more than one trip, then the userID can occur more than once in the table. You are wanting to edit a single record so you need to have a parameter or set of parameters that will ensure a unique record. Generally, if one has a table such as trips, then there would be an autonumber ID field. So, the short answer to your question is yes.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
mjmtravel
Posts: 330
Joined: 7/30/2006
Status: offline
|
RE: drw update - 3/28/2009 17:19:56
Duane
The customers do have multiple trips in some cases. I'm using the customerId as the reference and it is also the auto number. I figured that would be safe and have no doubles. My problem is I'm getting all of the trips when I use the link to edit even if they are using different customerID numbers. Is it going to pull everything for that user because of the way I have my WHERE clause?
| |
|
|
|
William Lee
Posts: 1273
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: drw update - 3/30/2009 3:34:08
I see you have CustomerID as well as UserID.
Assuming the customerID is the 'TripID' that BTB talks about, you will want to fetch only that particular 'Trip' details and edit them with this query
fp_sQry="SELECT * FROM CustomerTrips WHERE CustomerID=::CustomerID::"
This will work with the link 2commentsedit.asp?CustomerID=CustomerID
HTH
_____________________________
William Lee
pǝssǝɟoɹd-ɟ1ǝs ʎɥʇɹoʍʇsnɹʇ ʇsoɯ ɹnoʎ
nɹnb ǝsɐqɐʇɐp & dsɐ ,ʍɹp ,ǝbɐdʇuoɹɟ
| |
|
|
|
mjmtravel
Posts: 330
Joined: 7/30/2006
Status: offline
|
RE: drw update - 3/30/2009 8:39:46
William
Doing it that way, for some reason its bringing up every trip in the database. CustomerID is the auto number that I'm using to reference each individual trip. CustomerID is the spooky log in ID, which maybe I don't even need in the table. I figured I would because how else would it know to pull up a specific users trip?
The customer ID, I'm carrying over from the edit form page like this <%=Request("UserID")%>
Its not adding this number to the database either? Not sure why. When I manually add the CustomerID to the database thinking it would then separate the trips I get this error.
Database Results Error
Description: Syntax error (missing operator) in query expression 'CustomerID='.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine
Thanks again
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: drw update - 3/30/2009 15:46:03
The proper link is as follows:
<a href="2commentsedit.asp?CustomerID=<%=FP_FieldVal(fp_rs,"CustomerID")%>">
I edited your page to test it for sure and it works as it should. CustomerID was a poor choice of names for the field because it is really the trip you are identifying, not the customer, but no big deal. You should be up and going now.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
mjmtravel
Posts: 330
Joined: 7/30/2006
Status: offline
|
RE: drw update - 3/30/2009 16:45:20
Thanks Duane
You are the man. So I kind of needed a link plus carrying the ID. I can still change the name to tripID in my spare time.
Thanks again.
| |
|
|
|
mjmtravel
Posts: 330
Joined: 7/30/2006
Status: offline
|
RE: drw update - 3/30/2009 18:55:00
Duane
Almost there. When I do go to update one of the trip records that now show up indivdually like they should, I get this error when clicking on update button. Not sure what its saying about mismatch in criteria?
Database Results Error
Description: Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft JET Database Engine
One or more form fields were empty. You should provide default values for all form fields that are used in the query.
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: drw update - 3/31/2009 9:52:32
What is the SQL for the UPDATE?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
mjmtravel
Posts: 330
Joined: 7/30/2006
Status: offline
|
RE: drw update - 3/31/2009 13:25:22
Here is what I have.
fp_sQry="UPDATE CustomerTrips SET UserID = '::UserID::', TripTitle = '::TripTitle::', TripDate = '::TripDate::', FlightDepart = '::FlightDepart::', FlightReturn = '::FlightReturn::', MyComments = '::MyComments::', AirMiles = '::Airmiles::' WHERE CustomerID=::CustomerID::"
fp_sDefault="UserName"
fp_sNoRecords="Please Wait a Moment."
fp_sDataConn="Database2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&CustomerID=3&UserID=3&UserName=202&TripTitle=202&TripDate=202&FlightDepart=202&FlightReturn=202&MyComments=203&AirMiles=202&"
fp_iDisplayCols=6
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<p>
<%=FP_FieldVal(fp_rs,"UserID")%></p>
<p>
<%=FP_FieldVal(fp_rs,"TripTitle")%></p>
<p>
<%=FP_FieldVal(fp_rs,"TripDate")%></p>
<p>
<%=FP_FieldVal(fp_rs,"FlightDepart")%></p>
<p>
<%=FP_FieldVal(fp_rs,"FlightReturn")%></p>
<p>
<%=FP_FieldVal(fp_rs,"MyComments")%></p>
<p>
<%=FP_FieldVal(fp_rs,"AirMiles")%></p>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: drw update - 3/31/2009 17:28:11
Probably should be:
fp_sQry="UPDATE CustomerTrips SET UserID = ::UserID::, TripTitle = '::TripTitle::', TripDate = #::TripDate::#, FlightDepart = #::FlightDepart::#, FlightReturn = #::FlightReturn::#, MyComments = '::MyComments::', AirMiles = '::Airmiles::' WHERE CustomerID=::CustomerID::"
FP often will not put in the correct delimiters. UserID is a numeric field so, ::UserID:: should not be surrounded by single quotes. Likewise, TripDate, FlightDepart and FlightReturn are date fields and so their corresponding form fields should be surrounded by ## as I have done above.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
mjmtravel
Posts: 330
Joined: 7/30/2006
Status: offline
|
RE: drw update - 3/31/2009 18:15:31
Duane
Now I just get this error. I have all of my columns in that table labled text so I don't have those issues. I'm trying some different variations to see if I cannot get the correct one.
Database Results Error
Description: Syntax error in UPDATE statement.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine
| |
|
|
|
mjmtravel
Posts: 330
Joined: 7/30/2006
Status: offline
|
RE: drw update - 3/31/2009 18:55:32
Duane
Went through every line, changed CustomerID to TripID like you suggested. I removed the UserID completely. I don't think I needed it and that was the confusion/mismatch. No-one was editing the UserID. I figured I needed that to tell what user is logged in and which trip to pull.
Anyway I believe its working now. I'll try logging in as another user to see if that works also.
Thanks again for all your help
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
ky