System.Data.SqlClient.SqlException: 'Invalid column name

Question

I'm trying to learn Vb.Net and using sql together and currently I ran into an issue when trying to insert information into my database, it always displays the same error when trying to insert information from a textbox into de DB.

System.Data.SqlClient.SqlException: 'Invalid column name 'nombre'.
Invalid column name 'calle'.
Invalid column name 'colonia'.
Invalid column name 'ciudad'.
Invalid column name 'cp'.
Invalid column name 'Tel1'.
Invalid column name 'Tel2'.
Invalid column name 'RFC'.'

Here is my code

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

Public Class Agregar
    Private myConn As SqlConnection
    Private myCmd As SqlCommand
    Private myReader As SqlDataReader
    Private results As String


    Private Sub Agregar_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        myConn = New SqlConnection("Initial Catalog=Database1;" &
        "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\sergi\source\repos\AppsPadre\AppsPadre\Database1.mdf;Integrated Security=True")

        myConn.Open()


    End Sub



    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Me.Close()
        Inicio.Show()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click


        'Dim connectionString As String = "Data Source= DB\Database1;Initial Catalog=Orders;Integrated Security=True"
        'Using connection As New SqlConnection(connectionString)

        Dim nombre As String = txtNombre.Text
        Dim calle As String = txtCalle.Text
        Dim colonia As String = txtColonia.Text
        Dim ciudad As String = txtCiudad.Text
        Dim cp As String = txtCP.Text
        Dim Tel1 As String = txtTel1.Text
        Dim Tel2 As String = txtTel2.Text
        Dim RFC As String = txtRFC.Text

        If txtNombre.Text.Equals("") Then
            MessageBox.Show("Porfavor escriba un Nombre.")

        ElseIf txtCalle.Text.Equals("") Then
            MessageBox.Show("Porfavor escriba una Calle.")

        ElseIf txtColonia.Text.Equals("") Then
            MessageBox.Show("Porfavor escriba una Colonia.")

        ElseIf txtCiudad.Text.Equals("") Then
            MessageBox.Show("Porfavor escriba una Ciudad.")

        ElseIf txtCP.Text.Equals("") Then
            MessageBox.Show("Porfavor escriba un Codigo Postal.")

        ElseIf txtTel1.Text.Equals("") Then
            MessageBox.Show("Porfavor escriba un Telefono.")

        ElseIf txtRFC.Text.Equals("") Then
            MessageBox.Show("Porfavor escriba un RFC.")

        Else
            /* Dim MyValues As String = String.Format("'{0}', '{1}', '{2}', {3}', '{4}', '{5}', '{6}', '{7}'", txtNombre.Text, txtCalle.Text, txtColonia.Text, txtCiudad.Text, txtCP.Text, txtTel1.Text, txtTel2.Text, txtRFC.Text)*/
            Dim insertCommand As String = "INSERT INTO Remitente([nombre], [calle], [colonia], [ciudad], [cp], [tel_1], [tel_2], [rfc]) VALUES(nombre, calle, colonia, ciudad, cp, Tel1, Tel2, RFC)"
            Dim cmd As New SqlCommand(insertCommand, myConn)
            cmd.ExecuteNonQuery()

        End If
    End Sub
End Class

And here is my Database code/script:

CREATE TABLE [dbo].[Remitente] (
    [Id_remitente] INT           NOT NULL IDENTITY,
    [nombre]       VARCHAR (50)  NOT NULL,
    [calle]        VARCHAR (100) NOT NULL,
    [colonia]      VARCHAR (50)  NOT NULL,
    [ciudad]       VARCHAR (50)  NOT NULL,
    [cp]           SMALLINT      NOT NULL,
    [tel_1]        SMALLINT      NOT NULL,
    [tel_2]        SMALLINT      NULL,
    [rfc]          SMALLINT      NOT NULL,
    CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED ([Id_remitente] ASC)
);

I already look around and can't find a solution to my problem, any help would be appreciated.

The values are not supplied correctly and SQL cannot bind the parameters (with the same names as the table columns) that the code incorrectly believe it is inserting as “values”. Refer to a SQL tutorial on how to use a parameterized query. Also, make sure to post minimal (and valid) code: some essential-to-cause code is commented out..? — user2864740
– user2864740, Commented Jul 18, 2020 at 22:35
⚠️ This (using string interpolation) is a low-quality insecure approach prone to SQL Injection and accidental breakages. See links provided above for how to use parameters. — user2864740
– user2864740, Commented Jul 19, 2020 at 2:52
@user2864740 OP asked whats wrong :-) I give the solution. It says, "I am learning vb". So, let OP learn. One step at the time. — T.S.
– T.S., Commented Jul 19, 2020 at 5:00
@Mary Little Bobby will get the OP, not me. OP is just learning. This is not a production problem. We get him going - good enough — T.S.
– T.S., Commented Jul 19, 2020 at 5:02

allmhuran · Accepted Answer · 2020-07-18 22:56:12Z

-1

In your code you prepare a string called MyValues to use as your values list, but you forgot to actually use it.

You also have all of the values in single quotes inside your values string, but not all of the columns in the table are varchar columns. Four of them are smallint. This won't cause an error, because SQL will automatically try to convert them to smallint, but it's an inaccuracy.

Dim MyValues As String = String.Format(
   "'{0}', '{1}', '{2}', '{3}', {4}, {5}, {6}, {7}", 
   txtNombre.Text, txtCalle.Text, txtColonia.Text, txtCiudad.Text, txtCP.Text, txtTel1.Text, txtTel2.Text, txtRFC.Text)

Dim insertCommand As String = "INSERT INTO Remitente(" & _
   "[nombre], [calle], [colonia], [ciudad], [cp], [tel_1], [tel_2], [rfc]) " & _
   "VALUES(" & MyValues & ")"

answered Jul 18, 2020 at 22:56

allmhuran

4,5091 gold badge12 silver badges31 bronze badges

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

10 Comments

Sergio Flores Over a year ago

Thanks!, I had the string but it gave me errors, so I tried to use diferent methods, after adding the corrections you said, it now gives this error: System.Data.SqlClient.SqlException: 'Incorrect syntax near ','.' . But it doesn't say in which line is the error, I'm kinda lost now.

allmhuran Over a year ago

@SergioFlores Ah, are tel_1 and tel_2 meant to hold telephone numbers? If so they should not be smallint values in the table. Perhaps there is text in the textboxes which messes up the string. Could you add line of code that says MessageBox.Show(insertCommand) just after the Dim insertCommand line, so that we can see the string value that is being constructed? I think we might need to use different SQL column types. Or perhaps I have made a VB error while constructing the string, I haven't used it for 15 years ;)

Sergio Flores Over a year ago

Indeed, they are for telephone numbers, after adding the MessageBox and seeing the information, the error changed, to an int related problem, so I just changed the phone and other to varchar and gave it the maximum of 10 characters, still thanks a lot!

user2864740 Over a year ago

🛑 ⚠️ This is a low-quality insecure approach prone to SQL Injection and accidental breakages. See links provided above for how to use parameters.

user2864740 Over a year ago

I am talking to promoting poor practices. Since the answer did not include such important information, a warning comments it gets. Shame on not clearly providing guidance and working to establish better coding habits.

|

Collectives™ on Stack Overflow

System.Data.SqlClient.SqlException: 'Invalid column name

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related