CODEWITHSUNDEEP
sql-serverwpfvb.net
Sharpmetalobject
Sharpmetalobject

Reputation: 25

Null value error - inserting data into SQl database

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]

Upvotes: 0

Views: 2254

Answers (2)

Carmen
Carmen

Reputation: 181

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.

Upvotes: 2

hardkoded
hardkoded

Reputation: 21597

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()

Upvotes: 0

Related Questions