| |
 How to insert records into the database?
by: Itfunda
Product Type: Tips and Tricks (Books)
Technologies: ASP.NET ADO.NET
To connect to the database and insert records into the database, we can follow this approach.
ASPX Page
<asp:Label ID="lblMessage" runat="server" ForeColor="Green" />
<h5>
Create, Read, Update, Delete operation</h5>
<div>
<table>
<tr>
<td>
First name:
</td>
<td>
<asp:TextBox ID="txtFirstName" runat="server" />
</td>
</tr>
<tr>
<td>
Last name:
</td>
<td>
<asp:TextBox ID="txtLastName" runat="server" />
</td>
</tr>
<tr>
<td>
Age:
</td>
<td>
<asp:TextBox ID="txtAge" runat="server" />
</td>
</tr>
<tr>
<td>
Active:
</td>
<td>
<asp:DropDownList ID="dropActive" runat="server">
<asp:ListItem Text="Yes" Value="True" />
<asp:ListItem Text="No" Value="False" />
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="SubmitData" />
</td>
</tr>
</table>
</div>
Code Behind
string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
/// <summary>
/// Submits the data.
/// </summary>
/// <param name="sender">The sender.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
protected void SubmitData(object sender, EventArgs e)
{
int returnValue = 0;
// get the connection
using (SqlConnection conn = new SqlConnection(_connStr))
{
// write the sql statement to execute
string sql = "INSERT INTO PersonalDetail (FirstName, LastName, Age, Active) VALUES " + "(@FirstName, @LastName, @Age, @Active)";
// instantiate the command object to fire
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
// attach the parameter to pass, if no parameter is in the sql no need to attach
SqlParameter[] prms = new SqlParameter[4];
prms[0] = new SqlParameter("@FirstName", SqlDbType.VarChar, 50);
prms[0].Value = txtFirstName.Text.Trim();
prms[1] = new SqlParameter("@LastName", SqlDbType.VarChar, 50);
prms[1].Value = txtLastName.Text.Trim();
prms[2] = new SqlParameter("@Age", SqlDbType.Int);
prms[2].Value = int.Parse(txtAge.Text.Trim());
prms[3] = new SqlParameter("@Active", SqlDbType.Bit);
prms[3].Value = bool.Parse(dropActive.SelectedValue);
cmd.Parameters.AddRange(prms);
conn.Open();
returnValue = cmd.ExecuteNonQuery();
conn.Close();
}
}
if (!returnValue.Equals(0))
{
lblMessage.Text = " Records inserted successfully !";
}
}
In the above code snippet, on the aspx page we have three TextBoxes, a DropDownList (First name, Last name, Age & Active) and a Submit button. On click of the Submit, we have fired the SubmitData server side method.
In the SubmitData method, we have a SqlConnection object and SqlCommand object in the same way we had for fetching the records from the database. The only change here is the sql command that is going to execute. As our sql command has four parameters, we need to attach four SqlParameters to the command object. To execute the Insert, Update and Delete sql query, we need to fire the ExecuteNonQuery method of the command object (before executing this command, ensure that the database connection is open and after executing close this immediately to avoid any memory leak).
ExecuteNonQuery method returns number of records affected with the Sql statement. So we have checked if it is not equal to 0 then wrote success message.
OUTPUT
|