Solved

SQL Data Reader does not have any record output

Posted on 2013-06-18
10
93 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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