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.

Advertisements

Reset/Restart/Set a Identity Column in SQL Server / MYSQL / PostgreSQL

July 15, 2009

 

A relative common task that you will do someday in your life will be to reset an identity columns. Yes, those that autoincrement their value when a new row is inserted in the database table.

SQL SERVER

   1: dbcc CHECKIDENT ('table',reseed,0)

You need to call the TSQL function checkindent. Pass in the table name (as string), then ‘reseed’ or ‘noressed’ and last but not least the number you want to set the current identity.

Reseed should be passed in, but you can use noreseed if you dont want to the current identity value to be corrected.

But.. hey.. what does corrected means??.

Well, simply, the table will fix its internal sequence, so the next insert action will produce a number that will not cause conflicts.

If you don’t reseed depending of your table configuration and your current records, you will get the:

Checking identity information: current identity value 'some-number', current column value 'some-number'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

But be careful, anyway, if you have 100 records, and each of them have an ID from 1 to 100, and then you reseed your table to 1, you will get the conflict, because the values needs to be unique.

MYSQL

   1: ALTER TABLE tablename AUTO_INCREMENT = value

As easy as that. Simply set the autoincrement.

PostgreSQL

   1: ALTER SEQUENCE seqname RESTART WITH 1;

In PostgreSQL the tables can support n sequences. So, you need to alter the sequence that your auto numeric columns is using.

Simply use the alter clause, and set a value.