I am trying to use my textboxes and checkedlistboxes to insert data to a ms access database that is local just as a preliminary step to get my app running to show what it can do. I have connected to the database through the datasource connection in Visual studio 2008 and created an insert method in the xsd file of the datasource but so far have only found ways to use sql server or using VB to accomplish the insertion part. I might be a little thick to be able to convert but I'm sort of new to this part of app development. Any help would be greatly appreciated. Here is the code I'm working with,

public partial class projectInfoForm : Form
    {
        public projectInfoForm()
        {
            InitializeComponent();
            OleDbConnection  conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= |DataDirectory|\IR&D data.accdb;Persist Security Info=True");

            string projectInfoProjectName;
            string projectInfoOrg;
            string projectInfoDescription;
            projectInfoProjectName = Convert.ToString(projectInfoProjectNametextBox);
            projectInfoOrg = Convert.ToString(projectInfoOrgTextBox);
            projectInfoDescription = Convert.ToString(projectInfoDescriptionTextBox);
        }

and then the button click event to do the insert

private void projectInfoSubmitButton_Click(object sender, EventArgs e)
        {

                this.Project_InformationTableAdapter.InsertCommand.CommandText =
            "INSERT INTO `Project Info` (`Project Name`, `Project Number`, `Organization`, `Description`)" +
            "VALUES     ('" + this.projectInfoProjectNameTextBox.Text
            + "','" + this.projectNumberTextBox.Text +
            "' , " + this.projectInfoOrgTextBox.Text + ",'" +
            this.projectInfoDescriptionTextBox.Text + "')";

                //open the bridge between the application and the datasource
                this.OleDbConnection.Open();
                this.Project_InformationTableAdapter.InsertCommand.Connection = OleDbConnection;

                //execute the qurey 
                this.Project_InformationTableAdapter.InsertCommand.ExecuteNonQuery();

                //close the connection
                this.OleDbConnection.Close();

            this.Hide();
            forwardLookComplexityLvlForm ForwardLook = new forwardLookComplexityLvlForm();
            ForwardLook.Show();
        }

I'm not much in to MS Access but you have spaces in table name and field names. The use of "`" character seems odd. Try using "[" and "]" around names:

this.Project_InformationTableAdapter.InsertCommand.CommandText =
"INSERT INTO [Project Info] ([Project Name], [Project Number], Organization, Description)" + " VALUES ('" + this.projectInfoProjectNameTextBox.Text
+ "','" + this.projectNumberTextBox.Text +
"' , '" + this.projectInfoOrgTextBox.Text + "','" +
this.projectInfoDescriptionTextBox.Text + "')";

Also, I added '-characters around this.projectInfoOrgTextBox.Text assuming it's also field of type text. And one space added to "VALUES ('" so it will be ..., Description) VALUES ('... after evaluation.

Otherwise connection object, command object, SQL clause, opening and closing connection and command execution seem to be fine to me.

HTH

You don't have to use ' character in T-SQL query except in data that you want to insert into table.

INSERT INTO table (column1, column2, column3, ...) VALUES('', '', '', ...)
This article has been dead for over six months. Start a new discussion instead.