I'm creating a school library management system. I created a module that retrieves and shows all the user's borrowed book from the school library (database). I have tables users, library, and user_borrow. User_borrow connects my users and books table.
I get this error:
MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'b.borrower ='1'' at line 1'
db.openConnection();
command = new MySqlCommand("SELECT a.img_drt, a.title, a.author," +
"a.genre, a.publisher, a.yearpub, a.isbn, b.borrow_date FROM library a" +
"INNER JOIN user_borrow b ON a.id = b.book WHERE b.borrower ='"+ login_user.id +"'", " +
"db.getConnection()) ;
reader = command.ExecuteReader();
while (reader.Read())
{
long len = reader.GetBytes(0, 0, null, 0, 0);
byte[] array = new byte[Convert.ToInt32(len) + 1];
reader.GetBytes(0, 0, array, 0, Convert.ToInt32(len));
pictureBox = new PictureBox();
pictureBox.Width = 209;
pictureBox.Height = 317;
pictureBox.Location = new Point(12, 14);
pictureBox.BackgroundImageLayout = ImageLayout.Stretch;
System.IO.MemoryStream ms = new System.IO.MemoryStream(array);
pictureBox.BackgroundImage = Image.FromStream(ms, true, true);
panel = new Panel();
panel.Location = new Point(25, 29);
panel.Width = 551;
panel.Height = 346;
panel.Margin = new Padding(25, 29, 70, 56);
panel.BackColor = Color.FromArgb(0, 166, 251);
title = new Label();
title.Text = reader["title"].ToString().ToUpper();
title.AutoSize = true;
title.MaximumSize = new Size(267, 87);
title.Font = new Font("Inter Black", 11);
title.ForeColor = Color.Black;
title.Location = new Point(254, 14);
author = new Label();
author.Text = "BY: " + reader["author"].ToString().ToUpper();
author.AutoSize = true;
author.MaximumSize = new Size(267, 45);
author.Font = new Font("Inter SemiBold", 9);
author.ForeColor = Color.Black;
author.Location = new Point(254, 119);
genre = new Label();
genre.Text = "GENRE: " + reader["genre"].ToString().ToUpper();
genre.Size = new Size(284, 72);
genre.Font = new Font("Inter SemiBold", 9);
genre.ForeColor = Color.Black;
genre.Location = new Point(254, 165);
published = new Label();
published.Text = "YEAR PUBLISHED: " + reader["yearpub"].ToString().ToUpper();
published.Size = new Size(284, 24);
published.Font = new Font("Inter SemiBold", 9);
published.ForeColor = Color.Black;
published.Location = new Point(254, 239);
panel.Controls.Add(pictureBox);
panel.Controls.Add(title);
panel.Controls.Add(author);
panel.Controls.Add(genre);
panel.Controls.Add(published);
flowLayout.Controls.Add(panel);
}
reader.Close();
db.closeConnection();
Now I get:
MySql.Data.MySqlClient.MySqlException
HResult=0x80004005
Message=You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'b.borrower ='1'' at line 1
Source=MySql.Data
StackTrace:
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at Library_Management_System.book_request.loadRecord() in D:\Users\user\Documents\College\3rd year\2nd Sem\SE\Library Management System\book_request.cs:line 49
at Library_Management_System.book_request.book_request_Load(Object sender, EventArgs e) in D:\Users\user\Documents\College\3rd year\2nd Sem\SE\Library Management System\book_request.cs:line 37
at System.Windows.Forms.UserControl.OnLoad(EventArgs e)
at System.Windows.Forms.UserControl.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, WM msg, IntPtr wparam, IntPtr lparam)
getConnection(). Even if you add a comma, this will just leak connections. Don't try to put everything into a single line or expression. It doesn't help in anything but does cause a lot of bugs. Put the SQL string into a separate variable, create the connection properly in ausingblock and then create the command withnew MySqlCommand(sql, connection).