0

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)
philipxy
  • 14,867
  • 6
  • 39
  • 83
June
  • 9
  • 2
  • 1
    Please re-read the [mre] guidance on posting code - currently the post contains way too much code unrelated to the error. Also posting code that includes SQL injection is a bad idea if you need answers to some other problem. For now I closed the post as duplicate of SQL injection fix - please follow that and [edit] the question to remove that part so it can be potentially re-opened. Also consider practicing from console application as at very least call stack would not be so deep and you have less other issues to worry about like control layout/behavior. – Alexei Levenkov Mar 13 '23 at 17:38
  • 1
    What you posted won't even compile. There's no comma between the query string and `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 a `using` block and *then* create the command with `new MySqlCommand(sql, connection)`. – Panagiotis Kanavos Mar 13 '23 at 17:52
  • 2
    June - you really should really read the [mre] guidance - editing code to have different errors (like "... aINNER JOIN...") is not the direction toward *reproducible* example. – Alexei Levenkov Mar 13 '23 at 18:00
  • Ask re 1 bad query/function at a time via a [mre]. MySQL<>MariaDB. Debug 1 software level at a time.--Please construct an SQL-only question. Please avoid social & meta comentary in posts. [ask] [Help] [Tips for asking a good SQL question](https://meta.stackoverflow.com/q/271055/3404097) – philipxy Mar 13 '23 at 23:38
  • Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Mar 13 '23 at 23:41

0 Answers0