C# access database error

Question

This code when used in MS-Access is running and updating in property, but when using through database it's giving syntax error

string item = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();

string h="update Follow_Date set Current_Date='" + dateTimePicker1.Value.ToLongDateString() + "', Current_Time='" + dateTimePicker3.Value.ToLongTimeString() + "', Type='" +
                            comboBox1.SelectedItem.ToString() + "', Remarks='" +
                            textBox1.Text + "', Next_Follow_Date='" + dateTimePicker2.Value.ToLongDateString()+ "' where Follow_Id='" +
                            item.ToString() +"'";

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\lernovo\Documents\JDB.mdb");

con.Open();

OleDbCommand cmd = new OleDbCommand(h, con);
cmd.ExecuteNonQuery();

Error is syntax error.

I would strongly advice you to move away from the approach of using string concatenation when creating sql queries. This is a sql injection disaster waiting to happen — TGH
– TGH, Commented Jul 6, 2013 at 6:37

Praveen · Accepted Answer · 2013-07-06 07:01:05Z

string item = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();

string h="update Follow_Date set @Current_Date, @Current_Time, @Type, @Remarks, @Next_Follow_Date where @Follow_Id";

try
{
Using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\lernovo\Documents\JDB.mdb"))
{
  con.Open();

  Using (OleDbCommand cmd = new OleDbCommand(h, con))
  {    
    cmd.Parameters.Add("Current_Date", dateTimePicker1.Value.ToLongDateString());
    cmd.Parameters.Add("Current_Time", dateTimePicker3.Value.ToLongTimeString());
    cmd.Parameters.Add("Remarks", textBox1.Text);
    cmd.Parameters.Add("Type", comboBox1.SelectedItem.ToString());
    cmd.Parameters.Add("Next_Follow_Date", dateTimePicker2.Value.ToLongDateString());
    cmd.Parameters.Add("Follow_Id", item.ToString());
    cmd.ExecuteNonQuery();
  }
}
}
catch(SQLException ex)
{
System.Console.WriteLine(ex.Message, ex.StackaTrace)
}

You're not closing your Database connection and try to use Parameter instead of concatenation(Probe to SQL Injection).

Catch your error message and it trace it using StackTrace. Try to use Using statement to dispose object properly.

dhaval · Accepted Answer · 2013-07-06 06:39:47Z

0

Looks like a trivial mistake...

You must have to open the db connection before executing queries on it..

conn=new conn(db param);
try
{
  conn.open()
}
catch(Exception e)
{
  e.getMessage();
}

answered Jul 6, 2013 at 6:39

dhaval

1761 gold badge1 silver badge7 bronze badges

2 Comments

Rahul Over a year ago

Not downvoting but he have already opened the connection. See the question properly.

user2555677 Over a year ago

sir it's writtent con.Open()

Collectives™ on Stack Overflow

C# access database error

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related