5 Replies - 24655 Views - Last Post: 01 December 2011 - 03:24 AM

#1 Gawker99  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 26-June 10

2007 Access Database and C# INSERT INTO Trouble

Posted 26 June 2010 - 07:10 PM

How is everyone doing around here? good I hope.

I was wondering if anyone could help me solve this current problem I have. I'm programming in C# using an access database called Booking with a table named Records. The program is a flight booking application, and I am trying to insert user input values into the database. I am coding the data tier using the parameter values transferred from the main form, but I'm having some trouble with the insert statement. Does anyone have any advice for me?

  class DataTier
    {
        private OleDbCommand cmd = new OleDbCommand();

        public void WriteToDatabase(DateTime booktime, string name, string CardType, int crdNum,
            string selectedAirline, string regPrice, string discPrice, string radButton)
        {
            
            OleDbConnection mycon = new OleDbConnection();
            mycon.ConnectionString [email protected]"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Owner\Documents\Visual Studio 2008\Projects\BookingSystem\BookingSystem\bin\Debug\Booking.accdb";

               cmd = mycon.CreateCommand();
               cmd.CommandText = "INSERT INTO Records(" + booktime + ",'" + name + ",'" + CardType + ",'" +
                crdNum + ",'" + crdNum + ",'" + selectedAirline + ",'" + regPrice + ",'" + 
                radButton  + ",'" + discPrice +"');";
            mycon.Open();
         
            int temp = cmd.ExecuteNonQuery();
            if (temp > 0)
            {
                MessageBox.Show("Writing is complete, Success!", "Prompt", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("Application Error, Try Again!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            mycon.Close();
        }
        
    }
    



Is This A Good Question/Topic? 0
  • +

Replies To: 2007 Access Database and C# INSERT INTO Trouble

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 5152
  • View blog
  • Posts: 13,842
  • Joined: 18-April 07

Re: 2007 Access Database and C# INSERT INTO Trouble

Posted 26 June 2010 - 07:49 PM

Take a look at where your single quotes are located. You appear to missing several of them. For instance, you should have some around dates (usually) and where you have "name" you start off with a single quote but then you don't have one ending it the field before the comma.

Example...

// NOT RIGHT
",'" + name + ",'"

// RIGHT
",'" + name + "','"



Notice the single quote before the comma. This results in the SQL reading as 'Tom', instead of 'Tom,. Remember that you have to wrap the field in single quotes in your SQL query (for string and dates). So you should have single quotes following fields name, cardtype, crdnum, selectedAirline etc.

I also notice you have crdnum listed twice which I am not sure you want. Rarely do you want to repeat data in two columns like that. So fix those single quotes and see if it looks good. Step through the statement and check the final CommandText field to make sure it is right.

:)

This post has been edited by Martyr2: 26 June 2010 - 07:50 PM

Was This Post Helpful? 0
  • +
  • -

#3 Gawker99  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 26-June 10

Re: 2007 Access Database and C# INSERT INTO Trouble

Posted 26 June 2010 - 08:05 PM

Yeah silly me about the quotes thing, I should know better than that. Also, to answer your question about the two card numbers being identical. I was doing that as a sort of a test at first. Since I have encryption and decryption methods for credit card numbers working on the main form, I'll eventually put those in place. This probably looks a little better, but it still says the error is on the insert into statement. I think it may be something with the connection, I don't know.

 OleDbConnection mycon = new OleDbConnection();
        mycon.ConnectionString [email protected]"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Owner\Documents\Visual Studio 2008\Projects\BookingSystem\BookingSystem\bin\Debug\Booking.accdb";

               cmd = mycon.CreateCommand();
               cmd.CommandText = "INSERT INTO Records('" + booktime + "','" + name + "','" + CardType + "'," +
                crdNum + "," + crdNum + ",'" + selectedAirline + "'," + regPrice + ",'" + 
                radButton  + "'," + discPrice +");";


Was This Post Helpful? 0
  • +
  • -

#4 Gawker99  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 26-June 10

Re: 2007 Access Database and C# INSERT INTO Trouble

Posted 29 June 2010 - 09:39 PM

Okay, well I did get everything to work. After I put the insert for complete syntax(ie. INSERT INTO RECORDS(this field, that field, blah,)VALUES(, , , , , , ,), it worked like a charm.
Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: 2007 Access Database and C# INSERT INTO Trouble

Posted 29 June 2010 - 10:44 PM

View PostGawker99, on 26 June 2010 - 06:05 PM, said:

 OleDbConnection mycon = new OleDbConnection();
        mycon.ConnectionString [email protected]"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Owner\Documents\Visual Studio 2008\Projects\BookingSystem\BookingSystem\bin\Debug\Booking.accdb";

               cmd = mycon.CreateCommand();
               cmd.CommandText = "INSERT INTO Records('" + booktime + "','" + name + "','" + CardType + "'," +
                crdNum + "," + crdNum + ",'" + selectedAirline + "'," + regPrice + ",'" + 
                radButton  + "'," + discPrice +");";



This code is prime for a SQL Injection attack, read this on using parametrized queries in your code
Was This Post Helpful? 0
  • +
  • -

#6 jainroh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 01-December 11

Re: 2007 Access Database and C# INSERT INTO Trouble

Posted 01 December 2011 - 03:24 AM

Hi,

I am trying to insert a record into an MS Access 2010 table and I keep getting an OleDBException (0x80040E14). Here's my code:-


String fname = textBox1.Text;
String lname = textBox2.Text;
String id = textBox6.Text;
DateTime date = Convert.ToDateTime(textBox3.Text);
DateTime stime = Convert.ToDateTime(textBox4.Text);
DateTime etime = Convert.ToDateTime(textBox5.Text);
String type = comboBox1.Text;
String connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\V470\My Documents\Visual Studio 2010\Projects\WindowsFormsApplication1\WindowsFormsApplication1\Test Database.accdb;Persist Security Info=False";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
OleDbCommand command = new OleDbCommand();
command = connection.CreateCommand();
command.CommandText = "INSERT INTO Appointments(First Name,Last Name,Id Number,Date,Start Time,End Time,Type)VALUES('" + fname + "','" + lname + "','" + id + "','" + date + "'," + stime + "," + etime + ",'" + type + "');";
try
{
command.ExecuteNonQuery();
}
catch (OleDbException exception)
{
MessageBox.Show(exception.ToString());
}
connection.Close();
}

I'm using Visual Studio 2010 on Windows 7, if that helps.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1