Hello! I have a problem with selecting some string from a datatable containing character " ' " under the values of some attributes. For example I have a table called Table and attribute type of NVCHAR(255) called SomeString. This attribute contains strings with a charcter " ' " and I am unable to pull out the value using SELECT statement.
For example:
SELECT SomeAttribute FROM Table WHERE SomeString =' He's riding a bike ';
Because character " ' " is reserved for borders of the string and is also a part of my string. Is there any possibility to solve such a "problem"?
Thanx
Just escape the quote char.
SELECT SomeAttribute FROM Table WHERE SomeString =' He''s riding a bike ';
A better thing is using paramitrimized queries. Then you never have to worry about format's or SQL Injection.
It's olso better for the preformance, because you don't need to have to concatenate a string for example:
string query = "SELECT * FROM Table1 WHERE ID = " + txtId.Text + " AND Name = \"" + "txtName.Text + "\"";
No escape characters needed, you doesn't have to think about using a " or not etc.
Parameters are like placeholders, you use them in Stored Procedures as well.
A little example:
// TODO: Set date variable.
DateTime date = DateTime.Now;
// Set query and parameters.
const string query = "SELECT * FROM Table1 WHERE MyDate = @.MyDate";
SqlParameter pMyDate = new SqlParameter("@.MyDate", SqlDbType.DateTime);
pMyDate.Value = date;
// Create connection and open it.
SqlConnection dbConn = new SqlConnection("ConnectingString");
dbConn.Open();
try
{
using(SqlCommand dbCommand = new SqlCommand(query, dbConn))
{
// Add paramter to Command.
dbCommand.Parameters.Add( pMyDate );
// Execute the query and get results.
SqlDataReader reader = dbCommand.ExecuteReader();
try
{
// Walkthrough results.
while(reader.Read())
{
// TODO: Do something with the data.
}
}
finally
{
// Close reader.
reader.Close();
}
}
}
finally
{
// Close connection.
dbConn.Close();
}
