Web Programming guide – Inserting data using SQL data source controller

In developing web base system, retrieving and inserting data is a basic thing. As in ASP.NET, it makes development easy. As we’re going to explore one of the control-SqlDataSource control which I have tested it out with a simple web application form name ‘NewContract.aspx’ in SCM test n error 2 project.

NewContract.aspx
NewContract.aspx

Let’s  start from the scratch. Before we start, remember to have your database connected, in each table, there is a relevant primary key.

In a new aspx page, just drag the tools you needed from the toolbox and place it into your form. As I have put in 2 labels, 2 text box field and a button. After that you can drag a control from the toolbox call ‘SqlDataSource’ and place it into the form.

Then click on the arrow button beside the controller to configure your data source setting.

Configure your data source setting

Choose your data connection to connect to your database

Choose data connection
Choose data connection

Specify which fields and tables will be use in the INSERT operation. At the Advance button to click the two check boxes to enable INSERT statement to be use.

After that, get back to the form, right click the data source controller to click on the properties, allowing the property table to be shown. In the property table, there is a property name ‘InsertQuery’ where you are allow to specify the parameter and assigning the source to the parameter. You can choose what parameter source to use, but as for me, I have use ‘Form’ where I can assign the test box Id to the parameter.

The codes will be generated automatically in your aspx file. Here is mine.

<%@ Page Language=”C#” AutoEventWireup=”true” CodeBehind=”NewContract.aspx.cs” Inherits=”SCM_test_n_error_2.NewContract” %>

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;

<html xmlns=”http://www.w3.org/1999/xhtml&#8221; >

<head runat=”server”>

<title></title>

</head>

<body>

<form id=”form1″ runat=”server”>

<div>

<p>

<asp:Label ID=”Label1″ runat=”server” Text=”Agreement No:”></asp:Label>

<asp:TextBox ID=”AgreementNo” runat=”server”>test1</asp:TextBox>

</p>

<p>

<asp:Label ID=”Label2″ runat=”server” Text=”Agreement Date:”></asp:Label>

<asp:TextBox ID=”AgreementDate” runat=”server” Text=”test2″></asp:TextBox>

</p>

</div>

<asp:Button ID=”Button1″ runat=”server” Text=”Save” onclick=”Button1_Click”/>

</form>

<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server”

ConflictDetection=”CompareAllValues”

ConnectionString=”<%$ ConnectionStrings:ConnectionString %>”

DeleteCommand=”DELETE FROM [Agreement] WHERE [id] = @original_id AND (([AGRNO] = @original_AGRNO) OR ([AGRNO] IS NULL AND @original_AGRNO IS NULL)) AND (([AGRDAT] = @original_AGRDAT) OR ([AGRDAT] IS NULL AND @original_AGRDAT IS NULL))”

InsertCommand=”INSERT INTO Agreement(AGRNO, AGRDAT) VALUES (@AGRNO, @AGRDAT)”

OldValuesParameterFormatString=”original_{0}”

SelectCommand=”SELECT * FROM [Agreement]”

UpdateCommand=”UPDATE [Agreement] SET [AGRNO] = @AGRNO, [AGRDAT] = @AGRDAT WHERE [id] = @original_id AND (([AGRNO] = @original_AGRNO) OR ([AGRNO] IS NULL AND @original_AGRNO IS NULL)) AND (([AGRDAT] = @original_AGRDAT) OR ([AGRDAT] IS NULL AND @original_AGRDAT IS NULL))”>

<DeleteParameters>

<asp:Parameter Name=”original_id” Type=”Int32″ />

<asp:Parameter Name=”original_AGRNO” Type=”String” />

<asp:Parameter Name=”original_AGRDAT” Type=”String” />

</DeleteParameters>

<UpdateParameters>

<asp:Parameter Name=”AGRNO” Type=”String” />

<asp:Parameter Name=”AGRDAT” Type=”String” />

<asp:Parameter Name=”original_id” Type=”Int32″ />

<asp:Parameter Name=”original_AGRNO” Type=”String” />

<asp:Parameter Name=”original_AGRDAT” Type=”String” />

</UpdateParameters>

<InsertParameters>

<asp:FormParameter FormField=”AgreementNo” Name=”AGRNO” Type=”String” />

<asp:FormParameter FormField=”AgreementDate” Name=”AGRDAT” Type=”String” />

</InsertParameters>

</asp:SqlDataSource>

</body>

</html>

One more step to go, assigning the insert action to the save button in NewContract.aspx.cs file.

using System;

using System.Collections.Generic;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace SCM_test_n_error_2

{

public partial class NewContract : System.Web.UI.Page

{

protected void Button1_Click(object sender, EventArgs e)

{

SqlDataSource1.Insert();

}

}

}

Whoa la!! all data will be save into the database without any sweat. Good luck! Happy trying.

One thought on “Web Programming guide – Inserting data using SQL data source controller

  1. http://jingleyanqiu.wordpress.com/2010/06/16/thursday-poets-rally-week-23-june-17-23-2010/

    I invite you to join our weekly Thursday Poets Rally,
    As a fresh or first time participant,
    what you do is to comment under my post with a poem link,
    let me know,
    comment 18 poets from the perfect poet award winner list under the participants list on top…
    then let me know and done….

    poetry awards will be assigned based on your comments and poem contribution…

    Happy Thursday!
    BEST!😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s