Click here to Skip to main content
15,888,113 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
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
SQL
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 :
VB
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.
Posted
Updated 22-Apr-16 7:52am
v2
Comments
Ben J. Boyle 22-Apr-16 13:25pm    
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.
Sascha Lefèvre 22-Apr-16 13:29pm    
You could post this as a solution :)
Ben J. Boyle 22-Apr-16 13:30pm    
Bit wary of doing that with something that's more or less just informational - been jumped on for doing it in the past.
Sascha Lefèvre 22-Apr-16 13:34pm    
In this case it's perfectly ok from my point of view - he now knows everything required to solve this problem.
dell-gl62m 22-Apr-16 13:54pm    
I've been updated my question with database

1 solution

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.
 
Share this answer
 
v2
Comments
[no name] 22-Apr-16 13:30pm    
A 5 for your help.
dell-gl62m 22-Apr-16 13:34pm    
For the image datatype I used varbinary(max) and the rest datatype I used nvarchar(50). Before this I am able to store all data inside my database by using the same coding. This error just appear just now.
Sascha Lefèvre 22-Apr-16 13:35pm    
It also applies to text-columns - in this case I see no other explanation than that you're attempting to store a string with a length > 50 characters.
dell-gl62m 22-Apr-16 13:37pm    
My other page is working fine with the text with datatype nvarchar(50). So I guess it's only because of the Image. But it should be okay when the datatype is varbinary(max). Aren't it?
Sascha Lefèvre 22-Apr-16 13:40pm    
Don't guess :) Either run your application in debug mode and inspect the lengths of the strings or log the string/lengths to some output.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900