Sql标识列(ID)在插入时不增加

本文关键字:插入 增加 标识 ID Sql | 更新日期: 2023-09-27 18:09:21

我在现有的SQL数据库中创建了一个新表来保存上传的文件。插入的每一行的ID都是0。它不是递增的。我做错了什么?更新:顺便说一句,我在发布之前做了研究。我搜索了SO(在这里)和谷歌。我没有找到任何解释或解决我的问题,我不想把我的帖子弄乱(现在我有):(.

表结构是这样的:

Table: tblFileUploads
Columns: 
  ID int not null (PK, IDENTITY 1,1)
  Name nvarchar (100)
  ContentType varchar(50)
  Size bigint
  Data varbinary(max)
  FirmID int

存储过程(根据收到的评论更新—仍然没有设置ID)

ALTER PROCEDURE [dbo].[InsertUploadedFiles] 
    -- Add the parameters for the stored procedure here
    @Name nvarchar(100),
    @ContentType varchar(50),
    @Size bigint,
    @Data varbinary(max),
    @FirmId int 
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.tblFileUploads (Name, ContentType, Size, Data, FirmID)
    VALUES (@Name, @ContentType, @Size, @Data, @FirmId)
END
sql:
public void InsertFile ( string filename, string contentType, int size, byte[]data)
    {
        //create new sqlconnection
        using (SqlConnection Sqlcon = new SqlConnection(GetConnectionString()))
        {
            using (SqlCommand cmdInsertFile  = new SqlCommand())
            {
                try
                {
                    //firm exists  
                    if (HttpContext.Current.Session["ActiveFirmId"] != null)
                    {
                        OpenConnection(Sqlcon);
                        //Sqlcon.Open();
                        //Insert tblFileUploads
                        cmdInsertFile.Connection = Sqlcon;
                        cmdInsertFile.CommandTimeout = 0;
                        cmdInsertFile.CommandType = CommandType.StoredProcedure;
                        cmdInsertFile.CommandText = "InsertUploadedFiles";                     
                        //Filename
                        cmdInsertFile.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar,100));
                        cmdInsertFile.Parameters["@Name"].Value = filename;
                        //Content Type
                        cmdInsertFile.Parameters.Add(new SqlParameter("@ContentType", SqlDbType.VarChar,50));
                        cmdInsertFile.Parameters["@ContentType"].Value = contentType;
                        //Size
                        cmdInsertFile.Parameters.Add(new SqlParameter("@Size", SqlDbType.Int));
                        cmdInsertFile.Parameters["@Size"].Value = size;
                        //Data
                        cmdInsertFile.Parameters.Add(new SqlParameter("@Data", SqlDbType.VarBinary));
                        cmdInsertFile.Parameters["@Data"].Value = data;//DBNull.value;
                        //FirmId
                        cmdInsertFile.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FirmId", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "FirmId", System.Data.DataRowVersion.Original, null));
                        cmdInsertFile.Parameters["@FirmId"].Value = HttpContext.Current.Session["ActiveFirmId"].ToString();
                        cmdInsertFile.ExecuteNonQuery();
                        Sqlcon.Close();
                    }
                    //new firm
                    else
                    {
                        Label lblWarning = new Label();
                        lblWarning.Text = "Please Save new firm before you may upload their application documents.";
                    }
                }//end try
              catch (SqlException sqlEx)
                {
                    WriteToEventLog("SqlException Message: " + sqlEx.Message + ", " + sqlEx.Source + ", " + sqlEx.LineNumber);
                }//end of catch
            }//end of 2nd using
        }//end of 1st using
    }//end of method

UPDATE:获取上载按钮点击值的代码。我认为这与我的SQL问题无关,因为数据正在被捕获和插入,这是不工作的身份列。

   protected void btnUploadFile_Click(object sender, EventArgs e)
    {
        //check file extension
        Boolean fileOK = false;
        if (FileUpload1.HasFile)
        {            
                //Verify that file to upload is PDF format
                string fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                string[] allowedExtensions = {".pdf"};
                for (int i = 0; i < allowedExtensions.Length; i++)
                {
                    if (fileExtension == allowedExtensions[i])
                    {
                        fileOK = true;
                    }
                    else
                    {
                    Label1.Text = "Cannot accept files of this type. Please scan document to PDF and try upload again.";
                    }
                }
            }//end if (hasfile)
        else
        {
            Label1.Text = "You have not specified a file.";
        } //end else
        if (fileOK)
        {
            try
            {
                HttpFileCollection files = Request.Files;
                foreach (string fileTagName in files)
                {
                    HttpPostedFile file = Request.Files[fileTagName];
                    if (file.ContentLength > 0)
                    {
                        int size = file.ContentLength;
                        string name = file.FileName;
                        int position = name.LastIndexOf("''");
                        name = name.Substring(position + 1);
                        string contentType = file.ContentType;
                        byte[] fileData = new byte[size];
                        file.InputStream.Read(fileData, 0, size);
//pass to database
                        SqlConnLib inserter = new SqlConnLib();
                        inserter.InsertFile(name, contentType, size, fileData);
                    }
                }
            }//try
            catch (Exception ex)
            {
                //Display failure message
                Label1.Text = "ERROR Message: " + ex.Message.ToString() +
                    ", <br />Source:  " + ex.Source +
                    ", <br />TargetSite: " + ex.TargetSite +
                    ", <br />StackTrace: " + ex.StackTrace;
            } //end catch 
        }//if
        }//end method

Sql标识列(ID)在插入时不增加

您的表没有使用标识字段作为ID,或者您的数据没有被插入(听起来不像这种情况)

您应该展开表目录,并展开所需表的'keys'。你应该能看到你的身份栏。双击键,转到列属性,它应该是允许null = no,默认值= blank,类型= int,身份规范是,(在身份规范下)是身份= yes,身份增量= 1,身份种子= 1

如果这些语句不为真,说明您没有正确创建标识,您应该重新创建表。

create table database.dbo.demo
(
    id int not null identity(1, 1)
, name nvarchar(100)
, contenttype varchar(50)
, size bigint
, data varbinary(max)
, firmid int
)
insert into database.dbo.demo (name, contenttype, size, data, firmid)
select name, contenttype, size, data, firmid from database.dbo.oldtable

我认为在存储过程中进行匹配字段将得到解决。

ALTER PROCEDURE [dbo].[InsertUploadedFiles] 
    @Name nvarchar(100),
    @ContentType varchar(50),
    @Size bigint,
    @Data varbinary,
    @FirmId int 
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO tblFileUploads (Name,ContentType,Size,Data,FirmId)
    VALUES (@Name, @ContentType, @Size, @Data, @FirmId)
END