InsertCommand (sql insert) using SqlDataSource / GridView


We need a set of controls (textbox, dropDowns, etc), then, when the user fill that, he/she clicks the “Ok button”, and the insert is performed against the database, and we want to use the “insert command” contained into the sqldatasource that is already being used by the gridview control. 

We usually think we need to use the Gridview to accomplish this task, but it’s not necessary at all.

If we have a sqldatasource like this:

   1: <asp:SqlDataSource ID="SqlDataSource2" Runat="server" 

   2: SelectCommand="SELECT * from TEST"

   3: ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"

   4: InsertCommand="INSERT INTO TEST (testData) values (@testData)">

   5:     <InsertParameters>

   6:          <asp:Parameter Type="String" Name="testData"></asp:Parameter>

   7:      </InsertParameters>

   8: </asp:SqlDataSource>

The fill-in controls can be located outside the gridview, in any place in the page.

   1: <asp:TextBox ID="txtTest"  Runat="server"></asp:TextBox>

Also, create a button, give it an identity, and handle the click event as usual. After we click this button, we will perform the INSERT operation.

   1: <asp:Button ID="buttonOk" Runat="server" Text="Ok" OnClick="Button1_Click" />

In the code behind, assuming the sqldatasource object you want to use is called SqlDataSource1 :

   1: void Button1_Click(object sender, EventArgs e)

   2:   {     

   3:       SqlDataSource1.InsertParameters["testData"].DefaultValue = txtData.Text;

   4:       SqlDataSource1.Insert(); //CALL the INSERT METHOD

   5:   }

You can also add any data validation routine at this point.

Note that we called the insert method by hand after we set the values in the parameters as we needed.


Leave a Reply

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

You are commenting using your 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

%d bloggers like this: