Solved

SQL Data Reader does not have any record output

Posted on 2013-06-18
10
94 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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

729 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