Writing to access database from asp.et

RaZorCleaN · October 9th, 2003, 06:40 AM

trying to write to a access data base with this code

Code:

tring strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
  strConnection += @"Data Source=C:\Inetpub\wwwroot\asp\Hemsida\temp\news.mdb";
  data_src.Text = strConnection;
  string strSQL = "INSERT INTO newsen1 (datum, forfattare, innehall) VALUES (@2003-10-09, @Nisse, @TESTAR)";

also wanna know how to post thru a html dokument to the access database with a submit button. anyone got a good code sample for that??

//Fredrik

//RaZ
www.razorclean.com

Bob Bedell · October 9th, 2003, 11:43 AM

Hi,

I made a simple Web Form that inserts a new record into the Categories table of Northwind. First, add the following connection string info to your Global.asax.vb file. It's a handy place to keep connection info:

Public Class Global
    Inherits System.Web.HttpApplication

    ' Constants
    Public Const DbString As String = _
          "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=C:\Inetpub\wwwroot\Submit_OleDb\Northwind.m db;" & _
          "User ID=Admin;" & _
          "Password="

This goes right above the Component Designer Generated Code. I just put the .mdb in the projects root directory on IIS. Also, enable impersonation on your ASP.NET worker process account by adding the <identity> attribute to your Web.Config file:

<?xml version="1.0" encoding="utf-8" ?>
  <configuration>
    <system.web>
     <identity impersonate="true"/>

To make impersonation work (you're impersonating your Windows login to get write acces to the db), go into IIS, right click on your ASP.NET project, select Properties -> Directory Security -> Edit, uncheck the Anonymous Access checkbox, and be sure the Integrated Windows Authentication checkbox is checked in the Authentication Methods dialog. Your ASP.NET worker process account will now use your system login which shold have write access to the Northwind.mdb. If you don't go through this process, you will be able to read from, but not write to, the database.

Second, create a simple Web Form with 5 controls: txtCategoryName and a label, txtDescription and a label, btnSubmit. I named the form Submit_OLEDB.aspx.

Third, paste the following in the Web Form's aspx.vb code-behind file:

Imports System.Data.OleDb

Public Class Submit_OLEDB
    Inherits System.Web.UI.Page

' #Region " Web Form Designer Generated Code "

Private Sub btnSubmit_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles btnSubmit.Click

  If Page.IsValid Then
    Dim connection As New OleDbConnection(Global.DbString)

    Dim strSQL As String = "INSERT INTO Categories " & _
                                                       "(CategoryName, Description) VALUES (?,?)"

    Dim command As New OleDbCommand(strSQL, connection)

    command.Parameters.Add("CategoryName", OleDbType.VarChar, 32, "CategoryName")
    command.Parameters.Add("Description", OleDbType.VarChar, 128, "Description")

    command.Parameters("CategoryName").Value = txtCategoryName.Text
    command.Parameters("Description").Value = txtDescription.Text

    Try
        connection.Open()
        command.ExecuteNonQuery()
    Catch ex As Exception
        Response.Write(ex.Message)
        Response.End()
    Finally
        If connection.State = ConnectionState.Open Then
            connection.Close()
        End If
     End Try

     Response.Write("A new record has been added")
     Response.End()
   End If

  End Sub

End Class

That should perform a simple submit/insert operation for you.

HTH,

Bob

RaZorCleaN · October 9th, 2003, 01:16 PM

Thannx Mate

//RaZ
www.razorclean.com