Stored Procedure will not execute on second button click

Scott G

I have a stored procedure that executes a search when a button is clicked in my appplication. Two parameters are passed to the procedure and all works great the first time the procedure is executed. But if I change the search parameter and string and try to execute it again it doesn't work. I was getting an error that I was sending "too many arguments specified". I realized I wasn't clearing my parameters before executing the sp again. so I added a clear statement and now it will not run at all on the second click. Any suggestions?

public partial class InitialDebt : Form

{

static string conn = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnStr"].ConnectionString;

static SqlConnection misc = new SqlConnection(conn);

static SqlCommand initDebt = misc.CreateCommand();

DataTable dt = new DataTable();

SqlDataAdapter da = new SqlDataAdapter(initDebt);

static int index = 0;

private void btnSearch_Click(object sender, EventArgs e)

{

if (ddlFilter.Text == "SELECT")

{

MessageBox.Show("Please select a value from the drop down list");

}

else

{

try

{

initDebt.CommandType = CommandType.StoredProcedure;

initDebt.CommandText = "MyStoredProc";

initDebt.Parameters.Clear();

initDebt.Parameters.Add("@Filter", SqlDbType.VarChar, 20).Value = ddlFilter.Text;

initDebt.Parameters.Add("@SearchStr", SqlDbType.VarChar, 40).Value = txtSearchStr.Text;

da.Fill(dt);

txtSS.Text = dt.Rows[0]["SS_Num"].ToString();

txtLName.Text = dt.Rows[0]["last_Name"].ToString();

txtFName.Text = dt.Rows[0]["first_name"].ToString();

txtAgt.Text = dt.Rows[0]["agent_num"].ToString();

txtStore.Text = dt.Rows[0]["agent_name"].ToString();

}

catch (Exception ex)

{

MessageBox.Show("There are no records that match your search criteria. "+ ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Scott G7y ago
Thanks - I tried clearing the datatable and that didn't do anything either. Here is my sp. Maybe it is something stupid I am overlooking. This runs fine though from SSMS. I only get that error on the second click of the search button.

@Filter varchar(20),
@SearchStr varchar(40)
AS
BEGIN
SELECT SS_Num, upper(Last_Name) as Last_name, upper(First_Name) as first_name,
upper(Address) as address, upper(City) as city, upper(State) as state,
Zip_code, upper(Agency_code) as agency_code, Agent_num,
upper(Agent_name) as agent_name, debt_date, debt_amount
FROM myTable
WHERE
(
CASE
WHEN @Filter = 'SS #' THEN SS_Num
WHEN @Filter = 'LAST NAME' THEN Last_Name
WHEN @Filter = 'CITY' THEN City
WHEN @Filter = 'AGENT #' THEN Agent_num
WHEN @Filter = 'STORE' THEN Agent_Name
END
like '%'+@SearchStr+'%'
)
END
Udit Chauhan7y ago
Please do one thing clear Datatable after executing sp operation.
Scott G7y ago
Udit - thanks for the response. I already tried the following (tried it again now) with no success.

initDebt.Parameters.Add("@Filter", SqlDbType.VarChar, 20).Value = ddlFilter.Text ?? "";;
initDebt.Parameters.Add("@SearchStr", SqlDbType.VarChar, 40).Value = txtSearchStr.Text??"";
Udit Chauhan7y ago
PLease try this in your code

initDebt.Parameters.Add("@Filter", SqlDbType.VarChar, 20).Value = ddlFilter.Text ?? "";;
initDebt.Parameters.Add("@SearchStr", SqlDbType.VarChar, 40).Value = txtSearchStr.Text??"";
Scott G7y ago
The size specified is the same as the parameter length specified in the stored procedure. That being said I removed it anyway and it still does not work.

And, yes the parameters are the same name. The procedure executes the first time it is run. It wouldn't run at all if the parameters had a different name.
Piyush Pansuriya7y ago
Hello,

when you add the size of the parameter, you have to maintain char length.
initDebt.Parameters.Add("@Filter", SqlDbType.VarChar, 20).Value = ddlFilter.Text;
initDebt.Parameters.Add("@SearchStr", SqlDbType.VarChar, 40).Value = txtSearchStr.Text;
So, you have added 20 for filter and 40 for search.
Solution : Increase the size or Remove the size from parameters.
Udit Chauhan7y ago
Please make sure parameter you are passing in code in stored procedure should be match with stored procedure parameter in sql.

Hope so it resolve your problem.