Error when executing a SQL query in c#, no error when i manually input it

Question

I am executing a qry in c# which returns me this error when I run it from the program:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

and the exception when I catch the SqlException:

Incorrect syntax near '.'.

However when I run it in SQL Server and exchange the variables manually it runs fine. I feel like i am missing something basic but I am staring myself to death on it.

The query:

SELECT TOP 1 TempSelection.ErrorOccured
FROM
(
SELECT JL.CommandID AS cmdID, 
    CASE JL.Direction
        WHEN 1 THEN 'Out'
        WHEN 2 THEN 'In'
        WHEN 5 THEN 'ConcOut'
        ELSE 'Both' 
    END
    AS Direction,
    CH.ChannelName, A.Description AS AddressDescription, SG.SelectorGroupName, 
    CASE WHEN SML1.ID IS NOT NULL
            THEN SML1.FormatCode
            ELSE 
                CASE WHEN SML2.ID IS NOT NULL
                THEN SML2.FormatCode
                ELSE NULL 
                END 
    END 
    AS FormatCode,
    CASE WHEN SML1.ID IS NOT NULL
            THEN SML1.TypeCode
            ELSE 
                CASE WHEN SML2.ID IS NOT NULL
                THEN SML2.TypeCode
                ELSE NULL 
                END 
    END 
    AS TypeCode,
    CASE WHEN SML1.ID IS NOT NULL
            THEN CTP1Sender.PartnerName
            ELSE 
                CASE WHEN SML2.ID IS NOT NULL
                THEN CTP2Sender.PartnerName
                ELSE NULL 
                END 
    END 
    AS Sender,

        CASE WHEN SML1.ID IS NOT NULL
            THEN CTP1Recipient.PartnerName
            ELSE CASE 
            WHEN SML2.ID IS NOT NULL
                THEN CTP2Recipient.PartnerName
                ELSE NULL 
                END 
    END 
    AS Recipient,
    EL.ErrorDescription, EL.Assembly, EL.ErrorDetail, C.ErrorOccured


FROM
    @dbEnv.dbo.tbl_jobLog JL 
    LEFT JOIN @dbEnv.dbo.tbl_Commands C ON JL.CommandID = C.CommandID
    LEFT JOIN @dbEnv.dbo.tbl_ProcessFlows PF ON PF.ProcessFlowID = JL.ProcessFlowID 
    LEFT JOIN @dbEnv.dbo.tbl_ProcessFlowDefenitions PFD ON PFD.ProcessFlowDefID = JL.ProcessFlowDefID
    LEFT JOIN @dbEnv.dbo.tbl_Channels CH ON CH.ChannelID = JL.ItemID AND JL.ItemType LIKE 'CHA' AND CH.ChannelID IS NOT NULL
    LEFT JOIN @dbEnv.dbo.tbl_SelectorGroups SG ON SG.SelectorGroupID = JL.ItemID AND JL.ItemType LIKE 'SEL' AND SG.SelectorGroupID IS NOT NULL
    LEFT JOIN @dbEnv.dbo.tbl_ErrorLog EL ON JL.JobID = EL.JobID
    LEFT JOIN @dbEnv.dbo.tbl_inoutmessages IOM ON IOM.InOutMsgID = EL.InOutMsgID
    LEFT JOIN @dbEnv.dbo.tbl_Addresses A ON A.AddressID = IOM.AddressID 
    LEFT JOIN @dbEnv.dbo.tbl_StoredMessagesLog SML1 ON SML1.ID = IOM.StoreLinkID
    LEFT JOIN @dbEnv.dbo.VW_CodeToPartner CTP1Sender ON SML1.SenderCodeID = CTP1Sender.ID
    LEFT JOIN @dbEnv.dbo.VW_CodeToPartner CTP1Recipient ON SML1.RecipientCodeID = CTP1Recipient.ID  
    LEFT JOIN @dbEnv.dbo.tbl_StoredMessagesLog SML2 ON SML2.ID = EL.StoredmessagesID
    LEFT JOIN @dbEnv.dbo.VW_CodeToPartner CTP2Sender ON SML2.SenderCodeID = CTP2Sender.ID
    LEFT JOIN @dbEnv.dbo.VW_CodeToPartner CTP2Recipient ON SML2.RecipientCodeID = CTP2Recipient.ID

WHERE
CH.ChannelName {0}
AND A.Description {1}
AND SG.SelectorGroupName {2}
AND EL.ErrorDescription {3}
AND EL.Assembly {4}
AND EL.ErrorDetail {5}
) AS TempSelection
WHERE
Direction {6}
AND FormatCode {7}
AND TypeCode {8}
AND Sender {9}
AND Recipient {10}
";

The variables in the string are filled in like this:

SqlCommand cmd = new SqlCommand();
if (errorRow["Channel"] == null || errorRow["Channel"].ToString() == "")
{
    Channel = "IS NULL";
}
else
{
    Channel = "LIKE '@ChannelName'";
    param = new SqlParameter();
    param.ParameterName = "@ChannelName";
    param.Value = errorRow["Channel"].ToString();
    cmd.Parameters.Add(param);
}

After which the string is edited with String.Format:

sqlCommand = String.Format(sqlCommand, Channel,...);
cmd.CommandText = sqlCommand;

And finally the execution itself:

public static Boolean readLatestErrorOccured(SqlConnection sqlConn, SqlCommand sqlCommand)
    {
        try
        {
            sqlCommand.Connection = sqlConn;
            object obj = sqlCommand.ExecuteScalar();
            if (obj != null)
            {
                return Convert.ToBoolean(obj);
            }
        }
        catch (SqlException sqlEx)
        {
            MessageBox.Show("Error in readLatestErrorOccured: SqlException" + Environment.NewLine + Environment.NewLine + sqlEx.Message.ToString());
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error in readLatestErrorOccured" + Environment.NewLine + Environment.NewLine + ex.Message.ToString());
        }
        return false;
    }

PS: I am also getting an error when inputting a 'WITH(NOLOCK)' in the statement (example: LEFT JOIN GFKN.dbo.tbl_Commands C WITH(NOLOCK) ON JL.CommandID = C.CommandID). Again the qry functions in SQL server but not from the application.

Edit1: Up to this point i am basicly checking the output qry by just outputting some debug info on the commandtext and the parameters. the lower part of the qry would then look like this:

WHERE
    CH.ChannelName LIKE '@ChannelName'
    AND A.Description IS NULL
    AND SG.SelectorGroupName IS NULL
    AND EL.ErrorDescription LIKE '@ErrorDescription'
    AND EL.Assembly LIKE '@Assembly'
    AND EL.ErrorDetail LIKE '@ErrorDetail'
    ) AS TempSelection
    WHERE
    Direction LIKE '@Direction'
    AND FormatCode IS NULL
    AND TypeCode IS NULL
    AND Sender IS NULL
    AND Recipient IS NULL

param @dbEnv:GFKN
param @ChannelName:FTP FR.DHL
param @ErrorDescription:A warning occured while receiving messages.
param @Assembly:Porthus.GoldFish.FTP
param @ErrorDetail:A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
param @Direction:In

Answer 1

You cannot submit @DbEnv as a parameter like this from C#!

Use string.format to place the @DbEnv into your SQL string.

If you try to use something like this:

cmd.Parameters.AddWithValue("@dbEnv", "DatabaseName");

You will get the exact error you are seeing.