Error when trying to insert a variable from C# into MySQL

Question

Here is my code that I'm using to insert values into my database.

public void callSQL(string partNumber, string total, string numOfPacks, string dunsNumber,   string serialNumber, string laneNumber)

    {

 MySqlConnection myConnection = new MySqlConnection("SERVER=localhost;DATABASE=testing;UID=root;PASSWORD=********;");

        try
        {
            myConnection.Open();
            Console.WriteLine(laneNumber);
            MySqlCommand myCommand = new MySqlCommand("INSERT INTO test (Part_Number, total, number_of_packs, dunsNumber, serialNumber, truck_number) VALUES (" + partNumber +","+total+","+numOfPacks+","+dunsNumber+","+serialNumber+","+laneNumber+")", myConnection);
            myCommand.ExecuteNonQuery();
            Console.Write("Done");
            myConnection.Close();
        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());
        }
    }

Its really weird what happens. The problem I am getting is with the laneNumber variable. It is a string that I read from the console.

 Console.WriteLine("Please enter the date and lane number like so: ddmmyylanenumber.");
        string lanenum = Console.ReadLine();

However it only gets inputted into the database when it is all numbers. The moment there is a character in there, I am not able to input anything into the database. It works when I manually change the variable to a string, but not when I use the variable with characters inside. The error I get is this:

"MySQL.Data.MySqlClient.MySqlExectipn (0x8004005): Unknown column 'whatever_i_entered_into_console' in 'field list'

The line that the compiler complains about is the myCommand.ExectueNonQuery(); line.

Hope this is enough information. Thanks for the help in advance.

EDIT truck_number is a VARCHAR.

Don't include the values directly in your SQL - use parameterized SQL. That's the first thing to fix, and if you're still having problems, edit your question to show it. — Jon Skeet, Jan 31 '14 at 17:01

Sudhakar Tillapudi · Accepted Answer · 2014-01-31T17:16:29.193

from your comments you mentioned that truck_number column is a VARCHAR type.

Problem : You are not enclosing the string value lane_number within a single quotes.

Solution : You need to enclose the string values for VARCHAR types within single quotes to send them properly :

Try This: (I don't Suggest this)

MySqlCommand myCommand = new MySqlCommand("INSERT INTO test (Part_Number, total, number_of_packs, dunsNumber, serialNumber, truck_number) VALUES (" + partNumber +","+total+","+numOfPacks+","+dunsNumber+","+serialNumber+",'"+laneNumber+"')", myConnection);

Suggestion : Your INSERT INTO statement is open to SQL Injection Attacks. so i would suggest you to use Parameterised queries to avoid them.

when you use parameterised queries respective types willbe sent properly,so you don't even need to use single quotes while sending string values.

using Parameterised Queries :

MySqlCommand myCommand = new MySqlCommand("INSERT INTO test (Part_Number, total, number_of_packs, dunsNumber, serialNumber, truck_number) VALUES (@Part_Number,@total,@number_of_packs,@dunsNumber,@serialNumber,@truck_number)", myConnection);
myCommand.Parameters.AddWithValue("@Part_Number",partNumber);
myCommand.Parameters.AddWithValue("@total",total);
myCommand.Parameters.AddWithValue("@number_of_packs",numOfPacks);
myCommand.Parameters.AddWithValue("@dunsNumber",dunsNumber);
myCommand.Parameters.AddWithValue("@serialNumber",serialNumber);
myCommand.Parameters.AddWithValue("@truck_number",laneNumber);
myCommand.ExecuteNonQuery();

It is a varchar, I forgot to mention that. I even changed it in my workbench just to make sure. If I manually enter trucknumber as "123abc" it will work, but not when its a variable. — AlvinJ, Jan 31 '14 at 17:03

score 1 · Answer 2 · answered Jan 31 '14 at 17:07

1

Make sure that columnNames are correct

Try like ths

 MySqlCommand myCommand = new MySqlCommand("INSERT INTO test VALUES('" + partNumber +"','"+total+"','"+numOfPacks+"','"+dunsNumber+"','"+serialNumber+"','"+laneNumber+"')", myConnection);

answered Jan 31 '14 at 17:07

Vignesh Kumar A

27,863
13
63
115

Error when trying to insert a variable from C# into MySQL

2 Answers2