locked
Connecting to a mdb in C#? RRS feed

  • Question

  • User-1793215261 posted

    Hello

    This is my first ever excursion into Visual C# and know little about it, though I am attempting a couple of online tutorials.

    I have a simple 'new user' registration form for a Web site which uses an MS Access 2003 database, 'students.mdb', with the following columns, username, password, strEmail, in a table called 'university'.

    I have already designed the form in Visual Studio 2013 with the following form fields: username, password, confirm password, and email. 

    What is the simplest was of connecting to that database in C#, please?

    Many thanks.

    Bluenose

    Monday, August 11, 2014 8:04 AM

Answers

  • User-1199946673 posted

    I can use:

    OleDbConnection conn;

    conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database;Data Source=C:\myC#folder\students.mdb");

    but how does that relate to what I have in Web config?

    It doesn't relate, so that's why this isn't good practice, use the syntax in your previous post!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 14, 2014 8:33 PM
  • User-1360095595 posted

    Bluenose

    I can use:

    OleDbConnection conn;

    conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database;Data Source=C:\myC#folder\students.mdb");

    but how does that relate to what I have in Web config?

    It relates to it in that it replaces it. But hard coding your connection string in your code is not desirable or (as) maintainable.

    The idea of putting your connection string in web.config is really a way for you to know where to go if you need to change the connection string (for whatever reason). Instead of having to dig through your code and have to recompile/rebuild (in some cases not all) your application, you just open web.config in any text editor, change the connection string, and you're "good to go".

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 15, 2014 1:58 AM

All replies

  • User103196646 posted

    Hello Bluenose! Thanks for your post!

    Here's how to use C# to connect to MS Access 2003 database, 'students.mdb':

    using System.Data.OleDb;
    
    private string connectionName = "Provider=Microsoft.Jet.OLEDB.4.0;"
                + "Data Source=Students.mdb;";            
    
    var Myconnection = new OleDbConnection(connectionName);
    
    DataSet myDataSet = new DataSet();
    OleDbCommand myAccessCommand = new OleDbCommand("select * from university", Myconnection);
    OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);

    // Open connection
    Myconnection.Open();

    myDataAdapter.Fill(myDataSet,"University");

    Also, here's an MSDN article to check out: http://msdn.microsoft.com/en-us/library/aa288452(v=vs.71).aspx

    Regards!

    Monday, August 11, 2014 4:46 PM
  • User-1793215261 posted

    Hello csharpgreg

    Many thanks for your reply.

    This is what I have in my Register.aspx.cs file (Visual Studio put it there):

    using Microsoft.AspNet.Identity;
    using System;
    using System.Linq;
    using System.Web.UI;
    using formNew;
    
    public partial class Account_Register : Page
    {
        protected void CreateUser_Click(object sender, EventArgs e)
        {
            var manager = new UserManager();
            var user = new ApplicationUser() { UserName = UserName.Text };
            IdentityResult result = manager.Create(user, Password.Text);
            if (result.Succeeded)
            {
                IdentityHelper.SignIn(manager, user, isPersistent: false);
                IdentityHelper.RedirectToReturnUrl(Request.QueryString["ReturnUrl"], Response);
            }
            else
            {
                ErrorMessage.Text = result.Errors.FirstOrDefault();
            }
        }
    }

    and in my Web config file (Visual Studio put this there also):

    <?xml version="1.0"?>
    <configuration>
    
      <location path="Manage.aspx">
        <system.web>
          <authorization>
            <deny users="?"/>
          </authorization>
        </system.web>
      </location>
    
    </configuration>

    Should I add your code to the Web config file?

    Thanks again.

    Blue

    Monday, August 11, 2014 4:52 PM
  • User-1793215261 posted

    Just one other thing, csharpgreg, if I may?

    In your script, should that be:

    + "Data Source=students.mdb;";  

    Monday, August 11, 2014 4:55 PM
  • User-760709272 posted

    There is an example of using asp.net membership with Access here

    http://msdn.microsoft.com/en-us/library/vstudio/44w5aswa(v=vs.100).aspx

    however you'll need to use asp.net membership table names and definitions.  If you want to use the tables you have you might need to write your own provider, or just do the code from scratch and not use asp.net membership framework at all.

    Monday, August 11, 2014 5:05 PM
  • User103196646 posted

    Can I make a suggestion - .NET comes with SQL Express - instead of using Access 2003 I would use a SQL Express database.

    Also, you can create an ASP.NET membership database very easily -

    Walkthrough: Creating a Web Site with Membership and User Login - http://msdn.microsoft.com/en-us/library/vstudio/879kf95c(v=vs.100).aspx

    Regards!

    Monday, August 11, 2014 5:17 PM
  • User-1793215261 posted

    Hello AidyF

    Many thanks for your reply and the link.

    That example uses ODBC and is a bit above my level at the moment!

    I think my form will use something like "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=" & "C:\myDomain\students.mdb" 

    Monday, August 11, 2014 5:18 PM
  • User-1793215261 posted

    I think I'll take your advice, Greg, and follow that link and graduate to SQL Express. Is that already part of Visual Studio or do I need to download it?

    Some serious reading for me to get through!

    Monday, August 11, 2014 5:26 PM
  • User1918509225 posted

    Hi Bluenose,

    Thanks for your post here.

    About how to use Create a Local Database File in Visual Studio ,I suggest you try the link below:

    http://msdn.microsoft.com/en-US/us-en/library/ms233763.aspx#bkmk_createnewsqldb

    Best Regards,

    Kevin Shen.

    Tuesday, August 12, 2014 4:02 AM
  • User-1793215261 posted

    Thank you for that, Kevin.

    I will try to follow the tutorial today and post back!

    Thanks again.

    Bluenose

    Tuesday, August 12, 2014 4:37 AM
  • User103196646 posted

    Bluenose - Yes, SQL Server Express edition is included with Visual Studio in most cases. Which version of Visual Studio are you using?

    Walkthrough: Creating a Local Database File in Visual Studio - http://msdn.microsoft.com/en-us/library/vstudio/ms233763(v=vs.110).aspx

    Regards!

    Tuesday, August 12, 2014 9:28 AM
  • User-1793215261 posted

    Hello Greg

    Thank you for posting the link.

    I think that's part of the same tutorial posted by Kevin Shen above - I have bookmarked both links and will get to work ASAP!

    Cheers

    Tuesday, August 12, 2014 11:38 AM
  • User364663285 posted

    Here is the way to connect to Access

                OleDbConnection conn2;
                conn2 = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=????;Data Source=C:\folder1\DBfile.accdb");
    

    Wednesday, August 13, 2014 6:12 AM
  • User-1793215261 posted

    Thank you for that wmec

    At the moment, I have this in my aspx.cs file:

    using Microsoft.AspNet.Identity;
    using System;
    using System.Linq;
    using System.Web.UI;
    using formNew;
    
    public partial class Account_Register : Page
    {
        protected void CreateUser_Click(object sender, EventArgs e)
        {
            var manager = new UserManager();
            var user = new ApplicationUser() { UserName = UserName.Text };
            IdentityResult result = manager.Create(user, Password.Text);
            if (result.Succeeded)
            {
                IdentityHelper.SignIn(manager, user, isPersistent: false);
                IdentityHelper.RedirectToReturnUrl(Request.QueryString["ReturnUrl"], Response);
            }
            else
            {
                ErrorMessage.Text = result.Errors.FirstOrDefault();
            }
        }
    }

    MY form has another field (email) called strEmail but that does not appear in the source code above, and in my Web config I have:

    <?xml version="1.0"?>
    <configuration>
    
      <location path="Manage.aspx">
        <system.web>
          <authorization>
            <deny users="?"/>
          </authorization>
        </system.web>
      </location>
    
    </configuration>
    
    using System.Data.OleDb;
    
    private string connectionName = "Provider=Microsoft.Jet.OLEDB.4.0;"
    + "Data Source=students.mdb;";
    
    var Myconnection = new OleDbConnection(connectionName);
    
    // Open connection
    Myconnection.Open();
    

    There is no 'close connection' and I am getting an error here:

    Line 15: using System.Data.OleDb;

     Error:  Data at the root level is invalid. 

    My form has four fields: username, password, confirm password, and email (strEmail is the name of the column in my MS Access 2003 database).

    What I need to be able to do is insert data from these fields into that database.

    I would be grateful for any help you can provide.

    I will read up on SQL server Express, but I wanted to see how it was done using MS Access because that is the database that I usually work with.

    Bluenose

    Wednesday, August 13, 2014 9:34 AM
  • User-1793215261 posted

    Hello

    Thank you for your reply.

    I will go through this http://imar.spaanjaars.com/560/using-the-microsoft-access-providers-for-membership-roles-and-profile-under-aspnet-4 in the next day or so because it looks particularly useful.

    I have had a brief look at the other links that you have kindly sent me, though I will need to digest them more thoroughly.

    I am on the right lines with these baby steps?

    aspx.cs
    
    
    using Microsoft.AspNet.Identity;
    using System;
    using System.Linq;
    using System.Web.UI;
    using formNew;
    
    
    public static string GetConnString()
    {
      return WebConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    }
    
    
    string ConnString = Utils.GetConnString();
    string SqlString = "INSERT INTO university (username, password, strEmail) VALUES (@username,@password, @strEmail)";
    using (OleDbConnection conn = new OleDbConnection(ConnString))
    {
      using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
      {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("username", txtusername.Text);
        cmd.Parameters.AddWithValue("password", txtpassword.Text);
        cmd.Parameters.AddWithValue("strEmail", txtstrEmail.Text);
        conn.Open();
        cmd.ExecuteNonQuery();
      }
    }

    and 

    Web config
    
    <asp:SqlDataSource ID="SqlDataSource1" 
        runat="server"
        ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
        ProviderName="<%$ ConnectionStrings:MyConnectionString.ProviderName %>"
       SelectCommand="SELECT * FROM university">
    </asp:SqlDataSource>
    
    
    <connectionStrings>
        <add name="MyConnectionString"
        connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
               Data Source=|DataDirectory|students.mdb"
            providerName="System.Data.OleDb" />
    </connectionStrings>

    Thanks again for your time.

    Wednesday, August 13, 2014 5:16 PM
  • User-1360095595 posted

    That sqldatasource doesn't belong in the Web.config. 

    Also, you need to use [password] because I learned recently that's is a reserved keyword in access. Also, when setting the parameter values, use @password,  @username,  etc.

    See Mike's reply here (you need to scroll down some): http://forums.asp.net/t/2001586.aspx?Not+writing+to+database

    Wednesday, August 13, 2014 6:20 PM
  • User-1199946673 posted

    you need to use [password] because I learned recently that's is a reserved keyword in access

    No it is NOT a reserved word in Access

    http://support.microsoft.com/kb/286335

    When working with an mdb file in a web-environment, connecting using the Jet provider, you'r NOT dealing with an Access Database, but with a Jet Database Engine.

    Password is a Reserved Word in Jet!!!

    http://support.microsoft.com/kb/321266

    Also, when setting the parameter values, use @password,  @username,  etc.

    Why?

    OleDb parameters are NOT recognized by their name, but by their position only! The name is irrelevant, you can even use a totaly differnt name as the parameter in the SQL, or even the same name (or an empty string) for all parameters.....

    Wednesday, August 13, 2014 6:32 PM
  • User-1199946673 posted

    That sqldatasource doesn't belong in the Web.config. 

    And not only that. When you're connecting with the database in code, you don't need a sqlDatasource. The sqlDatasource can be used to connect with a database in HTML markup only, without the need to write any code at all!!!!

    Wednesday, August 13, 2014 6:37 PM
  • User-1360095595 posted

    Password is a Reserved Word in Jet!!!

    Oh no!!!!!! It's jet!!!!! Not access!!!!!! 

    Why?

    That's what I understood from Mike's reply in the thread I linked to. 

    [Anyway, thankfully I don't work with Access/jet; only real database engines like sql server & Oracle]

    Wednesday, August 13, 2014 6:53 PM
  • User-1793215261 posted

    All I am trying to do at the moment is gain a 'picture' in my mind as to how this simple Web form ought to be constructed. I have not ventured into Visual C# before and I am grateful for the help I have received so far from this forum.

    So, I don't need this in Web config?

    <asp:SqlDataSource ID="SqlDataSource1" 
    runat="server"
    ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
    ProviderName="<%$ ConnectionStrings:MyConnectionString.ProviderName %>"
    SelectCommand="SELECT * FROM university">
    </asp:SqlDataSource>

    This SelectCommand="SELECT * FROM university"> looks out of place, so I didn't think so

    Yes, 'password' is a reserved word: http://support.microsoft.com/kb/321266, so the INSERT INTO would be something like:

    "INSERT INTO university (username,[password], strEmail) VALUES (@username,@password, @strEmail)"

    Reserved words are allowed as MetalAsp.Net has said, in Classic ASP at least, provided they take square brackets.

    So, I just place this:

    <connectionStrings>
    <add name="MyConnectionString"
    connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source=|DataDirectory|students.mdb"
    providerName="System.Data.OleDb" />
    </connectionStrings>

    in my Web config?

    And does my aspx.cs look OK?

    using Microsoft.AspNet.Identity;
    using System;
    using System.Linq;
    using System.Web.UI;
    using formNew;
    
    public static string GetConnString()
    {
    return WebConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    }
    
    string ConnString = Utils.GetConnString();
    string SqlString = "INSERT INTO university (username, password, strEmail) VALUES (@username,@password, @strEmail)";
    using (OleDbConnection conn = new OleDbConnection(ConnString))
    {
    using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
    {
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("username", txtusername.Text);
    cmd.Parameters.AddWithValue("password", txtpassword.Text);
    cmd.Parameters.AddWithValue("strEmail", txtstrEmail.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
    }
    }

    Do I need to add anything else? Isn't the database still open?

    Wednesday, August 13, 2014 8:03 PM
  • User364663285 posted

    Hi Bluenose,

    You do not need the part within Web.config and can you try the connection string similar to mine above?

    Wednesday, August 13, 2014 9:47 PM
  • User-1793215261 posted

    OK, thank you all.

    I am trying to tidy things in my mind because different people are saying different things. As I understand it so far, I have this in Web config:

    <configuration>
    
    <connectionStrings>
    <add name="MyConnectionString"
    connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source=|DataDirectory|students.mdb"
    providerName="System.Data.OleDb" />
    </connectionStrings>
    
    </configuration>
    
    

    And in my aspx.cs:
     

    using Microsoft.AspNet.Identity;
    using System;
    using System.Linq;
    using System.Web.UI;
    using formNew;
    
    public static string GetConnString()
    {
    return WebConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    }
    
    string ConnString = Utils.GetConnString();
    string SqlString = "INSERT INTO university (username, password, strEmail) VALUES (@username,@password, @strEmail)";
    using (OleDbConnection conn = new OleDbConnection(ConnString))
    {
    using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
    {
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("username", txtusername.Text);
    cmd.Parameters.AddWithValue("password", txtpassword.Text);
    cmd.Parameters.AddWithValue("strEmail", txtstrEmail.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
    }
    }

    For some reason, there is no 'close' there?

    I can use:

    OleDbConnection conn;

    conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database;Data Source=C:\myC#folder\students.mdb");

    but how does that relate to what I have in Web config?

    There is no password on my database, so I have omitted it from the above string you kindly posted, wmec.

    If I use password, which I do as a column in my database, it goes in square brackets, as below, because it's a reserved word:

    "INSERT INTO university (username,[password], strEmail) VALUES (@username,@password, @strEmail)"

    According to hans, I do not need to use SqlDataSource.

    Thanks again and I'm grateful for your patience.

     

     

    Thursday, August 14, 2014 4:45 PM
  • User-1199946673 posted

    Do I need to add anything else? Isn't the database still open?

    No. Because you're using the 'using statement" This will automaticcaly close and dispose the connection for you

    http://msdn.microsoft.com/en-us/library/yh598w02.aspx

    Thursday, August 14, 2014 8:22 PM
  • User-1199946673 posted

    Oh no!!!!!! It's jet!!!!! Not access!!!!!! 

    It might seem insignificant, but if ypu don't realize the difference, you don't understand some problems.. Not only the list of reserved words is different, but also some buildin function's are nor available (for example Replace).. In Jet, the wildcard character is %, in Access it is *, And off course, only the tables and stored queries are available, so you can't access the Reports and forms, and also not any self created functions. So queries that will work in Access won't work anymore when connecting using the Jet Provider.....

    Thursday, August 14, 2014 8:30 PM
  • User-1199946673 posted

    I can use:

    OleDbConnection conn;

    conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database;Data Source=C:\myC#folder\students.mdb");

    but how does that relate to what I have in Web config?

    It doesn't relate, so that's why this isn't good practice, use the syntax in your previous post!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 14, 2014 8:33 PM
  • User-1360095595 posted

    Bluenose

    I can use:

    OleDbConnection conn;

    conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database;Data Source=C:\myC#folder\students.mdb");

    but how does that relate to what I have in Web config?

    It relates to it in that it replaces it. But hard coding your connection string in your code is not desirable or (as) maintainable.

    The idea of putting your connection string in web.config is really a way for you to know where to go if you need to change the connection string (for whatever reason). Instead of having to dig through your code and have to recompile/rebuild (in some cases not all) your application, you just open web.config in any text editor, change the connection string, and you're "good to go".

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 15, 2014 1:58 AM
  • User-1793215261 posted

    Thanks for that MetalASp.Net

    Oh, OK, I didn't know it replaced it.

    Yes, I can understand that if the connection string is in Web config, it would be easier to edit.

    Sorry for the delay in replying.

    Wednesday, August 27, 2014 12:46 PM
  • User-1793215261 posted

    Thank for your explanation, hans_v.

    Much appreciated.

    Sorry for taking so long to reply.

    Wednesday, August 27, 2014 1:03 PM