vb.net - 将遗留代码转换为参数化查询的问题

Question

我有一段看起来像这样的遗留代码：

Dim Sql = "declare @id int " &
        "select @id = id from wa_merchant where merchant_id = '" & db.SqlSafe(p.Merchant.MerchantID) & "' " &
        "insert into wa_postback_processing ( merchant_id, [url], [data] ) values ( " &
        "@id, " &
        "'" & db.SqlSafe(p.Postback.HttpsUrl) & "', " &
        "'response=" & db.SqlSafe(HttpUtility.UrlEncode(UTF8(pr.ToXml()))) & "' )"
        db.ExecuteNonQuery(Sql)

我将其转换为参数化插入。这是我的插入的样子：

Dim cmd As New SqlCommand("insert into wa_postback_processing ( merchant_id, [url], [data] ) values (@MerchantID , @Url1 , @url2", con)

        cmd.Parameters.Add("@MerchantID", sqlDbType:=SqlDbType.VarChar).Value = sMerchantID
        cmd.Parameters.AddWithValue("@url1", p.Postback.HttpsUrl)
        Dim url2 = HttpUtility.UrlEncode(UTF8(pr.ToXml()))
        cmd.Parameters.AddWithValue("@url2",  "response = " & "'" & url2 & "'")


             Try
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Trace.WriteLine(ex.Message & ex.Source, LogLevel.Debug)
        Finally
            cmd.Connection.Close()

        End Try

但是，我在执行时不断收到此异常：'@url2' 附近的语法不正确。

@url2 的数据如下所示

%3c%3fxml+version%3d%221.0%22+encoding%3d%22utf-8%22%3f%3e%0d%0a%3cPaymentResponseType+xmlns%3axsi%3d%22http%3a%2f%2fwww.w3.org%2f2001%2fXMLSchema-instance%22+xmlns%3axsd%3d%22http%3a%2f%2fwww.w3.org%2f2001%2fXMLSchema%22%3e%0d%0a++%3cResponse%3e%0d%0a++++%3cResponseIndicator%3eA%3c%2fResponseIndicator%3e%0d%0a++++%3cResponseCode%3e000001%3c%2fResponseCode%3e%0d%0a++++

我完全不知所措。谢谢

标签： vb.netado.net