get BLOB from database based on dropdown list selected value

[Elmo_loves_you]

Hi,

I have a webpage set up that allows users to upload a file to a server from the fileupload control, which is then saved to the database with the blob file and the file name. What I would like to do now is retrieve this file by selecting a particular value in a dropdown list and clicking on a 'download file' button.

This would then search the database for the ID of the file selected and then throw back the file giving the user the option to 'open, save or cancel'

I have found the following code that retrieves a blob from a database. But this does it only for a specific hardcoded filename. What I have been trying to do is write a stored procedure that takes the ID of the file and returns the BLOB....Its already in the database I just need a little help getting it out now...has anyone any ideas?

SqlDataAdapter da = new SqlDataAdapter("Select * From pub_info", myConnection);
                SqlCommandBuilder MyCB = new SqlCommandBuilder(da);
                DataSet ds = new DataSet();

                myConnection.Open();
                da.Fill(ds, "Pub_info");
                DataRow myRow;
                myRow = ds.Tables["Pub_info"].Rows[0];

                byte[] MyData;
                MyData = (byte[])myRow["logo"];

                Response.Buffer = true;
                //Replace the following commented out line with the lines below for word doc
                //Response.ContentType = "Image/JPEG";
                Response.AddHeader("Content-Disposition", "attachment;filename=blob.doc");
                Response.ContentType = "application/msword";
                
               Response.BinaryWrite(MyData);


                MyCB = null;
                ds = null;
                da = null;

                myConnection.Close();
                myConnection = null;

Please provide some suggestions.
Elmo

[Elmo_loves_you]

The following code it what I have for getting the blob from the database based on a value in a textbox.....its pulling out a blob but only the first one in the table.....anyone want to help me get this to loop through the table searching for the correct one

SqlConnection NewConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Assignment_Submissions.mdf;Integrated Security=True;User Instance=True"); //Connection string declared in web.config file
            NewConnection.Open();

            dataAdapter = new SqlDataAdapter("SELECT * FROM Assignment", myConnection);
            SqlCommandBuilder cb = new SqlCommandBuilder(dataAdapter);
            DataSet ds = new DataSet();

            command = new SqlCommand("getBrief", NewConnection);    //getAssBrief
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@assignmentID", 


SqlDbType.Int, 4, "AssignmentID"));
            command.Parameters["@assignmentID"].Value = txt_AssignmentID_ViewBrief.Text;

            reader = command.ExecuteReader();
            //byte[] myFile;
            string tempFilename = "";
            //string fileType = "";

            //StringBuilder sb = new StringBuilder();
            while (reader.Read())
            {
                tempFilename = reader.GetString(0);
                //sb.Append(reader.GetValue(0));
                //sb.Append(",");

            } //NewConnection.Close();

            myConnection.Open();
            
            dataAdapter.Fill(ds, "Assignment");
           
            DataRow myRow;
            myRow = ds.Tables["Assignment"].Rows[0];

            byte[] myData;
            myData = (byte[])myRow["AssignmentBrief"];

            Response.Buffer = true;
            Response.AddHeader("Content-Disposition", "attachment;filename=" + tempFilename); // + filename
            Response.ContentType = "application/msword";
            Response.BinaryWrite(myData);

            cb = null;
            ds = null;
            dataAdapter = null;