asked on

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

graye

What is "[Stream]" in your SQL?

SiliconXP

remove stream from the query and check the contents of your parameter

byte[] CrystalRepByteArr

the code works for with me after that change

Adnan

ASKER

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

ASKER CERTIFIED SOLUTION

graye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Adnan

ASKER

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

Adnan

ASKER

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

Adnan

ASKER

correct!