SQL INSERT statements in C#

Question

0.00/5 (No votes)

See more:

I'm stumped as to why this isn't working properly. Can someone shed some light? I am trying to insert a row into a database once a button is clicked.

This is the code segment I have right now.

C#

ins_comm.CommandText = "INSERT INTO vehicles (make, model_name, model_year, engine_type, transmission_type) VALUES ('" + makeComboBox.Text + "', '" + modelComboBox.Text + "', '" + yearComboBox.Text + "', '" + engineComboBox.Text + "', '" + transTypeComboBox.Text + "')";
ins_comm.ExecuteNonQuery();

The connection stays open once the first combobox is filled in and it will stay open until the form is closed in some fashion. The connection is to a local database contained within the project and it is a CE edition db.

UPDATE:

This is my new code segment.

SQL

SqlCeCommand insert_command = new SqlCeCommand("INSERT INTO vehicles (make, model_name, model_year, engine_type, transmission_type) " + "VALUES (@make, @model_name, @model_year, @engine_type, @transmission_type)", conn);

                insert_command.Parameters.AddWithValue("@make", makeComboBox.Text);
                insert_command.Parameters.AddWithValue("@model_name", modelComboBox.Text);
                insert_command.Parameters.AddWithValue("@model_year", yearComboBox.Text);
                insert_command.Parameters.AddWithValue("@engine_type", engineComboBox.Text);
                insert_command.Parameters.AddWithValue("@transmission_type", transTypeComboBox.Text);

                create_adapt.InsertCommand = insert_command;
                insert_command.ExecuteNonQuery();

I still do not see anything new in my db after running the program. Can anyone point me in the right direction as to what I'm missing?

Posted 14-Sep-12 7:43am

joshrduncan2012

Updated 14-Sep-12 9:06am

v4

Add a Solution

Comments

Valery Possoz 14-Sep-12 13:48pm

What is the error?

joshrduncan2012 14-Sep-12 13:50pm

I can't find my newly inserted row anywhere in the DB. It appears not to have been inserted at all.

ShotDriller 14-Sep-12 13:56pm

Did you open the Connection before ExecuteNonQuery?

joshrduncan2012 14-Sep-12 13:58pm

The open connection is declared in a previous function. It says open until the form closes in some fashion.

Kschuler 14-Sep-12 14:04pm

What kind of project is this for, a windows form project, web project...etc? What database are you using? And how is your connection setup? Is your database in some network location?

joshrduncan2012 14-Sep-12 14:06pm

This is an ADO.NET project. This is a SQL CE Edition local connection. DB is contained locally within the individual project.

Kschuler 14-Sep-12 14:10pm

You should click the "Improve Question" link and update your question and tags with this information. It's helpful to those trying to help.

[no name] 14-Sep-12 15:23pm

Why are you not checking the return value from ExecuteNonQuery? What is ExecuteNonQuery returning?

joshrduncan2012 14-Sep-12 15:59pm

I think I have the syntax and logic correct, but every time I run the program, my db connection always gets severed while in running mode.

3 solutions

Solution 4

I found out that the new inserted row doesn't appear at the bottom, so I may have a sort filter turned on somewhere that I can't find.

Posted 14-Sep-12 11:15am

joshrduncan2012

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2012-09-14T07:50:00

To start with, you are not using parameterized query to invite trouble!

From serious trouble like SQL injection to simple issue like incorrect query formation and logic errors. Though you have not shared what error exactly is happening on execution if the query above, try somehting like this:

SQL

INSERT INTO vehicles (make, model_name, model_year, engine_type, transmission_type) VALUES ("+ @makeComboBoxText + "," + @modelComboBoxText + "," + @yearComboBoxText + "," + @engineComboBoxText + "," + @transTypeComboBoxText + ")";

Much clearler details on implementation of parameterized query, here:
MSDN: Configuring Parameters and Parameter Data Types (ADO.NET)[^]
MSDN: DataAdapter Parameters (ADO.NET)[^]
MSDN: SqlCommand.Parameters Property [^]

Read about protecting from SQL Injection here: SQL Injection Mitigation: Using Parameterized Queries[^]

Abhinav S · Accepted Answer · 2012-09-14T08:06:00

Solution 3

Debug your code, pick up the query from here and paste it in sql server studio.
Running it there will give you a fair idea of what could be going wrong.

Posted 14-Sep-12 8:06am

Abhinav S

Comments

RaisKazi 14-Sep-12 16:01pm

yup, 5ed.

Abhinav S 15-Sep-12 3:31am

Thank you.

SQL INSERT statements in C#

3 solutions

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

SQL INSERT statements in C#

3 solutions

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us