How to use Stored Procedure for Searching data in databse from Asp.net Page

Article
2017-02-08

Question

_{Wednesday, February 8, 2017 11:52 AM}

Hi All,

Scenario-

when we give id in textbox and press the getbutton, data should be display in respected textbox.

i need to search data from the database using textbox ,for searching purpose i have a stored procedure

if data is exist then data should be display on form textbox like txtname, txtnumber,txtaddress.

if not it has to be display error.

how to use this stored procedure in web page.

can some one explain me.

Thanks & Regards,
Jyotsna

All replies (3)

_{Wednesday, February 8, 2017 12:17 PM}

There are alternatives like using ADO.NET, LinQ to SQL, Entity Framework that you can use to connect to your database and write code. Below code sample uses simple ADO.NET functions to connect to database, execute the Stored Procedure into a reader and show output in input controls.

ASPX Code:

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table>
                <tr>
                    <td>Search by Id:
                    </td>
                    <td>
                        <asp:TextBox runat="server" ID="txtId" />
                        <asp:Button Text="Search" ID="btnSearch" OnClick="btnSearch_Click" runat="server" />
                    </td>
                </tr>
                <tr>
                    <td>Name:
                    </td>
                    <td>
                        <asp:TextBox runat="server" ID="txtName" />
                    </td>
                </tr>
                <tr>
                    <td>Email Address:
                    </td>
                    <td>
                        <asp:TextBox runat="server" ID="txtEmailAddr" />
                    </td>
                </tr>
                <tr>
                    <td>Number:
                    </td>
                    <td>
                        <asp:TextBox runat="server" ID="txtNumber" />
                    </td>
                </tr>
                <tr>
                    <td colspan="2">
                        <asp:Label Text="" ID="lblMsg" runat="server" />
                    </td>
                </tr>
            </table>
        </div>
    </form>
</body>
</html>

C# Code Behind for this page:

protected void btnSearch_Click(object sender, EventArgs e)
{
    try
    {
        if (!string.IsNullOrEmpty(txtId.Text.Trim()))
        {
            using (SqlConnection sqlConn = new SqlConnection("data source=(local);initial catalog=myDb;user id=sa;pwd;123"))
            {
                using (SqlCommand sqlCmd = new SqlCommand("usp_SearchById", sqlConn))
                {
                    sqlConn.Open();
                    sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCmd.Parameters.AddWithValue("@Id", txtId.Text.Trim());

                    SqlDataReader reader = sqlCmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        txtName.Text = Convert.ToString(reader["tblColName"]);
                        txtNumber.Text = Convert.ToString(reader["tblColNumber"]);
                        txtEmailAddr.Text = Convert.ToString(reader["tblColEmailAddr"]);
                    }
                    else
                        lblMsg.Text = "No record found with Id: " + txtId.Text.Trim();
                    sqlConn.Close();
                }
            }
        }
    }
    catch (Exception)
    {

        throw;
    }
}

SP to call:

CREATE PROC usp_SearchById
    @Id BIGINT
AS
BEGIN
    SELECT
        tblColName, 
        tblColNumber, 
        tblColEmailAddr 
    FROM TABLE_1 
    WHERE tblColId = @Id
END

I would also suggest to look at some related articles on the same topic:

hope that helps./.

_{Wednesday, February 8, 2017 2:17 PM}

You asked "how to use this stored procedure in web page." Assuming you were speaking of ASP.Net WebPages (aka WebMatrix) and the Database helper (Your question was posted to the ASP.Net Web Pages forum), check out http://stackoverflow.com/questions/4715471/getting-the-results-from-a-stored-procedure-in-web-matrix

_{Thursday, February 9, 2017 2:15 AM}

Hi Jyotsna,

how to use this stored procedure in web page.

When you use ASP.NET Web Pages (Razor), try

@if (IsPost) {
  var LinkName = Request["LinkName"];
  var LinkURL = Request["LinkURL"];
  string SQL = "exec dbo.myprocname @0, @1";
  db.Execute(SQL, LinkName, LinkURL);
}

reference:

https://forums.asp.net/t/1649174.aspx

/en-us/aspnet/web-pages/overview/getting-started/introducing-aspnet-web-pages-2/displaying-data

Best Regards,

Chris

Share via

How to use Stored Procedure for Searching data in databse from Asp.net Page

Question

All replies (3)

Additional resources