Access DB, No reaction from the DB

Question

Encountered with a problem... maybe something in the syntax of the query, and the compiler doesn't throw any Exception. But the table do not receive information.

Anything you noticed that maybe wrong?

OleDbConnection conn;
OleDbCommand cmd;


public Commands(OleDbConnection con)
{
    conn = con;
}


public Commands()
{
    conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\DB.accdb");
}

public void node_Join(int id, byte[] mac)
{
    try
    {
        conn.Open();
        cmd = new OleDbCommand(@"INSERT INTO Nodes ([J_ID],[Node ID],[Node MAC],[Line Quality],[Status]) values('" + Convert.ToString(id) + @"',0,'" + BitConverter.ToString(mac) + @"',0,'Join')", conn);
        cmd.ExecuteNonQuery();
        conn.Close();
    }
    catch (Exception exc)
    {
        conn.Close();
        MessageBox.Show(exc.Message.ToString());
    }
}

Are you sure your connection string is right? What are the values of Convert.ToString(id) and BitConverter.ToString(mac)? Why do you think you should have these Commands methods? What are the types of your columns? And you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. — Soner Gönül
– Soner Gönül, Commented Jan 19, 2014 at 11:04
Types: J_ID -> Number, Node ID -> Number, Node MAC - > String, Line Q -> Number, Status -> String. right now im just learning to work with sql and etc. so parameterized queries not so needed. — Victor Lev
– Victor Lev, Commented Jan 19, 2014 at 11:10

Community · Accepted Answer · 2017-05-23 12:11:42Z

1

It is not clear what you try to do and I feel like taking a risk to answer but anyway..

As I said in my comment, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

And since your J_ID column is Number, there is no point to insert it a string with Convert.ToString(id). Using id (I assume it is an integer) will probably fine.

Also use using statement to dispose your OleDbConnection.

Try like this;

using(OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\DB.accdb"))
{
  using(cmd = new OleDbCommand(@"INSERT INTO Nodes ([J_ID],[Node ID],[Node MAC],[Line Quality],[Status]) values(?, ?, ?, ?, ?", conn))
  {
    cmd.Parameters.AddWithValue("@id", id);
    cmd.Parameters.AddWithValue("@nodeid", 0);
    cmd.Parameters.AddWithValue("@nodemac", BitConverter.ToString(mac));
    cmd.Parameters.AddWithValue("@line", 0);
    cmd.Parameters.AddWithValue("@status", "Join");
    conn.Open();
    cmd.ExecuteNonQuery();
  }
}

Also think Steve's suggestion in his comment.

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Jan 19, 2014 at 11:18

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Victor Lev Over a year ago

Same problem. The table is still empty.

Soner Gönül Over a year ago

@VictorLev Updated my answer. Can you please try only my code and let me know it is working or not?

Victor Lev Over a year ago

Found the problem, thanks to stackoverflow.com/users/242520/ta-speot-is Changed the "|DataDirectory|" in the the connection string to full path. Worked like charm. Your code is working perfectly and much more comfortable ... thanks for helping.

ta.speot.is Over a year ago

Also use using statement to dispose your OleDbConnection OleDbCommand is IDisposable too, because it's a DbCommand. Also conn.Close is redundant.

Collectives™ on Stack Overflow

Access DB, No reaction from the DB

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related