Getting OUTPUT values back from SQL Stored Procedure

[sean_morris9]

Hi,

I'm looking for help with this. I'm tring to get an image back from my SQL Server Express database. The code below is where I'm calling the stored procedure from. If I leave out the IF STATEMENT and WHILE LOOP I'm getting an error saying it's trying to read data when no data is present. From this I get that the SPOC isn't returning anything. I tried changing the parameter direction to inputoutput instead of output, but it's the exact same. It's also the same when I don't put the parameters = NULL.

string s; // creates a string to hold connection string
            //set string = connection string from web.config
            s = (ConfigurationManager.ConnectionStrings["Accommodation_Centre_Connection"].ToString());
            //create the connection using
            SqlConnection conn = new SqlConnection(s);
            conn.Open();//opens connection
            int pic_id = Convert.ToInt32(Request.QueryString["img"]); //declares a string to hold pic id
            SqlDataReader adm = null;//declares the data reader
           
            //Call the stored procedure
            
            SqlCommand cmd = new SqlCommand("spGet_image", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(
              new SqlParameter("@Image_id", pic_id));
            SqlParameter Image = new SqlParameter("@image", SqlDbType.Binary);
            //Image = null;
            Image.Direction = ParameterDirection.InputOutput;
            Image.Value = null;
            Image.Size = 8000;
            cmd.Parameters.Add(Image);
            SqlParameter Type = new SqlParameter("@type", SqlDbType.NVarChar);
           // Type=null;
            Type.Direction = ParameterDirection.InputOutput;
            Type.Value = null;
            Type.Size = 50;
            cmd.Parameters.Add(Type);
          
            adm = cmd.ExecuteReader();




            if (adm.HasRows)
            {
                while (adm.Read())
                {
                    Response.Redirect("image.aspx");
                    Response.ContentType = adm["Type"].ToString();
                    Response.BinaryWrite((byte[])adm["Image"]);
                    Response.Write("hello!!!!");
                }
                
            }
            else
            {
                Response.Write("NOOOOOOOOOOOOOOOOOOO");
            }
           
            
           
            adm.Close();
            conn.Close();

NOTE: It's not outputting eiter Response.Write("hello!!!!"); or Response.Write("NOOOOOOOOOOOOOOOOOOO"); I don't understand this, surely the reader either has rows or doesn't?

Below is the SPROC

ALTER PROCEDURE dbo.spGet_image 
	
	(
	@Image_id int = NULL,
	@type nvarchar(50) = NULL OUTPUT,
	@image varbinary(MAX)= NULL OUTPUT
	)

AS
BEGIN
SET @type=(
SELECT Image.Image_MIME_type
FROM Image
WHERE Image.Image_id = @Image_id)

SET @image=(
SELECT Image.Image_image
FROM Image
WHERE Image.Image_id = @Image_id)

END

Again I've tried variations on this. I've tried it without having the parameters = NULL. I've also tried leaving out the BEGIN and END, and a few more things. Any help would be appreciated!!!

Thanks

[Ramy Mahrous]

I see your SP not written well what about that

alter PROCEDURE dbo.spGet_image  	
(	@Image_id int
) AS
SELECT [Image_MIME_type]
      ,[Image_image]
  FROM [MailSystem].[dbo].[image] WHERE Image.Image_id = @Image_id

Pass your image ID

SqlCommand cmd = new SqlCommand("spGet_image", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(
              new SqlParameter("@Image_id", SqlDbType.INT,4));
cmd.Parameters["@Image_id"].Value = pic_id;
adm = cmd.ExecuteReader();
            if (adm.HasRows)
            {
                while (adm.Read())
                {
                    //System.Windows.Forms.MessageBox.Show("We got data");
                    Response.Redirect("image.aspx");
                    Response.ContentType = adm["Type"].ToString();
                    Response.BinaryWrite((byte[])adm["Image"]);
                    Response.Write("hello!!!!");
                }

            }
            else
            {
                System.Windows.Forms.MessageBox.Show("We have no data");
            }

It works with me :)

[sean_morris9]

Hi Ramy,
It's getting data back from the database now, but it's coming up as a broken image. Any ideas? So close!

if (adm.HasRows)
            {
                
                while (adm.Read())
                {
                 // Response.Write(adm["Image_MIME_type"].ToString());
                 
                        Response.ContentType = adm["Image_MIME_type"].ToString();
                        Response.BinaryWrite((byte[])adm["Image_image"]);  
                }
            }
            else
            {
                Response.Write("Reaer has no rows");

            }
            adm.Close();
            conn.Close();

[Ramy Mahrous]

did u insert the whole binary data for the image??!
how did u insert let me check your code??

[sean_morris9]

Thanks for alll you help Ramy,

Here is the code for inserting the image...
code behind page..

int imgLen = FileUpload1.PostedFile.ContentLength;
            string imgContentType = FileUpload1.PostedFile.ContentType;
            string imgName = txtname.Text;
            string ac_name; //declares a string to hold username
            
            ac_name = txtid.Text;//please ignore name of text box it's for the name not id
            Who_Am_I who = new Who_Am_I();
            int accommodation_id = who.mGet_ID(ac_name);
            string description = txtdescription.Text;
            byte[] imgBinaryData = new byte[imgLen];
           
            Content_Manager content = new Content_Manager();
            content.Insert_Image(accommodation_id, imgName, description, imgContentType, imgBinaryData);

Content_Manager C# class

public void Insert_Image(int id, string image_name, string alt, string type, byte[] img)
        {
            // Get a connection to the database
            string s;
            s = (ConfigurationManager.ConnectionStrings["Accommodation_Centre_Connection"].ToString());
            SqlConnection conn = new SqlConnection(s);
            conn.Open();//opens connection
            SqlDataReader adm = null;//declares the data reader
            //Call the stored procedure
            SqlCommand cmd = new SqlCommand("spInsert_Photo", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(
                new SqlParameter("@accomm_id", id));
            cmd.Parameters.Add(
                new SqlParameter("@name", image_name));
            cmd.Parameters.Add(
                new SqlParameter("@alt", alt));
            cmd.Parameters.Add(
                new SqlParameter("@type", type));
            cmd.Parameters.Add(
                new SqlParameter("@image", img));
                        adm = cmd.ExecuteReader();
            adm.Close();
            conn.Close();
        }

everything seems to be inserting into the database fine. Binary data shown as <binary data>. That's normal I think.

Maybe the binary is corrupted in some way?

Thank u so much for the help, I'm really under pressure.

Sean