System.Data.dll中发生类型为'System.Data.SqlClient.SqlException'的异常，但未在用户代码中处理 [英] An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

问题描述

你好

我尝试了许多方法来解决此问题，但我没有.我真的不知道该怎么办.我已经从Visual C#类中调用了存储的过程.在onClick按钮上，存储过程应将大数据保存到表中.我相信问题出在 (nvarchar和varchar限制).我试图将sql强制转换为"N"；解决此问题，但仍然存在相同的问题.我可以在sql management studio中运行查询，但是当我从webApp调用存储过程时，会出现标题中所述的错误. 我真的很重视任何帮助.

I have tried many ways to solve this problem but I could not. I don't know really what to do. I have stored procedure that is calling from Visual C# class. onClick button, the stored procedure should save big data to Table. I believe the problem is with (nvarchar and varchar limits). I tried to cast my sql to "N" to resolve this issue but still have the same problem. I could run the query in sql management studio but when I call the stored procedure from webApp throw an error as stated in the title. I really value any help.

这是我的存储过程

使用[客户]
开始
/******对象: StoredProcedure [dbo].[sph_NonProfit_ReconMail_SingleCampaign2]脚本日期:2015年9月12日上午8:43:57 ******/
设置ANSI_NULLS ON
开始
设置QUOTED_IDENTIFIER为ON
开始

USE [Clients]
GO
/****** Object:  StoredProcedure [dbo].[sph_NonProfit_ReconMail_SingleCampaign2]    Script Date: 9/12/2015 8:43:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

更改过程[dbo].[sph_NonProfit_ReconMail_SingleCampaign2]
@CampaignId NVARCHAR(4000)
作为
设置为nocount；
-声明@CampaignId nvarchar(50)
声明@SQL nvarchar(4000)
声明@CustomData nvarchar(50)
声明@cDate char(8)
声明@cTodayDate char(8)
声明@cTodayTime char(8)
声明@ cTodayTime6 char(6)
声明@cDwnldFlag char(30)
声明@DateTime日期时间

ALTER PROCEDURE [dbo].[sph_NonProfit_ReconMail_SingleCampaign2]
@CampaignId NVARCHAR (4000)
as
set nocount on;
--Declare  @CampaignId nvarchar(50)
Declare @SQL nvarchar(4000)
Declare @CustomData nvarchar (50)
Declare @cDate char(8)
Declare @cTodayDate char(8)
Declare @cTodayTime char(8)
Declare @cTodayTime6 char(6)
Declare @cDwnldFlag char(30)
Declare @DateTime datetime

--------------------------------------------------- ---------------------更改日期，用于检索过去的数据
设置@cDate = Convert(char(8)，GetDate()-0,112)--20150504 @cDate没有长时间用于下载，已验证日期=已使用空白. CAMPAIG的选择可以追溯到90天.
设置@cTodayDate = Convert(char(8)，GetDate()，112)-
设置@cTodayTime = Convert(char(8)，GetDate()，114)-
设置@ cTodayTime6 = substring(@cTodayTime，1，2)+ substring(@cTodayTime，4，2)+ substring(@cTodayTime，7，2)
设置@cDwnldFlag = Convert(char(30)，GetDate()，120)-下载标志
设置@DateTime = GetDate()
 
--SET @CampaignId ='93C45735-5CB4-4ABF-8E67-A7CBB134BA08'
  set @ CustomData =(从asdm.dbo.Campaigns中选择CustomData，其中ID =  @CampaignId)
 -设置@Sql = N'INSERT INTO Clients.dbo.TempReconDL(DEPT)值(``0'');'

--------------------------------------------------------------------Change Date Here for retrieving past data
Set @cDate = Convert(char(8),GetDate()-0,112) --20150504 @cDate NO LONGER USED FOR DOWNLOAD, VERIFIED DATE = BLANKS IS USED. FOR CAMPAIG SELECTION GOING BACK TO 90 DAYS.
Set @cTodayDate = Convert(char(8),GetDate(),112) --
Set @cTodayTime =   Convert(char(8),GetDate(),114) --
Set @cTodayTime6 = substring(@cTodayTime, 1, 2) + substring(@cTodayTime, 4, 2) + substring(@cTodayTime, 7, 2)
Set @cDwnldFlag = Convert(char(30),GetDate(),120) -- DOWNLOAD FLAG
set @DateTime = GetDate()
 
--SET @CampaignId ='93C45735-5CB4-4ABF-8E67-A7CBB134BA08'
 set @CustomData= (select CustomData from asdm.dbo.Campaigns where ID =  @CampaignId)  
 --set @Sql = N'INSERT INTO Clients.dbo.TempReconDL (DEPT) values (''0'');'

  set @sql = N'INSERT INTO dbo.TempReconDL SELECT
     "DEPT" = ISNULL(RTRIM(LTRIM(s.dept))，``0'')，
     "ABVDSC"是指= ISNULL(RTRIM(LTRIM(s.dwndsc))，'''')，
   "PROGYR" = ISNULL(RTRIM(LTRIM(s.pgyr))，``0'')，
     "OLDPROGNO" =``0000''，
   "UP_ABVDSC"； = ISNULL(s.abvdsc，'''')，
   客户" = ISNULL(RTRIM(LTRIM(s.Client))，``0'')，
   类" = ISNULL(SUBSTRING(s.leadtyp，4,1)，``'')，
   "SegOrig" = ISNULL(s.seg_origin，'''')，
   "SEGMENT" = ISNULL(s.seg，'''')，
   "SEGType"； = ISNULL(s.seg_type，'''')，'
设置@sql = @ SQL&N'" STATE" = ISNULL(UPPER(s.stabbr)，'''')，
    "Arch_Date" =''''，
    "Result_Code"； = SUBSTRING(cc.CustomCode，1,3)>时的情况''''然后SUBSTRING(cc.CustomCode，1,3)否则''000''结尾，
    处置"指的是处置". =当(SUBSTRING(cc.CustomCode，4,2))> ''''然后SUBSTRING(cc.CustomCode，4,2)否则``00''结尾，
      "DNCType"； = ISNULL(s.dnc_type，'''')，
    "DNCCode"； = ISNULL(s.dnc_code，'''')，
    "BorC"是指= ISNULL(s.borc，'''')，'
设置@sql = @SQL + N'" Emp#" = ISNULL(ei.empID，''000000000'')，
    "ProgNo"； = s.progno>时的情况''''然后是s.progno ELSE或s.progno END，
    系统" =``U''，
    填充物" = ISNULL(s.filler，'''')，
    "ACCTNO"是指= s.acctno
   从ASDM.dbo.calllog cl加上(nolock)'
设置@ SQL =  @ SQL + N'Left JOIN'+ @CustomData +'带有cl.customer_id = s.acctno上的(nolock)
   将CL.CREDITTO = ei.loginname
上的(nolock)左加入ASDM.dbo.v_EmpInfo ei    在CL.CUSTOMER_ID = CM.CUSTOMER_ID上以(nolock)内部连接ASDM.DBO.CALLMASTER cm    将cmd.lastresult = cc.id
上的(nolock)左连接asdm.dbo.customCodes cc      其中cm.campaign_id_fk ='''+ @CampaignId +'''和cm.lastresult in('''+'2C534BDC-06CE-4315-B1A1-F3535E73706B'+'''，
    '''+'B14F4855-72D2-4D7F-B8E6-B99C15E170FD'+'''，'''+'8F896D16-2189-4A2B-AFF6-EB4675CDE800'+'''，'''+'DF92E742-9882-4965 -94F4-08902A2A5E90'+'''，'''+'0CF07075-E898-4230-BFDC-2D18A0AB69CC'+'''，
    '''+'8CD5BAE0-4AD9-4227-918E-ACD14F40BA7B'+'''，'''+'1D2A7159-BF6B-458F-923A-1CBBE14D9128'+'''，'''+'A582678D-365B-4ADF -9C61-71718872F162'+'''，'''+'64CAEA05-AF7B-449D-987D-E43F52EC442F'+'''，
    '''+'E2CFC103-04AE-4087-A649-0BCF579BD3AF'+'''，'''+'E30C618E-1EF5-469D-A659-E7910669AA4B'+'''，'''+'1545280C-B33F-4C24 -9108-3979F7BD2B32'+'''，'''+'1A8402E4-C7E0-4070-8357-7FFD0CEFE38B'+'''， '''+'6A2D4C87-799B-4898-AC13-A62B04BB6BCC'+''')
   和(SUBSTRING(s.leadtyp，4,1)=``3''或SUBSTRING(s.leadtyp，4,1)=``4'')
   按cl.start排序'
 
  PRINT @ Sql 
 -exec(@Sql)
  exec sp_executesql @ Sql，N'@ CampaignId NVARCHAR(4000)'，@CampaignId

 set @sql = N'INSERT INTO dbo.TempReconDL SELECT
      "DEPT"  = ISNULL(RTRIM(LTRIM(s.dept)),''0''),
      "ABVDSC" = ISNULL(RTRIM(LTRIM(s.dwndsc)),''''),
   "PROGYR" = ISNULL(RTRIM(LTRIM(s.pgyr)),''0''),
      "OLDPROGNO" = ''0000'',
   "UP_ABVDSC" = ISNULL(s.abvdsc,''''),
   "CLIENT" = ISNULL(RTRIM(LTRIM(s.Client)),''0''),
   "Class" = ISNULL(SUBSTRING(s.leadtyp,4,1), ''''),
   "SegOrig" = ISNULL(s.seg_origin,''''),
   "SEGMENT" = ISNULL(s.seg,''''),
   "SEGType" = ISNULL(s.seg_type,''''),'
set @sql = @SQL + N'"STATE" = ISNULL(UPPER(s.stabbr),''''),
     "Arch_Date" = '''',
    "Result_Code" = Case When SUBSTRING(cc.CustomCode,1,3) > '' '' then SUBSTRING(cc.CustomCode,1,3) Else ''000'' End,
    "Disposition" = Case When (SUBSTRING(cc.CustomCode,4,2)) > '' '' then SUBSTRING(cc.CustomCode,4,2) Else ''00'' End,
       "DNCType" = ISNULL(s.dnc_type,''''),
    "DNCCode" = ISNULL(s.dnc_code,''''),
    "BorC" = ISNULL(s.borc,''''),'
set @sql = @SQL + N'"Emp#" = ISNULL(ei.empID,''000000000''),
    "ProgNo" = case when s.progno > '' '' then s.progno ELSE s.progno END,
    "SYSTEM"  = ''U'',
    "FILLER" = ISNULL(s.filler,''''),
    "ACCTNO" = s.acctno
    FROM ASDM.dbo.calllog cl with (nolock)'
set @SQL=  @SQL+ N'Left JOIN '+ @CustomData +' s with (nolock) on cl.customer_id = s.acctno
    left join ASDM.dbo.v_EmpInfo ei with (nolock) on CL.CREDITTO = ei.loginname
    inner join ASDM.DBO.CALLMASTER cm with (nolock) on CL.CUSTOMER_ID = CM.CUSTOMER_ID
    Left join asdm.dbo.customCodes cc with (nolock) on cm.lastresult = cc.id
       where cm.campaign_id_fk = '''+ @CampaignId + ''' and cm.lastresult in (''' + '2C534BDC-06CE-4315-B1A1-F3535E73706B' + ''',
    ''' + 'B14F4855-72D2-4D7F-B8E6-B99C15E170FD' + ''', ''' + '8F896D16-2189-4A2B-AFF6-EB4675CDE800' + ''', ''' + 'DF92E742-9882-4965-94F4-08902A2A5E90' + ''', ''' + '0CF07075-E898-4230-BFDC-2D18A0AB69CC' + ''',
    ''' + '8CD5BAE0-4AD9-4227-918E-ACD14F40BA7B' + ''', ''' + '1D2A7159-BF6B-458F-923A-1CBBE14D9128' + ''', ''' + 'A582678D-365B-4ADF-9C61-71718872F162' + ''', ''' + '64CAEA05-AF7B-449D-987D-E43F52EC442F' + ''',
    ''' + 'E2CFC103-04AE-4087-A649-0BCF579BD3AF' + ''', ''' + 'E30C618E-1EF5-469D-A659-E7910669AA4B' + ''', ''' + '1545280C-B33F-4C24-9108-3979F7BD2B32' + ''', ''' + '1A8402E4-C7E0-4070-8357-7FFD0CEFE38B' + ''', ''' + '6A2D4C87-799B-4898-AC13-A62B04BB6BCC' + ''' )
    and (SUBSTRING(s.leadtyp,4,1) = ''3'' or SUBSTRING(s.leadtyp,4,1) = ''4'' )
    order by cl.start '
 
 PRINT @Sql 
 --exec (@Sql)
 exec sp_executesql @Sql,N'@CampaignId NVARCHAR(4000)', @CampaignId

Montadar

推荐答案

在.ExecuteNonQuery()(在这里，您可以在其中找到)中尝试/捕获代码告诉SQL执行存储过程).然后，您将能够捕获正在抛出的异常，并且可以看到实际的问题，而无需进行猜测.

Put a try/catch in your code, around the .ExecuteNonQuery() (which is where you're telling SQL to execute the Stored Proc). You'll then be able to catch the exception that is being thrown and can see what the actual problem is, no guessing involved.

try
{
    // here's your command.ExecuteNonQuery()
}
catch (Exception ex)
{
    // put a debugging breakpoint here and look at the value of ex.Message, 
    // and, if ex.InnerException isn't null, also look at ex.InnerException.Message
}

如果是大小问题，请使用varchar(max)而不是将其限制为varchar(4000).

If it is a size issue, use varchar(max) instead of limiting it to varchar(4000).

这篇关于System.Data.dll中发生类型为'System.Data.SqlClient.SqlException'的异常，但未在用户代码中处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！