locked
Accessing Data using SQLConnection RRS feed

  • Question

  • User-1007380802 posted

    Hi Gurus,

    I want to display the EMPLOYEES data in a datagrid in asp.net using C# from the MS Access database. The Access database is in the folder C:|EXAMPLES\NorthWind.MDF.

    How can I go about it using SQLConnection command. I tried, but it does not work. Please help me.

     

    Isaac.

     

     

    Friday, April 8, 2011 1:24 AM

Answers

  • User-821857111 posted

    The only thing you need to do now is call DataBind:

     

    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Employees","Provider=Microsoft.JET.OLEDB.4.0;data source=C:\VS2010EX\NWind.mdb" );
    DataSet ds = new DataSet();
    da.Fill(ds, "Employees");
    DataGrid1.DataSource = ds.Tables["Employees"].DefaultView ;
    
    DataGrid1.DataBind();

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 11, 2011 9:46 AM

All replies

  • User-821857111 posted

    The Access database is in the folder C:|EXAMPLES\NorthWind.MDF.

    That's not an Access database - it's a SQL Server database. You need to attach it to an instance of SQL Server before you can use it.

     

    Friday, April 8, 2011 1:37 AM
  • Friday, April 8, 2011 1:40 AM
  • User3866881 posted

    Hi:)

    There are two ways for you to deal with that:

    1) You should attach the db to the SQL Server first and then make your SqlConnection string mapping to your sql server, something like this:

    using(SqlDataAdapter adapter = new SqlDataAdapter("select * from TableName",new SqlConnection("server=.;database=yourownname;integrated security=sspi"))

    {

       DataTable dt = new DataTable();

       adapter.Fill(dt);

    }

    2) You can also directly access the local db, however you must change your ConnectionString to(This can be done with SqlConnectionBuilder:http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.attachdbfilename.aspx#Y734)

    string connectString =
                    "Server=(local);" +
                    "Integrated Security=true";
                SqlConnectionStringBuilder builder =
                    new SqlConnectionStringBuilder(connectString);
                Console.WriteLine("Original: " + builder.ConnectionString);
                Console.WriteLine("AttachDBFileName={0}", builder.AttachDBFilename);

                builder.AttachDBFilename = @"C:\MyDatabase.mdf";
                Console.WriteLine("Modified: " + builder.ConnectionString);

                using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                {
                     SqlDataAdapter adapter = new SqlDataAdapter("select * from your table name",connection);

                      DataTable dt = new DataTable();

                     adapter.Fill(dt);

               }

    Sunday, April 10, 2011 10:28 PM
  • User-1007380802 posted

    Sorry for the mistake in typing the database name.

    Its the MS Access database name - NorthWind in the folder C:|VS2010EX\NWind.mdb

    I put the following code in the Page_Load event of my asp.net (web) example. But it does not show anything when I run this code. Please help me if this code is wrong.

    Isaac

    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Employees","Provider=Microsoft.JET.OLEDB.4.0;data source=C:\VS2010EX\NWind.mdb" );
    DataSet ds = new DataSet();
    da.Fill(ds, "Employees");
    DataGrid1.DataSource = ds.Tables["Employees"].DefaultView ;

    Monday, April 11, 2011 12:34 AM
  • User-821857111 posted

    The only thing you need to do now is call DataBind:

     

    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Employees","Provider=Microsoft.JET.OLEDB.4.0;data source=C:\VS2010EX\NWind.mdb" );
    DataSet ds = new DataSet();
    da.Fill(ds, "Employees");
    DataGrid1.DataSource = ds.Tables["Employees"].DefaultView ;
    
    DataGrid1.DataBind();

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 11, 2011 9:46 AM
  • User-1007380802 posted

    Thank You.Smile

    Wednesday, April 13, 2011 1:33 PM