|
| |
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
 |
update query - 5/25/2005 9:14:14
Is this the right format to post an update from a frontpage DRW to a MS SQL 2000 server?
UPDATE IM
SET username = '::username::', sentfrom = '::sentfrom::', status = '::status::', message = '::message::'
The page says it updates but the information never makes it to the database.
|
|
|
|
rdouglass
Posts: 9202
From: Biddeford, ME USA
Status: offline
|
RE: update query - 5/25/2005 9:21:27
If they're all text fields that update looks OK except I think status is a reserved word. How 'bout something like this:
UPDATE IM
SET username = '::username::', sentfrom = '::sentfrom::', [status] = '::status::', message = '::message::'
Also, you may want a WHERE clause in there. Otherwise it'll put that data in every record in the table (unless that's what you want).
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: update query - 5/25/2005 10:16:28
I think I explained it wrong... Here is what im doing
im trying to make a simple way for someone to reply to a message posted on a homebrewed message board. Kinda like an IM to teh original poster of the message.
Here is what i have.
I have a results page that lists the message a person posted
at the bottom is a hyperlink that says IM Message Sender
When clicked this hyperlink post something like this to the message page
http://www.evilbar.com/postim.asp?ID=14 (14 being the id of the message in the table)
NOw....
I have a FPDRW called postim.asp that shows the data for lets say ID number 14 in the table
inside this FPDRW is a form that shows information pertaining to the person you are trying to send an IM to.
The form has 3 hidden fields and 1 visible field called message.
The message field is where you input your comments ( instant message )
the submit button on the form is supposed to pass the information in all 4 fields to a page called imposter.asp
imposter.asp runs the update query that I posted above.
when the submit button is pushed it acts like it posted the data.
when i check the sql server there is nothing there....
if you want to see what i am talking about go here
http://www.evilbar.com/classread.asp?ID=14
if prompted to login use
username: gina
password: gina
Im pretty lost here.. I know i have done this in the past but i cant remember what i did...
Thanks...
Rich
| |
|
|
|
rdouglass
Posts: 9202
From: Biddeford, ME USA
Status: offline
|
RE: update query - 5/25/2005 10:31:11
Can you post the code from imposter.asp?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: update query - 5/25/2005 10:42:15
<!--webbot bot="DatabaseRegionStart" s-columnnames s-columntypes s-dataconnection="Database1" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns s-criteria s-order s-sql="UPDATE Spooky_IM SET [username]=username, [sentfrom]='::sentfrom::',[status]='::status::',[message]='::message::'<br>" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="sentfrom=&status=&message=" s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="5" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the start of a Database Results region.</font></td></tr>" b-WasTableFormat="TRUE" startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="UPDATE Spooky_IM SET [username]=username, [sentfrom]='::sentfrom::',[status]='::status::',[message]='::message::' "
fp_sDefault="sentfrom=&status=&message="
fp_sNoRecords="<tr><td colspan=16 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=5
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="395" -->
| |
|
|
|
drogers
Posts: 142
Joined: 5/7/2004
Status: offline
|
RE: update query - 5/25/2005 10:56:12
It looks like your problem is that you're not telling the database connection where to put the data. Depending on how you would like to do this, you either need to put in a WHERE clause at the end of your SQL UPDATE statement or you need to use INSERT instead.
Since you want these to be comments in response to an original message, unless you only want to allow one comment per message, I would create a new table for responses and then use an INSERT statement and add an ID field which matches the ID of the original post. This way when you are displaying results, you can then use a SELECT statement which pulls all the records with the ID = to the original message, and then sort the responses how ever you like.
If each message will only have one response, you could have empty fields in your original message row of data where the response could go. In that case you want to use the WHERE clause in your update and also probably put in some sort of check to make sure the fields are not already populated.
Let me know which way you want to proceed and I'll help you with more specific details.
| |
|
|
|
rdouglass
Posts: 9202
From: Biddeford, ME USA
Status: offline
|
RE: update query - 5/25/2005 10:59:51
It looks like there may be some problems with this query:
sql="UPDATE Spooky_IM SET [username]=username, [sentfrom]='::sentfrom::',[status]='::status::',[message]='::message::'<br>"
(in the Grey DRW code that is) Can you try changing the DRW query (using the Custom SQL Step) to this:
UPDATE Spooky_IM SET [username]='::username::',[sentfrom]='::sentfrom::',[status]='::status::',[message]='::message::'
But what I'm kinda' not getting is are you trying to UPDATE the original message or are you trying to INSERT a new message for the thread?
If you're doing an UPDATE, you probably only want to update 1 record don't you? That would require some kind of WHERE clause. If you're trying to create a new response or "thread" item, you're going to want to do an INSERT.
Or am I completely lost?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: update query - 5/25/2005 11:05:23
i want to insert into a new table called IM
I tried an isert command but it kept erroring out on me, so I thought an update would work.
In a perfect scenario this system would insert the message into a table called IM.
my hope is that when a user logs into my website it would say "welcome (username) you have two new messages. click to read"
The messages would be counted and displayed from the IM table that i set up.
After the user reads them they could mark them as "read"
Any help?
< Message edited by sentinel -- 5/25/2005 11:11:15 >
| |
|
|
|
drogers
Posts: 142
Joined: 5/7/2004
Status: offline
|
RE: update query - 5/25/2005 11:16:20
Ok, lets start over.
This is what I think you want to do:
You have a message board where people post messages, and other people reply to them. You want to add a feature where a user can click a link which opens a form where they can input into a text box a message for the poster. That message would be stored in a database and be retreivable by the original poster only. Ideally, when the original poster logs in after a message has been sent, that poster would be informed that they have new messages.
After the person sends the message, you'd probably want them redirected somewhere as well.
Is this all correct?
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: update query - 5/25/2005 11:16:31
I think I got it...
i did this
INSERT INTO Spooky_IM ("username","sentfrom","status","message")
VALUES ('::username::','::sentfrom::','::status::','::message::')
looks like it worked... ill let you know if it didnt...
thanks very much!!!
You guys ROCK
| |
|
|
|
drogers
Posts: 142
Joined: 5/7/2004
Status: offline
|
RE: update query - 5/25/2005 11:54:27
Don't forget that you need to somehow link the IM to the user it is going to.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: update query - 5/25/2005 12:03:38
Okay....
Thats the next issue.... I cant seem to link it now.... I think I have my stupid hat on today!
By now you may realize that im using the spooky login system ( dev version)
The table that is holding the instant message is called Spooky_IM
In the Success.asp page i tried to create a DRW that counted the messages for a given user name.
I got it to count... but now any user that logs in sees the count number of messages in the spooky_im table.
this is the code i used.. It does exactly what I told it to do.. It counts
SELECT count(username) AS countofusername FROM Spooky_IM
where username=username
I now realize that when i say username=username that does not mean it is going to recognize <%=Session(appName&"UserName")%> from the spooky login.
Is there a way to do this?
| |
|
|
|
drogers
Posts: 142
Joined: 5/7/2004
Status: offline
|
RE: update query - 5/25/2005 12:14:02
Haven't used the spooky login, but here is what I would do if Sesion(appName&"Username") is the logged in user and the field username is the person who is to receive the IM.
<% user = Session(appName&"Username")
SELECT count(username) AS countofusername FROM Spooky_IM WHERE username = user
%>
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: update query - 5/25/2005 12:15:14
you can put that code right into the FPDRW sql box?
| |
|
|
|
drogers
Posts: 142
Joined: 5/7/2004
Status: offline
|
RE: update query - 5/25/2005 12:17:42
Hmmm, I hand code my ASP because the DRW can get restrictive. Let me open up FP and take a look...
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: update query - 5/25/2005 12:22:54
I put it here:
<% user = Session(appName&"Username")%>
<!--webbot bot="DatabaseRegionStart" s-columnnames="countofusername" s-columntypes="3" s-dataconnection="Database1" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice="countofusername" s-menuvalue="countofusername" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="FALSE" b-listseparator="FALSE" i-listformat="0" b-makeform="TRUE" s-recordsource s-displaycolumns="countofusername" s-criteria s-order s-sql="<br><br>SELECT count(username) AS countofusername FROM Spooky_IM WHERE username = user<br>" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="4" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00" align="left"><font color="#000000">This is the start of a Database Results region.</font></td></tr></table>" b-WasTableFormat="FALSE" startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry=" SELECT count(username) AS countofusername FROM Spooky_IM WHERE username = user "
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="countofusername"
fp_sMenuValue="countofusername"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=4
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
Obviously that didn't work.... lol
Do I need to alter the fpdrw in in notepad and place it somewhere inside the fpdrw?
| |
|
|
|
drogers
Posts: 142
Joined: 5/7/2004
Status: offline
|
RE: update query - 5/25/2005 12:32:21
Here is the info on doing a spooky diet for a single page (that is, there was a thread somewhere about creating a spooky diet macro, but I can't find it):
http://www.outfront.net/spooky/adv_new_id.htm
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: update query - 5/25/2005 13:36:32
OKay...
I put the code on the spooky diet with the macro button
i changed the sql to count a field called ImID rather than UserName
Here is the new code
<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<% user = Session(appName&"Username")%>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry=" SELECT count(ImID) AS countofImID FROM Spooky_IM WHERE username = user"
fp_sDefault=""& user &"="
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=4
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<p>
<font color="#FFFFFF">
<%=FP_FieldVal(fp_rs,"countofImID")%></font></p>
The database does not error out it just returns a value of 0 for <%=FP_FieldVal(fp_rs,"countofImID")%>.
It should say 3
| |
|
|
|
rdouglass
Posts: 9202
From: Biddeford, ME USA
Status: offline
|
RE: update query - 5/25/2005 14:13:13
I don't know what the rest of all that other stuff is for but if you don't want to use a separate query you can use:
<%=fp_iCount%>
if you're inside the DRW. If you want to use it outside (and after the DRW) just assign a variable to it's value:
<%DIM myCount
myCOunt = fp_iCount%>
Then just call <%=myCount%> outside the DRW. That'll give you a count of all the records pulled from the query.
PS> If you use it in conjunction with a COUNT query, you won't get the count using fp_iCount.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: update query - 5/25/2005 14:22:52
rdouglas...
Thank you for your reply....
The situation is that I want to be able to count messages based on what "User" is logged into my system.
I have been trying to use
<% user = Session(appName&"Username")%>
and have the sql read
SELECT count(ImID) AS countofImID FROM Spooky_IM WHERE username = user
I have not been able to place the
<% user = Session(appName&"Username")%>
to a spot where it works.
My results just say 0 on the page
| |
|
|
|
drogers
Posts: 142
Joined: 5/7/2004
Status: offline
|
RE: update query - 5/25/2005 14:31:22
You can use what rdouglass suggested, if it works.
Try this code on the page:
|<%= Session(appName&"Username")%>|
(use the | & | around the session variable to check for leading and trailing spaces)
Now check to make sure that it matches the field name in the database.
What I do when I encounter problems like this is to write the QRY with values instead of variables, so put an actual username that should return a number of rows in the WHERE clause and see if it returns the value you expect. If it does, you've narrowed the problem down a bit.
fp_sQry=" SELECT count(ImID) AS countofImID FROM Spooky_IM WHERE username = 'gina'"
Now that I typed that out, I'm wondering if we need to let it know that user is text. Try this:
fp_sQry=" SELECT count(ImID) AS countofImID FROM Spooky_IM WHERE username = '" & user & "'"
| |
|
|
|
rdouglass
Posts: 9202
From: Biddeford, ME USA
Status: offline
|
RE: update query - 5/25/2005 14:52:49
Actually, don't want to throw too much of a wrench into things but if you're trying to track user posts, how about this logic:
Spooky Login has at least 1 spare field.
Use the spare field to record the number of posts. Each post increment by one; each delete, decrease by 1.
In my way of thinking doing a CountOf query each time could cause unnecessary stress on the server DB engine, especially when it gets busy. A simple grab of a single field is far more efficient IMO.
And yes, you will need the apostrophes around the user variable as drogers suggests.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: update query - 5/25/2005 15:20:33
OK...
The last post from DROGERS made it all happen.
RDOUGLAS- Im going to try what you said next. I think it is a very good idea!
You guys are always teaching me new things... i don't know what I would do without everyone here!
Thank you very much
-Rich
| |
|
|
|
drogers
Posts: 142
Joined: 5/7/2004
Status: offline
|
RE: update query - 5/25/2005 16:11:36
Hey Rich, glad I could 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 
