Stored Procedures - Output Parameters & Return Values

Besides using input parameters, stored procedures can also return OUTPUT parameters and return values. Output parameters behave similarly to input parameters, but have to be declared with the OUTPUT keyword. In addition, you should specify the OUTPUT keyword when executing a stored procedure containing the output parameter to get the value. The following procedure contains an input parameter of the title type and an output parameter of total quantity of titles sold for the specified type:
1.
CREATE PROC sales_for_type @type VARCHAR(55), @total_sales INT OUTPUT
2.
AS
3.
SELECT SUM(qty) FROM sales a, titles b
4.
WHERE
5.
a.title_id = b.title_id
6.
and
7.
b.type = @type


This procedure can be executed as follows:
1.
DECLARE @total_sales_business int
2.
EXEC sales_for_type business, @total_sales=@total_sales_business OUTPUT


Results:
1.
-----------
2.
90


Notice that in order to use the output parameter, we have to declare a variable with the same data type as the output parameter of the called stored procedure. We can easily extend the same procedure to return more than one output parameters:
1.
ALTER PROC sales_for_type @type VARCHAR(55), @total_sales INT OUTPUT, @avg_sales INT OUTPUT
2.
AS
3.
SELECT SUM(qty), AVG(qty) FROM sales a, titles b
4.
WHERE
5.
a.title_id = b.title_id
6.
and
7.
b.type = @type


Now, we can execute the new procedure as follows:
1.
DECLARE @total_sales_business INT, @avg_sales_business INT
2.
EXEC sales_for_type business, @total_sales=@total_sales_business OUTPUT,
3.
@avg_sales = @avg_sales_business OUTPUT


Results:
1.
----------- -----------
2.
90 18


You can execute the stored procedure with an output parameter without the OUTPUT keyword, but you won't be able to use the returned value in the calling program.
Return values can be used within stored procedures to provide the stored procedure execution status to the calling program. The return values -99 through 0 are reserved for SQL Server internal use. You can create your own parameters that can be passed back to the calling program. By default, the successful execution of a stored procedure (or any group of SQL statements) will return 0. The syntax of the return command is:
1.
RETURN integer_value


You can check the result of executing a stored procedures with return values as follows:
1.
EXEC @return_variable = stored_procedure_name


where @return_variable is a numeric variable used to check the return value.
You can optionally enclose the integer value in parenthesis. If you don't supply the integer value, SQL Server will provide a value for you, depending on the state of program execution. RETURN also unconditionally exits the program, so once a RETURN is encountered in your T-SQL code SQL Server will not check any other conditions.
The following example demonstrates usage of user-defined return codes; notice that even though multiple conditions are examined within the procedure, a single RETURN will cause the program to stop and return the appropriate value:


01.
ALTER PROC sales_for_type @type VARCHAR(55), @total_sales INT OUTPUT, @avg_sales INT OUTPUT
02.
AS
03.
IF @type IS NULL
04.
BEGIN PRINT 'type is required' RETURN (1)
05.
END
06.
SELECT @total_sales=SUM(qty), @avg_sales = AVG(qty) FROM sales a, titles b
07.
WHERE
08.
a.title_id = b.title_id
09.
and
10.
b.type = @type
11.
IF @total_sales IS NULL
12.
AND @avg_sales IS NULL
13.
BEGIN
14.
RETURN (3) -- both avg and sum are null
15.
END
16.
IF @avg_sales IS NULL
17.
BEGIN
18.
RETURN (1) -- avg is null
19.
END
20.
IF @total_sales IS NULL
21.
BEGIN
22.
RETURN (2) -- total is null
23.
END


Now, we can execute the procedure with intentionally wrong values to check how the RETURN statement works:


1.
DECLARE @total_sales_business INT, @avg_sales_business INT, @return_status INT
2.
EXEC @return_status = sales_for_type tomato, @total_sales=@total_sales_business OUTPUT,
3.
@avg_sales = @avg_sales_business OUTPUT
4.
SELECT @return_status


Result:


1.
-----------
2.
3
3.
DECLARE @total_sales_business INT, @avg_sales_business INT, @return_status INT
4.
EXEC @return_status = sales_for_type NULL, @total_sales=@total_sales_business OUTPUT,
5.
@avg_sales = @avg_sales_business OUTPUT
6.
SELECT @return_status


Result:


1.
type is required
2.
-----------
3.
1

How to Create Wsdl from the Asmx or Svc files

Here is the code

wsdl /out:TempConversion.cs http://localhost/TempConversion/TempConversion.asmx

file path: C:\Program Files\Microsoft Visual Studio 9.0\VC

How to Call C# function using json

Here is the article about how to call asp.net function through javascript
<%@ Page Language="C#" AutoEventWireup="true" %>


<%@ Import Namespace="System.Collections.ObjectModel" %>
<%@ Import Namespace="System.Web.Services" %>


<script runat="server">
    [WebMethod]
    public static Collection<Location> FillDropDownList(int myValue1)
    {
        //use myValue1 to fill data
        var locations = new Collection<Location>
                                                 {
                                                     new Location {CountryID = 0, CountryName = "Please Select"},
                                                     new Location {CountryID = 1, CountryName = "Country1"},
                                                     new Location {CountryID = 2, CountryName = "Country2"},
                                                     new Location {CountryID = 3, CountryName = "Country3"},
                                                     new Location {CountryID = 4, CountryName = "Country4"},
                                                     new Location {CountryID = 5, CountryName = "Country5"}
                                                 };
        return locations;
    }
    public class Location
    {
        public int CountryID { get; set; }
        public string CountryName { get; set; }
    }
    
</script>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>


    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>


    <script type="text/javascript">
        $(function() {


            $('#<%= FillDropDownListButton.ClientID %>').click(function() {
                doAjaxCall('Default.aspx/FillDropDownList');
                return false;
            });
            function doAjaxCall(url, data) {
                var param1 = 1;
                $.ajax({
                    type: 'POST',
                    url: url,
                    data: '{myValue1: ' + param1 + '}',
                    contentType: 'application/json; charset=utf-8',
                    dataType: 'json',
                    success: successHandler
                });
            }


            function successHandler(response) {
                var myDropDownList = $('#<%= MyDropDownList.ClientID %>');
                myDropDownList.find('options').remove();
                var data = response.d;
                var doc = $('<div></div>');
                for (var i = 0; i < data.length; i++) {
                    doc.append($('<option></option>').
                            attr('value', data[i].CountryID).text(data[i].CountryName)
                            );
                }
                myDropDownList.append(doc.html());
                doc.remove();
            }


        });
    </script>


</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="MyDropDownList" runat="server">
        </asp:DropDownList>
        <asp:Button ID="FillDropDownListButton" runat="server" Text="Fill DropDownList" />
    </div>
    </form>
</body>
</html>

How To Find Control in gridview on RowCommand event in asp.net

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)


    {


        if (e.Row.RowType == DataControlRowType.DataRow)


        {


            Label lblPrice = (Label)e.Row.FindControl("lblPrice");


            Label lblUnitsInStock = (Label)e.Row.FindControl("lblUnitsInStock");






            decimal price = Decimal.Parse(lblPrice.Text);


            decimal stock = Decimal.Parse(lblUnitsInStock.Text);






            totalPrice += price;


            totalStock += stock;






            totalItems += 1;


        }






        if (e.Row.RowType == DataControlRowType.Footer)


        {


            Label lblTotalPrice = (Label)e.Row.FindControl("lblTotalPrice");


            Label lblTotalUnitsInStock = (Label)e.Row.FindControl("lblTotalUnitsInStock");






            lblTotalPrice.Text = totalPrice.ToString();


            lblTotalUnitsInStock.Text = totalStock.ToString();






            lblAveragePrice.Text = (totalPrice / totalItems).ToString("F");


        }


    }

How to find control on GridView RowCommand Event?

Today We Will Learn 
Q1.How to find a control within a gridview to get some data of that control.
<Columns>
 <asp:TemplateField HeaderText="Team">
    <ItemTemplate>
     <asp:Label ID="lblTeam" runat="server" Text='<%# Eval("TeamName")%>'></asp:Label>
   </ItemTemplate>
 </asp:TemplateField>
</Columns> 
<Columns>
 <asp:TemplateField>
    <ItemTemplate>
       <asp:LinkButton ID="lnkEdit" Text="EDIT" runat="server" CssClass="coachEdit"   CommandArgument='<%# Eval("TeamId")%>' CommandName="Redirect"></asp:LinkButton>
   </ItemTemplate>
 </asp:TemplateField>
</Columns>




On the Row_command Use this Code to get the value of the control


GridViewRow row = (GridViewRow)(((LinkButton)e.CommandSource).NamingContainer);
 Label lblDate = (Label)row.Cells[0].FindControl("lblDate");
string teamName= lblDate.Text; 
int rowindex = row.RowIndex;

The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine.

The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine.


I recently got an error when trying to use Microsoft.ACE.OLEDB.12.0 to connect excel file.I am sure Microsoft.ACE.OLEDB.12.0 is registered on the local machine,but I keep getting on run time error like this.


I got this error in the different machine... Have a Look

After quite long time goolgling,I found out that it is because I am running 64-bit windows and there are no MS Access drivers that run 64 bit.So to resolve it you need to change the build configuration to x86 found in the programs properties.


To do so,right click on the project and click Properties

Then click on Build and change the Platform target from Any CPU to x86.Recompile your program , it works like charm.

DataGridView in Windows Forms – Tips, Tricks and Frequently Asked Questions(FAQ)

DataGridView in Windows Forms – Tips, Tricks and Frequently Asked Questions(FAQ)

DataGridView control is a Windows Forms control that gives you the ability to customize and edit tabular data. It gives you number of properties, methods and events to customize its appearance and behavior. In this article, we will discuss some frequently asked questions and their solutions. These questions have been collected from a variety of sources including some newsgroups, MSDN site and a few, answered by me at the MSDN forums.

Tip 1 – Populating a DataGridView

In this short snippet, we will populate a DataGridView using the LoadData() method. This method uses the SqlDataAdapter to populate a DataSet. The table ‘Orders’ in the DataSet is then bound to the BindingSource component which gives us the flexibility to choose/modify the data location.

C#

public partial class Form1 : Form

    {

        private SqlDataAdapter da;

        private SqlConnection conn;

        BindingSource bsource = new BindingSource();

        DataSet ds = null;

        string sql;

        public Form1()

        {

            InitializeComponent();

        }

        private void btnLoad_Click(object sender, EventArgs e)

        {

            LoadData();

        }

        private void LoadData()

        {

string connectionString = "Data Source=localhost;Initial Catalog=Northwind;" +"Integrated Security=SSPI;";

            conn = new SqlConnection(connectionString);

sql = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, Freight," + "ShipName, ShipCountry FROM Orders";

            da = new SqlDataAdapter(sql, conn);

            conn.Open();

            ds = new DataSet();

            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(da);          

            da.Fill(ds, "Orders");

            bsource.DataSource = ds.Tables["Orders"];

            dgv.DataSource = bsource;          

        }

    }

VB.NET

Public Partial Class Form1

      Inherits Form

            Private da As SqlDataAdapter

            Private conn As SqlConnection

            Private bsource As BindingSource = New BindingSource()

            Private ds As DataSet = Nothing

            Private sql As String

            Public Sub New()

                  InitializeComponent()

            End Sub

Private Sub btnLoad_Click(ByVal sender As Object, ByVal e As EventArgs)

                  LoadData()

            End Sub

            Private Sub LoadData()

Dim connectionString As String = "Data Source=localhost;Initial Catalog=Northwind;" & "Integrated Security=SSPI;"

                  conn = New SqlConnection(connectionString)

sql = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, Freight," & "ShipName, ShipCountry FROM Orders"

                  da = New SqlDataAdapter(sql, conn)

                  conn.Open()

                  ds = New DataSet()

Dim commandBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)

                  da.Fill(ds, "Orders")

                  bsource.DataSource = ds.Tables("Orders")

                  dgv.DataSource = bsource

            End Sub

End Class

Tip 2 – Update the data in the DataGridView and save changes in the database

After editing the data in the cells, if you would like to update the changes permanently in the database, use the following code:

C#

        private void btnUpdate_Click(object sender, EventArgs e)

        {

            DataTable dt = ds.Tables["Orders"];

           this.dgv.BindingContext[dt].EndCurrentEdit();

            this.da.Update(dt);

        }

VB.NET

Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As EventArgs)

                  Dim dt As DataTable = ds.Tables("Orders")

                  Me.dgv.BindingContext(dt).EndCurrentEdit()

                  Me.da.Update(dt)

      End Sub

Tip 3 – Display a confirmation box before deleting a row in the DataGridView

Handle the UserDeletingRow event to display a confirmation box to the user. If the user confirms the deletion, delete the row. If the user clicks cancel, set e.cancel = true which cancels the row deletion.

C#

private void dgv_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)

        {

            if (!e.Row.IsNewRow)

            {

                DialogResult res = MessageBox.Show("Are you sure you want to delete this row?","Delete confirmation",

                         MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                if (res == DialogResult.No)

                    e.Cancel = true;

            }

        }

VB.NET

Private Sub dgv_UserDeletingRow(ByVal sender As Object, ByVal e As DataGridViewRowCancelEventArgs)

                  If (Not e.Row.IsNewRow) Then

                        Dim res As DialogResult = MessageBox.Show("Are you sure you want to delete this row?", "Delete confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

                        If res = DialogResult.No Then

                              e.Cancel = True

                        End If

                  End If

End Sub

Tip 4 – How to autoresize column width in the DataGridView

The snippet shown below, first auto-resizes the columns to fit its content. Then the AutoSizeColumnsMode is set to the ‘DataGridViewAutoSizeColumnsMode.AllCells’ enumeration value which automatically adjust the widths of the columns when the data changes.

C#

private void btnResize_Click(object sender, EventArgs e)

        {

            dgv.AutoResizeColumns();

            dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

        }

VB.NET

Private Sub btnResize_Click(ByVal sender As Object, ByVal e As EventArgs)

                  dgv.AutoResizeColumns()

                  dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells

End Sub

Tip 5 - Select and Highlight an entire row in DataGridView

C#

int rowToBeSelected = 3; // third row

if (dgv.Rows.Count >= rowToBeSelected)

{             

       // Since index is zero based, you have to subtract 1

        dgv.Rows[rowToBeSelected - 1].Selected = true;

}

VB.NET

Dim rowToBeSelected As Integer = 3 ' third row

If dgv.Rows.Count >= rowToBeSelected Then

         ' Since index is zero based, you have to subtract 1

            dgv.Rows(rowToBeSelected - 1).Selected = True

End If

Tip 6 - How to scroll programmatically to a row in the DataGridView

The DataGridView has a property called FirstDisplayedScrollingRowIndex that can be used in order to scroll to a row programmatically.

C#

int jumpToRow = 20;

if (dgv.Rows.Count >= jumpToRow && jumpToRow >= 1)

{             

        dgv.FirstDisplayedScrollingRowIndex = jumpToRow;

        dgv.Rows[jumpToRow].Selected = true;

}

VB.NET

Dim jumpToRow As Integer = 20

If dgv.Rows.Count >= jumpToRow AndAlso jumpToRow >= 1 Then

            dgv.FirstDisplayedScrollingRowIndex = jumpToRow

            dgv.Rows(jumpToRow).Selected = True

End If

Tip 7 - Calculate a column total in the DataGridView and display in a textbox

A common requirement is to calculate the total of a currency field and display it in a textbox. In the snippet below, we will be calculating the total of the ‘Freight’ field. We will then display the data in a textbox by formatting the result (observe the ToString("c")) while displaying the data, which displays the culture-specific currency.

C#

private void btnTotal_Click(object sender, EventArgs e)

        {

            if(dgv.Rows.Count > 0)

             txtTotal.Text = Total().ToString("c");

        }

        private double Total()

        {

            double tot = 0;

            int i = 0;

            for (i = 0; i < dgv.Rows.Count; i++)

            {

                tot = tot + Convert.ToDouble(dgv.Rows[i].Cells["Freight"].Value);

            }

            return tot;

        }

VB.NET

Private Sub btnTotal_Click(ByVal sender As Object, ByVal e As EventArgs)

                  If dgv.Rows.Count > 0 Then

                   txtTotal.Text = Total().ToString("c")

                  End If

End Sub

Private Function Total() As Double

                  Dim tot As Double = 0

                  Dim i As Integer = 0

                  For i = 0 To dgv.Rows.Count - 1

                        tot = tot + Convert.ToDouble(dgv.Rows(i).Cells("Freight").Value)

                  Next i

                  Return tot

End Function

Tip 8 - Change the Header Names in the DataGridView

If the columns being retrieved from the database do not have meaningful names, we always have the option of changing the header names as shown in this snippet:

C#

private void btnChange_Click(object sender, EventArgs e)

        {

            dgv.Columns[0].HeaderText = "MyHeader1";

            dgv.Columns[1].HeaderText = "MyHeader2";

        }

VB.NET

Private Sub btnChange_Click(ByVal sender As Object, ByVal e As EventArgs)

                  dgv.Columns(0).HeaderText = "MyHeader1"

                  dgv.Columns(1).HeaderText = "MyHeader2"

End Sub

Tip 9 - Change the Color of Cells, Rows and Border in the DataGridView

C#

private void btnCellRow_Click(object sender, EventArgs e)

        {

            // Change ForeColor of each Cell

            this.dgv.DefaultCellStyle.ForeColor = Color.Coral;

            // Change back color of each row

            this.dgv.RowsDefaultCellStyle.BackColor = Color.AliceBlue;

            // Change GridLine Color

            this.dgv.GridColor = Color.Blue;

            // Change Grid Border Style

            this.dgv.BorderStyle = BorderStyle.Fixed3D;

        }

VB.NET

Private Sub btnCellRow_Click(ByVal sender As Object, ByVal e As EventArgs)

                  ' Change ForeColor of each Cell

                  Me.dgv.DefaultCellStyle.ForeColor = Color.Coral

                  ' Change back color of each row

                  Me.dgv.RowsDefaultCellStyle.BackColor = Color.AliceBlue

                  ' Change GridLine Color

                  Me.dgv.GridColor = Color.Blue

                  ' Change Grid Border Style

                  Me.dgv.BorderStyle = BorderStyle.Fixed3D

End Sub

Tip 10 - Hide a Column in the DataGridView

If you would like to hide a column based on a certain condition, here’s a snippet for that.

C#

private void btnHide_Click(object sender, EventArgs e)

        {

            this.dgv.Columns["EmployeeID"].Visible = false;

        }

VB.NET

Private Sub btnHide_Click(ByVal sender As Object, ByVal e As EventArgs)

                  Me.dgv.Columns("EmployeeID").Visible = False

End Sub

Tip 11 - Handle SelectedIndexChanged of a ComboBox in the DataGridView

To handle the SelectedIndexChanged event of a DataGridViewComboBox, you need to use the DataGridView.EditingControlShowing event as shown below. You can then retrieve the selected index or the selected text of the combobox.

C#

private void dataGridView1_EditingControlShowing(object sender,DataGridViewEditingControlShowingEventArgs e)

        {

            ComboBox editingComboBox = (ComboBox)e.Control;

            if(editingComboBox != null)

                editingComboBox.SelectedIndexChanged += newSystem.EventHandler(this.editingComboBox_SelectedIndexChanged);

        }

private void editingComboBox_SelectedIndexChanged(object sender, System.EventArgs e)

        {

            ComboBox comboBox1 = (ComboBox)sender;

            // Display index

            MessageBox.Show(comboBox1.SelectedIndex.ToString());

            // Display value

            MessageBox.Show(comboBox1.Text);

        }

VB.NET

Private Sub dataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e AsDataGridViewEditingControlShowingEventArgs)

                  Dim editingComboBox As ComboBox = CType(e.Control, ComboBox)

                  If Not editingComboBox Is Nothing Then

                        AddHandler editingComboBox.SelectedIndexChanged, AddressOfeditingComboBox_SelectedIndexChanged

                  End If

End Sub

Private Sub editingComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e AsSystem.EventArgs)

                  Dim comboBox1 As ComboBox = CType(sender, ComboBox)

                  ' Display index

                  MessageBox.Show(comboBox1.SelectedIndex.ToString())

                  ' Display value

                  MessageBox.Show(comboBox1.Text)

End Sub

Tip 12 - Change Color of Alternate Rows in the DataGridView

C#

private void btnAlternate_Click(object sender, EventArgs e)

        {

            this.dgv.RowsDefaultCellStyle.BackColor = Color.White;

            this.dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.Aquamarine;

        }

VB.NET

Private Sub btnAlternate_Click(ByVal sender As Object, ByVal e As EventArgs)

                  Me.dgv.RowsDefaultCellStyle.BackColor = Color.White

                  Me.dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.Aquamarine

End Sub

Tip 13 - Formatting Data in the DataGridView

The DataGridView exposes properties that enable you to format data such as displaying a currency column in the culture specific currency or displaying nulls in a desired format and so on.

C#

private void btnFormat_Click(object sender, EventArgs e)

        {

            // display currency in culture-specific currency for

            this.dgv.Columns["Freight"].DefaultCellStyle.Format = "c";

            // display nulls as 'NA'

            this.dgv.DefaultCellStyle.NullValue = "NA";

        }

VB.NET

Private Sub btnFormat_Click(ByVal sender As Object, ByVal e As EventArgs)

                  ' display currency in culture-specific currency for

                  Me.dgv.Columns("Freight").DefaultCellStyle.Format = "c"

                  ' display nulls as 'NA'

                  Me.dgv.DefaultCellStyle.NullValue = "NA"

End Sub

Tip 14 – Change the order of columns in the DataGridView

In order to change the order of columns, just set the DisplayIndex property of the DataGridView to the desired value. Remember that the index is zero based.

C#

private void btnReorder_Click(object sender, EventArgs e)

        {

             dgv.Columns["CustomerID"].DisplayIndex = 5;

             dgv.Columns["OrderID"].DisplayIndex = 3;

             dgv.Columns["EmployeeID"].DisplayIndex = 1;

             dgv.Columns["OrderDate"].DisplayIndex = 2;

             dgv.Columns["Freight"].DisplayIndex = 6;

             dgv.Columns["ShipCountry"].DisplayIndex = 0;

             dgv.Columns["ShipName"].DisplayIndex = 4;

        }

VB.NET

Private Sub btnReorder_Click(ByVal sender As Object, ByVal e As EventArgs)

                   dgv.Columns("CustomerID").DisplayIndex = 5

                   dgv.Columns("OrderID").DisplayIndex = 3

                   dgv.Columns("EmployeeID").DisplayIndex = 1

                   dgv.Columns("OrderDate").DisplayIndex = 2

                   dgv.Columns("Freight").DisplayIndex = 6

                   dgv.Columns("ShipCountry").DisplayIndex = 0

                   dgv.Columns("ShipName").DisplayIndex = 4

End Sub

I hope this article was useful and I thank you for viewing it.