[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 122
  • Last Modified:

SQL Data Reader does not have any record output

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
VBBRett
Asked:
VBBRett
  • 5
  • 2
  • 2
  • +1
3 Solutions
 
käµfm³d 👽Commented:
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
 
VBBRettAuthor Commented:
My overall goal is to return the @ProfilePictureUrl parameter as an output from the stored procedure.
0
 
käµfm³d 👽Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
VBBRettAuthor Commented:
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
 
Surendra NathTechnology LeadCommented:
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
 
Anthony PerkinsCommented:
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
 
VBBRettAuthor Commented:
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
 
VBBRettAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
VBBRettAuthor Commented:
Anthony was correct.  I have since corrected what comments I have chosen as my answers.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now