How to set this Connection String?

Question

Hi expert, I need help here. Problem is connection string return null and no data load into datatable. Here is the code

 

public partial class Form1 : Form

    {       

        OpenFileDialog dialog = new OpenFileDialog();

        DialogResult dr;

 

        public Form1()

        {

            InitializeComponent();

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

 

            dialog.Filter = "Access 2007 |*.accdb";

            dialog.Title = "Please select a database";

            dialog.FilterIndex = 1;

 

            dr = dialog.ShowDialog();

            if (dr == DialogResult.OK)

            {

                textBox1.Text = dialog.FileName;

                string dbConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +

                    "Data Source=" + textBox1.Text.ToString() + ";" +

                    "Jet OLEDB:Database Password=123456;Persist Security Info=False";

 

                DataTable dtLoad = new DataTable();

                DataRow strDatarow = null;

                dtLoad.Columns.Add("First Name");

                dtLoad.Columns.Add("Last Name");

                dtLoad.Columns.Add("Address");

                dtLoad.Columns.Add("Contact");

                dtLoad.Columns.Add("Reg Date");

              

                string strGenReport = "SELECT fname,lname,address,contact,regdate FROM tcustomer";

 

                using (OleDbConnection conString = new OleDbConnection(dbConn))

                {

                    using (OleDbCommand cmdGenReport = new OleDbCommand(strGenReport, conString))

                    {

                        try

                        {

                            conString.Open();

                            OleDbDataReader readGenReport;

                            readGenReport = cmdGenReport.ExecuteReader();

 

                            while (readGenReport.Read())

                            {

 

                                strDatarow["First Name"] = readGenReport["fname"].ToString();

                                strDatarow["Last Name"] = readGenReport["lname"].ToString();

                                strDatarow["Address"] = readGenReport["address"].ToString();

                                strDatarow["Contact"] = readGenReport["contact"].ToString();

                                strDatarow["Reg Date"] = readGenReport["regdate "].ToString();

                                dtLoad.Rows.Add(strDatarow);

                            }

                            readGenReport.Close();

 

                        }

                        catch (Exception ex)

                        {

                            ex.ToString();

                        }

                    }

                }

       

        dataGridView1.DataSource = dtLoad;

           

            }           

        }

   

}

Thanks in advance...

Answer 1

Connection string will be usually this way for password protected..

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Jet OLEDB: Database Password=MyDbPassword;

---

Karthikeya

Answer 2

check this link http://msdn.microsoft.com/en-us/library/5ybdbtte(v=vs.71).aspx

regards

joon

Answer 3

thanks for your reply, but in this case, user will select database from its local and password is remain the same. the reason is database is not located in absolute location, instead it is located in the relative location base on where the user save the database.

To be simple, the database directory is changing. So how to handle this. Also tried this yet still not working

AppDomain.CurrentDomain.SetData("DataDirectory", textBox1.Text);
                string dbConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                    "Data Source=|DataDirectory|\\DbClient.accdb;" +
                    "Jet OLEDB:Database Password=123456;Persist Security Info=False";

or

AppDomain.CurrentDomain.SetData("DataDirectory", textBox1.Text);
                string dbConn = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                    @"Data Source=|DataDirectory|\\DbClient.accdb;" +
                    @"Jet OLEDB:Database Password=123456;Persist Security Info=False";

Answer 4

Try something a little more simple:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        private OleDbConnectionStringBuilder sb;      
        private OleDbConnection conn;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            sb = new OleDbConnectionStringBuilder();
            sb.Provider = "Microsoft.ACE.OLEDB.12.0";
            sb.PersistSecurityInfo = false;
            sb.Add("Jet OLEDB:Database Password", "123456"); 


            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Accdb files (*.accdb)|*.accdb";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                sb.DataSource = ofd.FileName;
                conn = new OleDbConnection(sb.ConnectionString);
            }

            DataTable table = new DataTable();
            if (!String.IsNullOrEmpty(conn.ConnectionString))
            {
                table = FillTable("SELECT fname, lname, address, contact, regdate FROM tcustomer");
            }
            dataGridView1.DataSource = table;
        }

        private DataTable FillTable(String sql)
        {
            DataTable table = new DataTable();
            using (OleDbDataAdapter da = new OleDbDataAdapter(sql, conn))
            {
                da.Fill(table);
            }
            return table;
        }
    }
}


       
       

       

Answer 5

Or try break it down even further , try this:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        private OleDbConnectionStringBuilder sb;      
        private OleDbConnection conn;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            sb = new OleDbConnectionStringBuilder();
            sb.Provider = "Microsoft.ACE.OLEDB.12.0";
            sb.PersistSecurityInfo = false;
            sb.Add("Jet OLEDB:Database Password", "123456");


            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Accdb files (*.accdb)|*.accdb";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                sb.DataSource = ofd.FileName;
                conn = new OleDbConnection(sb.ConnectionString);
                dataGridView1.DataSource = FillTable("SELECT fname, lname, address, contact, regdate FROM tcustomer");
            }           
     
        }

        private DataTable FillTable(String sql)
        {
            DataTable table = new DataTable();
            using (OleDbDataAdapter da = new OleDbDataAdapter(sql, conn))
            {
                da.Fill(table);
            }
            return table;
        }
    }
}


       
       

       

Answer 6

get complete help from here, this is for beginner purpose -

http://www.codeproject.com/Articles/4416/Beginners-guide-to-accessing-SQL-Server-through-C

---

Pl. Mark/Proposed as Answer if found your solution Vote as Helpful if related to your topic. Always Motivate others by performing this Action.

Answer 7

I see that your Excel files are password protected. Are you getting "Could not Decrypt File" error?

You cannot use ADO .Net (both ACE / JET engines) for opening a password protected Excel file even if you are supplying right credentials.. It is a well known and long time issue as described here.

Don't get disheartened, you have 2 workarounds at least.

(1) Keep the excel file opened inside Excel application just when you are establishing the connection - Still, I don't know whether the connection creation will pass when you keep it opened.

(2) Another best-possible clean approach is described here. The information about GetObject method is available here. Don't worry about needing to include VisualBasic namespace. I hope that is the only way right now.

Hope it helps.

Answer 8

Sorry for late reply. Thank you very much. Great example by

JohnGrove

(MCC)

that's really solved the problem