CODEWITHSUNDEEP
c#
Martijn
Martijn

Reputation:

C# How to connect to MS Access 2007

I have a problem connecting to my MS Access DB 2007. Code:

private void btnSave_Click(object sender, EventArgs e)
    {
        OleDbConnection Conn = new OleDbConnection();

        try
        {
            string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ Directory.GetCurrentDirectory() +"\\dvd_manager.accdb;Persist Security Info=False;";
            Conn.ConnectionString = conn;

            Conn.Open();

            int i = cbbLocatie.SelectedIndex + 65;
            char c = (char)i;

            string sql = "INSERT INTO DVD (titel, locatie)VALUES(@titel, @locatie)";
            OleDbCommand Com = new OleDbCommand();
            Com.CommandText = sql;
            Com.Connection = Conn;

            OleDbParameter Param = new OleDbParameter("@titel", txtTitle.Text);
            Com.Parameters.Add(Param);

            Param = new OleDbParameter("@locatie", c);
            Com.Parameters.Add(Param);

            Com.ExecuteNonQuery();
            Conn.Close();

            MessageBox.Show("Data is opgeslagen " + sql);
        }
        catch (Exception ex)
        {
            MessageBox.Show("Fout opgetreden: " + ex.Message);
        }
        finally
        {
            Conn.Close();
        }
    }

When I run this code, the messagebox comes up. This should mean that my data is inserted. But when i open the accdb file no data is inserted. What am i doing wrong?

Thnx

Edit: The return value of ExecuteNonQuery() is 1 (I edit my post, because i cannot add any comments, when I click add comment, the box doesn't show up..)

Edit 2: I have created a class with the Title and Location properties. Code: private void btnSave_Click(object sender, EventArgs e) { OleDbConnection Conn = new OleDbConnection();

try
{
    string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ Directory.GetCurrentDirectory() +"\\dvd_manager.accdb;Persist Security Info=False;";
    Conn.ConnectionString = conn;

    // Create object
    Medium M = new Medium();
    int i = cbbLocatie.SelectedIndex + 65;
    char c = (char)i;

    M.Location = c;
    M.Title = txtTitle.Text;

    Conn.Open();

    string sql = "INSERT INTO DVD (titel, locatie)VALUES(@titel, @locatie)";
    OleDbCommand Com = new OleDbCommand();
    Com.CommandText = sql;
    Com.Connection = Conn;

    OleDbParameter Param1 = new OleDbParameter("@titel", M.Title);
    Com.Parameters.Add(Param1);

    OleDbParameter Param2 = new OleDbParameter("@locatie", M.Location);
    Com.Parameters.Add(Param2);

    int ret = Com.ExecuteNonQuery();
    Conn.Close();

    MessageBox.Show("Data is opgeslagen " + ret);
}
catch (OleDbException ex)
{
    MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
    MessageBox.Show("Fout opgetreden: " + ex.Message);
}
finally
{
    Conn.Close();
}

}

Since i still cannot click on the add comment button, here's my new code with nameless sql parameters:

// some code
Conn.Open();

string sql = "INSERT INTO DVD (titel, locatie)VALUES(?, ?)";
OleDbCommand Com = new OleDbCommand();
Com.CommandText = sql;
Com.Connection = Conn;

OleDbParameter Param1 = new OleDbParameter("@p1", OleDbType.VarChar, 1);
Param1.Value = M.Title;
Com.Parameters.Add(Param1);

OleDbParameter Param2 = new OleDbParameter("@p2", OleDbType.VarChar, 255);
Param2.Value = M.Location;
Com.Parameters.Add(Param2);

int ret = Com.ExecuteNonQuery();
Conn.Close();
// morde code

Upvotes: 5

Views: 36879

Answers (3)

Ahmed Yassin
Ahmed Yassin

Reputation: 1

When I code, I like to simplify my work and not to redo ever time I have a method. I would simple create a method for parameters such as:

public void setParameter(String paramAT, String paramTxt)
{
    OleDbCommand myCommand;
    DbParameter parameter = myCommand.CreateParameter();
    parameter.ParameterName = paramAT;
    parameter.Value = paramTxt;
    myCommand.Parameters.Add(parameter);
}

public int CreateDVD()
{
    try
    {
        string strSqldvd = "INSERT INTO DVD(title,locatie)VALUES(@title,@locate?)";

        myCommand = (OleDbCommand)dbconn.MyProvider.CreateCommand();
        dbconn.MyConnection.Open();
        myCommand.Connection = dbconn.MyConnection;
        myCommand.CommandText = strSqldvd;
        setParameter("@title",M.Title );
        setParameter("@locate", M.Location);
    }
    catch (Exception)
    {
        throw new ArgumentException();
    }

    int count = myCommand.ExecuteNonQuery();
    dbconn.MyConnection.Close();
    return count;
}

This is how simply. I insert and keep using this parameter method in my update and insert etc.... Hope this will help.

Upvotes: 0

Jesper Palm
Jesper Palm

Reputation: 7248

To my knowledge you can't use named parameters with the OleDbParameter.

Your insert should look like:

string sql = "INSERT INTO DVD (titel, locatie)VALUES(?, ?)";

And then you have to add OleDbParameters in the correct order. The names are not used.

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx

Edit:

Untested code below but here is an example of how I would do it.

using(OleDbConnection connection = new OleDbConnection(CONNECTION_STRING))
{
  using(OleDbCommand command = connection.CreateCommand())
  {
    command.CommandType = CommandType.Text;
    command.CommandText = "INSERT INTO DVD(title,locatie)VALUES(?,?)";
    command.Parameters.Add("@p1", OleDbType.VarChar, 1).Value = M.Title;
    command.Parameters.Add("@p2", OleDbType.VarChar, 255).Value = M.Location;

    connection.Open();
    int ret = command.ExecuteNonQuery();
  }
}

Upvotes: 1

Khadaji
Khadaji

Reputation: 2167

ExecuteNonQuery will return an int indicating the number of rows affected. The first thing I would do is check the return. ExecuteNonQuery can execute and not affect any rows, that won't trigger the catch.

Upvotes: 1

Related Questions