Basic

Thursday, 5 December 2013

Execute another server's SP - SQL

SELECT *
FROM    OPENROWSET( 'SQLOLEDB',
                    'Server=10.10.1.89\SQLEXPRESSR2;UID=sa;PWD=password',
                    'SET FMTONLY OFF;SET NOCOUNT ON; exec BASE.dbo.GetPWD @UID=''21'''
                  )

here we will execute GetPWD sp.
The GetPWD sp available in BASE database.
The BASE database available in 10.10.1.89\SQLEXPRESSR2 Machine.

--------------------------------------------------------------------------------------------

select * into temrp from STR_Item

here str_item table have 100 rows
while execute of this line table temrp will create as per str_item table structure and fill all the selected rows.

Tuesday, 3 December 2013

Pass multiple control ids to javascript function as argument

<asp:CheckBox ID="chkCritical" runat="server"/>
                                
<asp:TextBox ID="txtMinOrderQty" runat="server" ></asp:TextBox>
                                
<asp:TextBox ID="txtOrderingLevel" runat="server"></asp:TextBox>

JavaScript Function

 function disp(chk, txt1, txt2) {
        
        var txtMin = document.getElementById(txt1)
        var txtLevel = document.getElementById(txt2);
        if (chk.checked) {

            txtMin.disabled = false;
            txtLevel.disabled = false;
            txtMin.value = "true";
            txtLevel.value = "0";
            txtMin.style.background = "#FFFFFF";
            txtLevel.style.background = "#FFFFFF";
        }
        else {
            txtMin.disabled = true;
            txtLevel.disabled = true;
            txtMin.value = "FALSE";
            txtLevel.value = "0";
            txtMin.style.background = "#D8D8D8";
            txtLevel.style.background = "#D8D8D8";
        }
    }

in code behind

 protected void Page_Load(object sender, EventArgs e)

    {

        chkCritical.Attributes.Add("onclick", "disp(this,'" + 
txtMinOrderQty.ClientID + "','" + txtOrderingLevel.ClientID + "')");

    }

// For reference

var cbs = document.getElementsByTagName('input');
        for (var i = 0; i < cbs.length; i++) {
            if (cbs[i].type == 'checkbox' && cbs[i].name.indexOf("chkCritical") != -1) {
                if (cbs[i].checked == true) {

// Further actions

Saturday, 30 November 2013

comma separated string of selected values in mysql

SELECT GROUP_CONCAT(id)  FROM table_level where parent_id=4 group by parent_id;

Reference from

Tuesday, 26 November 2013

select text in textbox on focus in asp.net

<!DOCTYPE html>
<html>
<body>

<form action="demo_form.asp">
First name: <input type="text" name="FirstName" onfocus="return this.select();" value="Mickey"><br>
Last name: <input type="text" name="LastName" value="Mouse"><br>

</form>

</body>
</html>

Thursday, 17 October 2013

concat array string with comma

string[] ids = {"2343","2344","2345"};

        string idString = String.Join(",",ids);

        Response.Write(idString);

ref developerfusion.com

Remove duplicates from array

int[] s = { 1, 2, 3, 3, 4};
int[] q = s.Distinct().ToArray();

ref: http://stackoverflow.com/questions/9673/remove-duplicates-from-array

Tuesday, 8 October 2013

Crystal Report 8.5 with SQL Temp Table(no rowset was returned for this table, query, or procedure)

We have a stored procedure (SQL Server 2005) for a crystal reports report (8.0).
Part of this stored procedure is a fetch which combines all kind of data and puts it into a temporary table.
Then a selection is made from this temp table (usually everything is selected) and this selection is the result of the SP.

The SP works fine when called in SQL Server.
However when I want to use this SP in a CR report, it won't work.
When trying to put it into the report, it gives the message: "no rowset was returned for this table, query, or procedure."

According to one of my colleagues, this is because CR doesn't like temp tables.

However, when I leave out the temp table stuff, I get the results from the fetch, which are a dozen separate results.
When I try putting this in a CR report, it only takes the first result.

Is there a way to combine the separate results from the fetch into a single recordset, without using a temp table?

SOLUTION:

SET NOCOUNT ON  for your temp table.

ref: http://galahtech.stinkbugonline.com/forum/index.php?topic=12967.0