creating Simple Form in Asp.net inserting data into Database Table in SQl server
STEP1: Creating the Table in Database.
Create table in SQL using Mangment studio and Solution Explorer , Expand the database Right click on table -> click on new table , create your column as per your requirement like the following.
Note: in this article, The Id is set to auto increment so that the id will be automatically generated for every new added row. To do this select the Column name “Id” and in the column properties set the “Identity Specification” to yes.
Then after adding all the necessary fields, name your Table the way you like.
STEP2: Setting up the UI.
ASPX:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Sample Registration Pagetitle>
<style type="text/css">
.style1
{
width: 100%;
}
style>
head>
<body>
<form id="form1" runat="server">
<div>
<table class="style1">
<tr>
<td>
Full Name:td>
<td>
<asp:TextBox ID="TxtName runat="server">asp:TextBox>
td>
tr>
<tr>
<td>
Username:td>
<td>
<asp:TextBox ID="TxtUserName" runat="server">asp:TextBox>
td>
tr>
<tr>
<td>
Password:td>
<td>
<asp:TextBox ID="TxtPassword" runat="server" TextMode="Password">asp:TextBox>
td>
tr>
<tr>
<td>
Re Password:td>
<td>
<asp:TextBox ID="TxtRePassword" runat="server" TextMode="Password">asp:TextBox>
td>
tr>
<tr>
<td>
Address:td>
<td>
<asp:TextBox ID="TxtAddress" runat="server">asp:TextBox>
td>
tr>
<tr>
<td>
Age:td>
<td>
<asp:TextBox ID="TxtAge" runat="server">asp:TextBox>
td>
tr>
<tr>
<td>
Gender:td>
<td>
<asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="true">
<asp:ListItem Value="-1">Selectasp:ListItem>
<asp:ListItem>Maleasp:ListItem>
<asp:ListItem>Femaleasp:ListItem>
asp:DropDownList>
td>
tr>
table>
div>
<asp:Button ID="Button1" runat="server" Text="Save" onclick="Button1_Click" />
form>
body>
html>
As you can see, the UI is very simple. Now let’s set up the connection string.
STEP3: Setting up the Connection String
In your web.config file set up the connection string there as shown below:
<connectionStrings>
<add name="MyConsString" connectionString="Data Source=localhost;Initial Catalog=MyDatabase;Uid=sa;pwd=sa@123"
providerName="System.Data.SqlClient" />
connectionStrings>
Note: MyConsString is the name of the Connection String that we can use as a reference in our codes for setting the connection string later.
STEP4: Calling up the ConnectionString in our codes
Here’s the method for calling the connection string that was set up in the web.config file.
C#
public string GetConnectionString()
{
//sets the connection string from your web config file "ConnString" is the name of your Connection String
return System.Configuration.ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString;
}
VB.net
Public Function GetConnectionString() As String
'sets the connection string from your web config file "ConnString" is the name of your Connection String
Return System.Configuration.ConfigurationManager.ConnectionStrings("MyConsString").ConnectionString
End Function
STEP5: Writing the method for inserting the data from the registration page to the database.
In this Article, we are using the ADO.NET objects for manipulating the data from the page to the database.
Here’s the code block for inserting the data to the database.
private void ExecuteInsert(string name, string username, string password, string gender, string age, string address)
{
SqlConnection conn = new SqlConnection(GetConnectionString());
string sql = "INSERT INTO tblRegistration (Name, UserName, Password, Gender, Age, Address) VALUES "
+ " (@Name,@UserName,@Password,@Gender,@Age,@Address)";
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter[] param = new SqlParameter[6];
//param[0] = new SqlParameter("@id", SqlDbType.Int, 20);
param[0] = new SqlParameter("@Name", SqlDbType.VarChar, 50);
param[1] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
param[2] = new SqlParameter("@Password", SqlDbType.VarChar, 50);
param[3] = new SqlParameter("@Gender", SqlDbType.Char, 10);
param[4] = new SqlParameter("@Age", SqlDbType.Int, 100);
param[5] = new SqlParameter("@Address", SqlDbType.VarChar, 50);
param[0].Value = name;
param[1].Value = username;
param[2].Value = password;
param[3].Value = gender;
param[4].Value = age;
param[5].Value = address;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
conn.Close();
}
}
VB.Net
Private Sub ExecuteInsert(ByVal name As String, ByVal username As String, ByVal password As String, ByVal gender As String, ByVal age As String, ByVal address As String)
Dim conn As SqlConnection = New SqlConnection(GetConnectionString())
String sql = "INSERT INTO tblRegistration (Name, UserName, Password, Gender, Age, Address) VALUES "
Dim " (@Name,@UserName,@Password,@Gender,@Age,@Address)" As +
Try
conn.Open()
Dim cmd As SqlCommand = New SqlCommand(sql,conn)
Dim param() As SqlParameter = New SqlParameter(6) {}
'param[0] = new SqlParameter("@id", SqlDbType.Int, 20);
param(0) = New SqlParameter("@Name", SqlDbType.VarChar, 50)
param(1) = New SqlParameter("@UserName", SqlDbType.VarChar, 50)
param(2) = New SqlParameter("@Password", SqlDbType.VarChar, 50)
param(3) = New SqlParameter("@Gender", SqlDbType.Char, 10)
param(4) = New SqlParameter("@Age", SqlDbType.Int, 100)
param(5) = New SqlParameter("@Address", SqlDbType.VarChar, 50)
param(0).Value = name
param(1).Value = username
param(2).Value = password
param(3).Value = gender
param(4).Value = age
param(5).Value = address
Dim i As Integer
For i = 0 To param.Length- 1 Step i + 1
cmd.Parameters.Add(param(i))
Next
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
Catch ex As System.Data.SqlClient.SqlException
Dim msg As String = "Insert Error:"
msg += ex.Message
Throw New Exception(msg)
Finally
conn.Close()
End Try
End Sub
STEP6: Calling the method ExecuteInsert()
You can call the method above at Button_Click event for saving the data to the database. Here’s the code block below:
protected void Button1_Click(object sender, EventArgs e)
{
if (TxtPassword.Text == TxtRePassword.Text)
{
//call the method to execute insert to the database
ExecuteInsert(TxtName.Text, TxtUserName.Text, TxtPassword.Text, DropDownList1.SelectedItem.Text, TxtAge.Text, TxtAddress.Text);
Response.Write("Record was successfully added!");
ClearControls(Page);
}
else
{
Response.Write("Password did not match");
TxtPassword.Focus();
}
}
VB.NEt
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
If TxtPassword.Text = TxtRePassword.Text Then
'call the method to execute insert to the database
ExecuteInsert(TxtName.Text, TxtUserName.Text, TxtPassword.Text, DropDownList1.SelectedItem.Text, TxtAge.Text, TxtAddress.Text)
Response.Write("Record was successfully added!")
ClearControls(Page)
Else
Response.Write("Password did not match")
TxtPassword.Focus()
End If
End Sub
As you can see from the above code block, we check the value of the TxtPassword and TxtRePassword to see if match. If it match then call the method ExecuteInsert else display the error message stating that the “Password did not match”.
You also noticed that we call the method ClearControls for clearing the Text fields in the page. See the code block below for the ClearControls method:
public static void ClearControls(Control Parent)
{
if (Parent is TextBox)
{ (Parent as TextBox).Text = string.Empty; }
else
{
foreach (Control c in Parent.Controls)
ClearControls(c);
}
}
VB.net
Public Shared Sub ClearControls(ByVal Parent As Control)
If TypeOf Parent Is TextBox Then
(Parent as TextBox).Text = String.Empty
Else
Dim c As Control
For Each c In Parent.Controls
ClearControls(c)
Next
End If
End Sub
it helps us too sir
ReplyDeletesir kucch aur topics upload kijiye
Really,it's very helpful.......
ReplyDeleteThanks
ReplyDeletePost your problem here, so that I can try to Solve, it
This comment has been removed by the author.
ReplyDelete