How to use sql parameters for a select query?

Question

I need to fetch the records based on a 'like' match against a set of records,

The below query im using is not working . Does anyone knows what's wrong with the query?

 sqlCommand.CommandText =String.Format("SELECT * FROM Customer" +
                " WHERE (Name like @Name)","'%" +searchString.Trim()+"%'");
            sqlCommand.Parameters.AddWithValue("Name", searchString);

This query isnt fetching the desired records.

I'm getting the following error while running the above snippet:

Must declare the scalar variable "@Name".

You have AddWithValue("Name", ... shouldn't that be @Name? — Aaron Bertrand
– Aaron Bertrand, Commented Apr 24, 2012 at 3:53
You didn't include the error you were getting. Can you please include it as a part of the question? — Aaron Bertrand
– Aaron Bertrand, Commented Apr 24, 2012 at 3:55

Aaron Bertrand · Accepted Answer · 2012-04-24 04:28:34Z

14

What happens this way?

sqlCommand.CommandText = "SELECT * FROM Customer WHERE Name LIKE @Name;";
sqlCommand.Parameters.AddWithValue("@Name", "%" + searchString + "%");

You could also code it as follows to avoid all the wildcard formatting in the first place:

sqlCommand.CommandText = "SELECT * FROM Customer WHERE CHARINDEX(@Name, Name) > 0;";
sqlCommand.Parameters.AddWithValue("@Name", searchString);

If you're going to insist on doing it the unsafe way, at the very least double-up any single quotes found in searchString, e.g.

searchString.Replace("'", "''")

edited Apr 24, 2012 at 4:28

answered Apr 24, 2012 at 3:42

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NewBie Over a year ago

I'm getting this error "Must declare the scalar variable "@Name"."

vpiTriumph Over a year ago

Wouldn't the wildcards (%) be in the CommandText vs the parameter. That is: sqlCommand.CommandText="SELECT * FROM Customer WHERE Name LIKE %@Name%"

Aaron Bertrand Over a year ago

@vpiTriumph that's not the way I'd do it. Similar to a parameter to a stored procedure, in the body I'd have WHERE col LIKE @param - then I can pass 'foo%', '%bar' or '%splunge%'...

StuartLC · Accepted Answer · 2012-04-24 03:45:18Z

1

String.Format needs a placeholder, like {0} {1} etc.

sqlCommand.CommandText = "SELECT * FROM Customer WHERE Name LIKE @Name;";
sqlCommand.Parameters.AddWithValue("@Name", String.Format("%{0}%", searchString));

answered Apr 24, 2012 at 3:45

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Comments

cmkrushchev · Accepted Answer · 2018-02-19 02:34:33Z

-3

If Not con.State = ConnectionState.Open Then con.Open() End If

    Try

        Dim cmd As New OleDbCommand("UPDATE med_records SET Medicine=@Medicine,Dosage=@Dosage,Format=@Format,Expiration_date=@Expiration_date,Quantity=@Quantity where M_id=@M_id", con)
        cmd.Parameters.AddWithValue("@Medicine", txtMedicine.Text)
        cmd.Parameters.AddWithValue("@Dosage", txt_Dosage.Text)
        cmd.Parameters.AddWithValue("@Format", txt_Format.Text)
        cmd.Parameters.AddWithValue("@Expiration_date", txt_Expirationdate.Text)
        cmd.Parameters.AddWithValue("@Quantity", NumericUpDown1.Text)
        cmd.Parameters.AddWithValue("@M_id", txt_M_id.Text)
        cmd.ExecuteNonQuery()
        MsgBox("Update data")
        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message)

    End Try

answered Feb 19, 2018 at 2:34

cmkrushchev

11 bronze badge

1 Comment

Eduard Malakhov Over a year ago

Code-only answers are discouraged at SO because they lack context and may be not helpful for future readers. Please edit your answer to comment on your solution.

Collectives™ on Stack Overflow

How to use sql parameters for a select query?

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related