MSSQL Query Error: "Cannot insert the value NULL into column <X>."

Question

alt text

Following error occurs in /// this coding "

Cannot insert the value NULL into column 'boarding', table 'C:\USERS\VBI SERVER\DOCUMENTS\VISUAL STUDIO 2008\WEBSITES\VOLVO\APP_DATA\ASPNETDB.MDF.dbo.boardingpt'; column does not allow nulls. INSERT fails. The statement has been terminated.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "INSERT INTO boardingpt (service, travelid, bdpt_name, time) VALUES('" & Trim(TextBox3.Text.ToString) & "', '" & Trim(TextBox4.Text.ToString) & "', '" & Trim(TextBox1.Text.ToString) & "','" & Trim(TextBox2.Text.ToString) & "')"
        cmd.ExecuteNonQuery()
        con.Close()

    End Sub

Where do you assign the value to 'boarding' ? it is required. — Adnan
– Adnan, Commented Dec 20, 2010 at 10:44
on a side note - i suggest you read up on sql injection attacks - as your code is extremely vulnerable to that. I tried to find the DROP Freddy Tables cartoon but couldn't find it. dang. — RPM1984
– RPM1984, Commented Dec 20, 2010 at 10:58
Ahh, Bobby Tables - no wonder i couldn't find it. :) stackoverflow.com/questions/3890091/… — RPM1984
– RPM1984, Commented Dec 20, 2010 at 11:04

Adriaan Stander · Accepted Answer · 2010-12-20 10:43:07Z

1

You need to change the Column to an IDENTITY

or under SSMS in the column defintion select

Table Designer -> Identity Specification -> (Is Identity) to true

answered Dec 20, 2010 at 10:43

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Oded Over a year ago

I would normally agree with you, however, it is not clear from the question that the field should be an identity field. Yes, it would resolve the problem, but is it the correct data model?

Adriaan Stander Over a year ago

If you look at the Table design, that would almost be the obvious choice. Or maybe it shoudl be a foreign key then?

Oded Over a year ago

My point is that without knowing more about the field, we can't categorically say it should be an Identity field. And yes, it could very well be a foreign key. There isn't enough information, IMHO.

Adriaan Stander Over a year ago

I thought that maybe the INSERT statement provided seemed like a good point to start from...

RPM1984 Over a year ago

Agree with @astander - it's an INT, and it's a PK. Given also the table is called Boarding, pretty safe bet it should be an identity column. As you say though, can't be 100% sure.

Oded · Accepted Answer · 2010-12-20 10:50:18Z

1

You are not providing a value for the boarding field, which is a NOT NULL field (Allow Nulls is not checked).

Being a NOT NULL field means you have to provide a value for it.

If the field is supposed to be the ID of the rows in the table, you should make it into an IDENTITY field.

See this on MSDN for how to change the identity properties using the Visual Studio Database designer.

edited Dec 20, 2010 at 10:50

answered Dec 20, 2010 at 10:42

Oded

501k102 gold badges900 silver badges1k bronze badges

Comments

Sebastian Piu · Accepted Answer · 2010-12-20 10:43:40Z

0

you did not set the boarding column to be Identity, so is trying to put a null in there (as you haven't provided its value in the insert query)

answered Dec 20, 2010 at 10:43

Sebastian Piu

8,0581 gold badge35 silver badges51 bronze badges

Comments

Vivek Navadia · Accepted Answer · 2010-12-20 17:35:36Z

0

You are trying to insert NULL value for the column which is set to NOT NULL.

Solution

Make boarding column to ALLOW NULL and remove Primary key (because primary key never can be null)

or

Set IDENTITY to YES in your table design mode for boarding column

Hope this helps

Thanks Vivek

answered Dec 20, 2010 at 17:35

Vivek Navadia

5841 gold badge9 silver badges19 bronze badges

Collectives™ on Stack Overflow

MSSQL Query Error: "Cannot insert the value NULL into column <X>."

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related