string or binary data would be truncated on gridview

Question

Return the table in database consist of

project_id
task_name
start
complete
location

somehow i wanna insert data from the gridview into sql. please check the code

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim SQLCmd As New SqlCommand
        SQLCmd.Connection = SQLCon

        SQLCon.open()

        ''insert data to sql database row by row
        Dim taskname, location, start, complete As String

        For i As Integer = 0 To Me.DataGridView1.Rows.Count
            taskname = Me.DataGridView1.Rows(i).Cells(0).ToString()
            start = Me.DataGridView1.Rows(i).Cells(1).ToString()
            complete = Me.DataGridView1.Rows(i).Cells(2).ToString()
            location = Me.DataGridView1.Rows(i).Cells(3).ToString()

            SQLCmd.CommandText = "insert into timeline ( project_id , taskname , start , complete , location) values (@pid,@task,@start,@complete,@location)"
            SQLCmd.Parameters.AddWithValue("@pid", TextBox1.Text)
            SQLCmd.Parameters.AddWithValue("@task", taskname)
            SQLCmd.Parameters.AddWithValue("@start", start)
            SQLCmd.Parameters.AddWithValue("@complete", complete)
            SQLCmd.Parameters.AddWithValue("@location", location)
            SQLCmd.ExecuteNonQuery()
        Next

        SQLCon.Close()
    End Sub

score 0 · Answer 1 · answered Nov 13 '14 at 05:00

This Error indicates that your one of the fields DATATYPE length is small & you are try to insert more then the capacity of that DATATYPE length.

For Example :
project_id is Declared as INT and you are passing suppose 12312312313 value which is greater then the limit of INT datatype.

So, Please Debug your code n check that what is the datatype of all the coulmns & according to that datatype are you passing the data correctly.

Example :

DECLARE @UserDetails TABLE(UserId INT, UserName VARCHAR(25),Designation VARCHAR(10))
INSERT INTO @UserDetails(UserId,UserName,Designation)
VALUES(1,'Hardik Parmar','Senior Software Engineer')
SELECT * FROM @UserDetails

This example wiil throw the same error as yours Coz

For

Designation

coulmn we have given VARCHAR(10) this much lenght & we are trying to insert around VARCHAR(25) but the limit of the Designation coulmn is VARCHAR(10) then it will give you error as

string or binary data would be truncated on gridview

how about. all of my column i used nvarchar(50) ? why the error is still occur? — Adam_Code, Nov 13 '14 at 05:11
can you please show me what are the datatype n what are the values that you are passing to the query — Hardik Parmar, Nov 13 '14 at 05:12

string or binary data would be truncated on gridview

1 Answers1