Inserting Data Into Database

[szkoda]

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.

[DanielGreen]

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

[szkoda]

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

[prapulkumar]

program to display the out put of
1
12
123
1234

[sknake]

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.