Member Avatar for 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.

Edited by szkoda because: n/a
  • 4 Contributors
  • 4 Replies
  • 290 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by sknake

Recommended Answers

All 4 Replies

Member Avatar for 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

Edited by Nick Evan because: n/a
Member Avatar for 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

Member Avatar for prapulkumar

program to display the out put of
1
12
123
1234

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.