Error in SQL - No Error message - Command not executing

Question

I am working on a database application for asset management for my company. I don't have a ton of experience with SQL or MS Access for database management. I am working on a solution for adding data to the database using C# in visual studio by having it run SQL commands. I have worked in some text boxes to reveal if my code is or is not running in different places, and I have (I think) narrowed it down to my SQL, though I don't know for sure, I haven't found much about Access and OleDb through searching.

I have altered the capitalization and phrasing, as well as the commas and quotes for my SQL code, as well as baked in places where my errors could be caught by the code.

private void SubmitButton_Click(object sender, EventArgs e)
        {
                try
                {
                    //declares connection
                    OleDbConnection con = new OleDbConnection();
                    OleDbCommand command = new OleDbCommand();
                    con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\eric.varner\Documents\AssetDB.accdb";
                    //open connection to Database
                    con.Open();
                    StatusLabel.Text = "Connected";
                    //declares command type
                    command.Connection = con;

                    //SQL  commands to call database to write data.

                    if (AssetTypeBox.Text == "iPad")
                    {
                        command.CommandText = "INSERT INTO AssetsiPad (Asset Tag, Condition, Location, Serial Number) VALUES('" + AssetBox.Text + "','" + ConditionBox.Text +
                            "','" + LocationBox.Text + "','" + SerialNumBox.Text + "')";
                    MessageBox.Show("The if statement runs fine");
                    }
                    else if (AssetTypeBox.Text == "iPhone")
                    {
                        command.CommandText = "INSERT INTO AssetsiPhone (Asset Tag, Condition, Location, Serial Number) VALUES('" + AssetBox.Text + "','" + ConditionBox.Text +
                            "','" + LocationBox.Text + "','" + SerialNumBox.Text + "')";
                    }
                    else if (AssetTypeBox.Text == "MR AP")
                    {
                        command.CommandText = "INSERT INTO AssetsMR (Asset Tag, Condition, Location, Serial Number, MAC Address, IP Address) VALUES('" + AssetBox.Text + "','" 
                        + ConditionBox.Text + "','" + LocationBox.Text + "','" + SerialNumBox.Text + "','" + MACaddressBox.Text + "','" + IPsubnetBox.Text + "')";
                    }
                    else if (AssetTypeBox.Text == "MX Security")
                    {
                        command.CommandText = "INSERT INTO AssetsMX (Asset Tag, Condition, Location, Serial Number) VALUES('" + AssetBox.Text + "','" + ConditionBox.Text + "','" 
                        + LocationBox.Text + "','" + SerialNumBox.Text + "',)";
                    }
                    else if (AssetTypeBox.Text == "Laptop")
                    {
                        command.CommandText = "INSERT INTO AssetsLaptop (Asset Tag, Condition, Location, Serial Number) VALUES('" + AssetBox.Text + "','" + ConditionBox.Text + "','" 
                        + LocationBox.Text + "','" + SerialNumBox.Text + "',)";
                    }
                    else
                    {
                        MessageBox.Show("you aren't reaching the correct statement");
                    }

                    command.ExecuteNonQuery();

                    //close connection to Database
                    con.Close();
                    MessageBox.Show("Data Saved");
                }
                catch (Exception ex)
                {
                    StatusLabel.Text = "Not Connected";
                    MessageBox.Show("your sql didn't run correctly");
                }

        }

When I enter my strings correctly such as "iPad" I get the message boxes that say "The if statement runs fine" and "Your SQL didn't run correctly." The AssetTypeBox is the only thing that I have any kind of catches built into. The other fields should be able to accept any type or amount of data without issue. I hope I'm not leaving anything out.

Welcome! What is the actual question? The behavior your describe seems to match with your code, so please describe clearly what is wrong with it, and what behavior you expected. If it's about `Your SQL didn't run correctly`, I'd suggest you remove the messagebox, and just re-raise the exception, or at least show the message of the exception. Currently you catch any and every exception and replace it with a generic messagebox, because of which you (or we) don't have any idea what actually went wrong. — GolezTrol, Feb 04 '19 at 21:15
I was looking to have the code edit my database by adding the things I enter into the textboxes. My SQL commands aren't running, but I would like them to. Thanks so much. — Eric, Feb 04 '19 at 21:18
Change `MessageBox.Show("your sql didn't run correctly");` to `MessageBox.Show(string.Format("{0}:\r\n{1}",ex.Message,ex.StackTrace));` to give a more detailed error description. — Frank Nielsen, Feb 04 '19 at 21:18
For debugging purposes, showing the exception is easiest, of course, but it may be better to implement some logging (for instance to a file). That way, you can show all the nice messages that you want to the user, while having all the nitty gritty details available in your log file, in case you need them. — GolezTrol, Feb 04 '19 at 21:20
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Feb 04 '19 at 21:49

score 2 · Answer 1 · answered Feb 04 '19 at 21:19

2

I think the INSERT command cannot accept field names with spaces, unless you enclose them in square brackets:

[Asset Tag]

answered Feb 04 '19 at 21:19

Nick

4,787
2
18
24

Thank you so much. That was exactly where I went wrong. – Eric Feb 04 '19 at 21:26

score 0 · Answer 2 · answered Feb 04 '19 at 21:21

The if statement runs fine 
Your SQL didn't run correctly

the above result is pretty expected. Your SQL Query gets executed when you run command.ExecuteNonQuery(); which means you don't get exception before this point.

the if statement with IPad check satisfies and MessageBox.Show("The if statement runs fine"); runs after that your code executes command.ExecuteNonQuery(); and Exception occurs.

since you have exception block, the error is handled by the code below

 StatusLabel.Text = "Not Connected";
 MessageBox.Show("your sql didn't run correctly");

Error in SQL - No Error message - Command not executing

2 Answers2