无法将对象从DBNull强制转换为其他类型。 [英] Object cannot be cast from DBNull to other types.

问题描述

你好..



我在sql server中尝试自动生成数字..



但是插入过程中出现异常



异常 - 对象无法从DBNull强制转换为其他类型。





plz有人帮我











table-

hello..

am trying for auto generate number in sql server..

but getting exception during insertion

Exception-Object cannot be cast from DBNull to other types.


plz somebody help me





table-

CREATE TABLE [dbo].[Users](
    [UserId] [varchar](50) NOT NULL,
    [UserName] [varchar](50) NULL,
    [MiddleName] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

更改程序[dbo]。[Insert_Autono]

- 添加存储的参数这里的程序



@UserName nvarchar（max），

@MiddleName nvarchar（max）





AS

BEGIN

- 添加SET NOCOUNT ON以防止额外的结果集

- 干扰SELECT语句。

设置NOCOUNT ON;





DECLARE @totalcount INT

DECLARE @count VARCHAR（50）

SELECT @ totalcount =（SELECT COUNT（UserId）FROM Users）/ *这一个用于返回表中的记录总数* /

IF @t otalcount IS NULL / *检查totalcount是否包含值的条件* /

SET @ count ='08U1'+ CONVERT（VARCHAR（50），3000）

ELSE

SET @ count ='08U1'+ CONVERT（VARCHAR（50），3000 +（@ totalcount））





- 在此处插入程序声明

插入用户

（

UserId，

UserName ，

MiddleName

）

值

（

@count，

@UserName，

@MiddleName

）

END

ALTER PROCEDURE [dbo].[Insert_Autono]
-- Add the parameters for the stored procedure here

@UserName nvarchar(max),
@MiddleName nvarchar(max)


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


DECLARE @totalcount INT
DECLARE @count VARCHAR(50)
SELECT @totalcount=(SELECT COUNT(UserId) FROM Users) /*This one is used to return total number of records in table */
IF @totalcount IS NULL /*Condition to check whether totalcount contains value or not*/
SET @count='08U1'+CONVERT(VARCHAR(50),3000)
ELSE
SET @count='08U1'+CONVERT(VARCHAR(50), 3000+(@totalcount))


-- Insert statements for procedure here
Insert into Users
(
UserId,
UserName,
MiddleName
)
values
(
@count,
@UserName,
@MiddleName
)
END

public Int64 InsertAdvertiesment()
    {
        Int64 Result = 0;
        try
        {
            SqlConnection con = new SqlConnection(ConnectionString);

            Cmd = new SqlCommand("Insert_Autono", con);
            Cmd.CommandType = CommandType.StoredProcedure;

            Cmd.Parameters.Add("@UserName", UserName);
            Cmd.Parameters.Add("@MiddleName", MiddleName);


            con.Open();
            Cmd.ExecuteNonQuery();
            Cmd.Dispose();

            Cmd = new SqlCommand("Select @@Identity", con);
            Result = Convert.ToInt32(Cmd.ExecuteScalar());
        }
        catch (Exception ex)
        {

        }
        return Result;
    }

推荐答案

不要这样做。

如果你想要自动编号，然后使用SQL IDENTITY规范和SQL将为你排序。



如果你按照自己的方式手动完成，将来会遇到问题。其中两个是：

1）SQL是一个多用户系统：这意味着两个用户可以同时插入行。您的系统可以轻松地在两个单独的系统上为两行返回相同的值。这导致很难发现间歇性问题和大量头部问题。

2）如果删除用户会怎样？系统中的行数会下降，但用户ID不会重新编号。这意味着下一个请求开始重复值。



有一个系统可以进行自动编号。用它。如果你需要一个包含数据和身份值的id，那么使用计算列，不要试图重新发明轮子！

Don't do it like that.
If you want autonumber, then use SQL IDENTITY specification and SQL will sort it all out for you.

If you do it manually in the way you are, you will get problems in the future. Two of these are:
1) SQL is a multiuser system: which means that two users can insert rows at the same time. Your system could easily return the same value for both rows on two separate systems. This leads to really difficult to spot intermittent problems and a lot of head scratching.
2) What happens if you delete a user? The number of rows in the system drops, but user IDs are not renumbered. Which means the next request starts duplicating values.

There is a system there to do autonumbering already. Use it. If you need an id that contains data and the identity value, then use a computed column, don't try to reinvent the wheel!

这篇关于无法将对象从DBNull强制转换为其他类型。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！