Geek asks and answers

How to use sql parameters for the selected query?

I need to get records based on a "similar" match to a set of records,

The following use query im not working. Does anyone know what is wrong with the request?

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

This query does not retrieve the required records.

When running the above snippet, I get the following error:

Must declare the scalar variable "@Name".
+3
source share
3 answers

What's happening?

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

You can also encode it as follows to avoid formatting the lookup in the first place:

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

If you intend to insist on doing this in an unsafe way, at least double any single quotes found in searchString, for example.

searchString.Replace("'", "''")
+10
source

String.Format , {0} {1} ..

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

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
0
source

More articles:


All Articles