Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 3rd, 2005, 02:05 PM
Authorized User
 
Join Date: Jun 2005
Location: HSP, , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default Displaying user image from sql database

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.
Reply With Quote
  #2 (permalink)  
Old June 3rd, 2005, 03:02 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old June 3rd, 2005, 05:03 PM
Authorized User
 
Join Date: Jun 2005
Location: HSP, , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #4 (permalink)  
Old June 3rd, 2005, 05:36 PM
Authorized User
 
Join Date: Jun 2005
Location: HSP, , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #5 (permalink)  
Old June 3rd, 2005, 06:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try this.
Code:
CREATE PROCEDURE sproc_IMTest
@UserID CHAR
AS
    SELECT††Username,
            UserID,
            Dir,
            THEFILE
    FROM Pictures
    WHERE 
        (PicExists='1') AND
        (Headline='1') AND
        (UserID = @UserID)
GO
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
Reply With Quote
  #6 (permalink)  
Old June 4th, 2005, 11:45 AM
Authorized User
 
Join Date: Jun 2005
Location: HSP, , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #7 (permalink)  
Old June 4th, 2005, 09:49 PM
Authorized User
 
Join Date: Jun 2005
Location: HSP, , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #8 (permalink)  
Old June 6th, 2005, 12:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old June 8th, 2005, 02:09 PM
Authorized User
 
Join Date: Jun 2005
Location: HSP, , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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>

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

Reply With Quote
  #10 (permalink)  
Old June 15th, 2005, 03:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Code:
<%=ORsFM("PicName")%>
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
Code:
<img src="http://userphotos.domain.com/<%=ORsFM("Dir")%>/<%=ORsFM("THEFILE")%>"
I dont see any issues with the way you call the procedure in the ASP code, except that
Code:
 FM = "Execute sproc_IMTest '" & UID & "', '" &DIR & "', ' & PicName & "'"
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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to load image from sql database to datagrid huyfamily SQL Server ASP 1 April 14th, 2014 11:05 PM
Displaying Database Image into Crystal Reports krishnavaloor Crystal Reports 0 February 22nd, 2008 11:28 PM
Displaying an image from database table Essien Visual Basic 2005 Basics 1 January 4th, 2006 02:40 PM
Problems Displaying an image retrieved from SQL Se jazzcatone ASP.NET 1.x and 2.0 Application Design 2 September 3rd, 2005 09:53 AM
Problem displaying User Status from SQL palvin Classic ASP Databases 0 August 2nd, 2005 03:40 PM



All times are GMT -4. The time now is 06:12 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.