CODEWITHSUNDEEP
asp.netvb.net
nasy
nasy

Reputation: 11

database search function

i want to search a record from sql database searching by first name so im using a function in the data layer but it is not working please correct me where i went wrong here is my function: 

Public Function searchCustomer(ByVal custFname As String) As DataTable
            Dim tabletdata As New DataTable
            Dim conn As New SqlConnection(con_string)
            conn.Open()
            Dim dCmd As New SqlCommand("selectCustomerByFname", conn)
            dCmd.CommandType = CommandType.StoredProcedure
            Try
                dCmd.Parameters.AddWithValue("@Cust_Fnam", custFname)
                'dCmd.ExecuteNonQuery()
                Dim dadaptr As New SqlDataAdapter(dCmd)
                dadaptr.SelectCommand = dCmd
                dadaptr.SelectCommand.ExecuteNonQuery()
                dadaptr.Fill(tabletdata)


                Return tabletdata
            Catch
                Throw
            Finally
                dCmd.Dispose()
                conn.Close()
                conn.Dispose()
            End Try

        End Function

Upvotes: 1

Views: 318

Answers (1)

KV Prajapati
KV Prajapati

Reputation: 94625

Fill method opens and close connection implicitly. Fill Method

SUMMARY: The Fill method retrieves rows from the data source using the SELECT statement specified by an associated SelectCommand property. The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

Public Function searchCustomer(ByVal custFname As String) As DataTable
  Dim tabletdata As New DataTable
  Dim conn As New SqlConnection(con_string)
  Dim dCmd As New SqlCommand("selectCustomerByFname", conn)
  dCmd.CommandType = CommandType.StoredProcedure

  dCmd.Parameters.AddWithValue("@Cust_Fnam", custFname)
  Dim dadaptr As New SqlDataAdapter(dCmd)
  dadaptr.SelectCommand = dCmd
  dadaptr.Fill(tabletdata)
  Return tabletdata
End Function

Upvotes: 1

Related Questions