INSERT from ASP.NET to MS Access

Question

We are trying to build a Help Desk ticketing system just for intranet. Deciding upon the ASP .NET (C#) with Visual Studio 2008 Express (think we have a full version floating around if we need it). Nothing fancy, couple of pages grabbing NTLM information, system information and storing it along with their problem in a database. Goal is to make it simple, but instead of using our SQL Server 2000 back end, the admin wants me to use MS Access. I have the GridView and connections running smooth. Can pull select queries until my heart is content. However, tying in a couple variables with a text box on a submit button into say an INSERT statement.. well I don't even know where to begin with MS Access. Every internet example is in VB .NET plus seems to be hand coding what Visual Studio has already done for me in a few clicks.

Is MS Access going to be too hard for all we want to do? If not, where do we begin to simply submit this data into the tables?

Edit: After a bunch of playing around we have the OleDB working. It's not pretty, yes SQL Server would be awesome but, sometimes you just have to play ball.

Edit: Anyone looking for an actual coded answer, here you are. There has got to be others out there in the same boat.

    string userIP = Request.UserHostAddress.ToString();
    string userDNS = Request.UserHostName.ToString();
    string duser = Request.ServerVariables["LOGON_USER"];  //NTLM Domain\Username
    string computer = System.Environment.MachineName.ToString(); //Computer Name
    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\helpdesk.MDB;";

    OleDbConnection conn = new OleDbConnection(connectionString);
    conn.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;
    cmd.CommandText = "INSERT INTO ticketing ([user], [comp], [issue]) VALUES (@duser, @computer, @col3)";
    cmd.Parameters.Add("@duser", OleDbType.VarChar).Value = duser;
    cmd.Parameters.Add("@computer", OleDbType.VarChar).Value = computer;
    cmd.Parameters.Add("@col3", OleDbType.LongVarChar).Value = TextBox1.Text;
    cmd.ExecuteNonQuery();
    conn.Close();

Answer 1

The admin is nuts. Access is an in-process database, and as such is not well suited for web sites where users will be creating or updating records.

But as far as creating INSERT queries go, Access is no harder than anything else. If you can't create INSERT queries for Access you'll probably have trouble with SQL Server as well.

Answer 2

I also suggest using SQL Server, but considering your problem: What is your problem writing an INSERT query for Access ? You should make use of the classes that you'll find in the System.Data.OleDb namespace:

• OleDbConnection
• OleDbCommand

Quick'n dirty code (not compiled whatsoever):

OleDbConnection conn = new OleDbConnection (connectionString);

OleDbCommand command = new OleDbCommand();
command.Connection = conn;
command.CommandText= "INSERT INTO myTable (col1, col2) VALUES (@p_col1, @p_col2)";
command.Parameters.Add ("@p_col1", OleDbType.String).Value = textBox1.Text;
...
command.ExecuteNonQUery();

There are some caveats with the OleDb classes however (like adding the Parameters to the collection in the order that they occur in your SQL statement, for instance).

Answer 3

Don't bother with Access. Use SQL Server Express. There's also an admin tool for it that looks like the full blown SQL Server management tool.

Answer 4

Access has its place, and can usually do more than what most people give it credit for, but yes you want to use SQL Server in ones of its many forms (eg. SQL Server Express) or another proper "server" database for a web app like this.