Null value error - inserting data into SQl database

Question

I'm trying to connect to a SQL Database and read/write data to it. Some context - Visual Studio 2015, Windows 10, Connecting to SQL Server 2005 over VPN, Using ADO.Net 4.1. My code is as follows:

Imports System.Data.SqlClient

Class MainWindow

Dim con As New SqlConnection
Dim cmd As New SqlCommand


Private Sub btn_WriteInt_Click(sender As Object, e As RoutedEventArgs) Handles btn_WriteInt.Click

    Try
        'Establish connection to SQL Database
        con.ConnectionString = "Data Source=serverIP;Initial Catalog=mydatabase;Persist Security Info=True;User ID=user;Password=password"
        con.Open()
        cmd.Connection = con
        'Save data to table
        Dim int_IntTest As Integer = Int(txt_IntTest.Text)
        cmd.CommandText = "INSERT INTO tbl_SQLTest([IntTest]) VALUES('" & txt_IntTest.Text & "')"
        cmd.ExecuteNonQuery()

    Catch ex As Exception
        MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
    Finally
        con.Close()
    End Try

End Sub

Private Sub btn_TestInt_Click(sender As Object, e As RoutedEventArgs) Handles btn_TestInt.Click
    Dim int_IntTest As Integer = Int(txt_IntTest.Text)
    MsgBox(int_IntTest)
End Sub
End Class

The SQL Server table looks like this: tbl_SQLTest

When I run the application, enter "2" into txt_IntTest and click btn_WriteInt, I get the following error:

Error Message]

You aren't inserting any value for that column. Use parameters to avoid sql injection and formatting issues. — LarsTech
– LarsTech, Commented Jun 24, 2017 at 16:34
In your DBMS set a default value for that column. Then when nothing is passed your default would be used... — Trevor
– Trevor, Commented Jun 24, 2017 at 17:28

Carmen · Accepted Answer · 2017-06-24 16:50:49Z

2

When you insert a new row in the table if you don't add a value for 'StringTest' column null is added, so you need to do one of these:

Allow null for this column un your table,
Pass an empty value in your query.
Add a default '' in the StringTest column.

edited Jun 24, 2017 at 16:50

answered Jun 24, 2017 at 16:32

Carmen

1818 bronze badges

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

2 Comments

Sharpmetalobject Over a year ago

ah i see, I didnt add and name a new row. Would you mind giving me the syntax for adding and naming the row in my cmd.CommandText statement?

Carmen Over a year ago

Yes, "INSERT INTO tbl_SQLTest (IntTest, StringTest) VALUES ('yourdata', ' ')";

hardkoded · Accepted Answer · 2017-06-24 16:48:37Z

0

Based on the accepted answer (which I think is the correct one and I upvoted). I recommend you to do something like this:

cmd.CommandText = "INSERT INTO tbl_SQLTest([IntTest], [StringTest) VALUES(@intTest, @stringTest)"

cmd.Parameters.AddWithValue("@intTest", Int(txt_IntTest.Text))
cmd.Parameters.AddWithValue("@stringTest", "")

cmd.ExecuteNonQuery()

answered Jun 24, 2017 at 16:48

hardkoded

22k3 gold badges64 silver badges77 bronze badges

1 Comment

Trevor Over a year ago

Use Add and specify the data type... consequences could occur; for example precision...

Collectives™ on Stack Overflow

Null value error - inserting data into SQl database

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related