[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 812
  • Last Modified:

Simple ASP.NET Insert Data Into SQL

Given a very basic FORM
How should I fill in the blank on Insert_Click() ?
Please help me include the SQL Connection String Sample, etc.
I need to run a SQL statement to insert the form into a table.

<%@ Page Language="vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<Script Language="VB" RunAt='Server'>
Sub Insert_Click()

       'How should I code this part?

End Sub
</Script>


<HTML><BODY>
<Form Method="Post"  EncType="Multipart/Form-Data" RunAt="Server">
<input name="name" type="text" id="name" RunAt="Server">
<Input Type="Submit" Value="Insert" OnServerclick="Insert_Click" RunAt="Server">
</Form>
</BODY></HTML>
0
dennisdominic
Asked:
dennisdominic
1 Solution
 
YZlatCommented:
  Dim conn As SQLConnection

        Dim strConn As String = "your connection string here"


        conn = New OleDbConnection(strConn)
        conn.Open()
Dim sql as string
sql="INSERT INTO MyTable (name) VALUES('" & name.Text & "')"
Dim Cmd As SQLCommand = New SQLCommand(sql, Conn)
        Cmd.CommandText = sql
        Cmd.ExecuteNonQuery()
        Conn.Close()
0
 
YZlatCommented:
Sub Insert_Click()
      Dim conn As SQLConnection
        Dim strConn As String = "Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;User Id=Your_Username;Password=Your_Password;"
        conn = New SQLConnection(strConn)
        conn.Open()
      Dim sql as string
      sql="INSERT INTO MyTable (name) VALUES('" & name.Text & "')"
      Dim Cmd As SQLCommand = New SQLCommand(sql, Conn)
        Cmd.CommandText = sql
        Cmd.ExecuteNonQuery()
        Conn.Close()

End Sub
0
 
antiblandCommented:
Here's a working example I have used before.  When the user clicks submit, the code checks to see if all the fields are filled and if the page is being loaded in response to a client postback.  If this is true, it calls insertRecord() which:

1) establishes a connection (my db was named 'cards.db.'; you could obviously change this)
2) removes any quotes in the form field values before inserting those values into the table
3) looks at the email address, which is the primary key in the table.  if it's not unique, there's a problem and the script is halted; otherwise it continues successfully.

#####################VB.NET Code

Sub InsertRecord()
      Dim conClasf As OleDbConnection
      Dim cmdClasf As New OleDbCommand
      Dim strClasf As String
      Dim strSQL as String
      Dim intRowsAff As Integer
      
      lblErrMsg.Text = ""
      lblRecsAff.Text = ""
      strClasf = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=" & _
                        server.MapPath("cards.mdb") & ";"
      conClasf = New OleDbConnection(strClasf)
      conClasf.Open
      
      strSQL = "INSERT INTO card_list (" & _
                   "Email, FirstName, MiddleName, LastName, Title, Suffix, OrganizationName, Service, Address, City, State, PostalCode, Country, WorkPhone, FaxNumber " & _
                   ") VALUES ('" & _
                                     Replace(txtEmail.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(txtFirstName.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(txtMiddleName.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(txtLastName.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(selTitle.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(selSuffix.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(txtOrgName.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(txtService.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(txtAddress.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(txtCity.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(selState.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(txtPostalCode.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(txtCountry.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(txtWorkPhone.Value, "'", "''") & _
                                    "', '" & _
                                    Replace(txtFaxNumber.Value, "'", "''") & _
                                    "')"
                  
      cmdClasf = New OleDbCommand(strSQL, conClasf)
      Try
            intRowsAff = cmdClasf.ExecuteNonQuery()
      Catch ex as exception
            lblErrMsg.Text = "This email address already exists in out system.  Please choose a unique email address"
      End Try
      conClasf.Close
End Sub

Sub btnSubmit_OnClick(Obj as Object, E as EventArgs)
      If Page.IsValid And Page.IsPostBack Then
            InsertRecord()
      End If
End Sub


#####################XHTML

<fieldset>
<legend>Insert a Record</legend>
<form method="post" runat="server">
  <table border="0" cellpadding="2" cellspacing="0" style="margin-left: 75px; width: 550px;">
    <tr>
      <td>Email Address</td>
      <td><input type="text" id="txtEmail" maxlength="50" runat="server" /></td>
        <td class="err">
            <asp:RequiredFieldValidator
              runat="server"
              id="reqEmail"
              ControlToValidate="txtEmail"
              ErrorMessage = "You must provide an email address"
          display="Dynamic" />
        </td>
    </tr>
    <tr>
      <td>Title</td>
      <td><select id="selTitle" runat="server">
                <option value="">-----</option>
          <option value="Mr">Mr</option>
          <option value="Mrs">Mrs</option>
          <option value="Ms">Ms</option>
          <option value="Dr">Dr</option>
        </select></td>
    </tr>
    <tr>
      <td>First Name</td>
      <td><input type="text" id="txtFirstName" maxlength="50" runat="server" /></td>
        <td class="err">
            <asp:RequiredFieldValidator
              runat="server"
              id="reqFirstName"
              ControlToValidate="txtFirstName"
              ErrorMessage = "You must provide a first name"
          display="Dynamic" />
        </td>
    </tr>
    <tr>
      <td>Middle Name</td>
      <td><input type="text" id="txtMiddleName" maxlength="50" runat="server" /></td>
    </tr>
    <tr>
      <td>Last Name</td>
      <td><input type="text" id="txtLastName" maxlength="50" runat="server" /></td>
        <td class="err">
            <asp:RequiredFieldValidator
              runat="server"
              id="reqLastName"
              ControlToValidate="txtLastName"
              ErrorMessage = "You must provide a last name"
          display="Dynamic" />
        </td>
    </tr>
    <tr>
      <td>Suffix</td>
      <td><select id="selSuffix" runat="server">
                <option value="">-----</option>
              <option value="Jr">Jr</option>
          <option value="Sr">Sr</option>
              <option value="PhD">PhD</option>
              <option value="MD">MD</option>
        </select></td>
    </tr>
    <tr>
      <td>Name of Organization/Business</td>
      <td><input type="text" id="txtOrgName" maxlength="50" runat="server" /></td>
        <td class="err">
            <asp:RequiredFieldValidator
              runat="server"
              id="reqOrg"
              ControlToValidate="txtOrgName"
              ErrorMessage = "You must provide an organization/business name"
          display="Dynamic" />
        </td>
    </tr>
    <tr>
      <td>Service Provided</td>
      <td><textarea id="txtService" rows="8" cols="20" runat="server" wrap="soft"></textarea></td>
        <td class="err">
            <asp:RequiredFieldValidator
              runat="server"
              id="reqService"
              ControlToValidate="txtService"
              ErrorMessage = "You must provide a description of the service you offer"
          display="Dynamic" />
        </td>
    </tr>
    <tr>
      <td>Address</td>
      <td><input type="text" id="txtAddress" maxlength="50" runat="server" /></td>
        <td class="err">
            <asp:RequiredFieldValidator
              runat="server"
              id="reqAddress"
              ControlToValidate="txtAddress"
              ErrorMessage = "You must provide an address"
          display="Dynamic" />
        </td>
    </tr>
    <tr>
      <td>City</td>
      <td><input type="text" id="txtCity" maxlength="50" runat="server" /></td>
        <td class="err">
            <asp:RequiredFieldValidator
              runat="server"
              id="reqCity"
              ControlToValidate="txtCity"
              ErrorMessage = "You must provide the city where your organization is located"
          display="Dynamic" />
        </td>
    </tr>
    <tr>
        <td>State</td>
      <td><select id="selState" runat="server">
                <option class="selHdr" value="">Select State</option>
              <option value=""></option>
              <option value="">------------------</option>
              <option value=""></option>
          <option value="Alabama">Alabama</option>
              <option value="Alaska">Alaska</option>
              <option value="American Samoa">American Samoa</option>
              <option value="Arizona">Arizona</option>
              <option value="Arkansas">Arkansas</option>
              <option value="California">California</option>
              <option value="Colorado">Colorado</option>
              <option value="Conneticut">Conneticut</option>
              <option value="Deleware">Deleware</option>
              <option value="District of Columbia">District of Columbia</option>
              <option value="Florida">Florida</option>
              <option value="Georgia">Georgia</option>
              <option value="Guam">Guam</option>
              <option value="Hawaii">Hawaii</option>
              <option value="Idaho">Idaho</option>
              <option value="Illinois">Illinois</option>
              <option value="Indiana">Indiana</option>
              <option value="Iowa">Iowa</option>
              <option value="Kansas">Kansas</option>
          <option value="Kentucky">Kentucky</option>
              <option value="Lousiana">Lousiana</option>
              <option value="Maine">Maine</option>
              <option value="Marshall Islands">Marshall Islands</option>
              <option value="Maryland">Maryland</option>
              <option value="Massachusetts">Massachusetts</option>
              <option value="Michigan">Michigan</option>
              <option value="Minnesota">Minnesota</option>
              <option value="Mississippi">Mississippi</option>
              <option value="Missouri">Missouri</option>
          <option value="Montana">Montana</option>
              <option value="Nebraska">Nebraska</option>
              <option value="Nevada">Nevada</option>
              <option value="New Hampshire">New Hampshire</option>
              <option value="New Jersey">New Jersey</option>
              <option value="New Mexico">New Mexico</option>
              <option value="New York">New York</option>
              <option value="North Carolina">North Carolina</option>
              <option value="North Dakota">North Dakota</option>
              <option value="Northern Mariana Islands">Northern Mariana Islands</option>
          <option value="Ohio">Ohio</option>
              <option value="Oklahoma">Oklahoma</option>
              <option value="Oregon">Oregon</option>
              <option value="Palau">Palau</option>
              <option value="Pennsylvania">Pennsylvania</option>
              <option value="Puerto Rico">Puerto Rico</option>
              <option value="Rhode Island">Rhode Island</option>
              <option value="South Carolina">South Carolina</option>
              <option value="South Dakota">South Dakota</option>
              <option value="Tennessee">Tennessee</option>
              <option value="Texas">Texas</option>
              <option value="Utah">Utah</option>
              <option value="Vermont">Vermont</option>
              <option value="Virgin Islands">Virgin Islands</option>
              <option value="Virginia">Virginia</option>
              <option value="Washington">Washington</option>
              <option value="West Virginia">West Virginia</option>
              <option value="Wisconsin">Wisconsin</option>
              <option value="Wyoming">Wyoming</option>
        </select></td>
        <td class="err">
            <asp:RequiredFieldValidator
              runat="server"
              id="reqState"
              ControlToValidate="selState"
              ErrorMessage = "You must provide the state where your organization is located"
          display="Dynamic" />
        </td>
    </tr>
    <tr>
      <td>Postal Code (Zip)</td>
      <td><input type="text" id="txtPostalCode" maxlength="5" runat="server" size="5" /></td>
        <td class="err">
            <asp:RequiredFieldValidator
              runat="server"
              id="reqPostalCode"
              ControlToValidate="txtPostalCode"
              ErrorMessage = "You must provide the postal code where your organization is located"
          display="Dynamic" />
        </td>
    </tr>
    <tr>
      <td>Country</td>
      <td><input type="text" id="txtCountry" maxlength="50" runat="server" /></td>
        <td class="err">
            <asp:RequiredFieldValidator
              runat="server"
              id="reqCountry"
              ControlToValidate="txtCountry"
              ErrorMessage = "You must provide the country where your organization is located"
          display="Dynamic" />
        </td>
    </tr>
    <tr>
      <td>Work Phone</td>
      <td><input type="text" id="txtWorkPhone" maxlength="50" runat="server" /></td>
        <td class="err">
            <asp:RequiredFieldValidator
              runat="server"
              id="reqWorkPhone"
              ControlToValidate="txtWorkPhone"
              ErrorMessage = "You must provide a work phone number"
          display="Dynamic" />
        </td>
    </tr>
    <tr>
      <td>Fax Number</td>
      <td><input type="text" id="txtFaxNumber" maxlength="50" runat="server" /></td>
    </tr>
      <tr>
            <td></td>
            <td><asp:button type="submit" name="btnSubmit"
             onclick="btnSubmit_OnClick"
             text="Add Your Card" runat="server"/>
            </td>
      </tr>
  </table>
</form>
<p>
  <asp:Label ID="lblRecsAff" runat="server" />
</p>
<p>
  <asp:Label ID="lblErrMsg" runat="server" ForeColor="#FF0000" />
</p>
</fieldset>
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
designbaiCommented:
with little bit of explanation
First, you will need to import the System.Data.SqlClient name space at the top of your page.

Imports System.Data.SqlClient

Next, we write the actual code to connect to SQL Server and insert the data. We apply the below commands to the Click Event of a button control. Notice that we are getting our values from the .Text property of the name TextBox.

Sub Insert_Click()
Dim strConn As String = "data source=xx.xx.xxx.xxx; User ID=xxxxxx; Password=xxxxxx; Persist Security Info=True;packet size=4096"
Dim cmd As New SqlCommand("INSERT INTO Yourtable(name)VALUES('" & name.Text & "')", New SqlConnection(strConn))
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
LoadData()
End Sub


Hope this helps.
0
 
antiblandCommented:
You can download the above file (from that long-winded post) here:

http://www.dustyroaddesigns.com/storage/insert.aspx

- antibland
0
 
dennisdominicAuthor Commented:
Not the best looking code.. but simple enough for someone who's switching from plain ASP.


<%@ Page Language="vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<Script Language="VB" RunAt='Server'>
Sub Insert_Click()
      Dim sql as string
      Dim Cmd as SQLCommand
      Conn = New SqlConnection("SERVER=xx.xx.xx.xxx;User Id=xxx;PASSWORD=xxxxxx;DATABASE=xxxx")  
    Conn.Open()
      sql="INSERT INTO hCollection (name) VALUES('" & Request.Form("name1") & "')"
      Cmd = New SQLCommand(sql, Conn)
      Cmd.CommandText = sql
      Cmd.ExecuteNonQuery()
      Conn.Close()
End Sub
</Script>


<HTML><BODY>
<Form Method="Post"  EncType="Multipart/Form-Data" RunAt="Server">
<input name="name" type="text" id="name1" RunAt="Server">
<Input Type="Submit" Value="Insert" OnServerclick="Insert_Click" RunAt="Server">
</Form>
</BODY></HTML>

0
 
YZlatCommented:
you can improve it by using more functions:

<%@ Page Language="vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<Script Language="VB" RunAt='Server'>
Sub Insert_Click()
     Dim sql as string
     sql="INSERT INTO hCollection (name) VALUES('" & Request.Form("name1") & "')"
      RunQuery(sql)
 
    End Sub

Function GetConnection() As SQLConnection
       Dim Conn As SQLConnection
       Dim strConn As String
      strConn="SERVER=xx.xx.xx.xxx;User Id=xxx;PASSWORD=xxxxxx;DATABASE=xxxx"
     Conn = New SqlConnection(strConn)
    Try
           Conn.Open()
    Catch ex As Exception
        Console.Write(ex.Message)
   End Try
    GetConnection=Conn
End Function

Sub RunQuery(ByVal sql As String)
    Dim Conn As SQLConnection
     Conn=GetConnection()

     Cmd = New SQLCommand(sql, Conn)
     Cmd.CommandText = sql
     Try
            Cmd.ExecuteNonQuery()
     Catch ex As Exception
           Console.Write(ex.Message)
   Finally
     Conn.Close()
End Try
End Sub

</Script>


<HTML><BODY>
<Form Method="Post"  EncType="Multipart/Form-Data" RunAt="Server">
<input name="name" type="text" id="name1" RunAt="Server">
<Input Type="Submit" Value="Insert" OnServerclick="Insert_Click" RunAt="Server">
</Form>
</BODY></HTML>
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now