CODEWITHSUNDEEP
c#sqlms-accessoledb
Eric
Eric

Reputation: 9

Error in SQL - No Error message - Command not executing

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.

Upvotes: 0

Views: 600

Answers (2)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30663

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

Upvotes: 0

Nick
Nick

Reputation: 5042

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

[Asset Tag]

Upvotes: 2

Related Questions