错误“字符串或二进制数据将被截断” [英] Error "string or binary data would be truncated"

问题描述

我在尝试在SQL Server数据库中插入数据时收到错误字符串或二进制数据将被截断。我想是的，可能是因为图像长度。



数据库

I am getting error String or binary data would be truncated when trying to insert data in SQL Server Database. What I think is, it might be because of the image length.

Database

CREATE TABLE [dbo].[tblImg](
	[ImgID] [nvarchar](50) NULL,
	[Img_UserID] [int] NOT NULL,
	[ImgName] [nvarchar](50) NULL,
	[img] [varbinary](max) NULL,
	[img_desc] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

我的尝试：



以下是我目前用于将数据和图像插入数据库的编码：

What I have tried:

Here is the coding that I currently use to insert data and image into database :

Private Sub getImg()
    Dim len As Integer = selectedFile.PostedFile.ContentLength
    Dim pic As Byte() = New Byte(len - 1) {}
    selectedFile.PostedFile.InputStream.Read(pic, 0, len)

    Dim fileName As String = selectedFile.PostedFile.FileName

    Using lCnn As New SqlConnection(gsConnString)
        lCnn.Open()
        Using lCmd As New SqlCommand
            Dim lsCmd As String
            lCmd.Connection = lCnn
            lsCmd = "INSERT INTO tblImg"
            lsCmd &= " (ImgID, Img_UserID, ImgName, img, img_desc)"
            lsCmd &= " VALUES(" & SQLQUOTE(gsImgID)
            lsCmd &= ", " & SQLQUOTE(gsUserID)
            lsCmd &= ", @imgName, @img, " & SQLQUOTE(Trim(imgDescTxt.Text)) & ")"
			
			lCmd.Parameters.AddWithValue("@imgName", fileName)
            lCmd.Parameters.AddWithValue("@img", pic)
            lCmd.CommandText = lsCmd
            lCmd.ExecuteNonQuery()
        End Using
        lCnn.Close()
    End Using
End Sub

有人请帮我解决这个错误。谢谢。

Somebody please help me with this error. Thank you.

推荐答案

几件事情。首先，这不是你要问的问题，不要使用字符串连接来构建你的查询字符串 - 改为使用参数，而不仅仅是某些列。



关于您的问题，您需要检查数据库中列的最大长度以及您尝试保存的数据大小。一个或多个数据项大于您尝试存储它的字段，但不知道表格结构或您正在使用的数据我们无法告诉您哪个。

Couple of things. First, and it's not the problem that you're asking about, don't use string concatenation to build your query string - use parameters instead, consistently and not just for some columns.

As to your question, you'll need to check the max length of the columns in your database, and the size of the data your trying to save. One or more of the data items is bigger than the field you're trying to store it in, but without knowing the table structure or the data you're using we can't tell you which.

这篇关于错误“字符串或二进制数据将被截断”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！