Hello all,

I am new to this forums. I am working on a Instant Messaging application for one of our company websites. The Instant Messaging program is functional and I have used SQl procedures, ASP and Flash. Now I want to display user image whenever somebody starts Instant Messaging. I wrote a procedure for this thing and here is the code for that procedure.
*******************************************
CREATE PROCEDURE sproc_IMPicdisplay
@UID CHAR(16)
AS
BEGIN


    CREATE TABLE #ChatPic
    (
    ID INT IDENTITY(1,1),
    Username CHAR(16),
    UserID CHAR(15),
    DIR CHAR(15),
    PicName CHAR(50)
    )
    INSERT INTO #ChatPic(Username, UserID, DIR, PicName)
    SELECT TOP 1
            UI.Username,
            UL.UserID,
            P.Dir,
            P.THEFILE
    FROM UserInfo UI INNER JOIN Userlogin UL ON UI.Username=UL.Username
        INNER JOIN Pictures P ON P.Username=UI.Username

    WHERE
        (P.PicExists='1') AND
        (P.Headline='1')


SELECT *
    FROM #ChatPic

    DROP TABLE #ChatPic
END
/*
    SELECT TOP 1 *
    FROM UserInfo UI INNER JOIN UserLogin UL ON UI.Username = UL.Username INNER JOIN CountryInfo CI ON UI.countryid = CI.countryid INNER JOIN Pictures P ON UL.UserID = P.UserID WHERE (UI.RegistrationFlag = '3') AND (P.PicExists = '1') AND (P.HeadLine = '1') AND (UI.genderIDUser = @GenderIDUser) ORDER BY UI.DateReg DESC
*/
GO
************************************************** ************

Now I am calling this procedure in chat window so that the user picture is displayed. Here is how I call this procedure

<%
Set ConnFM = Server.CreateObject("ADODB.connection")
        GenderIDUser = "3"
        ConnFM.open "DSN=somename; uid=id; pwd=password"
        FM = "Execute sproc_IMPicdisplay '" & UserID & "'"
    Set ORsFM = ConnFM.Execute(FM)
path = "http://urltophotos.com/" & ORsFM("DIR") & "/"
%>
********************************

Here is how I am trying to display the picture on asp page

<td>
<img src=<%=path & ORsFM("PicName")%>" width="66" height="82" border="0" alt=""></a>
</td>

But my probelm is that it is picking up the same directory everytime regardless of different usernames. I just started ASP and SQl. Please suggest.

Your "Select" statement looks fine, but I dont understand why you use TOP 1 there. Is there many rows for each user that has user specific picture. If you get the same DIR for every user, the only reason could be your join is not working as expected. May be you got to rework on that.

Also I dont understand why you are inserting it into a temporary table, when the data is available straight forward. I dont see any necessity for inserting it into temp table.

_________________________
- Vijay G
Strive for Perfection

Thank you for your quick reply. I omitted TOP1 and it is still giving the same result. So do you think that a problem also might be due to temporary table? And as far as joint goes I think you are right, I might be wrong in applying the joins. Actually as I mentioned earlier, I am learning and applying my knowledge here as this is my first project in SQL.

Ok, I removed the JOINS as I am accessing the data from a single table here as I just want to retrieve user image. Here is what it looks like now
************************************************** ******
CREATE PROCEDURE sproc_IMTest

@UserID CHAR
AS
BEGIN


    CREATE TABLE #ChatPic
    (
    ID INT IDENTITY(1,1),
    Username CHAR(16),
    UserID CHAR(16),
    DIR CHAR(15),
    PicName CHAR(50)
    )
    INSERT INTO #ChatPic(Username, UserID, DIR, PicName)
    SELECT TOP 1
            P.Username,
            P.UserID,
            P.Dir,
            P.THEFILE
    FROM Pictures P

    WHERE
        (P.PicExists='1') AND

        (P.Headline='1')


SELECT *
    FROM #ChatPic

    DROP TABLE #ChatPic
END
GO

************************************************** ************

I think I am making a mistake in calling the appropriate parameter in asp code here
************************************************** *************

<%
Set ConnFM = Server.CreateObject("ADODB.connection")
        ConnFM.open "DSN=dsnname; uid=user; pwd=password"
        FM = "Execute sproc_IMTest '" & UserID & "'"
    Set ORsFM = ConnFM.Execute(FM)

%>
************************************************** ******
As here I am giving the parameter UserID.
Or the other problem might be that I am not declaring proper variable in my stored procedure at the beginning where I write

CREATE PROCEDURE sproc_IMTest

@UserID CHAR
AS
BEGIN

Can you please suggest me something because it is still giving me the same result and is showing the same old directory.

Try this. I found that, Even in your first post, you were not using the UID in the WHERE clause. In this too you are accepting USERID as Parameter in the proc, but forgot to make use of that in the WHERE clause. Thats the reason, you get the same DIR and PIC everytime as WHERE doesn't work specific to the UID/USERID that you pass to the proc.

_________________________
- Vijay G
Strive for Perfection

Hi I tried it. Now it is giving me this error.

************************************************** ***
ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/instantmessage2.asp, line 0
************************************************** ****

Hi,

I read an article here http://www.aspfaq.com/show.asp?id=2053

which says something about sessions. Now I am calling this procedure and using a parameter <%dir%> and <%pathanme%>. So do you think that might be the problem.

Thanks.

Not sure if you have made changes to the procedure to accept those two parameters, mentioned in your last post. And no code posted regarding your last but one post. So I am not sure if I got that right.

_________________________
- Vijay G
Strive for Perfection

Ok This is the procedure now:

************************************************** *******
CREATE PROCEDURE sproc_IMTest

@UID CHAR,
@DIR CHAR,
@PicName CHAR
AS
    SELECT Username, UserID, Dir, THEFILE
    FROM Pictures

    WHERE
        (PicExists='1') AND

        (Headline='1') AND
        (Dir=@DIR) AND
        (THEFILE=@PicName) AND
        (UserID=@UID)
GO
************************************************** ***

Can you suggest me how I should call and pass the parameters in ASP coz I am sure thats where I am doing something wrong.

This is what I am using right now

************************************************** ***************
<table width="160" border="0" cellspacing="0" cellpadding="1">


                <tr>
<%
Set ConnFM = Server.CreateObject("ADODB.connection")

        ConnFM.open "DSN=dsnname; uid=user; pwd=password"
        FM = "Execute sproc_IMTest '" & UID & "' , '"DIR"' , '"PicName"'"
    Set ORsFM = ConnFM.Execute(FM)
%>

                  <td align="center" valign="top">
                    <img src="http://userphotos.domain.com/<%=ORsFM("DIR")%>/<%=ORsFM("PicName")%>" width="66"

height="82" border="0" alt=""></a></td>


                      </tr>
                    </table>

************************************************** ***********

I dont really understand why you got to pass all those as parameters to the proc. As per my understanding, any user is gonna have just one pic/dir/uid, so either passing username/uid would do. But I see you passing all those except one, and not sure if you look for username by passing all others as parameters.

Also I see those params declared as CHAR with no lenght mentioned.

Also while displaying the pic, you do is something like But the select statement in your proc is retrieving just the Username, UserID, Dir, THEFILE and I dont see PicName in there. So obviously you wont be getting anything there. I believe it should have been I dont see any issues with the way you call the procedure in the ASP code, except that And the parameters in the proc seem to accept just a CHAR of length=1. Though I am not sure what the values that you pass in there.

Hope that helps.

_________________________
- Vijay G
Strive for Perfection