locked
Invalid Column Name RRS feed

  • Question

  • Hi all,

    I am trying to use the following VB code to add a row to a SQL database, but am getting errors.

    Dim connString As String = "Data Source=MYSERVER\MYINSTANCE;Initial Catalog=MYDATABASE;User ID=MYID;Password=MYPASS" Using connection As New SqlConnection(connString) Dim insertStatement As String = "INSERT INTO EmbedPartsExtraction (JobNo, JobName) VALUES (" & strJobNo & "," & "TEST" & ")" Dim insertCommand As New SqlCommand(insertStatement, connection) Try connection.Open() insertCommand.ExecuteNonQuery() connection.Close() Catch ex As SqlException MsgBox("SQL Server error: " & ex.Number & ", " & ex.Message & ", " & ex.GetType.ToString) End Try End Using

    I am using SQL Server 2008 R2 SP2 Express Edition.

    The full error is:

    SQL Server error: 207, Invalid column name 'TEST'.,

    System.Data.SqlClient.SqlException

    Not sure why it is pulling TEST as the column name when that is the value I am trying to add to the JobName column.  I have also tried using string variables (similar to strJobNo) and it sometimes works and sometimes generates the same error.

    I have another database on this same instance that uses similar code with no problems when writing rows to a table.

    Any ideas what I am doing wrong?

    Tuesday, May 14, 2013 7:03 PM

Answers

  • Hi Dull_Blades,

    I think you should add single quotes for the value TEST. You can try:

    Dim insertStatement As String = "INSERT INTO EmbedPartsExtraction (JobNo, JobName) VALUES (" & strJobNo & "," & "'TEST'" & ")"
    

    Best regards,

    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Chester Hong Wednesday, May 29, 2013 9:36 PM
    Thursday, May 16, 2013 8:22 AM
  • In addition to Chester's suggestion I would also recommend using Command Parameters to avoid the syntax issues and limit exposure to SQL injection:

    	Dim insertStatement As String = "INSERT INTO EmbedPartsExtraction (JobNo, JobName) VALUES (@JobNum, @JobName)"
    
    	Dim insertCommand As New SqlCommand(insertStatement, connection)
            Dim insertCommand.Parameters.AddWithValue("@JobNum", strJobNo)
            Dim insertCommand.Parameters.AddWithValue("@JobName", "TEST")


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Chester Hong Wednesday, May 29, 2013 9:36 PM
    Thursday, May 16, 2013 12:53 PM

All replies

  • Hi Dull_Blades,

    I think you should add single quotes for the value TEST. You can try:

    Dim insertStatement As String = "INSERT INTO EmbedPartsExtraction (JobNo, JobName) VALUES (" & strJobNo & "," & "'TEST'" & ")"
    

    Best regards,

    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Chester Hong Wednesday, May 29, 2013 9:36 PM
    Thursday, May 16, 2013 8:22 AM
  • In addition to Chester's suggestion I would also recommend using Command Parameters to avoid the syntax issues and limit exposure to SQL injection:

    	Dim insertStatement As String = "INSERT INTO EmbedPartsExtraction (JobNo, JobName) VALUES (@JobNum, @JobName)"
    
    	Dim insertCommand As New SqlCommand(insertStatement, connection)
            Dim insertCommand.Parameters.AddWithValue("@JobNum", strJobNo)
            Dim insertCommand.Parameters.AddWithValue("@JobName", "TEST")


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Chester Hong Wednesday, May 29, 2013 9:36 PM
    Thursday, May 16, 2013 12:53 PM