Attachment does not get uploaded to .mdb but no errors occur whatsoever

Question

I need to upload an attachment to a MS Access .mdb, nonetheless, the code runs until the end, but nothing gets updated.

Reviewing the query syntax and checking the OleDbTypes that are used in the code.

protected void Button1_Click(object sender, EventArgs e)
{
    mdlTrab mdl = new mdlTrab();
    mdl.nome = txtNome.Text;
    mdl.trab = titTrab.Text;
    mdl.campo = FileUpload1.FileBytes;

    if (FileUpload1.HasFile)
    {
        try
        {
            string fileExtension = Path.GetExtension(FileUpload1.FileName);
            if (fileExtension.ToLower() == ".doc" || fileExtension.ToLower() == ".docx" || fileExtension.ToLower() == ".pdf")
            {
                if (FileUpload1.PostedFile.ContentLength > 1024000)
                {
                    StatusLabel.ForeColor = System.Drawing.Color.Red;
                    StatusLabel.Text = "Status do upload: O arquivo deve ter menos de 1000 kb!";
                }
                else
                {
                    string conexaoAccess = ConfigurationManager.ConnectionStrings["conexaoAccess"].ToString();
                    using (OleDbConnection conexaodb = new OleDbConnection(conexaoAccess))
                    {
                        conexaodb.Open();
                        OleDbCommand cmd = new OleDbCommand("UPDATE tbl_reg SET Campo1 = @campo, titulo_trab = @trab WHERE nome_user = @nome", conexaodb);

                        cmd.Parameters.Add("@campo", OleDbType.LongVarBinary).Value = mdl.campo;
                        cmd.Parameters.Add("@trab", OleDbType.LongVarChar).Value = mdl.trab;
                        cmd.Parameters.Add("@nome", OleDbType.LongVarChar).Value = mdl.nome;

                        int rowsChanged = cmd.ExecuteNonQuery();
                        StatusLabel.ForeColor = System.Drawing.Color.Green;
                        StatusLabel.Text = "Arquivo carregado!";
                    }

                }
            }
            else
            {
                StatusLabel.ForeColor = System.Drawing.Color.Red;
                StatusLabel.Text = "Status do upload: Somente arquivos .doc, .docx e .pdf são aceitos!";
            }
        }
         catch (InvalidOperationException ex)
        {
            StatusLabel.ForeColor = System.Drawing.Color.Red;
            StatusLabel.Text = "Status do upload: Erro ao carregar arquivo, ocorreu o seguinte erro: " + ex.Message;
        }
        catch (OleDbException ex)
        {
            StatusLabel.ForeColor = System.Drawing.Color.Red;
            StatusLabel.Text = "Status do upload: Erro ao atualizar banco de dados, erro oorrido: " + ex.Message;
        }

        catch (Exception ex)
        {
            Console.WriteLine("Erro: ", ex.Message);
        }
    }
}

The Model

public class mdlTrab
{
    public int codigo { get; set; }
    public string nome { get; set; }
    public string trab { get; set; }
    public byte[] campo { get; set; }
}

Expected: Upload the file in the database. Actual results: no results

Empty catch blocks are a really bad idea. Also, your empty if blocks are a fairly bad idea. In years to come someone will use this code and be like "why isn't this f*%]*} document uploading?" And they will have to read the code to find out that it's limited to a megabyte. For the love of sanity, put some logging or message in there at least — Caius Jard, Nov 05 '19 at 22:25
`and no error,` Your comment may or may not be true - https://softwareengineering.stackexchange.com/questions/291283/what-if-the-catch-block-is-empty — mjwills, Nov 05 '19 at 22:26
You code swallows Excetpions. All Exceptions. There is **no way** for you to tell if there was one anywhere inside the try, thanks to `catch (Exception ex) { }` Fix that part first. Here are two articles on proper exception handling that I link often: https://blogs.msdn.microsoft.com/ericlippert/2008/09/10/vexing-exceptions/ | https://www.codeproject.com/Articles/9538/Exception-Handling-Best-Practices-in-NET — Christopher, Nov 05 '19 at 22:32
No, the ifs and the catch are empty on purpose, just to make it smaller to post here.. — tkruise, Nov 06 '19 at 11:31
Code updated, ExecuteNonQuery placed, Try Catches that i´m currently using were added to the post. — tkruise, Nov 06 '19 at 12:06
Query error: An UPDATE or DELETE query cannot contain a multi-valued field, is it about the many fields that I want to update or about the LongVarBinary? — tkruise, Nov 06 '19 at 12:07
`No, the ifs and the catch are empty on purpose, just to make it smaller to post here..` Don't do that in future - especially if your question says `no error`. — mjwills, Nov 06 '19 at 22:32
Did you Google for `An UPDATE or DELETE query cannot contain a multi-valued field`? Please update your question, since it says no errors occur (which is no longer true). — mjwills, Nov 06 '19 at 22:33

Steve · Answer 1 · 2019-11-06T07:26:54.883

You need to call ExecuteNonQuery to effectively execute the command but there is another error that you need to fix. With OleDb, parameters are not recognized by their name but by their position in the command text.

You need to add the parameters in the exact order in which they appear in your command and so, rearrange the code lines in this order

cmd.Parameters.Add("@campo", OleDbType.LongVarBinary).Value = mdl.campo;
cmd.Parameters.Add("@trab", OleDbType.LongVarChar).Value = mdl.trab;
cmd.Parameters.Add("@nome", OleDbType.LongVarChar).Value = mdl.nome;

and finally call

int rowsChanged = cmd.ExecuteNonQuery();

Of course, all the comments above about the empty try/catch are very important. If you do not handle the exception (for example writing to a log file or displaying a message) then it is better to remove the try/catch block.
Another point that you should consider is the disposing of the connection object. This object keeps internally unamanaged resources and you need to free them as soon as possible. So you should use the using statement

string conexaoAccess = ".....";
using(OleDbConnection conexaodb = new OleDbConnection(conexaoAccess))
{  
    conexaodb.Open();

    ... create the command, add parameters and execute the command

} // Here the connection will be closed and disposed

since the connection object is inside the using I won´t have to conexaodb.Close()? It will be automatically disposed upon closing the } ? — tkruise, Nov 06 '19 at 11:49
Yes, exactly. The using statement will do it for you. The using statement is replaced at compile time by code that will always call the Dispose method at exit. And Dispose calls Close on a connection object. This is a link to a question that explains what code will replace the using statement when compiled: https://stackoverflow.com/questions/946999/curious-c-sharp-using-statement-expansion — Steve, Nov 06 '19 at 13:37
Appreciate the help Steve, if I could vote you up I would. Thanks! — tkruise, Nov 15 '19 at 12:43
I do know Steve, sorry. I posted the solution above, you gave me very helpful tips but they didn’t help me through the root of the problem. — tkruise, Nov 15 '19 at 14:13

score 0 · Accepted Answer · answered Nov 15 '19 at 12:40

0

Solved by inserting an hyperlink to the file into the field, for what I was looking for it was the best approach, DAO just wouldn´t attend to my needs: Pasting a hyperlink value from C# to Access

Appreciate the help. Thank you folks!

answered Nov 15 '19 at 12:40

tkruise

3
7

Attachment does not get uploaded to .mdb but no errors occur whatsoever

2 Answers2