我在现有的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
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
您的表没有使用标识字段作为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