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
– GolezTrol, Commented Feb 4, 2019 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
– Eric, Commented Feb 4, 2019 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
– Frank Nielsen, Commented Feb 4, 2019 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
– GolezTrol, Commented Feb 4, 2019 at 21:20
Possible duplicate of What are good ways to prevent SQL injection? — mjwills
– mjwills, Commented Feb 4, 2019 at 21:49

Nick · Accepted Answer · 2019-02-04 21:19:21Z

2

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

[Asset Tag]

answered Feb 4, 2019 at 21:19

Nick

5,0712 gold badges20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric Over a year ago

Thank you so much. That was exactly where I went wrong.

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-02-04 21:21:01Z

0

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");

answered Feb 4, 2019 at 21:21

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Collectives™ on Stack Overflow

Error in SQL - No Error message - Command not executing

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related