update query (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

sentinel -> 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 -> 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?



sentinel -> 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 -> RE: update query (5/25/2005 10:31:11)

Can you post the code from imposter.asp?



sentinel -> 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=&amp;status=&amp;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 -> 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 -> 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?



sentinel -> 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?



drogers -> 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 -> 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 -> 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 -> 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 -> 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 -> RE: update query (5/25/2005 12:15:14)

you can put that code right into the FPDRW sql box?



drogers -> 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 -> 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 -> RE: update query (5/25/2005 12:30:11)

Yeah, it's not going to work.  The suggested work around is:

http://www.frontpagewebmaster.com/m_81288/p_4/tm.htm



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



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



sentinel -> 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 -> RE: update query (5/25/2005 16:11:36)

Hey Rich, glad I could help.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
6.152344E-02