C# checking if record exists in SQL error

Question

I am using this code to check whether a value (guid1) already exists in the 'guid' table:

string selectString = "SELECT guid" + "FROM trafficScotland" + "WHERE guid = " + guid1;

SqlCommand myCommand = new SqlCommand(selectString, myConnection);
String strResult = String.Empty;
strResult = (String)myCommand.ExecuteScalar();

 if (strResult.Length == 0)

But at the

 strResult = (String)myCommand.ExecuteScalar();

line, I get the sqlException error

Incorrent syntax near 'guid'

Please show me what is wrong here?

Answer 1

"SELECT guid" + "FROM trafficScotland" + "WHERE guid ="

That's:

SELECT guidFROM trafficScotlandWHERE guid =

It makes no sense to break that down into separate strings anyway, but you are missing spaces between words :)

string resultGuidAsString = null;

// build command object
string cmdQuery = "SELECT guid FROM trafficScotland WHERE guid=@guid";
SqlCommand myCmd = new SqlCommand(cmdQuery, myConnection);

// safely pass in GUID parameter value
myCmd.Parameters.AddWithValue("@guid", guid1);

// read result, check for nulls in DB
object result = myCmd.ExecuteScalar();
if (result != DBNull.Value && result != null)
{
    resultGuidAsString = result.ToString();
}

^^ Here's an improved version. Several points for criticism if I may:

• No parameters were used for your query: just building one string. A security, readability and maintainability risk
• Presumably you're checking whether there is an entry with that guid, suggesting there might not be, but you're not checking for DBNull.Value in case there isn't
• Just a bit confusing - you're returning a string but dealing with Guids. Odd.

Answer 2

Do something like this instead:

var selectString = "SELECT 1 FROM trafficScotland WHERE guid = @guid"
var myCommand = new SqlCommand(selectString, myConnection);
myCommand.Parameters.AddWithValue("@guid", guid1);

var itExists = (Int32)myCommand.ExecuteScalar() > 0;
if (itExists) {
    // do stuff...
}