none
C# INSERT Into Acesss Database RRS feed

  • Question

  • Why it is not inserting into databse.I have included database as part of the project.Does it has to do something with properties.I have' Build Action' property set to 'Compile' and 'Copy to Output Directory set to 'Copy Always'

    private void button1_Click_1(object sender, EventArgs e)
            {
               
               string str2 = contactFirstNameTextBox.Text;
               string str3 = contactLastNameTextBox.Text;
               string str4 = billingAddressTextBox.Text;
               string str5 = phoneNumberTextBox.Text;
               string str6 = emailAddressTextBox.Text;
               string str7 = notesTextBox.Text;
              
               string str = "INSERT INTO Customers(ContactFirstName,ContactLastname,BillingAddress,PhoneNumber,EmailAddress,Notes)VALUES(?,?,?,?,?,?)";
            
                using  (OleDbConnection con = new OleDbConnection(scon))
    {   
      using (OleDbCommand cmd = new OleDbCommand(str,con))
      {
        
        cmd.CommandType = CommandType.Text;
     
           cmd.Parameters.AddWithValue("ContactFirstName",str2);
           cmd.Parameters.AddWithValue("ContactLastName",str3);
           cmd.Parameters.AddWithValue("BillingAddress",str4);
           cmd.Parameters.AddWithValue("PhoneNumber",str5);
           cmd.Parameters.AddWithValue("EmailAddress",str6);
           cmd.Parameters.AddWithValue("Notes",str7);
              
             
        con.Open();
        cmd.ExecuteNonQuery();
       }
    }
    Monday, November 26, 2007 1:24 PM

Answers

  • I did a quick scan of the code and text and sprocs do the same thing inside OLEDB.  Therefore it probably isn't the parameters.  The OLEDB provider doesn't support named parameters when the type is text.  Some of this is documented in MSDN although there was a KB article about incorrect documentation so I'm not sure which way to believe at this point.  A sproc will still be easier to code and debug then the aforementioned approach.

     

    It is not necessary to open a connection before setting up a command.  Most of the time it is better to prepare the command before opening the connection anyway.  In some cases you don't have to even explicitly open the connection as the underlying command will do that as well (not in this case though).  I can't remember the cases where that occurs so I always open the connection just before calling Execute...

     

    I would recommend skipping over the params initially and just hard code a few values into the INSERT statement.  If they get inserted into the DB then it is the params.  If not then you should get back an error message that you can then use to diagnose the issue. 

     

    Michael Taylor - 11/26/07

    http://p3net.mvps.org

     

     

     

     

    Monday, November 26, 2007 8:10 PM
  • Hi,

     

    I think there is no error with your code.

     

    I suggest you check following things:

    1.      From your configuration, the database file is copied to the debug directory when compiling .There will be two database files, one in the project directory, one in the debug directory. So you should check which one you are referring to in your connection string. The new record will be only inserted into the referred database.

    2.      Check whether the inserting value is subject to the constraints of your table.

    3.      Check whether you have provided values for all non-null columns of your table.

     

    Wednesday, November 28, 2007 7:04 AM

All replies

  • Parameters are only useful when dealing with sprocs.  In your case you're trying to execute SQL directly.  AFAIK OLEDB won't like that.  Instead of using parameters you should just build the command directly.  Be sure to enclose your strings in quotes to ensure they get passed properly.

     

    Code Block

    StringBuilder bldr = new StringBuilder();
    bldr.Append("INSERT INTO ... (...) VALUES (");

    bldr.AppendFormat("'{0}', contactFirstNameTextBox.Text);

    ...

     

    using (OleDbCommand cmd = new OleDbCommand(bldr.ToString(), con))
    {
       cmd.CommandType = CommandType.Text;

       con.Open();
       cmd.ExecuteNonQuery();
    };

     

     

    You need to filter out each of the textboxes before you use them.  You need to convert any invalid SQL characters to something else or error out.  For example you would want to convert any single quotes to two single quotes.  If you don't do this then your code can be used for a SQL injection attack where I can do things like delete your tables by sending you properly formatted SQL text.

     

    Michael Taylor - 11/26/07

    http://p3net.mvps.org

     

    Monday, November 26, 2007 2:47 PM
  • Parameters are only useful when dealing with sprocs.

     

    That can't be true. Or are you saying that's only true for the OleDb driver? And even then, I highly doubt it. Else parameterized queries/updates/etc are not very useful. I would highly doubt developers would be required to escape their own statements like you're suggesting -- that's one thing parameterized queries are for!
    Monday, November 26, 2007 7:35 PM
  • So back to the original question.

    1) It looks odd that you're creating a Command, and THEN opening the Connection. Somehow I doubt that would work. Normally one opens the connection first.

    2) Maybe your code isn't even being executed at all (a dead method). Are you able to do some simple debugging? Simple Console.WriteLine statements sprinkled in there would help if you can't actually use a real debugger.

     

    Monday, November 26, 2007 7:39 PM
  • I did a quick scan of the code and text and sprocs do the same thing inside OLEDB.  Therefore it probably isn't the parameters.  The OLEDB provider doesn't support named parameters when the type is text.  Some of this is documented in MSDN although there was a KB article about incorrect documentation so I'm not sure which way to believe at this point.  A sproc will still be easier to code and debug then the aforementioned approach.

     

    It is not necessary to open a connection before setting up a command.  Most of the time it is better to prepare the command before opening the connection anyway.  In some cases you don't have to even explicitly open the connection as the underlying command will do that as well (not in this case though).  I can't remember the cases where that occurs so I always open the connection just before calling Execute...

     

    I would recommend skipping over the params initially and just hard code a few values into the INSERT statement.  If they get inserted into the DB then it is the params.  If not then you should get back an error message that you can then use to diagnose the issue. 

     

    Michael Taylor - 11/26/07

    http://p3net.mvps.org

     

     

     

     

    Monday, November 26, 2007 8:10 PM
  • There is nothing wrong with using parameters, and indeed, that is exactly what you should be using.  Please avoid using string concatenation when creating statements such as

     

    string sql = "INSERT into table(field1, field2, field3) VALUES (" + field1 + ", " + field2... etc

     

    This leaves the code open for sql injection attacks amongst other problems.

     

    I think the problem you are having is that you are defining your insert statement with anonymous paramters (?), and then you are trying to add the values to them by using the names.  Try changing your code to

     

    string str = "INSERT INTO Customers (ContactFirstName, ContactLastname, BillingAddress, PhoneNumber, EmailAddress, Notes) VALUES (@ContactFirstName, @ContactLastName, @BillingAddress, @PhoneNumber, @EmailAddress, @Notes)";

     

    Now when you call Paramters.AddWithValue, it knows which parameter you are refering to with the name.  As it currently stands, the system has no way of knowing which parameter is named "ContactFirstName", etc.

     

    Hope this helps

    Tuesday, November 27, 2007 8:50 AM
  • Hi,

     

    I think there is no error with your code.

     

    I suggest you check following things:

    1.      From your configuration, the database file is copied to the debug directory when compiling .There will be two database files, one in the project directory, one in the debug directory. So you should check which one you are referring to in your connection string. The new record will be only inserted into the referred database.

    2.      Check whether the inserting value is subject to the constraints of your table.

    3.      Check whether you have provided values for all non-null columns of your table.

     

    Wednesday, November 28, 2007 7:04 AM
  • I have an easier way for you.

    First of all, add:

    Code Block

    using System.Data;
    using System.Data.OleDb;

     

     

    The connection and the actions:

     

    Code Block

                OleDbConnection conn;
                OleDbCommand comm;
                conn = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\\folder\\db.mdb");
                comm = new OleDbCommand("INSERT INTO...", conn);
                conn.Open();
                comm.ExecuteNonQuery();
                conn.Close();

     

     

    Try it.
    Wednesday, November 28, 2007 7:48 AM