SqlDataException: Incorrect Syntax near '='

Question

I am trying get the data from database but I am getting this error. I am sure I am missing some simple logic but I couldn't figure out what's wrong with the query as it is working fine directly on DB.

SqlCommand retrievedgeids = new SqlCommand("Select edgeid from Edges where fromIntersection = @fromid and toIntersection = @toid", sqlconnection);

retrievedgeids.Parameters.AddWithValue("@fromid", fromid);
retrievedgeids.Parameters.AddWithValue("@toid", toid);

using (SqlDataReader reader = retrievedgeids.ExecuteReader())
{
    while (reader.Read())
    {
         if (reader["edgeid"] != System.DBNull.Value)
         {
              edgeids.Add(Convert.ToInt32(reader["edgeid"]));
         }
    }
}

And this is the error message:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '='.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at NewRNG.UpdatedRNG.getalledges() in C:\Users\ssindhu\source\repos\NewRNG\NewRNG\UpdatedRNG.cs:line 148 ClientConnectionId:36446293-901d-49a3-85a7-da73e2acaedd Error Number:102,State:1,Class:15

Can you help me figure out this issue?

Answer 1

Here is the link to the Microsoft documentation for the Parameter class you are using : https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparameter?view=dotnet-plat-ext-5.0

You would need to change your code to something like this :

System.Data.SqlClient.SqlCommand retrievedgeids = new System.Data.SqlClient.SqlCommand("Select edgeid from Edges where fromIntersection =     @fromid and toIntersection = @toid", sqlconnection);
System.Data.SqlClient.SqlParameter fromIdParameter = new System.Data.SqlClient.SqlParameter("fromid", SqlDbType.Int);
fromIdParameter.Value = fromId;
retrievedgeids.Parameters.Add(fromIdParameter);

Answer 2

Use Parameters.Add instead of AddWithValue like this:

retrievedgeids.Parameters.Add("@fromid", SqlDbType.Int);
retrievedgeids.Parameters["@fromid"].Value = fromid;