CODEWITHSUNDEEP
asp.netc#-4.0
Dotnet ReSource
Dotnet ReSource

Reputation: 201

How to show database values on textboxes?

This is my stored procedure:

create procedure SP_ShowUse
   (@employeeName varchar (50))
as 
begin
     select 
         PTS_Employee.Emp_Username, PTS_Approval.Approval_ApprovedBY,  
         PTS_Branches.Branch_BranchName
    from 
         PTS_Employee, PTS_Approval, PTS_Branches
    where 
         PTS_Employee.Branch_BranchID = PTS_Branches.Branch_BranchID
         AND PTS_Employee.Approval_ApprovedID = PTS_Approval.Approval_ApprovedID
         AND PTS_Employee.Emp_Username = @employeeName
end

I want to display all the three values selected in 3 different textboxes

How can I do that ?

Upvotes: 1

Views: 8436

Answers (3)

SoftwareNerd
SoftwareNerd

Reputation: 1895

store the data in dataset and extract fromit...

      SqlConnection con = new SqlConnection(connstring);
        con.Open();
        SqlCommand mycomm=new SqlCommand ("SP_ShowUse",con);
        mycomm.CommandType=CommandType.StoredProcedure;
        mycomm.Parameters.Add("@employeeName", SqlDbType.VarChar).Value = "Anil";
        SqlDataAdapter showdata = new SqlDataAdapter(mycomm);
        DataSet ds = new DataSet();
        showdata.Fill(ds);
        txtEmployeename.Text = ds.Tables[0].Rows[0]["Emp_Username"].ToString();
        txtBranchName.Text = ds.Tables[0].Rows[0]["Branch_BranchName"].ToString();
        txtApprvdby.Text = ds.Tables[0].Rows[0]["Approval_ApprovedBY"].ToString();
        binddropdownlist();
        con.Close();

Upvotes: 2

marc_s
marc_s

Reputation: 754258

Going with the "oldest" solution - ADO.NET - you could write something like this:

// define a class to hold the data returned from the stored procedure
public class SPReturnData
{
    public string EmployeeUsername { get; set; }
    public string ApprovedBy { get; set; }
    public string BranchName { get; set; }
}

// define a method to call the stored proc and return the data 
public SPReturnData LoadData(string connectionString, string employeeName)
{
    // initialize the structure to be returned
    SPReturnData result = new SPReturnData();

    // setup ADO.NET connection and command
    using(SqlConnection conn = new SqlConnection(connectionString))
    using(SqlCommand cmd = new SqlCommand("dbo.SP_ShowUse", conn))
    {
       // it's a stored procedure
       cmd.CommandType = CommandType.StoredProcedure;

       // set up the command's parameters
       cmd.Parameters.Add("@EmployeeName", SqlDbType.VarChar, 50).Value = employeeName;

       // open connection, execute command, close connection
       conn.Open();

       // execute reader
       using(SqlDataReader rdr = cmd.ExecuteReader())
       {
          if(rdr.Read())
          {
              // first column -> employee user name
              result.EmployeeUserName = rdr.GetString(0);

              // second column -> approved by name
              result.ApprovedBy = rdr.GetString(1);

              // third column -> Branch name
              result.BranchName = rdr.GetString(2);
          }

          rdr.Close();
       }

       conn.Close();
    }

    return result;
}

And from your code - use something like this:

// pass in the connection string (e.g. get it from config or something)
// and the employee name - get back a "SPReturnData" object with the data items
SPReturnData data = LoadData("server=.;database=.....;", "Fred Flintstone");

// set your textboxes to the values returned    
txtEmployeeName.Text = data.EmployeeName;
txtApprovedBy.Text = data.ApprovedBy;
txtBranchName.Text = data.BranchName;

Upvotes: 2

Dor Cohen
Dor Cohen

Reputation: 17080

Do you fimiliar with one of the following?

  1. ADO.NET - http://msdn.microsoft.com/en-us/library/h43ks021(v=vs.100).aspx
  2. NHibernate - http://nhforge.org/Default.aspx
  3. EntityFramework - http://msdn.microsoft.com/en-us/library/bb399572.aspx

ADO.Net is the oldest and you can perform select commands in there, I recommend you to read and use one of the other 2 options.

Upvotes: 1

Related Questions