Stored Procedure will not execute on second button click

Question

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); } } } }

Piyush Pansuriya · Answer

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.

Scott G · Answer

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 Chauhan · Answer

Please do one thing clear Datatable after executing sp operation.

Scott G · Answer

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 Chauhan · Answer

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 G · Answer

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.

Udit Chauhan · Answer

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.