TSQL get records of last week

Today I’ve received this request:
realyze a stored proc that return all records from monday of the previus week to today.

So If we run this stored proc on monday we will get the last 7 days but if we run this on friday, we get 11 days from monday of the previus week.

declare @DateFromTarget datetime
declare @DateToTarget datetime

	declare @TodayNumber integer
	declare @DateFromTmp DateTime

	set @TodayNumber = DATEPART(weekday, GETDATE())
	-- Tolgo i giorni necessari per raggiungere il lunedi della settimana scorsa
	set @DateFromTmp = DATEADD(day, -@TodayNumber - 5, GETDATE())
	-- faccio il cast per mantenere solo la parte Date senza time
	set @DateFromTmp = CONVERT(varchar, @DateFromTmp, 112)

	set @DateFromTarget = @DateFromTmp


	set @DateToTarget = GETDATE()
	

SELECT        *
FROM            [TABLE]
WHERE @DateFromTarget < TimeStamp  AND TimeStamp < @DateToTarget 

Inserito in SQL | Etichette , , | Lascia un commento

How to use fancybox, save values in separte popup page – Step 2

Fancybox Series

  1. How to use fancybox, save values – Step 1
  2. How to use fancybox, save values in separte popup page – Step 2

In the previus article I showed how to use fancybox to open a popup that will display some html inside the current page.
How to use fancybox, save values – Step 1

I don’t like use fancybox in that way, because the result is a very caotic page.

The solution I’ll show now is realyze a separte page .aspx and display it inside the popup.
With this approuch the code necessary to fill the control with data will be move from the .cs of the main page to the separate .cs of the popup.
Also the html will be move to the .aspx page, so it’s more easy to design the popup page.

With this approach we have to write the necessary code to load the data on page_load like a usual page.

We can realyze this in fancybox by 2 mode:

  • default
  • iframe

Now I show how to use the default mode and the mode I suggest.

Here the exactly point to port the example of the previus article to a separate popup

1) code in the main .aspx page:

<script type="text/javascript">
    $(document).ready(function () {
        $('#contrattoLink').fancybox();
    }
</script>

<a id="contrattoLink" href="popupPage.aspx">Open Popup</a></div>

Notice that I removed all the html of the popup and now the href of link doesn’t point to an element id but to the page we have created before.
To initialize fancybox I used it like before on 3rd line

2) Create a new .aspx page “popupPage.aspx” and put this code:

<div id="funcyBox_Contratto" style="padding: 10px; margin: 20px;">
    <div>Select something</div>
    <div style="margin: 7px 0px 5px 0px;">
        <asp:ListBox ID="ListBoxContratti" runat="server"></asp:ListBox>
    </div>
    <div style="text-align: right;">
        <input type="button" id="BtnContratto_Save" value="Salva" onclick="return saveFancyBoxListBoxTmp('<%=ListBoxContratti.ClientID%>');"/>
        <input id="BtnContratto_Cancel" onclick="$.fancybox.close();" type="button" value="Cancel" />
    </div>
</div>

Obviously on the page load specify the code necessary to load the items

Put this code on the popupPage.aspx

function saveFancyBoxListBoxTmp(listBxId) {
    // Obtain the text of the option selected
    var descrSelected = $("#" + listBxId + " option:selected").text();
    // obtain the value of the option selected
    var valueSelected = $("#" + listBxId).val();
    
    saveFancyBoxListBox(valueSelected, descrSelected);
}

This code will be put in the popup page and it’s target is obtain the description and the value of oure listbox, combobox, or textbox.
If we want we can also avoid to pass the id as parameter and put it directly in code like this

function saveFancyBoxListBoxTmp() {
    // Obtain the text of the option selected
    var descrSelected = $("#<%=ListBoxContratti.ClientID%> option:selected").text();
    // obtain the value of the option selected
    var valueSelected = $("#<%=ListBoxContratti.ClientID%>").val();
    
    saveFancyBoxListBox(valueSelected, descrSelected);
}

...

<input type="button" id="BtnContratto_Save" value="Salva" onclick="return saveFancyBoxListBoxTmp();"/>

this is a your decision, I like this one because it’s more clean for me but there is no difference between the two solution.

Now we need to analyze: saveFancyBoxListBox

3) put the saveFancyBoxListBox on the main page (not the popup) defined as this:

function saveFancyBoxListBox(valueSelected , descrSelected ) {
    // Description set
    $("#<%=txtBoxContratto.ClientID%>").val(descrSelected);
    // Value set
    $("#<%=hiddenBoxContratto.ClientID%>").val(valueSelected);

    $.fancybox.close();
}

Like before we set the value on the right place.

That’s all code.
Now we can try to analyze it more in detail:
On the main page we initlyze a link as fancybox to a separate aspx page.
Then we define the function saveFancyBoxListBox to store the value selected.
We can’t place it on the popup page because It can’t solve $(“#”) adn $(“#”).

Then we realyze the html of the popup and write the function to get the value to pass to parent windows.
Like before, we can’t do it on the main page because the main page don’t know $(“#”).

Using fancybox as default and not with iframe it’s possibile see all function and variable defined in the main page!!!
Other solution to store the result in the main page is thisone:

on MainPage.aspx

var txtBoxContrattoID;
var hiddenBoxContratto;
$.ready(
   function()
   {
       txtBoxContrattoID = $("#<%=txtBoxContratto.ClientID%>");
       hiddenBoxContratto = $("#<%=hiddenBoxContratto.ClientID%>");
   }
);

on PopupPage.aspx

function saveFancyBoxListBoxTmp() {
    // Obtain the text of the option selected
    var descrSelected = $("#<%=ListBoxContratti.ClientID%> option:selected").text();
    // obtain the value of the option selected
    var valueSelected = $("#<%=ListBoxContratti.ClientID%>").val();
    
    // use the global variable containing the right Id
    // Description set
    $("#txtBoxContrattoID").val(descrSelected);
    // Value set
    $("#hiddenBoxContratto").val(valueSelected);
}

This solution permit to write all the js code to save the values in the popup and make the main page clean.
I don’t like this because it introduces many global variable, and in addition looking only in the code of the main page, is not possible understand who write the hidden field or the textbox.

Choose the solution you prefer.

In next post I’ll analyze how to use fancybox and iframe

Have a nice code

Inserito in Asp.Net, c#, jquery | Lascia un commento

SQL Obtain Date only from DateTime

Another little post:

To obtain only the date from the DateTime this is the simpliest method I foud:

declare @DateFromTmp DateTime
set @DateFromTmp = CONVERT(varchar, GETDATE(), 112)

Inserito in SQL | Lascia un commento

SQL Convert nvarchar to DateTime

Many times I need to convert a date stored in a nvarchar column with this format dd/MM/yyyy

CAST('17/05/1985' AS DateTime)
CONVERT(DateTime, '17/05/1985')

will generate error because it will expect the date in atomic format 1985-05-17

an easy way to convert it is this

CONVERT(DateTime, '17/05/1985', 103)

this work

very usefull in WHERE statement like this:

GETDATE() < CONVERT(DateTime, column1, 103)

Inserito in SQL | Etichette , , , | Lascia un commento

How to use fancybox, save values – Step 1

Fancybox Series

  1. How to use fancybox, save values – Step 1
  2. How to use fancybox, save values in separte popup page – Step 2

I use fancybox many times and I think it’s help is not so clear, so I preferred do a little series on how to use it

Fancybox is a javascript library similar to lightbox and many other library.
Fancybox is based to jquery, so is necessary to include jquery before.

It’s target is open a “popup”, it can be used to realize a picture gallery or to show some html part or a full html/aspx/php page.
In his site is possible to see how implement a simple gallery and how to configure the result popup

Usaully fancybox will be used to open a popup by click some link.
Here an example to that show how to display some html part in a popup and save some value to hidden field

popup show html part

<script type="text/javascript">
    $(document).ready(function () {
        $('#contrattoLink').fancybox();
    }
</script>
<div>
    <div style="display: none;">
        <div id="funcyBox_Contratto" style="padding: 10px; margin: 20px;">
            <div>Select something</div>
            <div style="margin: 7px 0px 5px 0px;">
                <asp:ListBox ID="ListBoxContratti" runat="server"></asp:ListBox>
            </div>
            <div style="text-align: right;">
                <input type="button" id="BtnContratto_Save" value="Salva" onclick="return saveFancyBoxListBox('<%=ListBoxContratti.ClientID%>', '<%=txtBoxContratto.ClientID%>', '<%=hiddenBoxContratto.ClientID%>');"/>
                <input id="BtnContratto_Cancel" onclick="$.fancybox.close();" type="button" value="Cancel" />
            </div>
        </div>
    </div>
</div>

<a id="contrattoLink" href="#funcyBox_Contratto">Open Popup</a></div>

In the previus code the important point are:
1) realyze a tag “<a>” with an id
2) at the document.ready obtain the object tab “<a>” and call .fancybox() method
3) Fancybox will display the value of href so fancybox will display the div with id=”funcyBox_Contratto”
Notice that the parent div of href value (“funcyBox_Contratto”) must be setted:

<div style="display:none;">

without this style, the div will be displayed in the page.

With this code the popup will be displayed, to save values to the parent page I realyzed a function called saveFancyBoxListBox.
This function need the id of the listbox that contain the values, the id of the parend textbox that will display the description and the id of hidden field where save the value selected.
Here the code:

function saveFancyBoxListBox(listBxId, txbxDestination, hiddenDestination) {
    // Obtain the text of the option selected
    var descrSelected = $("#" + listBxId + " option:selected").text();
    // obtain the value of the option selected
    var valueSelected = $("#" + listBxId).val();

    // Description set
    $("#" + txbxDestination).val(descrSelected);
    // Value set
    $("#" + hiddenDestination).val(valueSelected);

    $.fancybox.close();
}

Notice that in this function and in the previus “cancel button” the code to close fancybox is:

$.fancybox.close();

notice also that the button aren’t submit button or asp:Button

In this post I’ve explained how to open fancybox and disply some part of HTML, in addiction how to save some values and how to close fancybox by code.
I suggest to use this situation if the pase is short and not complex otherwise look the next post.

In the next post I’ll explain how use fancybox to open a .aspx page and how to save the values, it’s little different.

Inserito in Asp.Net, jquery, usability | Etichette , , , | Lascia un commento

Open Expand ComboBox or click a button from Code

In a WPF project I’had to open the ComboBox by pressing the near icon.

To solve this request I’ll use this code:

ComboBoxAutomationPeer peer = new ComboBoxAutomationPeer(myComboBox);
IExpandCollapseProvider provider = peer.GetPattern(PatternInterface.ExpandCollapse) as IExpandCollapseProvider;
provider.Expand();

In WPF and Silverlight there is a class AutomationPeer very usefull to simulate the user action on the control.
Usually the right class is named [Control]AutomationPeer

So for the combobox the right name is ComboBoxAutomationPeer and for a button ButtonAutomationPeer

If you need to press a button invoking it by code:

ButtonAutomationPeer peer = new ButtonAutomationPeer(myButton);
IInvokeProvider provider = peer.GetPattern(PatternInterface.Invoke) as IInvokeProvider;
provider.Invoke();

Inserito in c#, Silverlight, WP7 | Etichette , , , | Lascia un commento

Fancybox pass value to parent window using jquery or not

Some times when I work with fancybox I’ve to update some values to the parent Window.

Default.aspx

<script type="text/javascript">
    var btnSearchId;


    $(document).ready(function () {
        // set fancybox to the link
        $('#AddNumberoLink').fancybox();
        // save in a global variable the ClientID of the Asp.Net Button
        btnSearchId = '<%=BtnSearch.ClientID %>';
    });
</script>
<div>
    <div>
        <h2>Search</h2>
    </div>
    <asp:Panel runat="server" ID="PanelSearch" DefaultButton="BtnSearch">
        <div style="float:right">
            <a id="AddNumberoLink" href="AddEditNumero.aspx">New</a>
        </div>
        <table>
            <tr>
                <td>
                    Name: <asp:TextBox ID="TextBoxSearch" runat="server" />
                </td>
                <td>
                    <asp:Button ID="BtnSearch" runat="server" onclick="BtnSearch_Click" Text="Cerca" />
                </td>
            </tr>
        </table>
    </asp:Panel>
</div>

And this is the code of the page will be opened in fancybox by clicking on New link

AddEditNumero.aspx

<script type="text/javascript">
    function removeItem() {
        if (confirm("Procedere con l' eliminazione del numero selezionato?")) {
            $.ajax({
                type: "GET",
                dataType: "html",
                cache: false,
                url: "RubricaHandler.ashx?action=removeItem&IdNumero=" + $('#<%=HiddenIdNumero.ClientID %>').val(),
                beforeSend: function () { },
                complete: function () { },
                error: function (xhr, status, errorThrown) {
                    $("#operationResult").html(errorThrown + '\n' + status + '\n' + xhr.statusText);
                },
                success: function (result) {
                    disableUI();
                    $("#operationResult").html("Numero rimosso");
                    setTimeout("closeFancybox()", 1500);

                    window.parent.$("#" + btnSearchId).click();
                }
            });
        }
    }
</script>
<div>
...
    <asp:HiddenField runat="server" ID="HiddenIdNumero" />
    <a href="#" onclick="removeItem()">Remove</a>
</div>
<div id="operationResult">
</div>

By pressing the “Remove” link will be called removeItem() function, the function use ajax to call a GenericHandler and in the success callback I’ll update the div “operationResult”, I’ll close fancybox after 1500ms and I send the click to the search button present in the parent window.

Other thing to notice is that the button is an Asp.Net button so I don’t know the clientId of the control on the popup.
To have this we need to save on a global varibale and use it when it’s necessary.

Other scenario is when we need to open a popup and modify a complex value, then comeback to the edit page and save some information of the modifyed data to an hidden field of the parent window like this:

window.parent.$("#HiddenFieldState").val("MyValue");

if we don’t use jquery but the DOM the code will be

var myHidden = window.parent.document.getElementById('HiddenFieldState");

Inserito in .Net, Asp.Net, c#, jquery | Etichette , , , | Lascia un commento