Solved

SQL Data Reader does not have any record output

Posted on 2013-06-18
10
88 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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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
Comment Utility
My overall goal is to return the @ProfilePictureUrl parameter as an output from the stored procedure.
0
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 167 total points
Comment Utility
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
 

Author Comment

by:VBBRett
Comment Utility
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
Comment Utility
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Anthony was correct.  I have since corrected what comments I have chosen as my answers.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now