Stored Procedure not Working in VB.NET

[EDN Admin]

Hello All,
Im trying to call a stored procedure in SQL using VB.Net. Im sure Im missing something because is not working when I call it. Im also trying to use parameters with users input on a textbox(es) using a partial result.
Can you anyone please help guiding me to the right direction? Below follow the procedure and calling codes:CREATE PROCEDURE SearchCustomer
(
@AcctNumber VARCHAR(10) = NULL,
@FirstName VARCHAR(20) = NULL,
@LastName VARCHAR(20) = NULL,
@CustAddress VARCHAR(40) = NULL,
@City VARCHAR(20) = NULL,
@CustState VARCHAR(2) = NULL,
@Zip VARCHAR(10) = NULL,
@HomePhone VARCHAR(15) = NULL,
@DigitalPhone VARCHAR(15) = NULL,
@CellPhone VARCHAR(15) = NULL,
@Email VARCHAR(30) = NULL
)

AS
BEGIN

SELECT DISTINCT
AcctNumber, FirstName, LastName, CustAddress, City, CustState, Zip,
HomePhone, DigitalPhone, CellPhone, Email
FROM dbo.CustInfo

WHERE
(AcctNumber LIKE ISNULL (@AcctNumber, AcctNumber) + %) AND
(FirstName LIKE ISNULL (@FirstName, FirstName) + %) AND
(LastName LIKE ISNULL (@LastName, LastName) + %) AND
(CustAddress LIKE ISNULL (@CustAddress, CustAddress) + %) AND
(City LIKE ISNULL (@City, City)+ %) AND
(Zip LIKE ISNULL(@Zip, Zip) + %) AND
(HomePhone LIKE ISNULL (@HomePhone, HomePhone) + %) AND
(DigitalPhone LIKE ISNULL(@DigitalPhone, DigitalPhone) + %) AND
(CellPhone LIKE ISNULL (@CellPhone, CellPhone) + %) AND
(Email LIKE ISNULL (@Email, Email) + %)
ORDER BY FirstName, LastName

END






Private Sub btnOk_Click(sender As System.Object, e As System.EventArgs) Handles btnOk.Click

Using conn As SqlConnection = GetConnect()
conn.Open()

Dim cmd As New SqlCommand()

cmd.Parameters.AddWithValue("@AcctNumber", txtAccountNumber.Text)
cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
cmd.Parameters.AddWithValue("@LastName", txtLastName.Text)
cmd.Parameters.AddWithValue("@CustAddress", txtAddress.Text)
cmd.Parameters.AddWithValue("@City", txtCity.Text)
cmd.Parameters.AddWithValue("@CustState", cboState.SelectedItem)
cmd.Parameters.AddWithValue("@Zip", txtZip.Text)
cmd.Parameters.AddWithValue("@HomePhone", txtHomePhone.Text)
cmd.Parameters.AddWithValue("@DigitalPhone", txtDigitalPhone.Text)
cmd.Parameters.AddWithValue("@CellPhone", txtCellPhone.Text)
cmd.Parameters.AddWithValue("@Email", txtEmail.Text)


cmd.CommandText = "SearchCustomer"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = conn

Dim dr As SqlDataReader
dr = cmd.ExecuteReader

With DGVResult
.AutoGenerateColumns = True
.DataSource = dr
End With

End Using


End Sub
I appreciate any help on this.

View the full article