首页 / C# / 在c#中执行SQL查询时出错,手动输入时没有错误

在c#中执行SQL查询时出错,手动输入时没有错误

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了在c#中执行SQL查询时出错,手动输入时没有错误，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4886字，纯文字阅读大概需要7分钟。

内容图文

我在c#中执行qry,当我从程序中运行它时会返回此错误：

System.Data.dll中发生了’System.Data.SqlClient.SqlException’类型的第一次机会异常

和我捕获SqlException时的异常：

Incorrect syntax near ‘.’.

但是当我在SQL Server中运行它并手动交换变量时它运行正常.
我觉得我错过了一些基本的东西,但我正在盯着自己去死.

查询：

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}
";

字符串中的变量填写如下：

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);
}

之后使用String.Format编辑字符串：

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

最后执行本身：

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：我在语句中输入’WITH(NOLOCK)’时也遇到错误(例如：
    LEFT JOIN GFKN.dbo.tbl_Commands C WITH(NOLOCK)ON JL.CommandID = C.CommandID).
qry函数再次在SQL Server中运行,但不是来自应用程序.

Edit1：到目前为止,我只是通过输出命令文本和参数的一些调试信息来基本检查输出qry. qry的下半部分看起来像这样：

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

解决方法:

你不能从C#提交@DbEnv作为这样的参数！

使用string.format将@DbEnv放入SQL字符串中.

如果你尝试使用这样的东西：

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

您将收到您所看到的确切错误.

内容总结

以上是互联网集市为您收集整理的在c#中执行SQL查询时出错,手动输入时没有错误全部内容，希望文章能够帮你解决在c#中执行SQL查询时出错,手动输入时没有错误所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/903479.html

来源：【匿名】