ASP.Net/ADO.net Database/Insert OleDbConnection

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

Generate SQL insert command (VB.net)

<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.OleDb" %>
<html>
  <head>
    <title>Validating a Field</title>
  </head>
  <body>
    <form id="Form1" method="post" runat="server">
      <table id="Table1"
             style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 8px"
             cellSpacing="0" cellPadding="0" width="300" border="0">
        <tr>
          <td style="WIDTH: 115px">
            <asp:Label id="Label1" runat="server">FirstName</asp:Label>
          </td>
          <td>
            <asp:TextBox id="txtCategoryName" runat="server" width="193" />
          </td>
        </tr>
        <tr>
          <td style="WIDTH: 115px">
            <asp:Label id="Label2" runat="server">LastName</asp:Label>
          </td>
          <td>
            <asp:TextBox id="txtDescription" runat="server" width="193" />
          </td>
        </tr>
        <tr>
          <td style="WIDTH: 115px" colSpan="2">
            <asp:Button id="btnInsert" runat="server"
                 OnClick="btnInsert_Click" width="298" text="INSERT!" />
          </td>
        </tr>
      </table>
      <asp:RequiredFieldValidator id="rfvCategoryName" runat="server"
          style="Z-INDEX: 102; LEFT: 316px; POSITION: absolute; TOP: 14px"
          ErrorMessage="Please insert the new category name"
          ControlToValidate="txtCategoryName" />
    </form>
  </body>
</html>
<script language="VB" runat="server">
Dim objConnection As OleDbConnection
Sub Page_Load(Source as Object, E as EventArgs)
  objConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _
                        "data source=" + MapPath("EmployeeDatabase.mdb"))
End Sub
Sub btnInsert_Click(Sender As Object, E As EventArgs)
  If Page.IsValid Then
    Dim strSQL As String = "INSERT INTO Employee " & _
                           "(FirsName, LastName) VALUES (?, ?)"
    Dim dbComm As New OleDbCommand(strSQL, objConnection)
    dbComm.Parameters.Add("FirstName", OleDbType.VarChar, 8, "FirstName")
    dbComm.Parameters.Add("LastName", OleDbType.VarChar, 8, "LastName")
    dbComm.Parameters("FirstName").Value = txtCategoryName.Text
    dbComm.Parameters("LastName").Value = txtDescription.Text
    Try
      objConnection.Open()
      dbComm.ExecuteNonQuery()
    Catch ex As Exception
      Response.Write(ex.Message)
      Response.End
    Finally
      If objConnection.State = ConnectionState.Open Then
        objConnection.Close()
      End If
    End Try
    Response.Write("A new record has been added")
    Response.End
  End If
End Sub
</script>


<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>


Insert data to database using SQL (C#)

<%@ Page Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="C#" runat="server">
  void Page_Load (Object sender, EventArgs e)
  {
    OleDbConnection objConnection = null;
    OleDbCommand objCmd = null; 
    String strConnection, strSQL;
    strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
//    strConnection += @"Data Source=C:\Northwind.mdb";
    strConnection += @"Data Source="+MapPath("EmployeeDatabase.mdb");
     
    // Create and open the connection object
    objConnection = new OleDbConnection(strConnection);
    objConnection.ConnectionString = strConnection;
    objConnection.Open();
    
    // set the SQL string
    strSQL = "INSERT INTO Employee (FirstName , LastName ) " +
    "VALUES ( "Beth" , "Hart" )";
    // Create the Command and set its properties
    objCmd = new OleDbCommand(strSQL, objConnection);
    
    // execute the command
    objCmd.ExecuteNonQuery();
    lblStatus.Text = "Command run";
  }
</script>
<html>
  <body>
    <h2>Using SQL directly</h2>
    <asp:Label id="lblStatus" runat="server"/>
  </body>
</html>


<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>


Insert data to OleDbConnection using SQL (VB.net)

<%@ Page Language=VB Debug=true %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OLEDB" %>
<script runat=server>
Sub SubmitBtn_Click(Sender As Object, E As EventArgs)
    Dim DBConn as OleDbConnection
    Dim DBAdd As New OleDbCommand
    DBConn = New OleDbConnection( _
        "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
        & "DATA SOURCE=" _
        & Server.MapPath("EmployeeDatabase.mdb;"))
    DBAdd.rumandText = "Insert Into Employee (" _
        & "LastName, FirstName ) values (" _
        & """ & Replace(txtLastName.Text, """, """") _
        & "", " _
        & """ & Replace(txtFirstName.Text, """, """") _
        & "")"
    DBAdd.Connection = DBConn
    DBAdd.Connection.Open
    DBAdd.ExecuteNonQuery()
End Sub
</SCRIPT>
<HTML>
<HEAD>
<TITLE>Adding Access Data</TITLE>
</HEAD>
<Body LEFTMARGIN="40">
<form runat="server">
<BR>
Last Name:
<BR>
<asp:textbox
    id="txtLastName"
    runat="Server"
/>
<BR>
First Name:
<BR>
<asp:textbox
    id="txtFirstName"
    runat="Server"
/>
<BR><BR>
<asp:button 
    id="butOK"
    text="  OK  "
    onclick="SubmitBtn_Click" 
    runat="server"
/>  
</form>
</BODY>
</HTML>


<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>

Источник — «http://nfex.ru/index.php?title=ASP.Net/ADO.net_Database/Insert_OleDbConnection&oldid=1581»