error "must declare the scalar variable "@searchkey" VS 2010

Question

1.00/5 (1 vote)

See more:

Having the error message.

Please assist to identify where the error is coming from.

C#

//INCREMENTAL SEARCH BEGINS
           try
           {
               // Connection to the database

               string str;
               str = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
               SqlConnection sqlCon = new SqlConnection(str);

               //Call the statement

               SqlCommand SqlCmd = new SqlCommand(@"Select * from Loanmaster where Id_Code  LIKE @searchkey or Customer LIKE @searchkey or Apprs_No LIKE @searchkey", sqlCon);


               SqlCmd.Parameters.AddWithValue("@searchkey", "%" + txt_Search.Text + "%");


               //Open the sql data connection
               sqlCon.Open();

               //Execute the program
               SqlCmd.ExecuteNonQuery();

               //Display confirmation message
               lblstatus.Text = "Search Result with string : " + txt_Search + "was sucessful";

               //Clear all text boxes
               txt_Acct1.Text = "";
               txt_Acct2.Text = "";
               txt_Acct3.Text = "";


               SqlDataAdapter da = new SqlDataAdapter(@"Select * from Loanmaster where Id_Code  LIKE @searchkey or Customer LIKE @searchkey or Apprs_No LIKE @searchkey", sqlCon);


               DataSet ds = new DataSet();
               da.Fill(ds, "MLOANJOIN");

               GridView1.DataSourceID = "SqlDataSource1";
               GridView1.DataBind();


               txt_Acct1.Text = ds.Tables["MLOANJOIN"].Rows[0]["ACCT_NUMBER1"].ToString();
               txt_Acct2.Text = ds.Tables["MLOANJOIN"].Rows[0]["ACCT_NUMBER2"].ToString();
               txt_Acct3.Text = ds.Tables["MLOANJOIN"].Rows[0]["ACCT_NUMBER3"].ToString();


               lblstatus.Text = "First record successful";
               sqlCon.Close();
               GridView1.SelectedIndexChanged += new EventHandler(GridView1_SelectedIndexChanged);
               txt_Id_Code.Enabled = false;
               txt_Appraisal.Enabled = false;
               txt_Customer.Focus();

               sqlCon.Close();

           }
           catch (Exception ex)
           {
               lblstatus.Text = ex.Message;
           }


           btn_Add.Enabled = false;
           //
           //Updating the gridview and form end
           //

           //INCREMENTAL SEARCH ENDS

Posted 9-Jun-14 1:48am

Member 10744248

Updated 9-Jun-14 3:42am

Thanks7872

v3

Add a Solution

Comments

[no name] 9-Jun-14 7:56am

In your second query, you did not add your parameter.

Member 10744248 9-Jun-14 8:15am

Please assist.

new to c#

Thanks

[no name] 9-Jun-14 8:21am

Okay so how could I possibly assist you any more?

Member 10744248 9-Jun-14 8:25am

I have added it yet the error persisted.

SqlCmd.Parameters.AddWithValue("@searchkey", "%" + txt_Search.Text + "%");

[no name] 9-Jun-14 8:59am

Okay so update your question with your current code along with the error message and indicate the line of code that is throwing the error.

Prasad Avunoori 9-Jun-14 8:00am

@searchkey is a variable. You had used it as inline string.

Member 10744248 9-Jun-14 8:13am

Please clarify

new to c# thanks

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2014-06-09T02:01:00

Easy enough. In your DataAdapter declaration, you're creating a new SqlCommand object by specifying the SQL statement in the constructor but you never added a SqlParameter to it.

Frankly, you're executing the exact same query twice. I have no idea why you even need the DataAdapter.

On top of that, keep in mind that LIKE operation only works on text fields, not numeric fields. But, since we don't know what types your db columns are, this is only a suggestion.

DamithSL · Answer 2 · 2014-06-09T03:58:00

Solution 3

You need to add parameter to the data adapter select command as below

C#

da.SelectCommand.Parameters.AddWithValue("@searchkey", "%" + txt_Search.Text + "%");

Or you can use select command you already created

C#

SqlDataAdapter adapter = new SqlDataAdapter(SqlCmd);
DataSet ds = new DataSet();
da.Fill(ds, "MLOANJOIN");
GridView1.DataSource =ds.Tables[0];
GridView1.DataBind();

Posted 9-Jun-14 3:58am

DamithSL

Updated 9-Jun-14 8:24am

v4

Comments

Member 10744248 9-Jun-14 12:55pm

Error around 'cmd'

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DamithSL 9-Jun-14 14:26pm

it should be SqlCmd
the name of your sql command object

Member 10744248 9-Jun-14 12:56pm

please refine it looking at the codes the right substitute

Thanks

Member 10744248 9-Jun-14 13:11pm

Both DataSource and DataSourceID are defined on 'GridView1'. Remove one definition.]

DamithSL 9-Jun-14 14:27pm

remove DataSourceID property from the aspx page

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 3 · 2014-06-09T08:46:00

Solution 5

What are you doing?

Let me explain what you are doing in code.

1. You are executing the same query twice.
2. Not sure why exactly you are retrieving, because you are not doing anything with first query execution.
3. Next - the second execution retrieves you result and you are binding it to a DataSet. Then you are showing Row[0] values in the TextBoxes. So, what exactly happens to other rows? Is that what you wanted to achieve?
4. Then you are binding one GridView with another SqlDataSource, which is also not justified in this scenario.

My Suggestions

So, I suggest you to remove the first query execution code. Then think what exactly you want to do. Debug your code and find out the issue,

Posted 9-Jun-14 8:46am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Updated 9-Jun-14 8:50am

v2

Comments

Member 10744248 9-Jun-14 16:03pm

What I want to do is to search for records from 'loanmaster' tables which meets the specified criteria

and then load the results of the search into the gridview ans display the first record out of the criteria in the text boxes on the form.

Member 10744248 9-Jun-14 16:05pm

The user can next select the records in the resultants in the gridview to fill the form.

Member 10744248 9-Jun-14 16:40pm

I have taken off the first query execution and I have this

SqlDataAdapter da = new SqlDataAdapter(@"Select * from Loanmaster where Id_Code LIKE '+ @searchkey+' or
Customer LIKE '+ @searchkey+' or
Apprs_No LIKE '+ @searchkey'", sqlCon);

da.SelectCommand.Parameters.AddWithValue("@searchkey", "%" + txt_Search.Text + "%");

//Open the sql data connection
sqlCon.Open();

//Execute the program
da.ExecuteNonQuery();

Please help with "Execute the program" error

Prasad Avunoori · Answer 4 · 2014-06-09T01:58:00

Solution 1

Change your SqlAdapter code as follow:

C#

SqlDataAdapter da = new SqlDataAdapter(@"Select * from Loanmaster where Id_Code LIKE "+@searchkey +" or Customer LIKE "+ @searchkey+" or Apprs_No LIKE "+@searchkey, sqlCon);

Posted 9-Jun-14 1:58am

Prasad Avunoori

error "must declare the scalar variable "@searchkey" VS 2010

4 solutions

Solution 2

Solution 3

Solution 5

What are you doing?

My Suggestions

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us