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

Question

I'm trying to insert into a database table from visual studio but I'm getting the same error and I don't know what could it be.

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

This is my code, I made 2 classes, Gateway, Dept and the Form1:

namespace insertar
{
    class Dept
    {   
        public string Dept_No { get; set; }
        public string DNombre { get; set; }
        public string Loc { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace insertar
{
    class Gateway
    {
        public bool Save(Dept dept)
        {
            Form1 form = new Form1();

            string connectionString = @"Data Source=DESKTOP-IE39262;Initial Catalog=Hospital;Integrated Security=True";
            SqlConnection connection = new SqlConnection(connectionString);
            //connection.Open();

            /*string query = @"INSERT INTO Dept VALUES (" + dept.Dept_No + "," + dept.DNombre +
                             "," + dept.Loc + ")";*/

            SqlCommand command = new SqlCommand("INSERT INTO Dept(Dept_No, DNombre, Loc)" + "VALUES (" + dept.Dept_No + "," + dept.DNombre +
                             "," + dept.Loc + ")", connection);
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();

            return true;
        }
    }
}

private void guardarbtn_Click(object sender, EventArgs e)
{
    Dept dept = new Dept();
    dept.Dept_No = dept_no.Text;
    dept.DNombre = dnombre.Text;
    dept.Loc = loc.Text;

    Gateway gateaway = new Gateway(); //class gateway
    gateaway.Save(dept);

    MessageBox.Show("Departamento insertado exitosamente");

    dept_no.Text = "";
    dnombre.Text = "";
    loc.Text = "";
}

Why are you not parametrising your SQL? SQL Injection is far from your friend. — Thom A
– Thom A ♦, Commented Nov 6, 2018 at 16:10
Have you actually checked the database to make sure the colums exist? — mxmissile
– mxmissile, Commented Nov 6, 2018 at 16:12
Your strings are being passed without quotes, causing the server to interpret them as column names. Read up on how to use SqlParameter. — Jeroen Mostert
– Jeroen Mostert, Commented Nov 6, 2018 at 16:13

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

The error caused by your insert value is a string so you need to use ' to contain your value.

But There is a big issue than it is SQL-Injection.

I would suggest you use parameters instead of connected SQL statement string.

make sure your parameter data type size as same as your table schema.

string connectionString = @"Data Source=DESKTOP-IE39262;Initial Catalog=Hospital;Integrated Security=True";
string sqlQuery = "INSERT INTO Dept (Dept_No, DNombre, Loc) VALUES (@Dept_No,@DNombre,@Loc)";
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
    command.Parameters.Add("@Dept_No", SqlDbType.VarChar,100).Value = dept.Dept_No;
    command.Parameters.Add("@DNombre", SqlDbType.VarChar, 100).Value = dept.DNombre;
    command.Parameters.Add("@Loc", SqlDbType.VarChar, 100).Value = dept.Loc;
    connection.Open();
    command.ExecuteNonQuery();
}

NOTE

I would use using statement, because the purpose of Using statement is that when control will reach end of using it will dispose that object of using block and free up memory. its purpose is not only for auto connection close, basically it will dispose of connection object and obviously, connection also closed due to it.

According to MSDN:

As a rule, when you use an IDisposable object, you should declare and instantiate it in a using statement. The using statement calls the Dispose method on the object in the correct way, and (when you use it as shown earlier) it also causes the object itself to go out of scope as soon as Dispose is called. Within the using block, the object is read-only and cannot be modified or reassigned.

The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block; in fact, this is how the using statement is translated by the compiler. The code example earlier expands to the following code at compile time (note the extra curly braces to create the limited scope for the object):

So you can reduce the code connection.Close(); because using will help you do that.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 6, 2018 at 16:22

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

3 Comments

Panagiotis Kanavos Over a year ago

Adding parameters without a type and size is a bad idea as it can lead to wrong types or truncation. That's the reason AddWithValues was deprecated

D-Shih Over a year ago

Thank for your remind I edit the answer as Add with size.

Igor Over a year ago

You can "stack" using statements so there is less indentation making code easier to read (IMO). Feel free to change it back (roll back the edit) if you do not agree. Also I am a fan of opening the connection as late as possible (right before execution). In this case it should make 0 difference but if parameters are calculated and that calculation is CPU intensive or "slower" for whatever reason it results in a DB connection being open for slightly less time.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related