InsertCommand (sql insert) using SqlDataSource / GridView

September 30, 2009

 

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.