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.

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

 

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.

Send data using POST / HttpWebRequest

As you may know, the POST method embeds the contents inside the stream itself, but the way this vars are actually structured can be very similar to the query string /GET method. 

   1: HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create("http://www.url.com/login.php");

   2: httpRequest.CookieContainer = Cookies;

   3: httpRequest.Method = "POST";

   4: httpRequest.ContentType = "application/x-www-form-urlencoded";

We are using a HttpWebRequest object to configure a web request, we are also setting the method to POST, last but absolutely not least, we are adding a header in the request to tell we are actually posting data url encoded.

This means, our data can be like this:

   1: string poststring = string.Format("COOKIEusername={0}&COOKIEpass={1}", "user", "password", captcha.Text);

In this example, we are assuming we want to loging to a website, and this webside actually use some kind of cookie based authentication mechanism, this is why we are using a CookieContainer.

   1: CookieContainer Cookies = new CookieContainer();

Later on, if you want to do some other request, and you need the site remembers you, you can actually pass in the same CookieContainer to the next requests.

But anyway, back to the subject:

   1: byte[] bytedata = Encoding.UTF8.GetBytes(poststring);

   2: httpRequest.ContentLength = bytedata.Length;

First, We are getting a byte[] array from our post var data, and encoding it using UTF8. (extended ASCII support for special chars)

Then, we just tell the response the length of our content, which is the same of our post data string. Then:

   1: Stream requestStream = httpRequest.GetRequestStream();

   2: requestStream.Write(bytedata, 0, bytedata.Length);

   3: requestStream.Close();

We are getting the request byte stream, then writing the POST data inside it.

Then, we just need to get the response (action that will perform the request we just configured before).

   1: HttpWebResponse httpWebResponse =

   2: (HttpWebResponse)httpRequest.GetResponse();

We’re done.

After this, we may want to get the response text to do some actions with it

   1: Stream responseStream = httpWebResponse.GetResponseStream();

   2:  

   3: StringBuilder sb = new StringBuilder();

   4:  

   5: using (StreamReader reader =

   6: new StreamReader(responseStream, System.Text.Encoding.UTF8))

   7: {

   8:     string line;

   9:     while ((line = reader.ReadLine()) != null)

  10:     {

  11:         sb.Append(line);

  12:     }

  13: }

  14:  

  15: //use sb.ToString() to get the final html

Get UNIX Time Stamp using .NET

Sometimes we could need to get the UNIX time stamp, as you may know, that number is the amount of seconds since 1-1-1970 until now.

You can get this number using javascript this way:

   1: //Dialog the UNIX time stamp

   2: alert( (new Date()).getTime() );

If you want to get the same number using .NET framework, you can do something like:

   1: TimeSpan t = (DateTime.Now - new DateTime(1970, 1, 1).ToLocalTime());

We’re simply subtracting dates, Now – 1/1/1970

Then, we can get the totalSeconds:

   1: int timestamp = (int)t.TotalSeconds;

But wait!, if you render this now.

   1: System.Diagnostics.Debug.Write(timestamp);

You´ll maybe obtain a floating point number, this is because DateTime.Now is a very precise object, but maybe we will not need that, so, we can do it like this:

   1: DateTime n = DateTime.Now;

   2: DateTime now = new DateTime(n.Year , n.Month , n.Day , n.Hour , n.Minute, n.Second );

   3: TimeSpan t = (now - new DateTime(1970, 1, 1).ToLocalTime());

   4: int timestamp = (int)t.TotalSeconds;

Do some test, and do it in the way it fit your needs.

Change Thickbox Title in runtime

The Quest.

You want to change the thickbox title, runtime or thought the ASP vb/c# code.

Mitigation

The thickbox title is stored inside a div ID TB_ajaxWindowTitle

Simply use Jquery to get your way to this DIV and change the value to the one you want:

   1: $("#TB_ajaxWindowTitle").val("New Title")

I request you to use jquery, because if you’re using thickbox, this means you already have jquery included in your page.

But if you do not want to use jquery, do this:

   1: window.document.getElementById("TB_ajaxWindowTitle").innerHTML = "New Title"

Doing that in Runtime with ASP.NET

Ok, first ask yourself “why” would you do that?. I Recommend you always (when possible) keep all your client code in the cient side, but if you have found a good reason, then, simply call the RegisterClientScriptBlock.

   1: ClientScript.RegisterClientScriptBlock(Me.GetType(), "scriptKey", "$("#TB_ajaxWindowTitle").val("New Title")", True)

This will work if you are in a ASPX page (inheriting directly or indirectly from System.Web.UI.Page).

If you are inside a component, call Me.ClientScript.Page.RegisterClientScriptBlock.

And yes, use Me.Page.getType() to fill the first parameter.

Open/Call thickbox without using the “class” attribute.

The Quest

Sometimes we want to open a Thickbox in a different fashion than a anchor-class-title combination. For example:

1. When the thickbox needs to be open concerning some logic.
2. When you want to open a thickbox immediately after your web document is loaded.
3. Any other case in which you need to have more control.

Mitigation

Use the tb_show JavaScript function.

This function is defined inside the thickbox.js file (or thickbox-compressed.js).

   1: tb_show('Title String','URL&ThickboxVars',imageGroup)

This is an example:

   1: <a onclick="tb_show('Clients','options/clients.aspx?KeepThis=trueTB_iframe=true&height=430&width=600')"

   2:                                             href="#" title="Clients">Manage Clients</a>

Title String

This is just the title you want to appear in the top of the thickbox (as long the thickbox is not modal).

Url&ThickboxVars

This is the URL you want to be loaded inside the thickbox.

Just in case, lets explain that a little more:

• options/clients.aspx: This is the URL, nothing strange here, right?
• KeepThis=true: This is just a variable we want to pass to the page using the GET protocol (QueryString).
• It is not necesary that you use KeepThis var in al your thickbox calls. I mean, this is perfectly valid:

   1: <a onclick="tb_show('Clients','options/clients.aspx?otherValue=trueTB_iframe=true&height=430&width=600')"

   2:                                             href="#" title="Clients">Manage Clients</a>

• TB_iframe=true&height=430&width=600: This string is used by the thickbox to know the dimensions it should be. And also, if the thickbox is going to be inline or not. Also you can define if the thickbox is modal or not like this so:

   1: TB_iframe=true&height=430&width=600&modal=true

Some thing you’ve maybe noticed is that there´s not an & between the TB_iframe and the KeepThis=true strings.

This is not really necessary because  the thickbox splits this string and TB_Iframe and rest of the strings after that is not passed to the URL via GET protocol. (Yes, you wil not have any var “width” or “height” defined in the destination page).

If you add an & like this anything bad is going to happen though :

   1: KeepThis=true&TB_iframe=true&height=430&width=600&modal=true

But for your understanding, you are actually defining a new GET variable, an empty one.

Things you don’t want to do while using tb_show

 

Do not write any GET (query string) variable after the TB_iframe or TB_inline.

If you do this:

   1: TB_iframe=true&height=430&width=600&modal=true&MyVar=value

That variable MyVar will be ignored. Since all the Query String after TB_iframe will be cropped.

Be careful using character, like “, or ´ inside the title string, since this will brake the JS code.

This may be trivial, but for example, if you are using thickbox inside a GridView Item Template, and you use something like:

   1: <ItemTemplate>

   2:  <a class="link" onclick="tb_show('<%# Eval("articleName") %>','articleDetail.aspx?articleID=<%# Eval("artID") %>TB_iframe=true&height=599&width=1000');">

   3:      <%
   1: # DataBinder.Eval(Container, "DataItem.masterNumber")
%>
   4:  </a>
   5: </ItemTemplate>

If the articleName have “ or ‘, this will brake the thickbox JS code.

Escape the characters, or replace them.