CODEWITHSUNDEEP
c#sqlwinformsstored-procedures
buddy
buddy

Reputation: 428

How to populate listbox through a parametrized stored procedure?

I have a Winforms app and an employeeListBox, departmentComboBox and some textboxes to show employee information e.g. fNameTextbox, lNameTextBox.....

I want to populate the employeelistBox through the departmentCombobox selected value and populate the textboxes from the employeeListBox. I have this stored procedure for selection of employees of a department

ALTER PROCEDURE [dbo].[selectEmployee] 
   @departID int
-- Add the parameters for the stored procedure here
AS
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

   -- Insert statements for procedure here
   declare @ErrorCode int



   BEGIN TRANSACTION
      if ( @ErrorCode = 0 )
      Begin
        SELECT 
            EmpID, firstname, lastName, dateOfBirth, Gender, contactNumber, maritalStatus, 
            emailAddress, resentAddress, permanentAddress, nationality, bloodGroup, 
            qualification, Skills, Experience, joiiningdate, probation, departmentID, 
            Salary, paymentMode, active 
        FROM Employee
        WHERE departmentID = @departID

set @ErrorCode = @@error
      End

      if ( @ErrorCode = 0 )
     COMMIT TRANSACTION
      else
         ROLLBACK TRANSACTION

     return @ErrorCode

and to populate the listbox I wrote this code

    private void selectEmployee(int departID)
    {
        string connString = BL.dbConn.ConnStr;
        DataSet ds = new System.Data.DataSet();
        SqlConnection conn = new SqlConnection(connString);
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "dbo.selectEmployee";
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        adapter.Fill(ds);
        listBox1.DataSource = ds.Tables[0].DefaultView;
        listBox1.ValueMember = "EmpID";
        listBox1.DisplayMember = "firstname";
        cmd.Parameters.Clear();
        conn.Close();
        conn.Dispose();
    }

I do not know how to pass the departmentid to the stored procedure second how to populate the textboxes from the listbox dataset?

Upvotes: 2

Views: 4841

Answers (2)

Pranay Rana
Pranay Rana

Reputation: 176936

for passing departmentid you need to create sql parameter and need to attache with the sql command will do the work for you

thing you forget in you code is da.SelectCommand = cmd;specify the select command 

        SqlConnection conn = new SqlConnection(connString);

        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();
        DataTable dt = new DataTable();
        try
        {
            conn.Open();
            cmd = new SqlCommand("dbo.selectEmployee", conn );
            cmd.Parameters.Add(new SqlParameter("@departID", value);
            cmd.CommandType = CommandType.StoredProcedure;
            da.SelectCommand = cmd;
            da.Fill(dt);
            dataGridView1.DataSource = dt;
        }
        catch (Exception x)
        {
            MessageBox.Show(x.GetBaseException().ToString(), "Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }

for populating value in textboxes can you please post the code or example what you want to do than i can help you further in that

Upvotes: 1

lkaradashkov
lkaradashkov

Reputation: 8899

You need to do the following in order to add the DepartmentId to the stored procedure:

cmd.Parameters.Add(new SqlParameter("@departID", departID));

Upvotes: 1

Related Questions