How to update db table column with binary data.....

iam trying to update my setting table where i have a column name uploadfile wich i want to be updated when i select a crystal report rpt. file....
i do no get any exception or error, its executes the sql, but when i check my table the column is null......nothing has been updated.....! the query does execute but the table is not being updated... :(


Can somone help me with this?`first time iam working with updating a column with rpt file!!!
public void InsertRPT(byte[] CrystalRepByteArr)
        {
            try
            {
                string sql = "UPDATE setting SET [Stream] Uploadfile = @CrystalRepByteArr WHERE Setting_ID = 3";// +CrystalRepByteArr;
                IDbDataAdapter ad = GetDataAccess().GetNewDataAdapter(myDataAccess.GetNewCommand(sql));
                IDbCommand idcmd = GetDataAccess().GetNewCommand(sql);
                IDataParameter param = GetDataAccess().AddParameter(idcmd, "@CrystalRepByteArr", Adra.Data.DbType.VarBinary);
                param.Value = CrystalRepByteArr;
                GetDataAccess().ExecuteSql(idcmd);
               //int result = myDataAccess.EmptyBatch();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Open in new window

AdnanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

grayeCommented:
What is "[Stream]" in your SQL?
0
SiliconXPCommented:
remove stream from the query and check the contents of your parameter

byte[] CrystalRepByteArr

the code works for with me after that change
0
AdnanAuthor Commented:
i removed stream from my sql but still it dosent update the column......the value is null.

so i found another example on the internet and i changed the code below... and the code is working fine,  do not get any exception....if i se my sql server profiler the sql has been executed....but the column in db table is still null....?!

 string sql = "UPDATE Setting SET Uploadfile = (convert (binary(32),@CrystalRepByteArr)) WHERE Setting_ID = 3";
                string signup = conn;
                SqlConnection myConnection = new SqlConnection(signup);
                myConnection.Open();
                SqlCommand cmd = new SqlCommand(sql, myConnection);
                
                IDbDataAdapter ad = GetDataAccess().GetNewDataAdapter(myDataAccess.GetNewCommand(sql));
               // cmd.Parameters.AddWithValue("CrystalRepByteArr", "@CrystalRepByteArr");
                IDataParameter param = GetDataAccess().AddParameter(cmd, "@CrystalRepByteArr", Adra.Data.DbType.VarBinary);
                param.Value = CrystalRepByteArr;
                
                int i = cmd.ExecuteNonQuery();

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

grayeCommented:
Why are you adding extra stuff to the Update command?    There's no need for the convert, etc.   This is essentially no different from updating an integer field.
Just make sure that the database field is configured as a System.Byte[] and the data you're puting in there is a System.Byte[]
What is Adra?   What kind of database is this (including version number)?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AdnanAuthor Commented:
oki, sorry for late response, but i resolved my prob like the code below:

But it is a problom when you bulk insert you have to be bulk admin.....bla bla...
så i have to change the sql again for just insert the byte array direct into table.
 string sqlInsertFile = "INSERT INTO FileUpload(Filname,Extention,FileSize,[Date],[File])" +
                             " SELECT'" + fileName + "','" + extention + "','" + fileSize + "','" + dateTime +
                             "',BulkColumn FROM OPENROWSET(Bulk '" + file + "', SINGLE_BLOB) AS BLOB; SELECT @File_Id = SCOPE_IDENTITY()";

                cmd = myDataAccess.GetNewCommand(sqlInsertFile);
                cmd.Connection.Open();

                // Add image as SQL parameter
                SqlParameter p = new SqlParameter();
                //p.ParameterName
                p.ParameterName = "@File_Id";
                p.Size = 8;
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);
                
                SqlDataReader dr = (SqlDataReader)cmd.ExecuteReader();
                RptFileidOut = p.Value.ToString();
                dr.Read();
               
                cmd.Connection.Close();

Open in new window

0
AdnanAuthor Commented:
Here is m,y insert function without bulk inset statemnt...:
 public void InsertRPT(string fileName, string extention, string fileSize, DateTime dateTime, byte[] file)
        {
            try
            {
                //string sql = "UPDATE Setting SET Uploadfile = @CrystalRepByterr WHERE Setting_ID = 3";
                //string sqlInsertFile = "INSERT INTO FileUpload(Filname,Extention,FileSize,[Date],[File])" +
                //             " SELECT'" + fileName + "','" + extention + "','" + fileSize + "','" + dateTime +
                //             "',BulkColumn FROM OPENROWSET(Bulk '" + file + "', SINGLE_BLOB) AS BLOB; SELECT @File_Id = SCOPE_IDENTITY()";
                string sqlInsertFile =
                "INSERT INTO FileUpload(Filname,Extention,FileSize,[Date],[File])VALUES ('" + fileName + "'," +
                         " '" + extention + "', " + fileSize + ",'" + dateTime + "',@file); SELECT @File_Id = SCOPE_IDENTITY()";
               
                cmd = myDataAccess.GetNewCommand(sqlInsertFile);
                cmd.Connection.Open();

                // Add image as SQL parameter
                SqlParameter p = new SqlParameter();
                //p.ParameterName
                p.ParameterName = "@File_Id";
                p.Size = 8;
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);

                SqlParameter p2 = new SqlParameter("@file",SqlDbType.VarBinary);
                p2.Value = file;
                //p.ParameterName

                //p2.ParameterName = ;
 
                 cmd.Parameters.Add(p2);

                 int dr = cmd.ExecuteNonQuery();
                 RptFileidOut = p.Value.ToString();
                //dr.Read();
               
                cmd.Connection.Close();

            }
            catch
            {
                //MessageBox.Show("Failed to insert the .rpt file" + ex.Message);
                cmd.Connection.Close();
                throw;
            }
            
        }

Open in new window

0
AdnanAuthor Commented:
correct!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.