open/close oledb connection

open/close oledb connection

• toastman

  My bad. Just had to add
  this.combobox1.DisplayMember = "Country";
  and it works fine
  
  

• Thomas Mauer

  Well the answer to that one seems to be:
  Stick the function call in the backgroundWorker_RunWorkerCompleted event
  and call RunWorkerAsync()
  
  And it works fine....
  
  If this isn't the right way, please let me know...
  
  
  
  

• VDBA

  One more question... will the above code work for comboboxes
  eg:
  this.combobox1.datasource = dogetdata("select country from log");
  
  My combobox get filled but with "System.Data.DataRowView" and not the values from the table.
  
  Appreciate any help.
  
  

• NetPochi

  no you dont have to. the DataAdapter takes care of the connection status. You could if you like open the connection, fill it, then close it but I believe the dataAdapter will handle this

  to open the connection:

  theSQLCommand.Connection.Open();

   

  to close the connection:

  theSQLCommand.Connection.Close();

   

  the connection is the SqlConnection object, which is created either seperately or within the constructor of the SqlCommand object as the second parameter

  
  
  

• JUANCARLOSR

  Thanks. That worked great. I am trying to put this in a backgroundworker
  theDataGridView.DataSource = this.DoGetDataTable("SELECT * FROM table")
  but I get the error
  Cross-thread operation not valid: Control 'dataGridView1' accessed from a thread other than the thread it was created on.
  
  How can I call this code in a backgroundworker
  
  
  

• George Waters

  ah!

  im not sure about that to be honest. Take a look at this on working with the background worker:

  http://msdn2.microsoft.com/en-us/library/system.componentmodel.backgroundworker.aspx

  otherwise take a look at this on invoking controls in threading:

  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=564275&SiteID=1

  
  
  

• Steveinbeloit

  Thanks.
  
  Please can you let me know how I can use the above code in a reusable function
  where I just supply the SQL
  and function returns ds.Tables[0].defaultview
  
  That way I can call the function from anywhere without repeating the code.
  
  
  

• ankurtech

  not at all. :-) when I did this and from what people said, you will find that it will automatically open and close the connection to the database (Sql or Odbc or OleDb...whatever it is you are using). The DataAdapter takes care of the connection status.

  however if you want to be "sure" then do this:

  DataSet ds = new DataSet();
  OleDbDataAdapter da = new OleDbDataAdapter();
  OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\MyDb.mdb;User Id=admin;Password=;");
  string strSQL = "SELECT * FROM Log";
  OleDbCommand cmd = new OleDbCommand(strSQL, con);
  da.SelectCommand = cmd;

  con.Connection.Open();
  da.Fill(ds);

  con.Connection.Close();
  this.dataGridView1.DataSource = ds.Tables[0].DefaultView; //something extra for you...

  
  
  

• S_A_S

  whoa, you lost me there - blinded by science.
  
  So with the above code - if I use it as is - will I run into trouble
  
  

• XNA Rockstar

  sure.

  private DataTable DoGetData(string sqlStatement)

  {

  DataSet ds = new DataSet();
  OleDbDataAdapter da = new OleDbDataAdapter();
  OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\MyDb.mdb;User Id=admin;Password=;");
  
  OleDbCommand cmd = new OleDbCommand(sqlStatement, con);
  da.SelectCommand = cmd;

  con.Connection.Open();
  da.Fill(ds);

  con.Connection.Close();
  return ds.Tables[0];

  }

  hopefully that should do it. All you need to do now is just call it and give it an SQL Command (select * from table for example)

  usage:

  theDataGridView.DataSource = this.DoGetDataTable("SELECT * FROM table")

  
  
  
• open/close oledb connection