Solved

SQL Data Reader does not have any record output

Posted on 2013-06-18
10
98 Views
Last Modified: 2015-02-22
I wrote the following code and for some reason, I am not getting any output.  Essentially, there is no data in the stored procedure or the code that is being used.  Why am I getting this issue?  The funny thing is, when I enter the Profile GUID manually, I get a record in the stored procedure output.  Please help!  

Below is my code:

 public System.Data.SqlTypes.SqlBinary GetProfilePicture(Guid profileid)
        {
	    Profile profile = new Profile();

            SqlBinary profilepic;

            SqlConnection objConnection = new SqlConnection();
            DataSet objDataset = new DataSet();
            SqlDataAdapter objAdapter = new SqlDataAdapter();

            SqlCommand objCommand = new SqlCommand("spGetProfilePictureByProfileId");
            objCommand.CommandType = CommandType.StoredProcedure;

            profile.ProfileId = profileid;
            
            objConnection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
            objConnection.Open();
            objCommand.Connection = objConnection;
            objCommand.Parameters.Add(new SqlParameter("@ProfileId", profile.ProfileId));

            SqlParameter sqlparam = new SqlParameter();

            sqlparam.ParameterName = "@ProfilePictureUrl";
            sqlparam.Direction = ParameterDirection.Output;
            sqlparam.Size = -1;

            objCommand.Parameters.Add(sqlparam.ParameterName, SqlDbType.VarBinary);

            


            SqlDataReader reader;


             reader = objCommand.ExecuteReader();

            

            System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();

       
       
            if (reader.HasRows)
            {
                while (reader.Read())
                {

                    int fieldIndex = 1;
                    Byte[] raw;

                    if (reader.IsDBNull(fieldIndex))
                        raw = new byte[0];
                    else
                    {
                        raw = new Byte[(reader.GetBytes(fieldIndex, 0, null, 0, int.MaxValue))];
                        if (raw.Length > 0)
                            reader.GetBytes(fieldIndex, 0, raw, 0, raw.Length);
                    }

                    profilepic = raw;
                    profile.ProfilePictureUrl = profilepic;
                }

            }

            return profile.ProfilePictureUrl;

        }       

Open in new window


and the following is my stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[spGetProfilePictureByProfileId]
    @ProfileId			  uniqueidentifier,
	@ProfilePictureUrl		  varbinary(max) = null output 
AS
BEGIN
	
	SELECT	@ProfilePictureUrl = ProfilePictureUrl
	FROM	Profile
	WHERE ProfileId = @ProfileId
	
    IF (@@rowcount = 0)
        RETURN(1)
    RETURN(0)
END

Open in new window

0
Comment
Question by:VBBRett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39257953
The reader wouldn't have any data; the parameter @ProfilePictureUrl would. You didn't write a proc that would return data other than a simple 0 or 1, in addition to an output parameter. What is the overall goal here?
0
 

Author Comment

by:VBBRett
ID: 39257981
My overall goal is to return the @ProfilePictureUrl parameter as an output from the stored procedure.
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 167 total points
ID: 39258018
OK, change this line:

reader = objCommand.ExecuteReader();

...to this:

objCommand.ExecuteNonQuery();

Open in new window


And change this:

if (reader.HasRows)
{
	while (reader.Read())
	{

	    int fieldIndex = 1;
	    Byte[] raw;

	    if (reader.IsDBNull(fieldIndex))
		raw = new byte[0];
	    else
	    {
		raw = new Byte[(reader.GetBytes(fieldIndex, 0, null, 0, int.MaxValue))];
		if (raw.Length > 0)
		    reader.GetBytes(fieldIndex, 0, raw, 0, raw.Length);
	    }

	    profilepic = raw;
	    profile.ProfilePictureUrl = profilepic;
	}

}

Open in new window


...to this:

byte[] picData = (byte[])objCommand.Parameters["@ProfilePictureUrl"].Value;  // You might need to remove @ here, I can't remember

Open in new window


Then do something with "picData".

I'm not sure what you were attempting to do because "ProfilePictureUrl" implies to me that you were looking for a string, but your code suggests you were wanting the binary data of an image. The changes I suggest above are with respect to the latter.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:VBBRett
ID: 39258031
The value comes out to be null.  I don't get it.  Should I remove the tail end of the query where I have the following?

IF (@@rowcount = 0)
        RETURN(1)
    RETURN(0)
0
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 167 total points
ID: 39258047
I believe the issue when you ado.net is trying to map a appropriate conversion type

I believe it is not able to map the ProfileID type of GUID (in .net) to @profileID of UniqueIdentifier ( int stored proc)...

Convert the GUID type to uniqueIdentifier in .net function before assigning to the parameter and add the converted one to the new parameter function.

the below link might help you to get the enumeration
http://msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39258258
My overall goal is to return the @ProfilePictureUrl parameter as an output from the stored procedure.
Then as previously indicated (no points please) use the ExecuteNonQuery method instead of the ExecuteReader method.  Simply put your Stored Procedure does not return a resultset, does it (use SSMS to convince yourself) ?  Therefore you should not be using ExecuteReader.

Post your code using ExecuteNotnQuery and we can help further.
0
 

Author Comment

by:VBBRett
ID: 39263635
So apparently when I removed the parameter for the return value and just did a typical select statement in the stored procedure, everything seemed to have worked.
0
 

Author Comment

by:VBBRett
ID: 40624135
I've requested that this question be closed as follows:

Accepted answer: 250 points for kaufmed's comment #a39258018
Assisted answer: 250 points for Surendra Nath's comment #a39258047
Assisted answer: 0 points for VBBRett's comment #a39263635

for the following reason:

My solution was the number one solution to fix my problem.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 40623866
My solution was the number one solution to fix my problem.
It is also the least efficient.  Why would you want to return a result set when all you need is a single value?  Of the three ADO methods (you have actually chosen the worst.
0
 

Author Closing Comment

by:VBBRett
ID: 40624136
Anthony was correct.  I have since corrected what comments I have chosen as my answers.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question