Question Issue with dataset fill

[Pavanm]

Hi,

The issue may look very simple. I have a small piece of code which fills dataset by getting the data using storedproc. Here goes the code.

 Dim sqlAdp As SqlDataAdapter
        Dim com As New SqlCommand
        Dim ds As New DataSet
        sqlAdp = New SqlDataAdapter()
        Dim sqlselectcom As New SqlClient.SqlCommand
        sqlselectcom.Connection = Me.SqlConnection1
        Try
            sqlselectcom.Connection = Me.SqlConnection
            sqlselectcom.CommandText = "Get_Emp_Details"
            sqlselectcom.CommandType = CommandType.StoredProcedure
            sqlselectcom.Parameters.Add("@EmpID", SqlDbType.Int)
            sqlselectcom.Parameters("@EmpID").Value = empId




            sqlselectcom.Parameters.Add("@EmpStatus", SqlDbType.Bit)
            sqlselectcom.Parameters("@EmpStatus").Value = 1
            sqlAdp.SelectCommand = sqlselectcom
            sqlAdp.Fill(ds) 
            k = ds.Tables(0).Rows.Count

I have two empId ex : 1001 and 1002. When I execute the storedproc in sql server both the empId's returning 1 record. But when I tried to execute the above code from VS for the first empId the rows.count=1 but for the second empId it shows rows.count = 0.


Not sure why is this happening even though data exists in table and storedproc returns the record.

Can anyone help me in understanding what might be going wrong here.

Thanks in advance,
Pavan

 

[jmcilhinney]

Firstly, let's clean up that code a bit. It's way more verbose than is required. For one thing, you're creating two commands and only using one. For another, you're setting the Connection of the command you do use twice. You're also using a DataSet when you only use one DataTable and you're using multiple lines to do things that can be more succinctly done on one. Here's how I'd achieve that:

Dim adapter As New SqlDataAdapter("Get_Emp_Details", Me.SqlConnection)

With adapter.SelectCommand
    .CommandType = CommandType.StoredProcedure
    .Parameters.AddWithValue("@EmpID", empId)
    .Parameters.Add("@EmpStatus", SqlDbType.Bit).Value = 1
End With

Dim table As New DataTable

Try
    Dim rowCount = adapter.Fill(table)

Now, are you saying that when 'empId' is 1001, 'rowCount' is 1 while when 'empId' is 1002, 'rowCount' is 0?