SQL Server Invalid column name exception

Question

i'm getting exceptional error 'invalid column name'

but if use integer while inserting it is accepting.

please help i'm new to vb .net

Here Is the code

Imports System
Imports System.Data
Imports System.Data.SqlClient


Public Class Student
    Dim cs As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Example\Student.mdf;Integrated Security=True;User Instance=True")
    Dim cmd As New SqlCommand
    Dim dr As SqlDataReader
    Private Sub Student_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'StudentDataSet1.Student' table. You can move, or remove it, as needed.
        Me.StudentTableAdapter1.Fill(Me.StudentDataSet1.Student)
        'TODO: This line of code loads data into the 'StudentDataSet.Student' table. You can move, or remove it, as needed.
        Me.StudentTableAdapter.Fill(Me.StudentDataSet.Student)
        cmd.Connection = cs
    End Sub

    Private Sub StudentBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles StudentBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.StudentBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.StudentDataSet)
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        StudentBindingSource.AddNew()
        USNTextBox.Focus()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            Me.Validate()
            Me.StudentBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.StudentDataSet)
            MsgBox("1 record is added")
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub
    Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        If USNTextBox.Text <> "" And NameTextBox.Text <> "" And MarksTextBox.Text <> "" Then
            cs.Open()
            cmd.CommandText = "INSERT INTO Student" & "(USN, Name, Marks)" & "VALUES (" & USNTextBox.Text & ", " & NameTextBox.Text & ", " & MarksTextBox.Text & ")"
            cmd.ExecuteNonQuery()
            cs.Close()

            USNTextBox.Text = ""
            NameTextBox.Text = ""
            MarksTextBox.Text = ""
        End If
    End Sub

End Class

Answer 1

You need to wrap text columns in apostrophs. However, you should always use parametrized queries anyway to prevent sql-injection.

So instead of

cmd.CommandText = "INSERT INTO Student" & "(USN, Name, Marks)" & "VALUES (" & USNTextBox.Text & ", " & NameTextBox.Text & ", " & MarksTextBox.Text & ")"

this:

Dim sql = "INSERT INTO Student(USN, Name, Marks)VALUES(@USN, @Name, @Marks)"
Using cs = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Example\Student.mdf;Integrated Security=True;User Instance=True")
    Using cmd = New SqlCommand(sql, cs)
        cmd.Parameters.AddWithValue("@USN", USNTextBox.Text)
        cmd.Parameters.AddWithValue("@Name", NameTextBox.Text)
        cmd.Parameters.AddWithValue("@Marks", Int32.Parse(MarksTextBox.Text))
        cs.Open()
        cmd.ExecuteNonQuery()
    End Using
End Using

(assuming that Marks is an int column, otherwise remove the Int32.Parse)

Answer 2

The parameters answer of Tim is the proper solution

But this is how you would quote Name
If it is char or nchar it needs to be quoted
This opens you up to SQL injection attack

cmd.CommandText = "INSERT INTO Student" & "(USN, Name, Marks)" & "VALUES (" & USNTextBox.Text & ", '" & NameTextBox.Text & "', " & MarksTextBox.Text & ")"