No Holidays No Holidays - 5 months ago 10
MySQL Question

Getting a "you have an error in your SQL syntax" error[snap included]. What is going wrong with this query?

What I am doing: I am making a Outlook add-in for making firewall policy change request using C# and VisualStudio. A part of the add-in is the user inputting IP addresses.

What I want: Take IP address input from the user and add it to the Mysql DB , and show the added IP's in a DataGridView in the form itself

How it should work: imgur link of the snapshot of the form

What I am getting : imgur link of the error prompt

My Code:

public partial class Form1 : Form
{
static string conString = "Server=localhost;Database=aWork;uid=root;Pwd=password";
MySqlConnection mson = new MySqlConnection(conString);
MySqlCommand mcd;
MySqlDataAdapter adapter;
DataTable dt = new DataTable();

public Form1()
{
InitializeComponent();

//Datagridview properties
dataGridView1.ColumnCount = 8;
dataGridView1.Columns[0].Name = "ID" ;
dataGridView1.Columns[1].Name = "Source IP" ;
dataGridView1.Columns[2].Name = "Source Misc. IP" ;
dataGridView1.Columns[3].Name = "Destination IP";
dataGridView1.Columns[4].Name = "Misc. Destination IP";
dataGridView1.Columns[5].Name = "Application" ;
dataGridView1.Columns[6].Name = "Protocol";
dataGridView1.Columns[7].Name = "Port" ;

dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;

//Selection Mode
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.MultiSelect = false;





}


//INSERT INTO DB
private void add(string source_IP, string source_Misc_IP, string Dest_IP, string Misc_Dest_IP, string app, string Protocol , string port)
{
//SQL STMT
string sql = "INSERT INTO table1(Source IP,Source Misc. IP,Destination IP,Misc. Destination IP,Application,Protocol,Port) VALUES ('" + ipAddressControl1.ToString() + "','" + ipAddressControl2.ToString() + "','" + ipAddressControl3.ToString() + "','" + ipAddressControl4.ToString() + "','" + ipAddressControl5.ToString() + "','" + ipAddressControl6.ToString() + "','" + ipAddressControl7.ToString() + "')";
mcd = new MySqlCommand(sql, mson);

//OPEN MSON[CON] AND EXEC INSERT
try
{
mson.Open();
if (mcd.ExecuteNonQuery() > 0)
{
MessageBox.Show("Inserted Successfully ");
}
mson.Close();

retrieve();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
mson.Close();

}
} private void btn_add_Click(object sender, EventArgs e) //BUTTON FOR ADDING TO THE DB
{
add(ipAddressControl1.ToString(), ipAddressControl2.ToString(), ipAddressControl3.ToString(), ipAddressControl4.ToString(), ipAddressControl5.ToString(), ipAddressControl6.ToString(), ipAddressControl7.ToString());
}enter code here


My field is primarily Android Dev and so I am not familiar with making sql connections , so I apologize in advance for my lack of understanding

I have to furthermore add buttons to edit and delete this data in the datagridview[for which I have already written the code,which is not copy-pasted above]

I have no one to help me out so any help would be highly appreciated!

Thank You!

Answer

I have solved this out with the help of @Damien_The_Unbeliever and @Marc Gravell♦ I am posting this answer so that it may help someone in the future

First of all , in my above code I have made the horrible mistake here:

string sql = "INSERT INTO table1(Source IP,Source Misc. IP,Destination IP,Misc. Destination IP,Application,Protocol,Port) VALUES ('" + ipAddressControl1.ToString() + "','" + ipAddressControl2.ToString() + "','" + ipAddressControl3.ToString() + "','" + ipAddressControl4.ToString() + "','" + ipAddressControl5.ToString() + "','" + ipAddressControl6.ToString() + "','" + ipAddressControl7.ToString() + "')";

Instead , it should be something like :

"string sql = "INSERT INTO table1(SourceIP,SourceMiscIP,DestinationIP,MiscDestinationIP,Application,Protocol,Port) VALUES ('" + source_IP + "','" + source_Misc_IP + "','" + Dest_IP + "','" + Misc_Dest_IP + "','" + app + "','" + Protocol + "','" + port + "')";

then , ofc I made the noob mistake of using spaces and periods , because of which I had to edit not only my code , but the computer generated .cs files

[had to search through and edit >1700 lines of code! Though ctrl+f helped a lot...]

It was very tedious , but I learned my lessons

Thank you to all for helping me out :)

Have a great day ahead!