UPDATE query not doing its job.

Pis7ftw

New member
Joined
Aug 13, 2016
Messages
3
Programming Experience
Beginner
Hello!

I'm not sure what's going wrong here:
C#:
                    connection.Open();                    OleDbCommand command = new OleDbCommand();
                    command.Connection = connection;


                    command.CommandText = "SELECT COUNT(*) FROM tblAirportData WHERE airportIDENT LIKE @airportIdent";
                    command.Parameters.AddWithValue("@airportIdent", txtAirportIdentifier.Text);


                    int recs = (int)command.ExecuteScalar();
                     
                    MessageBox.Show(recs.ToString()); //just peeking to see if anything was found.


                    if (recs > 0)
                    {


                        command.CommandText = "UPDATE tblAirportData SET airportNAME=@airportName, airportIDENT=@airportIdent, airportCITY=@city, airportSTATE=@state WHERE ID = @ID";
                        command.Parameters.AddWithValue("@airportName", txtAirportName.Text);
                        command.Parameters.AddWithValue("@airportIdent", txtAirportIdentifier.Text);
                        command.Parameters.AddWithValue("@city", txtCity.Text);
                        command.Parameters.AddWithValue("@state", cboState.Text);
                        command.Parameters.AddWithValue("@ID", lblAirportID.Text); //I've tried parsing this as Int32 with no effect.


                        command.ExecuteNonQuery();
                        connection.Close();


                    }
                    else if (recs < 0)
                    {
                        command.CommandText = "INSERT INTO tblAirportData (airportNAME, airportIDENT, airportCity, airportState) VALUES (@airportName, @airportIdent, @city, @state)";
                        command.Parameters.AddWithValue("@airportName", txtAirportName.Text);
                        command.Parameters.AddWithValue("@airportIdent", txtAirportIdentifier.Text);
                        command.Parameters.AddWithValue("@city", txtCity.Text);
                        command.Parameters.AddWithValue("@state", cboState.Text);


                        command.ExecuteNonQuery();
                        connection.Close();
                    }
                    else
                    {
                        MessageBox.Show("Something unexpected happened");
                        connection.Close();
                    }

So, essentially I've got a MSACCESS backend attached to this thing. I'm looking to see if the airportIDENT exists in the database already. If it does, we're updating, if it doesn't we're inserting it. The INSERT works just fine when recs = 0. However, when recs = 1, the UPDATE query fails to update the existing row. I'm not sure if it's an issue with the UPDATE query itself, or the conditional statement. I suspect it's the update query itself, as the else if fires when recs = 0.

Any ideas? As always, assistance is always appreciated!

*EDIT* Also, there are absolutely no errors thrown, syntax or otherwise. In fact, I've purposefully screwed with the syntax of the update statement just so I could get an error thrown to make sure my conditional statement was even "trying" to fire.
 
Last edited:
Firstly, how can 'recs' ever be less than zero? You are saying that if there is a negative number of matching records in the database then insert a new record. How does that make sense?

Secondly, you shouldn't be getting your ID from a Label, whether you parse it or not. A Label is for displaying text to the user only. It's not for input so you should NEVER be getting anything from it. If you are retrieving an Int32 ID value and you want to use it later then put it in an Int32 variable. Variables are where you put data for storage, not controls.

As for the issue, the first thing to do is to test the value returned by ExecuteNonQuery. There are only three possible outcomes from that call:

1. The call succeeds and returns zero, in which case there were no matching records.
2. The call succeeds and returns a non-zero value, in which case there were one or more matching records and they were updated.
3. The call fails and an exception is thrown.

It's simply not possible for anything else to happen so it's extremely easy to diagnose your issue. Assign the result of ExecuteNonQuery to a variable, put a breakpoint on that line and then step when it's hit. If an exception isn't thrown then you can immediately check the value of the variable and know what happened.

One more thing to note. That doesn't look like code that would be in the Load event handler of a form but, just in case it is, any exceptions thrown would be swallowed silently, so you'd need an explicit exception handler to know if that's what was happening.
 
Back
Top Bottom