Inserting and Updating with a SQLDataSource

In most SQLDataSource articles, the data display is usually a Gridview, or a DetailsView. However, in this tutorial, we’re going to look at how to provide Inserting and Updating functions within a page with single-field type controls (TextBox/DropDownList, etc). Sometimes, for simpler pages, these types of controls are a bit overkill, or even with more complex pages where they don’t seem to fit the bill., you might need a more freehand approach

I’m going to use a very simple Insert sample here, with just two textboxes, but you can use any other controls you’d like. In the ASPX page, let’s insert two textboxes and some text, for labeling what should be put in the textboxes, just as you’d see on an average web page, along with a button:

First Name:
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
Last Name:
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
<br />
<asp:Button ID="btnSubmit" runat="server" Text="Submit" />

Of course, in our imaginary scenario, we’d have a table in the database (let’s call it ‘Test_Table’), with only two columns (Firstname, and Lastname). And along with this, we’d have a stored procedure called ‘Insert_Test’, with two parameters:

CREATE PROCEDURE [dbo].[Insert_Test]
@FirstName varchar(50),
@LastName varchar(50)

AS
Insert into Test_New (FirstName, LastName)
Values (@FirstName, @LastName)

Next, we’ll add a SQLDataSource control, and just call it ‘DS1′:

 <asp:SqlDataSource ID="ds1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyTest_dbConnectionString %>"
InsertCommand="Insert_Test"
InsertCommandType="StoredProcedure"
SelectCommand="Select * from Test">
<InsertParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

For the click event of the ‘Submit’ button, we’ll just put in one line:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
ds1.Insert()
End Sub

And the last thing we’ll do is to use the SQLDataSource’s ‘Inserting’ event:

Protected Sub ds1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles ds1.Inserting
e.Command.Parameters("@FirstName").Value = txtFirstName.Text
e.Command.Parameters("@LastName").Value = txtLastName.Text
End Sub

When you click the button, the INSERT method of the SQLDataSource is called, it uses the INSERTING event of the SQLDataSource to populate the stored procedure with the data you provide in the textboxes.

To Update your table, you’d do pretty much the same things, but instead of

       ds1.Insert()

You would use:

  ds1.Update()

Instead of using InsertParameters, you’d add UpdateParameters to the SQLDataSource, and fill in the name of the Update stored Procedure.

Now, that wasn’t hard, was it?

Related Posts:

• No Related Posts