Update existing database entries C# VS2010

Question

I'm using Visual Studio 2010 to create a Win Form in c#. It has a handful of Comboboxes, and textboxes that the user can fill out and then submit to an Access DB. My issue comes in when I try to update existing entries. I load an existing entry, make my changes and click update. I do not get any system errors, my connection to the DB is successful, but no changes are actually made to the data. Am I completely missing something? Thanks in advance for any help or insight.

Here is the code for the update button:

    private void updateButton_Click_1(object sender, EventArgs e)
    {
        {

        OleDbConnection conn = new OleDbConnection();
        conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\servicereq1.mdb";

      OleDbCommand cmd = new OleDbCommand("UPDATE servicereq SET DateLogged = @datelogged, LoggedBy = @loggedby, Function = @function, [Other Impacts] = @summary, Account = @earningsaccount, [From] = @from, [To] = @to, Description = @description, Fixer = @fixer, [Time Estimate] = @timeestimate, [Actual Start] = @actualstart, [Actual Finish] = @actualfinish, [Actual Time] = @actualtime, [Programs/Forms] = @programsforms, Comments = @comments, [Retest Date] = @requestdate, Tester = @tester, Status = @status, [Problem In Environment] = @problemfoundin, [Code In Environment] = @codein WHERE (ServiceRequestNumber = @servreq)");
        cmd.Connection = conn;

        conn.Open();

        if (conn.State == ConnectionState.Open)
        {
            cmd.Parameters.AddWithValue("@servreq", serviceRequestNumberTextBox.Text);                
            cmd.Parameters.AddWithValue("@datelogged", dateLoggedTextBox.Text);                
            cmd.Parameters.AddWithValue("@loggedby", loggedByComboBox.Text);                
            cmd.Parameters.AddWithValue("@problemfoundin", problem_In_EnvironmentComboBox.Text);                
            cmd.Parameters.AddWithValue("@function", functionTextBox.Text);               
            cmd.Parameters.AddWithValue("@summary", other_ImpactsTextBox.Text);                
            cmd.Parameters.AddWithValue("@earningsaccount", accountTextBox.Text);                
            cmd.Parameters.AddWithValue("@from", fromTextBox.Text);               
            cmd.Parameters.AddWithValue("@to", toTextBox.Text);                
            cmd.Parameters.AddWithValue("@status", statusComboBox.Text);              
            cmd.Parameters.AddWithValue("@description", descriptionTextBox.Text);                
            cmd.Parameters.AddWithValue("@fixer", fixerComboBox.Text);               
            cmd.Parameters.AddWithValue("@codein", code_In_EnvironmentComboBox.Text);               
            cmd.Parameters.AddWithValue("@programsforms", programs_FormsTextBox.Text);
            cmd.Parameters.AddWithValue("@timeestimate", time_EstimateTextBox.Text);              
            cmd.Parameters.AddWithValue("@actualstart", actual_StartTextBox.Text);               
            cmd.Parameters.AddWithValue("@actualfinish", actual_FinishTextBox.Text);                
            cmd.Parameters.AddWithValue("@actualtime", actual_TimeTextBox.Text);                
            cmd.Parameters.AddWithValue("@requestdate", retest_DateTextBox.Text);                
            cmd.Parameters.AddWithValue("@tester", testerComboBox.Text);                
            cmd.Parameters.AddWithValue("@comments", commentsTextBox.Text);            
            try
            {
                cmd.ExecuteNonQuery();
                MessageBox.Show("Form Updated Successfully");
                conn.Close();
            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.Message);
                conn.Close();
            }
        }
        else
        {
            MessageBox.Show("Connection Failed");
        }
        }
    }

And I know having column headings with spaces is poor setup. I inherited this table someone else and I have been asked not to change them :-( — AndrewD, Jul 23 '14 at 18:16
Is there a reason you are using variables in the SQL statement? Normally you use parameters.AddWithValue() because you are calling a stored procedure to eliminate SQL injection. Is there another reason you are trying to run it this way? — DotN3TDev, Jul 23 '14 at 18:20
How do you know “no changes are actually made to the data”? There is nothing in your code that checks for changes. — Dour High Arch, Jul 23 '14 at 20:43

score 2 · Answer 1 · answered Jul 23 '14 at 18:30

2

You shouldn't put your database parameters within quotes - they are evaluated as plain text instead of parameters if you do. There is no row where ServiceRequestNumber equals the literal string '@servreq', so nothing is updated.

Also, DataCommands don't pull in local variables as parameters - they must be explicitly added to the DataCommand object (cmd in this case). The reason you aren't getting any errors when you remove your parameter-adding code is because, as stated above, the query doesn't expect any parameters.

Also, the way parameters are being added in the code you removed is strange to say the least. This is much more normal, and significantly easier to read:

cmd.Paramaters.AddWithValue("@paramName", paramData);
//or
cmd.Parameters.Add(new OleDbParameter("@paramName", paramData));

answered Jul 23 '14 at 18:30

JoFlash Studios

102
6

1

cmd.Parameters.Add is depricated anyway so it is better to use cmd.Parameters.AddWithValue – DotN3TDev Jul 23 '14 at 18:38
Add(String, Object) is obsolete (doesn't even exist in the current version of the framework), but Add(SqlParameter) is fine to use. See the [documentation](http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparametercollection.add(v=vs.110).aspx). – JoFlash Studios Jul 23 '14 at 18:43
add(String, object) is still in 4.5 I am updating a bunch of software from 2.0 to 4.5 and it just gives warnings about it being deprecated. The same documentation you told me to look at shows you that. – DotN3TDev Jul 23 '14 at 18:50
1

Ah. My mistake. Perhaps Visual Studio doesn't show IntelliSense for obsolete overloads? I was not even aware that overload existed before looking up that documentation, so I (incorrectly) assumed it wasn't in the framework anymore. – JoFlash Studios Jul 23 '14 at 18:55
1

Yea with VS2012 & VS2013 they stopped IntelliSense for deprecated methods. – DotN3TDev Jul 23 '14 at 18:56
I have removed the quotes from the data command and I fixed the syntax of the parameter adding. When I try to update the form I am still faced with the same problem where nothing updates. I think I am missing something about your second statement about pulling local variables. Should I assign them differently in my query? I tried assigning it directly to the textbox.text and that didn't work any better... @JoFlashStudios – AndrewD Jul 23 '14 at 20:05
All I was saying is that our OleDbCommand must be explicitly given any parameters that your query needs to run. Declaring local strings like `String servreq = serviceRequestNumberTextBox.Text` does nothing. You must use the OleDbCommand.Paramaters.Add() (or AddWithValue()) method to make them available in your query, which you are doing now. – JoFlash Studios Jul 23 '14 at 20:39
Also, you still have some syntax errors: for instance, you declare `@datelogged`, but then your query references it without the @: `DateLogged = datelogged`. I'm surprised you aren't getting any exceptions - Access should blow up if given that code. – JoFlash Studios Jul 23 '14 at 20:42

score 0 · Accepted Answer · edited May 23 '17 at 12:11

0

After spending a little more time editing and moving code around, I stumbled on the fact that your parameters must be in the same order in the query as they are when you bind values to them. After making syntactical changes suggested by JoFlash Studios and putting my parameters in the correct order, I was able to make edits to existing data in my form.

edited May 23 '17 at 12:11

Community

1
1

answered Jul 24 '14 at 13:49

AndrewD

171
1
3
14

Ah - I've clearly done too much work with SQL Server. Looks like this is standard [OleDb behavior](http://stackoverflow.com/questions/1476770/oledbcommand-parameters-order-and-priority). – JoFlash Studios Jul 24 '14 at 16:00

Update existing database entries C# VS2010

2 Answers2