locked
Insert into Access DataBase in .CS file RRS feed

  • Question

  • User193304905 posted

    I want to capture users as they login and creat a record in an Access database. Since there is a .cs file associated with the Login page and I'm using a Master page for the Login page, I can't use the AccessDatasource1.Insert() on the Login page and I don't know how to do it in the .cs file.  Any ideas?

    Ray

    Wednesday, October 16, 2013 3:41 PM

Answers

  • User1786833724 posted

    try this

    Donwload Solution

     

    //Target Database : Ms-Access Database
                string strAccessConnectionString =
                                                    @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                                    @"Data Source=C:\Database\TestDB.mdb;";
                OleDbConnection accessConnection = new OleDbConnection(strAccessConnectionString);
                accessConnection.Open();
                OleDbCommand insertCommand = new OleDbCommand();
                insertCommand.Connection = accessConnection;
                insertCommand.CommandText = @"INSERT INTO Cust (CustomerID, CustomerName, Country) VALUES (@id, @name, @country)";
    
                foreach (DataRow row in dtCustomers.Rows)
                {
                    Console.WriteLine("{0}\t{1}\t\t\b{2}", row["customerNumber"].ToString(), row["customerName"].ToString().PadRight(25), row["country"].ToString());
                    insertCommand.Parameters.Clear();
                    insertCommand.Parameters.AddWithValue("@id", row["customerNumber"].ToString());
                    insertCommand.Parameters.AddWithValue("@name", row["customerName"].ToString());
                    insertCommand.Parameters.AddWithValue("@country", row["country"].ToString());
    
                    insertCommand.ExecuteNonQuery();
                }
                accessConnection.Close();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 17, 2013 1:56 PM
  • User1786833724 posted
    using System.Data.OleDb;

    at the top.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 19, 2013 4:01 PM
  • User193304905 posted

    Thank you for your assistance. I will need to work on reading and updating the data base bnut I'm able to make the connection, open and close the data base.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 19, 2013 4:56 PM

All replies

  • User-1392847221 posted

    Hi,

    either make a new data source on your page.

    or make AccessDatabaseSource1 to public and call  it from child.

     

    Wednesday, October 16, 2013 4:27 PM
  • User193304905 posted

    Rajat; Thank you for getting back to me. Could you elaborate on your recommendation to AccessDatabaseSource1 to public and call  it from child?

     

    Ray

    Thursday, October 17, 2013 10:35 AM
  • User1786833724 posted

    try this

    Donwload Solution

     

    //Target Database : Ms-Access Database
                string strAccessConnectionString =
                                                    @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                                    @"Data Source=C:\Database\TestDB.mdb;";
                OleDbConnection accessConnection = new OleDbConnection(strAccessConnectionString);
                accessConnection.Open();
                OleDbCommand insertCommand = new OleDbCommand();
                insertCommand.Connection = accessConnection;
                insertCommand.CommandText = @"INSERT INTO Cust (CustomerID, CustomerName, Country) VALUES (@id, @name, @country)";
    
                foreach (DataRow row in dtCustomers.Rows)
                {
                    Console.WriteLine("{0}\t{1}\t\t\b{2}", row["customerNumber"].ToString(), row["customerName"].ToString().PadRight(25), row["country"].ToString());
                    insertCommand.Parameters.Clear();
                    insertCommand.Parameters.AddWithValue("@id", row["customerNumber"].ToString());
                    insertCommand.Parameters.AddWithValue("@name", row["customerName"].ToString());
                    insertCommand.Parameters.AddWithValue("@country", row["country"].ToString());
    
                    insertCommand.ExecuteNonQuery();
                }
                accessConnection.Close();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 17, 2013 1:56 PM
  • User193304905 posted

    Do I include this code in my Login.cs file? Will this add a row to an existing file?

     

    Ray

    Wednesday, October 23, 2013 11:51 AM
  • User1786833724 posted

    It should (please check the url ), but for your particular scenario I would suggest this :

     

    Now as you can access database in the Ms-Access database file, may be you'd like to Have your database table looking like this ( in addition to all other columns you are using )

    ------------------------------------------------------------------------------------------------------
    | user (string) | passwd (string) | active (bool) | last-login (date-time) | last-logout (date-time) |
    ------------------------------------------------------------------------------------------------------

    So instead performing insert operation in the database table everytime a user logs-in, you may want to simply update a column [ active to TRUE ] or if needed, a couple of columns [ active to true & last-login to current System.DateTime.Now() ] wherever in database you are storing your users. I'd suggest it as a better-practice to update the database instead inserting and deleting to keep track of active users.

    Thursday, October 24, 2013 10:29 AM
  • User193304905 posted

    That's a great idea. Thanks, I will give it a try. Will changing from INSERT to UPDATE affect the code you proviously sent to me?

    Ray

    Thursday, October 24, 2013 11:16 AM
  • User1786833724 posted

    Of course, you may need to change insert command into update command.  The rest of the code would remain same for the most of the part

     

    Good Luck !

    Thursday, October 24, 2013 11:18 AM
  • User193304905 posted

    I inserted the following code in the C# file

     string strAccessConnectionString =
                                                    @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                                    @"Data Source=\DataBase\Users.mdb;";
                OleDbConnection accessConnection = new OleDbConnection(strAccessConnectionString);
                accessConnection.Open();
              
                accessConnection.Close();

    And received the following error message when I tested it.

     OleDbConnection accessConnection = new OleDbConnection(strAccessConnectionString);

    I know I didn't include all of the code you provided, but I wanted to see if I could make the connection to the database before I tried to modify it.

    Wednesday, November 13, 2013 2:44 PM
  • User193304905 posted

    I'm Sorry I didn't include the error message. Here it is.

     CS0246: The type or namespace name 'OleDbConnection' could not be found (are you missing a using directive or an assembly reference?)

    Wednesday, November 13, 2013 2:48 PM
  • User1786833724 posted
    using System.Data.OleDb;

    at the top.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 19, 2013 4:01 PM
  • User193304905 posted

    Thank you for your assistance. I will need to work on reading and updating the data base bnut I'm able to make the connection, open and close the data base.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 19, 2013 4:56 PM