DetailsView FileUpload Image to SQL

Dark-Star · April 12th, 2015, 09:05 PM

I added everything to this and it worked flawlessly.

OMG so many hours working through this one. I learned A LOT from this that I can continue to apply in other parts of my application!

I just needed to go back to _RowUpdating like you recommended and figure out the error of my ways.

Thank you again!

Imar · April 13th, 2015, 03:19 PM

Yep, you can save it directly from that event. You can retrieve the bytes from the FileUpload control.

Cheers,

Imar

Dark-Star · April 19th, 2015, 09:42 PM

I understand that an empty string is not NULL and I can't figure out how to ignore the @parameter during an update if the control has not been filled out by the user.

I pass all the @parameters during a record updated from a asp.net web form. On button click the following code is executed.

Code:

                    // Reusing the string from above
                    // string constr = ConfigurationManager.ConnectionStrings["sqloitmaineConnectionString"].ConnectionString;

                    using (SqlConnection con = new SqlConnection(constr))
                    {

                        string query = "INSERT INTO ProblemDescription (ImageName, Image, ProblemTitleNumb, ProblemDescription, FileType) values (@ImageName, @Data, @ProblemTitleNumb, @ProblemDescription, @DocumentLocation)";

                        using (SqlCommand cmd = new SqlCommand(query))
                        {

                            cmd.Connection = con;

                            TextBox TB3 = DetailsView1.FindControl("TextBox3") as TextBox;
                            TextBox TB5 = DetailsView1.FindControl("TextBox5") as TextBox;

                            cmd.Parameters.AddWithValue("@DocumentLocation", TB3.Text);
                            cmd.Parameters.AddWithValue("@ImageName", filename);
                            cmd.Parameters.AddWithValue("@Data", bytes);
                            cmd.Parameters.AddWithValue("@ProblemTitleNumb", Id3identity);
                            cmd.Parameters.AddWithValue("@ProblemDescription", TB5.Text);

                            con.Open();

                            cmd.ExecuteNonQuery();

                            con.Close();
                            con.Dispose();

I am not sure exactly how to approach this one. Excluding one @parameter would be easy but since there are 3 that can be NULL (once passed into update they wont pass as NULL). I need them to pass as NULL if there is nothing inputted into the controls of the forms.

Any help would be appreciated.

Dark-Star · April 20th, 2015, 01:42 AM

This is how I solved it:

Code:

using (SqlConnection con = new SqlConnection(constr))
                    {

                        string query = "INSERT INTO ProblemDescription (ImageName, Image, ProblemTitleNumb, ProblemDescription, FileType) values (@ImageName, @Data, @ProblemTitleNumb, @ProblemDescription, @DocumentLocation)";

                        using (SqlCommand cmd = new SqlCommand(query))
                        {

                            cmd.Connection = con;

                            TextBox TB3 = DetailsView1.FindControl("TextBox3") as TextBox;
                            TextBox TB5 = DetailsView1.FindControl("TextBox5") as TextBox;

                                
                                
                                
                                cmd.Parameters.AddWithValue("@ProblemTitleNumb", Id3identity);
                                cmd.Parameters.AddWithValue("@ProblemDescription", TB5.Text);

                                    // Check for no user input in control parameter
                                if (String.IsNullOrEmpty(TB3.Text) == true)
                                {   // Assign NULL to string or not
                                    cmd.Parameters.AddWithValue("@DocumentLocation", DBNull.Value);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@DocumentLocation", TB3.Text);
                                }
                                if (String.IsNullOrEmpty(filename) == true)
                                {   // Assign NULL to string or not
                                    cmd.Parameters.AddWithValue("@ImageName", DBNull.Value);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@ImageName", filename);
                                }
                                if  (fs.Length.Equals(0))
                                {   // Assign NULL to byte or not (set the type of parameter to Image) When passing in a NULL value, you need to specify that SqlDbType yourself, explicitly.
                                    SqlParameter ImageParam = new SqlParameter("@Data", SqlDbType.Image);
                                    ImageParam.Value = DBNull.Value;
                                    cmd.Parameters.Add(ImageParam);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@Data", bytes);
                                }

                                con.Open();

                                cmd.ExecuteNonQuery();

                                con.Close();
                                con.Dispose();

Is there a better way?

Imar · May 3rd, 2015, 10:50 AM

Another solution is to pass DBNull.Value when then the string is empty:

cmd.Parameters.AddWithValue("@DocumentLocation", !string.IsNullOrEmpty( TB3.Text) ? TB3.Text : DBNull.Value);

Hope this helps,

Imar