Visual Basic connected to sql database. Insert statement gives me an error of "string or binary data would be truncated."

Question

[this is my codes for visual basic]

    Dim Command As New SqlCommand
    Command.Connection = CN
    Dim ms As New MemoryStream
    ConON()

    Command.CommandText = "Insert into tbl_profile(Lastname,Firstname,Gender,Birthdate,ContactNum," & _
                  "Housenumstreet,Brgydistrict,Cityprovince) values ('" & AddProfile.tbxlname.Text & "','" & AddProfile.tbxfname.Text & _
                  "','female','" & AddProfile.tbxbdate.Text & "','" & AddProfile.tbxcontactnum.Text & _
                 "','" & AddProfile.tbxhousenostrt.Text & "','" & AddProfile.tbxbrgy.Text & "','" & AddProfile.tbxcity.Text & "')"

    Command.ExecuteNonQuery()

and my code on sql:

Create table tbl_profile
(
[IdentityID] bigint identity(1,1),
Lastname varchar(max),
Firstname varchar(max),
Gender varchar null,
Birthdate varchar(max) null,
ContactNum varchar(max) null,
Housenumstreet varchar(max) null,
Brgydistrict varchar(max) null,
Cityprovince varchar(max) null,
Picture1 Image,
Picture2 Image null
)

insert statement on sql

please post your code as text instead of image – Imran Ali Feb 01 '17 at 07:59 — Imran Ali, Feb 01 '17 at 07:59

TheDanMan · Answer 1 · 2017-02-01T08:25:39.617

You didn't specify a length for your "Gender" column. By default, a varchar has aprrox a 30 character default length, but if your text has white spaces, it could very well be exceeding that length.

EDIT

Give some sample values that you're trying to insert. Also, does this error only occur when you execute from VB or do you get the error when you insert the SAME values directly via SMSS?

Trying a sample like so, maybe without the images

if not (OBJECT_ID('tempdb..#tbl_profile') is null)
    drop table #tbl_profile
create table #tbl_profile
(
    IdentityID int identity(1,1),
    LastName varchar(max) null,
    FirstName varchar(max) null,
    Gender varchar(50) null,
    Birthdate datetime null,
    ContactNum varchar(max) null,
    Housenumstreet varchar(max) null,
    Brgydistrict varchar(max) null,
    Cityprovince varchar(max) null,
    Picture1 image null,
    Picture2 image null,
    primary key clustered
    (
        IdentityID asc
    )
)

insert into
#tbl_profile
(
    LastName,
    FirstName,
    Gender,
    Birthdate,
    ContactNum,
    Housenumstreet,
    Brgydistrict,
    Cityprovince
)
select
    'Doe',
    'John',
    'Male',
    '2017-02-01',
    '12345678900',
    'Some random street somehwhere out there',
    'District 13',
    'Province of Glory'

select * from #tbl_profile

I already tried to execute it without the gender column but it still didn't work. TT — Smilessss, Feb 01 '17 at 08:13
It's only in my vb. I can insert values just fine on my db query. T_T — Smilessss, Feb 01 '17 at 09:05

Visual Basic connected to sql database. Insert statement gives me an error of "string or binary data would be truncated."

1 Answers1