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

Try and display the SQL command you are about to execute and see where that is wrong. You can prevent that problem (and more!) by using Parameters. — Hans Keﬆing, Dec 11 '13 at 15:50

score 4 · Answer 1 · answered Dec 11 '13 at 15:50

4

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)

answered Dec 11 '13 at 15:50

Tim Schmelter

450,073
74
686
939

Now its working with Gridview. But its not storing in database – Rohit Dec 11 '13 at 16:02
@user3091691: What does that mean? What's working in the `GridView` and what's not working in the database? Do you get an exception? – Tim Schmelter Dec 11 '13 at 16:03
@ Tim : There are no exceptions. When insert is executed values are stored and seen on grid view.But its not storing in backend database. – Rohit Dec 11 '13 at 16:07
@user3091691: Have you used the debugger to see that the `cmd.ExecuteNonQuery()` gets executed? Note that it also returs an `Int32` which indicates how many records were affected(are inserted). – Tim Schmelter Dec 11 '13 at 16:10
@ Tim: Yes I have used to debugger to see that the cmd.ExecuteNonQuiry() Gets Executed. – Rohit Dec 11 '13 at 16:12
@user3091691: Then i have no idea what's going on. It's strange why your `GridView` gets updated from the database with the new record but the database does not contain this row. I also don't understand why you use the `TableAdapterManager` if you use a single `SqlCommand` to insert the row anyway. Normally you would either use this command only or the `TableAdapter` with this command as `InsertCommand`. But then you need to add the `DataRow` to the appropriate `DataTable` in the `DataSet` first. – Tim Schmelter Dec 11 '13 at 16:16

score 0 · Answer 2 · answered Dec 11 '13 at 15:57

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 & ")"

SQL Server Invalid column name exception

2 Answers2