0

I have a database that I'm trying to inset data into called dogs.mdb.
For some reason the code below doesn't seem to insert a new record into the database...please help!

namespace dogs
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            string connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\\Billy Uni\\Application Program Development\\dogs1\\dogs1\\bin\\Debug\\dogs.mdb";
            string command = "INSERT INTO dogs(Dog Name, Breed) VALUES('NewName', 'NewBreed')";
             
            OleDbConnection myConn = new OleDbConnection(connection);
            OleDbCommand myCmd = new OleDbCommand(command, myConn);
            try
            {
                myConn.Open();
                myCmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception in DBHandler", ex);
            }
            finally
            {
                myConn.Close();
            }

        }
    }
}

when I run the program there are no errors, it just doesnt do anything.

I'm very much a beginner at this so if I've missed something simple please dont be too harsh :)

Thanks in advance.

Edited by szkoda: n/a

4
Contributors
4
Replies
7
Views
7 Years
Discussion Span
Last Post by sknake
0

hi!

please post your Exception text!
I think the problem is your column-name "Dog Name"

try [] around your column-name ->

"INSERT INTO dogs([Dog Name], [Breed]) VALUES('NewName', 'NewBreed')";

hope this helps
Daniel

Edited by Nick Evan: n/a

0

Fixed

Now how would I:

1. Use variables instead of hardcoded values to be inserted into the database

2. store this whole function in a method (or class?) so I can call it when the user clicks a button

Any help would be much appreciated

0

Here is an example of inserting data in to a database using OleDb:

public static string BuildExcel2007ConnectionString(string Filename, bool FirstRowContainsHeaders)
    {
      return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR={1}\";",
                            Filename.Replace("'", "''"),
                            FirstRowContainsHeaders ? "Yes" : "No");
    }
    private static void WriteExcelFile()
    {
      string connStr = BuildExcel2007ConnectionString(@"C:\Data\Spreadsheet.xlsx", true);
      //Note 'rowNumber' is the first column in my spreadsheet.
      string query = @"Insert Into [Sheet1$] ([Row], [Name]) Values (?,?);";
      using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr))
      {
        conn.Open();
        using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, conn))
        {
          cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("Row", Convert.ToDouble(5)));
          cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("Name", "Fred"));
          cmd.ExecuteNonQuery();
        }
      }
    }

In this case it is using Excel but you would go about it the same way using Access, you would just change your connection string. You should always use parameterized SQL instead of putting the values directly in to the command text.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.