InvalidCastException的 - 传递null值的存储过程 [英] InvalidCastException - passing null values to stored procedure

问题描述

我没有编程的很多经验，所以我不知道什么是我的错。
我工作的一个网站，在那里你可以搜索不同的值动态搜索。

i've not that much experience in programming, so I've no idea what is my fault. I'm working on a dynamic search for a website, where you can search for different values.

这是我的方法：

ALTER PROCEDURE [dbo].[Support_for_Search]
@ID int, 
@Benutzername nvarchar(100),
@DatumEingang date, 
@Eskalation date,
@Prio tinyint,
@namePrefix nvarchar(100)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT S.[ID]
  ,S.[Problembeschreibung]
  ,S.[Wunschtermin]
  ,S.[Projektkostenstelle]
  ,S.[Status]
  ,S.[BearbeiterID]
  ,S.[DatumEingang]
  ,S.[DatumAbschluss]
  ,S.[Priorität]
  ,S.[Eskalation]
  ,S.[Nutzerreaktion]
  ,S.[AntragstellerID]
  ,S.[Pcname]
  ,S.[Benutzername]
  ,S.[Bemerkungen] 
  ,B.Vorname + ' ' + B.Nachname AS AntragstellerName
  ,BA.Vorname + ' ' + BA.Nachname AS BearbeiterName
FROM [dbiSupportsystem].[dbo].[Support] S 
Left JOIN dbo.Benutzer B ON B.BenutzerNr = S.AntragstellerID
Left JOIN dbo.Benutzer BA ON BA.BenutzerNr = S.BearbeiterID
Where (@ID IS NULL OR ID = @ID)
AND 
(@Benutzername IS NULL OR Benutzername LIKE '%' + @Benutzername + '%')
AND
(@DatumEingang IS NULL OR DatumEingang = @DatumEingang)
AND 
(@Eskalation IS NULL OR Eskalation = @Eskalation)
AND
(@Prio IS NULL OR Priorität = @Prio)
AND 
(@namePrefix IS NULL OR B.Nachname LIKE '%' + @namePrefix + '%') 
END

用户可以搜索什么都不要，所以它应该有可能通过空值。当我测试在SQL Management Studio中的程序，甚至当我只传递空值正常工作。

The user could search for everything or nothing, so it should be possible to pass null values. When I test the procedure in the sql management studio, it works fine even when i pass only null values.

的结果将被绑定到一个GridView。
这里是我的背后可能：

The results will be bind to a GridView. Here is my could behind:

using (DataClassesDataContext context = new DataClassesDataContext())
        {
            SearchDG.DataSource = context.Support_for_Search(null, null,null, null,null, null);  
            SearchDG.DataBind(); 
        }

我传递空值仅用于调试，但我得到的DataBind一个InvalidCastException。

I'm passing the null values only for debugging, but i get an InvalidCastException on DataBind.

有些帮助将是非常大的。

Some help would be very great.

我希望你明白了一切，我的英语并不好。

I hope you understand everything, my english isn't that good.

贾斯汀

编辑：

我测试过的另一种方式：

I've tested another way:

Int32? supportnummer;
        int parseSupportnummer;

        bool supportnummerIsInt = Int32.TryParse(txtSnummer.Text, out parseSupportnummer);
        if (supportnummerIsInt)
        { supportnummer = parseSupportnummer; }
        else
        { supportnummer = null; }  

DateTime? datumEingang;
DateTime parseEingang; 

        bool eingangIsDate = DateTime.TryParse(txtDatumEingang.Value, out parseEingang);
        if (eingangIsDate)
            { datumEingang = parseEingang; }
        else 
            { datumEingang = null; }        

我已为我想通过每个参数可为空的变量。
我检查，如果文本框为空。如果是然后我将null分配给变量。

I've made a nullable variable for every parameter that i want to pass. I check if the textbox is empty. If it is then i assign null to the variable.

在结束我打电话与这些参数的过程：

At the end I'm calling the procedure with these parameters:

SearchDG.DataSource = context.Support_for_Search(supportnummer, benutzername, datumEingang, datumEskalation, prio, userNachname);

不过，我已经得到了同样的InvalidCastException的。

But I have gotten the same InvalidCastException.

我没有提到它，该职位不要太复杂了。

I did not mention it , that the post not get too complicated.

推荐答案

在 InvalidCastException的当施加显式强制转换发生。但类型不是在类型层次结构的相同的路径。演员不会成功。

The InvalidCastException occurs when an explicit cast is applied. But the type is not in the same path of the type hierarchy. The cast does not succeed.

在您的code你逝去的一切都为空。但 INT TINYINT 和日期不能为空。这就是为什么它抛出异常

In your code you are passing everything as null. But int, tinyint and date cannot be null. That is why it is throwing exception

另外，你可以让你的变量为空的通过增加一个在C＃code？喜欢这个：
诠释？ VARIABLENAME

Alternatively you can make your variables nullable in c# code by adding a ? like this: int? variableName

查看此链接，就如何使变量的更多详细信息为空。

Check out this link for more details on how to make variables nullable.

这篇关于InvalidCastException的 - 传递null值的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！