How to insert an integer into a database through command prompt

Question

I am trying to insert a integer into a database in C# using the code below, but everytime I run the compiler informs me that my integer is not a valid column "Invalid Column Name UserID"

Does anyone have any insight on this? Thanks.

Console.WriteLine("Please enter a new User Id");
        string line = Console.ReadLine();
        int UserID;
        if (int.TryParse(line, out UserID))
        {
            Console.WriteLine(UserID);
            Console.ReadLine();
        }



        //Prepare the command string
        string insertString = @"INSERT INTO tb_User(ID,f_Name, l_Name) VALUES (UserID,'Ted','Turner')";

Matthew Abbott · Accepted Answer · 2011-01-10 18:58:35Z

3

First things first, I would get into the habit of using parameterised queries, if you are not planning to use stored procedures. In your example, I would:

using (var command = new SqlCommand("INSERT INTO tb_User(ID, f_Name, l_Name) VALUES (@id, @forename, @surname)", conn))
{
  command.Parameters.AddWithValue("id", id);
  command.Parameters.AddWithValue("forename", forename);
  command.Parameters.AddWithValue("surname", surname);

  command.ExecuteNonQuery();
}

Where id, forename, surname are the appropriate variables. Notice I am also using using blocks, this ensures that my objects are cleaned up after it has completed.

answered Jan 10, 2011 at 18:58

Matthew Abbott

61.7k9 gold badges108 silver badges131 bronze badges

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

Comments

Steve E · Accepted Answer · 2011-01-10 18:55:06Z

0

it is because the 'UserID' within your insertString : ..."VALUES (UserID"... is invalid.

you need to pass a value for the UserID such as: ..."VALUES ('myUserIDGoesHere'"...

answered Jan 10, 2011 at 18:55

Steve E

134 bronze badges

Comments

Steve Danner · Accepted Answer · 2011-01-10 18:55:29Z

0

Your string is not dynamically reading the variables. Use something like this:

string insertString = string.Format(@"INSERT INTO tb_User(ID,f_Name, l_Name) VALUES ({0},'{1}','{2}')", UserId, "Ted", "Turner");

There are better ways depending on what kind of data access you're using, but this is just to make the point of how to correct the string.

answered Jan 10, 2011 at 18:55

Steve Danner

22.2k7 gold badges45 silver badges54 bronze badges

1 Comment

Marc Gravell Over a year ago

Concatenating user input is very bad advice.

Marc Gravell · Accepted Answer · 2011-01-10 18:56:31Z

0

The problem is the first argument in VALUES - it simply isn't defined. If this is meant to be the value the user has entered, then you need to add a parameter to the command and use that parameter in the SQL; for example:

cmd.Parameters.AddWithValue("@id", UserID);

An then use

VALUES(@id, ...

in the TSQL.

Also, generally you might want to have the system generate the unique id. A the simplest level this could have an IDENTITY defined (an automatic sequence).

answered Jan 10, 2011 at 18:56

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

Phil Hunt · Accepted Answer · 2011-01-10 18:58:29Z

0

Use a parameterized query:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (var insertCommand = new SqlCommand(
        @"INSERT INTO tb_User (ID, f_Name, l_Name)
          VALUES (@ID, 'Ted', 'Turner')", connection))
    {
        insertCommand.Parameters.AddWithValue("@ID", userID);
        insertCommand.ExecuteNonQuery();
    }
}

answered Jan 10, 2011 at 18:58

Phil Hunt

8,5711 gold badge33 silver badges25 bronze badges

Comments

Ta01 · Accepted Answer · 2011-01-10 18:56:11Z

-2

To answer your question regardless of your approach, try:

string insertString = @"INSERT INTO tb_User(ID,f_Name, l_Name) VALUES ("
+ UserID + ",'Ted','Turner')";

answered Jan 10, 2011 at 18:56

Ta01

31.7k13 gold badges74 silver badges100 bronze badges

2 Comments

Ta01 Over a year ago

I said "regardless of your approach" geesh. Its seems he is just testing out a console app, I doubt this is an enterprise application he is writing for public consumption, yeah lets protect a console app from sql injection.

jpavlov Over a year ago

Thanks for the non-biased answer and I understand what you mean by regardless of approach. I just read that is one way to do it, but it has opportunity for so much risk. But for my small example and no knowledge on how to get it to operate, it is exactly what I was looking for. Thanks again.

Collectives™ on Stack Overflow

How to insert an integer into a database through command prompt

6 Answers 6

Comments

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related